Re: A Tale of Two Servers...

 From my testing, unless the tables are partitioned, clustering of data has a very limited impact on I/O activity. If the tables/indexes are the same sizes and have the same statistical values in each system, the only missing piece would be the data distribution.

The time is right for running sql_trace at level 12 and checking the raw trace file. That should tell you if the change in I/O is spread across all of the objects or if there is one object that is completely skewing the results.

Are you sure the stats are accurate? Is there a bug associated with the version of RH or the processor?

Here's a wild idea...was there other activity on the tables/indexes at the time the query was being run? Perhaps the I/O differences were the result of read consistency generation?
And another ... could the number of buffer cache chains be different enough to cause the system to have to walk the chain to a great depth?

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Thursday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


Wolfgang Breitling wrote:



> I didn't dare to say it, but that were my thoughts as well. There is 

> no way

> that the size of an L1-3 cache, any cache except Oracle's buffer pool, 

> can

> have an effect on the number of LIOs. Their speed, yes but not their

> number. The best explanation I can come up with is that the data in the

> second, faster, server is better clustered, potentially by importing the

> data from the first server. Rebuilding the indexes on the first server 

> may

> go some ways toward streamlining the acces, but the data may still be

> scattered all over the place. Unless the databases are clones of each 

> other

> - which they obviously are not - you can not make a valid comparison

> between the two.

>

> At 01:28 AM 4/10/2003 -0800, you wrote:

> >Steve,

> >

> >    I wish I could put it more diplomatically but IMHO the answer you 

> got

> > from OWS is pure rubbish. I could agree to a 'hardware on amphetamines'

> > explanation if the only difference was to be found in cpu time. But

> > saying that CPU cache affects the LIO count (as opposed to speed) is

> > utter nonsense; something like 2 or 3% at most of the Oracle kernel 

> code

> > is OS-dependent, do you seriously believe they can afford taking subtle

> > hardware differences into account? Certainly not. OWS have jumped on 

> the

> > only difference they could see to find a reason to close the case.

> >There is obviously something else, although I have no credible 

> explanation

> >coming to mind. Ian's db_block_size was an excellent idea. If this is 

> the

> >same, could there be a difference in the physical structure of 

> indexes? I

> >am thinking of something causing the equivalent of the table 'full 

> scan up

> >to the HWM' which has occasionally burnt some of us. Have you tried a

> >VALIDATE INDEX (does it still exist?) on both databases? Or 

> rebuilding all

> >involved indexes on the slower server?

>

> Wolfgang Breitling

> Centrex Consulting Corporation

> http://www.centrexcc.com

>

>

>                          ********************

>

> This email communication is intended as a private communication for 

> the sole use of the primary addressee and those individuals listed for 

> copies in the original message. The information contained in this 

> email is private and confidential and if you are not an intended 

> recipient you are hereby notified that copying, forwarding or other 

> dissemination or distribution of this communication by any means is 

> prohibited.  If you are not specifically authorized to receive this 

> email and if you believe that you received it in error please notify 

> the original sender immediately.  We honour similar requests relating 

> to the privacy of email communications.

>

> Cette communication par courrier électronique est une communication 

> privée à l'usage exclusif du destinataire principal ainsi que des 

> personnes dont les noms figurent en copie.  Les renseignements 

> contenus dans ce courriel sont confidentiels et si vous n'êtes pas le 

> destinataire prévu, vous êtes avisé, par les présentes que toute 

> reproduction, tout transfert ou toute autre forme de diffusion de 

> cette communication par quelque moyen que ce soit est interdit.  Si 

> vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si 

> vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur 

> original immédiatement.  Nous respectons les demandes similaires qui 

> touchent la confidentialité des communications par courrier électronique.

>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

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: ListGuru_at_fatcity.com (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).