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).
