From FREEMANR@tusc.com Wed, 18 Jun 2003 13:28:08 -0700 From: Freeman Robert - IL Date: Wed, 18 Jun 2003 13:28:08 -0700 Subject: 10046 madness Message-ID: MIME-Version: 1.0 Content-Type: text/plain Ok, I'm a bit perplexed and I need some ideas. I have two systems. From a hardware/software POV they are exactly alike. However, one performs consistently better than the other. I'm sure this is an IO related issue. I have a shell script that does a bit of work on 10046 trace files. On both systems I have a database. Each is configured like the other. In each I loaded data into the same table the same way on each database, so the cache should be primed in each (I didn't check x$bh to be sure, I should have but they should be the same), I then ran a benchmark stored procedure in both the same way. Both systems were static at the time, so no concurrency. Data was loaded onto the same RAC node that the procedure ran on. When I processes the 10046 with a shell script I have for such things I see more wait event numbers (see below for 10046 summary wait events) from one system than the other. The average wait event time is smaller but the numbers are higher. I'm trying to figure out what the devil might cause this? Is it an issue of physical placement of data on the disks? Any ideas or thoughts or suggestions are most welcome. Here are the summary results of the 10046 traces. These traces were all manually run, and are only for the execution of the SP which has one select cursor in it and a single delete statement, here is pseudo code: cursor = select xyz from b where c=d; cursor loop get x into a delete from y where z=a; end loop Here are the summary results of the 10046 trace: Test db: Wait Event Name # times in trace total wait avg wait sec secs db file scattered read 1144 19.077857 .016676448 db file sequential read 2592 19.947032 .007695614 global cache cr request 17696 1.640369 .000092697 global cache s to x 17694 11.375243 .000642887 ********** ----------- sum 52.040501 ELAPSED_TIME ------------ 383.715441 Here is the production run... db file scattered read 422 2.010246 .004763616 db file sequential read 19671 48.628433 .002472087 global cache cr request 10144 12.256521 .001208253 global cache open x 1834 1.039376 .000566726 global cache s to x 10168 5.374963 .000528616 log buffer space 46 25.039965 .544347065 ********** ----------- sum 94.349504 ELAPSED_TIME ------------ 412.876579 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).