Now it took 2 Hrs to run in prod. The only difference now is the dbms_stats is not running in prod while executing.

Is DBMS_STATS an issue ??

Its dbms_stats take 5 hrs to finish. The same procedure used to run in 10 mins and instead of dbms_stats we used to have analyze


Trace file output:
----------------

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 242 0.07 0.08 0 0 0 0
Execute 326935 42.71 163.55 61778 7876 1046370 146287
Fetch 200736 996.55 6247.29 5260835 151250052 0 182953
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 527913 1039.34 6410.93 5322613 151257928 1046370 329240

Misses in library cache during parse: 14


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 32 0.00 0.00 0 0 0 0
Execute 44 0.00 0.00 0 0 0 0
Fetch 110 0.00 0.22 66 162 0 69
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 186 0.01 0.23 66 162 0 69

Misses in library cache during parse: 9

242 user SQL statements in session.
32 internal SQL statements in session.
274 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: sa_prod.trc
Trace file compatibility: 9.02.00
Sort options: default

0 session in tracefile.
242 user SQL statements in trace file.
32 internal SQL statements in trace file.
274 SQL statements in trace file.
64 unique SQL statements in trace file.
531344 lines in trace file.