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.