Oracle-L: RE: Lord of the Data, The Two Servers

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Tue, 08 Apr 2003 08:04:12 -0800
Message-ID: <F001.0057CDC3.20030408080412@fatcity.com>

One server has 900MHZ intel CPUs and another one has 1.6 GHZ Athlons. Now, the RAM and the motherboard are vastly different because anything over 1.3 GHZ needs SDRAM which is much faster. Also, old motherboards sometimes don't support UDMA3, which is much faster. Sometimes newer Linuxes cannot turn on proper parameters for the old motherboards (see hdparm). If you've collected system statistics (dbms_stats) on the Athlon system and if you ran the query on the Intel server first, then a part of your indexes can be in the controller cache which will additionally speed up the query. Of course, the only things you can do is to analyze the optimizer with 10054 and then the process with 10046. Also, there is a nice little utility
for monitoring LIO on Anjo's oraperf site.

One browser to rule them all, one browser to find them, One browser to bring them all and int the darkness bind them In the land of Redmond where the shadows lie.

-----Original Message-----
Sent: Monday, April 07, 2003 9: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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Gogala, Mladen
INET: MGogala_at_oxhp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------