Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: A Tale of Two Servers...

RE: RE: A Tale of Two Servers...

From: Orr, Steve <sorr_at_rightnow.com>
Date: Thu, 10 Apr 2003 10:18:42 -0800
Message-ID: <F001.0057F018.20030410101842@fatcity.com> 





Stephane,



I understand and share your skepticism:

- db_block_size is the same all the way around;
- validate index was done;
- index rebuilds were done;
- temp tablespaces identical;
- oracle version, features, and init.ora are the same (except for 


  db_name of course;

- no SQL*Net, all local;
- statistics are the same;
- there is only one table and two indexes which I have dropped and 


  rebuilt repeatedly on both servers to make sure everything is 
  identical;

- index structures identical;
- table structure identical;
- I have also done export/import just to make sure everything is the 


  same (I "Stopped Defragmenting and Started Living" long ago and I 
  wanted to make sure I didn't have to start defragmenting again :-);

- Linux versions and kernel parms are the same;
- the only difference is CPU, performance, and the output of tkprof.
- Oh, the faster server has 1GByte LESS RAM but the SGA's are the same 


  and there is no swapping or other activity.



The differing LIO counts are indeed very curious/weird! But, since not all cache is the same, (up to 2GHz vs bus speed), it WOULD be good to differentiate it and since L1 cache is so fast... 



I'm still curious about a reasonable explanation and will gladly investigate anything suggested. Hypothesis... Experiment... Huh??? 



Stuff:


http://www.acm.org/sigs/sigmod/dblp/db/conf/vldb/AilamakiDHW99.html
http://ftp.us.dell.com/app/l2cachep.pdf




btw, the query in question is a horrendous self join twice over and has since been tuned... Of course I was only being facetious about abandoning query tuning and throwing hardware at bad queries. Nevertheless, cheap CPU vs my expensive billing rate has to come into the cost effectiveness equation. :-)




Wondering if robots will do my work for me and if life as I know it will lose all meaning... ;-)
Steve





-----Original Message-----


Sent: Thursday, April 10, 2003 3:29 AM


To: Multiple recipients of list ORACLE-L




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?



>----- ------- Original Message ------- -----

>From: "Orr, Steve" <sorr_at_rightnow.com>

>To: Multiple recipients of list ORACLE-L

><ORACLE-L_at_fatcity.com>

>Sent: Wed, 09 Apr 2003 16:53:35

>

>Apparently the difference is... hardware... raw

>power!

>

>The clock speed on the Athlon is twice as fast but

>the most significant factor is cache. Both CPU's

>have 256K of L2 cache which operates at bus speed.

>L1 cache is much faster, operating at chip speed,

>and the AMD CPU has 128K vs Intel's 32K. Does

>Oracle/tkprof not count CPU cache as LIO in

>comparison to other "buffer gets?" 

>

>Whilst composing this email I got a nice response

>from OWS:

>"Bigger L1 cache is better for overall performance

>of the CPU when the L2 cache is the same and in

>that aspect AMD chip should perform better and that

>is confirmed [by my test results]."

>

>Further OWS response...

>

>"Yes, the total no. of logical IO is much higher

>with Intel CPU and that could happen because of no.

>of calls made to the OS by Intel processor compared

>to the AMD processor. That could be the only

>explanation considering the fact that the execution

>plans are the exact same and the order of execution

>is also the same in both cases. If you notice the

>CPU % usage during this time from any OS utilities,

>you might find that the CPU % usage might be more

>in the case of Intel CPU because of more LIOs.

>

>Q1: Do the Oracle internals and tkprof reports not

>count CPU cache as buffer gets?

>Ans: I am sure Oracle does not keep track of the

>CPU cache for the sql_trace and hence, tkrpof will

>also have no idea that CPU cache is boosting the

>performance. As far as Oracle sql_trace is

>concerned, it will only consider buffer cache and

>logical and physical IO calls to indicate what is

>going on for a sql statement."

>

>Thanks OWS!!!

>

>Duh... size matters... hardware and CPUs matters.

>But if such subtle CPU features as the type of CPU

>cache make such huge performance differences then

>maybe I the lowly DBA need to keep up with CPU

>advancements. Sigh... Since Oracle licenses are

>based on CPU's it only stands to reason that we

>should get the fastest/best CPU's for our servers.

>Maybe I can go from 4 CPU's to 2 better CPU's, get

>better performance and save on Oracle licenses too.

>

>

>Some folks have rightly pointed out that excessive

>LIOs can be a performance problem which should be

