| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Tale of Two Servers...
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...
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:
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 servicesReceived on Wed Apr 09 2003 - 19:53:35 CDT
---------------------------------------------------------------------
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).
 |
 |