RE: 10046 madness

Hi Robert

> Ok, I'm a bit perplexed and I need some ideas. I have two
> systems. From a hardware/software POV they are exactly alike.

I hate that - I can never tell if they really, really are :(

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

Looks a bit likely

> 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

<ignore stuff which I know zilch about cos its RAC)

> global cache cr request 17696 1.640369
> .000092697
> global cache s to x 17694 11.375243
> .000642887

</ignore>

> ********** -----------
> 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

<ignore as before>
> 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

</ignore>

> log buffer space 46 25.039965
> .544347065
> ********** -----------
> sum 94.349504
> ELAPSED_TIME
> ------------
> 412.876579

It seems to me that you wait 42 seconds longer on prod but the process takes 29 seconds longer overall.

This looks to me like its because you are doing more index access on prod than you are on test, which suggests to me that the execution plan for the sql is different between the two. I wonder if this is verifiable. The average waits as you say are better on prod - there are just more of them.

If the software/hardware is the same - is the data different/stats different. It just looks to me like the access path is different (nested loops in prod/hash join in test maybe).

Looking forward to be shown to be daft.

Niall

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  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).