We have a package in production which has suddenly started consuming greater than normal time to execute. On debugging this further, we found the following:
The package takes 5 mins to come out.
The queries inside the package, if run seperately, come out in milliseconds in total.
On looking through the sql trace when the package was run, it was found that an insert on a global Temp table is consuming around 5 mins (elapsed = 310 secs).
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 55.55 310.37 396138 425556 19 65
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 55.55 310.37 396138 425556 19 65
However, if the same set of sqls are run from outside the package, they run in milliseconds (with same results of course)
So what is different in sqls running seperately, against running as part of a package? Why should the package take time?
Any clues or pointers to what might be happening?
There is activity seen on the temp tbs in the awr, which is expected because of the global temp tables.
Thanks in advance.
The package takes 5 mins to come out.
The queries inside the package, if run seperately, come out in milliseconds in total.
On looking through the sql trace when the package was run, it was found that an insert on a global Temp table is consuming around 5 mins (elapsed = 310 secs).
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 55.55 310.37 396138 425556 19 65
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 55.55 310.37 396138 425556 19 65
However, if the same set of sqls are run from outside the package, they run in milliseconds (with same results of course)
So what is different in sqls running seperately, against running as part of a package? Why should the package take time?
Any clues or pointers to what might be happening?
There is activity seen on the temp tbs in the awr, which is expected because of the global temp tables.
Thanks in advance.
Comment