-
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
-
-
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).
Last edited by castlerock; 05-17-2006 at 12:45 PM.
-
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....
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|