peformance tunning

Hi,
One of our developer is running a procedure ( which does a select and updates and inserts based on certain criteria). The insert is happening for every 10,000 records.Total records inserted will be 100000 in production which is taking 6 hrs.

the same procedure takes 3 hrs in development. This development environment is refreshed with the copy of production with the daily hotbackup.

In development it runs faster when compared to prod?? any ideas.

Thanks,

do all the inserts in one go, use bulk loading

but you need to trace the executions to see the differences between prod and test

http://marist89.blogspot.com/2005/07/trace-it_04.html

The same procedure is taking less time in development??

Thanks

My dbms_stats is running during the same time???

Starts at 5.00AM and Finishes at 9.00 AM

exec dbms_stats.gather_schema_stats(OWNNAME => 'SYSADM', CASCADE => TRUE);


procedure had processed 80,000 rows (approx. 80,000 rows in 5.5 hours).
as soon as my dbms_stats finishes
a total of 146,000 rows (approx 66,000 rows in 45 minutes).

you obviously missed the posts that said 'trace it'

My dbms_stats is running during the same time???

Starts at 5.00AM and Finishes at 9.00 AM

exec dbms_stats.gather_schema_stats(OWNNAME => 'SYSADM', CASCADE => TRUE);


thanks, will start the trace ...

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.

Dude, WTF???

Hmmmm, let me see I've got a batch of 1mil records that I have to load up. I wonder if I should run STATS at the same time?

Somebody git me a stick....