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

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