Skip to Main Content
  • Questions
  • Is there a view that a DBA can query to find out if "ORA-02393: exceeded call limit on CPU usage"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: January 13, 2021 - 10:36 pm UTC

Last updated: January 19, 2021 - 4:22 am UTC

Version: 19c

Viewed 100+ times

You Asked

Greetings,

I've seen when "cpu_per_call" limit is reached. ORA-02393 is sent to the SQL Plus. Is there a view that a DBA can query to find out if "ORA-02393: exceeded call limit on CPU usage" occurs to applications using the database since it isn't written to alert log.

Thanks,

John

and we said...

Not to my knowledge. However, you could look at trapping it with a server error trigger along the lines of:

SQL> create or replace trigger lock_expiry
  2  after servererror on database
  3  declare
  4    l_err varchar2(4000);
  5  begin
  6    if ( is_servererror(2393) ) then
  7      for i in (
  8      select *
  9      from  v$session
 10      where sid = sys_context('USERENV','SID')
 11      )
 12      loop
 13           ....
 14      end loop;
 15    end if;
 16  end;
 17  /

Trigger created.


Rating

  (2 ratings)

Comments

John, January 14, 2021 - 1:40 pm UTC

Thank you, Connor.

at least to ease my mind, can you answer your thoughts about setting profile to CPU_PER_CALL=600000 which is equivalent to 10 minutes? We are being forced by client security requirements to set it to a value so we set it to a value that should never be encountered in our OLTP only database. I never see an entire SQL run for more than 5 seconds.
Chris Saxon
January 15, 2021 - 9:00 am UTC

Are you referring to the option in CREATE PROFILE? If so be aware that we recommend using the Database Resource Manager to control CPU usage.

My main concern is ensuring 10 minutes is enough. In my experience, even "pure" OLTP databases have at least a couple of backend jobs with queries that normally to run for several minutes; these may start hitting that limit. If this happens, you could run these jobs as a different database user with a higher limit.

John, January 15, 2021 - 3:12 pm UTC

Thank you, Chris.

Yes, I am referring to the Create Profile.

As far as a backend job running for a long time, from my understanding the CPU_PER_CALL is based on just a call and so SQL request will have multiple calls, and calls range in the microsec range like so:

The following section of a trace file shows that multiple calls are executed to get blocks:
PARSE #139860437806472:c=0,e=589,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=36011995460
EXEC #139860437806472:c=1531,e=3275,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1475428744,tim=36011998816
FETCH #139860437806472:c=63,e=63,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=4,plh=1475428744,tim=36011998905
FETCH #139860437806472:c=5,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1475428744,tim=36011998936
STAT #139860437806472 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=7 pr=0 pw=0 str=1 time=124 us cost=15 size=690 card=5)'
STAT #139860437806472 id=2 cnt=1 pid=1 pos=1 obj=0 op='HASH JOIN OUTER (cr=7 pr=0 pw=0 str=1 time=112 us cost=14 size=690 card=5)'
STAT #139860437806472 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=7 pr=0 pw=0 str=1 time=103 us cost=14 size=690 card=5)'


10046-- e= c + Sum(ela) **********
c (microsec) total CPU capacity consumed by the db call. Isn't this just another wait event?
e (microsec) approx wall clock time that elapsed during the db call
ela (microsec) wall clock time that elapsed during the wait event.
tim (microsec) Time at which db call completed. In 9i, 10G, the number is given in microseconds.

Most of the lines above are each a call that satisfy the one SQL. For instance, the EXEC call took 3275 microsecs. The first FETCH call only took 63 microseconds. Therefore, it is unlikely that a "call" will ever be close to 10 minutes.

From Oracle documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PROFILE.html#GUID-ABC7AE4D-64A8-4EA9-857D-BEF7300B64C3 states this:
CPU_PER_CALL

Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

Is my understanding correct of what a "call" within "cpu_per_call" is? Do you still believe that it is possible for a single call to take 10 minutes? Also, no single SQL statement in our OLTP databases currently takes longer 10 seconds.

Thank you for your help.


Connor McDonald
January 19, 2021 - 4:22 am UTC

A call is from the perspective of "I made a request of the database and now I'm waiting", eg

SQL> create profile CPU_SMALL limit cpu_per_call 1000;

Profile created.

SQL> alter user scott profile cpu_small;

User altered.

SQL> conn scott/tiger@db19_pdb1
Connected.

SQL> declare
  2    x int;
  3  begin
  4    loop
  5      select count(*) into x from emp;
  6    end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-02393: exceeded call limit on CPU usage
ORA-06512: at line 5


If I did the exact same thing from a Java program, ie, each "select count(*)" was a different call, then it would be fine.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.