>fixed with SQL statement tuning... but why bother

>tuning SQL statements when you can just throw

>hardware at it? ;-)

>

>Aren't there some papers on enhanced Oracle

>performance with more cache on the CPU?

>Specifically L1 vs. L2 vs. L3 cache vs. etc.?

>

>

>Best regards,

>Steve Orr

>Bozeman, MT

>

>

>

>-----Original Message-----

>Sent: Tuesday, April 08, 2003 12:29 PM

>To: Multiple recipients of list ORACLE-L

>Importance: High

>

>

>Did you check the size of the LIO's.  Any

>difference in the number and or size of direct path

>reads?  Any difference in the temporary

>tablespaces?

>

>Ian MacGregor

>Stanford Linear Accelerator Center

>ian_at_SLAC.Stanford.edu

>

>-----Original Message-----

>Sent: Monday, April 07, 2003 6:44 PM

>To: Multiple recipients of list ORACLE-L

>

>

>It was the best of times, it was the worst of

>times...

>Server A: 

>Linux version 2.4.18-3smp (Red Hat Linux 7.3

>2.96-110)

>2GB of RAM

>2 CPU's Intel Pentium 3 846 MHz with 256K cache

>Oracle 9.2

>

>tkprof output:

>call	  count       cpu    elapsed  disk     

>query	  current   rows

>------- ------  -------- ---------- -----

>---------- ----------	-----

>Parse	      1      0.02	0.01	 0	   

>0	    0	   0

>Execute      1      0.00	0.00	 0	   

>0	    0	   0

>Fetch	    120     12.92      12.64	 0   

>1091204	  0   1783

>------- ------  -------- ---------- -----

>---------- ----------	-----

>total	    122     12.94      12.65	 0   

>1091204	  0   1783

>

>Rows	  Row Source Operation

>------- 

>---------------------------------------------------

>

>   1783  SORT ORDER BY


>   1783   NESTED LOOPS OUTER


>   1783    NESTED LOOPS


>    295     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>    607      INDEX RANGE SCAN SESSIONID (object id

>15726)

>   1783     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>   3588      INDEX RANGE SCAN TIMESTAMP (object id

>15727)

>      0    VIEW


>      0     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>1082281      INDEX RANGE SCAN SESSIONID (object id

>15726)

>Server B: 

>Linux version 2.4.18-24.7.xsmp (Red Hat Linux 7.3

>2.96-112)

>1GB of RAM

>2 CPU's AMD Athlon 1600 MHz with 256K cache

>Same kernel parms

>Oracle 9.2 same init.ora parameters

>

>tkprof output:

>call	  count       cpu    elapsed  disk     

>query	  current   rows

>------- ------  -------- ---------- -----

>---------- ----------	-----

>Parse	      1      0.00	0.00	 0	   

>0	    0	   0

>Execute      1      0.00	0.00	 0	   

>0	    0	   0

>Fetch	    120      1.18	1.15	 0     

>11309		0   1783

>------- ------  -------- ---------- -----

>---------- ----------	-----

>total	    122      1.19	1.16	 0     

>11309		0   1783

>

>Rows	  Row Source Operation

>------- 

>---------------------------------------------------

>

>   1783  SORT ORDER BY


>   1783   NESTED LOOPS OUTER


>   1783    NESTED LOOPS


>    295     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>    607      INDEX RANGE SCAN SESSIONID (object id

>6112)

>   1783     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>   3588      INDEX RANGE SCAN TIMESTAMP (object id

>6113)

>      0    VIEW


>      0     TABLE ACCESS BY INDEX ROWID CLICKTRACK


>1082281      INDEX RANGE SCAN SESSIONID (object id

>6112)

>Same query, same table, same data, same indexes,

>same statistics, same tablespace storage, and

>dba_segments is very close.

>

>RESULTS............


>Same execution plan but Server B has 1/100th the

>LIO's and is 10+ times faster. What gives? Linux

>memory tuning issue on server A? What do I

>investigate now? 

>

>

>What the Dickens is going on?

>Defarge 

>-- 

>-- 

>Please see the official ORACLE-L FAQ:

>http://www.orafaq.net

>-- 

>Author: Orr, Steve

>  INET: sorr_at_rightnow.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).

>---------------------------------------------------

>------------------

>---------------------------------------------------

>------------------

>---------------------------------------------------

>-------------------

>---------------------------------------------------

>------------------




Regards,



Stephane Faroult


Oriole

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: sorr_at_rightnow.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).


Received on Thu Apr 10 2003 - 13:18:42 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US