Re: A Tale of Two Servers...

ARE the statistics and especially the data distribution the same? Maybe in the faster server the data is better clustered along the index access. You are on Oracle 9.2, turn on statistics_level=all and you'll get rows source statistics so you'll see which of the individual operations incur the lio. Plus there are other reasons why consistent gets could be higher. Are the numbers consistently repeatable?

At 05:43 PM 4/7/2003 -0800, you wrote:
>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).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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