Spinning package call

[Laurel]

Hi Erman!
Any idea how to troubleshoot ?
We have  code that just spinning. ADM is saying -> tune this code.
BEGIN package_call.call1(:errbuf,:rc); END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1  18237.51   30593.14         13         51          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2  18237.51   30593.14         13         51          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
  latch: row cache objects                        1        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  direct path read                                4        0.00          0.00
  utl_file I/O                              3857529        0.16         35.14
  log file sync                                   1        0.00          0.00


 alter session set events '10046 trace name context forever, level 8'. Same info
Any idea how to analyze it ?

ThX!
Laurel

[Laurel]

and it stays all the time on CPU - consuming 1 CPU
select sid,
        decode(state, 'WAITING','Waiting',
                'Working') state,
        decode(state,
                'WAITING',
                'So far '||seconds_in_wait,
                'Last waited '||
                wait_time/100)||
        ' secs for '||event
        "Description"
from v$session
where sid=1515;
1515 Working Last waited -.01 secs for resmgr:cpu quantum

[ErmanArslansOracleBlog]

Hi Laurel,

1)It may be a bug. did you check this note->Large Waits With The Wait Event "Resmgr:Cpu Quantum" (Doc ID 806893.1)

2)May be a resource manager thing as well.. Did you enable resource manager? Is everything ok with it? Did you verify the resource manager plan?

3)Any other sessions waiting on this event? Did you see on top 5 in AWR?

4)Does the package use parallel execution? Do you have enough parallel slaves at the time of executing this?

[Laurel]

Hi Erman!
Thx  for ideas..
1. We dont have this as top5 and also not other sessions waiting on it
2. If it would be resource mgr I would see it on the OEM/top wait events
3 Not othersessions and not in top 5 awr
4. Not using parallel
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU                  3146,6                44.4
db file sequential read 2,242,204 757,5 0.34 10.7 User I/O
log file sync 36,844 159,9 4.34 2.3 Commit
db file parallel read 114,092 112,9 0.99 1.6 User I/O
direct path read 67,739 112,8 1.67 1.6 User I/O

Its on CPU constanly.Taking 1 CPU.
As this is plsql call and I cannot see anything  in trace .. Only that its spinning.
 I asked developer to include plsql hierarchial  profiler calls to package and debug it
The problem is that trace became from profiler 30Gb and we cannot analyze it / session crashes on out of memory when using plsql profiler,analyze call

Now I asked developer to cut in chunks package  and make more multilple traces files..

Do you have any other idea how to analyze in that case  from db perspective ?
Some loop in loop and in loop call i affraid..

Appreciate any ideas!
Thx laurel

[ErmanArslansOracleBlog]

I think it is related with the resource manager.
Please verify your resource manager configuration and resource plans.. (do your checks in detail)

[Laurel]

Hi Erman!
Disabled the resource mgr + the scheduler plans
execute dbms_scheduler.set_attribute('<>','RESOURCE_PLAN','');
Still Spinning..
the profiler showed the all  time goes  on  such call with multiset operations
list := all_list_a multiset union distinct all_list_b;

We planning to re-write it .. are you happened to know /aware of some limitations on CBO on these operations ?
Thx!
Laurel

[ErmanArslansOracleBlog]

Still spinning with what wait?
What is the wait event of the spinning process? (it should not be Resmgr:Cpu Quantum anymore)
Probably your code goes into a heavy loop..
Are there lots of elements in all_list_a and all_list_b?
If these are larger sets, why dont you use sql operators instead?

Here is what Tom Kyte says about it ->

Reference:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2314694900346831630