Oracle-L: A Tale of Two Servers...

From: Orr, Steve <sorr_at_rightnow.com>
Date: Mon, 07 Apr 2003 17:43:39 -0800
Message-ID: <F001.0057C79E.20030407174339@fatcity.com>

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

Received on Mon Apr 07 2003 - 20:43:39 CDT