10046 madness

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Wed, 18 Jun 2003 13:28:08 -0700
Message-ID: <F001.005B43B1.20030618114420@fatcity.com>

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). Received on Wed Jun 18 2003 - 15:28:08 CDT