| 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...
Orr, Steve,
hi, pay attention to index clustering factors. Did you export data from server A and import it into server B?Or did you do a ctas and order by?Or try to bounce two db and try event 10046 to see the IO done by the Server.
And in oracle 9i 9.2.0.2+, there is detailed cost in every step of the execution path like, maybe this helps you find t he difference :)
select view_count
from
pdtviewcount where product_id= 23570987
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 3 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.02 0.04 3 3 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26
Rows Row Source Operation
------- ---------------------------------------------------
0 INDEX UNIQUE SCAN OBJ#(6234) (cr=3 r=3 w=0 time=19853 us)(object id 6234)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 global cache cr request 2 0.00 0.00 db file sequential read 3 0.00 0.01 SQL*Net message from client 1 1.77 1.77
Regards
zhu chao
msn:chao_ping_at_163.com
www.cnoug.org(China Oracle User Group)
>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: chao_ping INET: chao_ping_at_vip.163.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 - 22:13:37 CDT
 |
 |