package takes long time to run insert statement

[vpai]

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.

[Medhatithi]

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.

I don't have any clues, but I request if you get any solution to this problem, can you please let me know. I am very interested about this

[Medhatithi]

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.

I can suggest a way to locate the pain area first. You can create a database table instead of the global temporary table and run the same package, now inserting into this datbase table. Then it would be evident if the time taken is due to insertion in a Global Temporary Table specifically or not.
Also, I can share one of my experiences with global temporary tables, may be it can give you any clue. My query joining a global temp table was taking a lot of time, but after I used the DYNAMIC_SAMPLIN G hint, the query executed in a flash