Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Two questions about resource profiles

Re: Two questions about resource profiles

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 15 Mar 2006 11:18:00 +0200
Message-ID: <6e49b6d00603150118j25125186o@mail.gmail.com>


Thanks to all who responded.

Firstly I'd like to clarify a bit more about the "that does NOT mean that every long running query IS "bad." Of course You are right, and I didn't say in my previous mails that resource limits per_call will be used for pure transactional part of this system. There will be another reporting/batch part running under another user without any per_call limits but with limits on simultaneous sessions. But for transactional part of the system we'd like to be sure that there wouldn't be a stupid search running probably in several instances (because it is web app and if user cannot get back results quickly I'm pretty sure they will try again and again!!!) killing all DB.

Secondly I'v found a rather obvious way to assure that Oracle really does IO and CPU - it of course can easily be found in v$mystat and below is the test case with some comments.

My per_call limits were 100 CPU and 100000 IO.

First run got CPU exceeded. 158 CPU were used - I assume 58 were for PARSE AND EXEC and probably some recursive SQL (didn't trace this time).

Second time I got IO exceeded. session logical reads were for 100001 more and some physical reads were added as well.

Third time I got IO exceeded. session logical reads were again 100001 more and no more physical reads were added.

SQL> conn ***/***@***
Connected.
SQL> select n.name, s.value from v$mystat s, v$statname n   2 where s.statistic#=n.statistic# and   3 n.name in('session logical reads','CPU used by this session','physical reads')
  4 /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                    20
CPU used by this session                                                  1
physical reads                                                            1

SQL> SELECT blahblah
ERROR at line 3:
ORA-02393: exceeded call limit on CPU usage SQL> select n.name, s.value from v$mystat s, v$statname n   2 where s.statistic#=n.statistic# and   3 n.name in('session logical reads','CPU used by this session','physical reads')
  4 /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                 80588
CPU used by this session                                                159
physical reads                                                         9059
SQL> SELECT blahblah
ERROR at line 3:
ORA-02395: exceeded call limit on IO usage SQL> select n.name, s.value from v$mystat s, v$statname n   2 where s.statistic#=n.statistic# and   3 n.name in('session logical reads','CPU used by this session','physical reads')
  4 /
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                180589
CPU used by this session                                                234
physical reads                                                        12268
SQL> SELECT blahblah
ERROR at line 3:
ORA-02395: exceeded call limit on IO usage SQL> select n.name, s.value from v$mystat s, v$statname n   2 where s.statistic#=n.statistic# and   3 n.name in('session logical reads','CPU used by this session','physical reads')
  4 /
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                280590
CPU used by this session                                                280
physical reads                                                        12268


Gints Plivna

2006/3/14, Mark W. Farnham <mwf_at_rsiz.com>:
>
> I think Ethan makes an excellent point, and there is more: While long
> running, expensive queries are the only ones worth looking at to determine
> whether the query is "bad," that does NOT mean that every long running query
> IS "bad." Some very expensive queries are also optimal and absolutely
> required.
>
> Flash back 25 years. The default timesharing quota control by user was "if a
> user's job consumes more than x in resources, kill it." "x" varied by user.
> As data accumulated for any time sharing user, job costs also tended to
> increase. So eventually some routine job would eventually exceed the
> "quotas" for that user for a single job and the job would get killed. Since
> most software of that vintage did not support the idea of savepoints and
> continuing a job with some of the work completed, that meant the customer
> paid for the job nearly twice. No one ended up happy. Of course no one
> wanted to pay for an infinite loop, either. In an Oracle environment you'll
> get even more extra cost and delay while the rollback executes before you
> can re-run the job. Of course if the job is not a monolith you may only have
> to roll back to some point in the process and be able to continue from
> there, but controlling the costs of resource control is not an issue to
> ignore.
>
> If your shop has job naming conventions, then you *may* have a way to let
> you know that job x by user y should be allowed to run, or set some string
> in the job envionment that lets you know that the job is a production job
> rather than an ad hoc query. Anyway good luck.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 15 2006 - 03:18:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US