9.2.0.4 buffer (sort)
- From: "Gamble, Scott" <Scott.Gamble@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Jul 2004 09:16:07 -0500
Platform HP Tru64 5.1b
DB version 9.2.0.4
During testing of 9.2.0.4 on a test database with full production data =
(fully analyzed) I have a query critical to this application that is =
performing slower in the 9.2.0.4 database.=20
I have been trying to find information on metalink about what exactly =
the difference is between a sort join and a buffer sort and not finding =
anything useful. That is one of the differences in the plan. The other =
difference is the moving of the table access to tril_adjustments but if =
I read that correctly that should be an improvement.
Tkprof output from both versions.
8.1.7.4
-----------------------------------------------
499 call count cpu elapsed disk query =
current rows
500 ------- ------ -------- ---------- ---------- ---------- =
---------- ----------
501 Parse 60 0.01 0.01 0 0 =
0 0
502 Execute 60 0.01 0.01 0 0 =
0 0
503 Fetch 180 1.34 4.49 476 158921 =
480 19368
504 ------- ------ -------- ---------- ---------- ---------- =
---------- ----------
505 total 300 1.36 4.51 476 158921 =
480 19368
506
507 Misses in library cache during parse: 1
508 Optimizer goal: CHOOSE
509 Parsing user id: 38
510
511 Rows Row Source Operation
512 ------- ---------------------------------------------------
513 328 SORT ORDER BY
514 328 NESTED LOOPS
515 869 MERGE JOIN CARTESIAN
516 32 TABLE ACCESS FULL TRIL_JOINS_WRK
517 899 SORT JOIN
518 28 TABLE ACCESS FULL TRIL_JOINS_WRK
519 328 TABLE ACCESS BY INDEX ROWID TRIL_ADJUSTMENTS
520 1196 INDEX RANGE SCAN (object id 3915)
521
522 =
*************************************************************************=
*******
9.2.0.4
-------------------------------------------------------------------------=
-----------------
call count cpu elapsed disk query current =
rows
------- ------ -------- ---------- ---------- ---------- ---------- =
----------
Parse 60 0.03 0.01 0 0 0 =
0
Execute 60 0.00 0.02 0 0 0 =
0
Fetch 180 3.49 8.11 349 160208 0 =
19368
------- ------ -------- ---------- ---------- ---------- ---------- =
----------
total 300 3.53 8.14 349 160208 0 =
19368
=20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 264 (SCPRICERDEV)
=20
Rows Row Source Operation
------- ---------------------------------------------------
328 SORT ORDER BY (cr=3D2675 r=3D11 w=3D0 time=3D236544 us)
328 TABLE ACCESS BY INDEX ROWID TRIL_ADJUSTMENTS (cr=3D2675 =
r=3D11 w=3D0 time=3D228352 us)
1197 NESTED LOOPS (cr=3D2635 r=3D6 w=3D0 time=3D138240 us)
868 MERGE JOIN CARTESIAN (cr=3D6 r=3D0 w=3D0 time=3D5120 us)
31 TABLE ACCESS FULL TRIL_JOINS_WRK (cr=3D3 r=3D0 w=3D0 =
time=3D1024 us)
868 BUFFER SORT (cr=3D3 r=3D0 w=3D0 time=3D4096 us)
28 TABLE ACCESS FULL TRIL_JOINS_WRK (cr=3D3 r=3D0 w=3D0 =
time=3D0 us)
328 INDEX RANGE SCAN TRIL_ADJUSTMENTS_IDX1 (cr=3D2629 r=3D6 =
w=3D0 time=3D123904 us)(object id 7656)
=20
=20
This is a third party application so I have no ability to influence the =
statement itself directly.=20
At this point I am just trying to figure out why the addition of the =
buffer sort seems to cause increased cpu/elapsed time and exactly what =
it means. I have checked this with multiple traces against the 9.2.0.4 =
database and it is always the same cpu/elapsed time is up for this =
query. This query runs millions of times a week and any increase in =
time is significant.
Scott Gamble
Oracle DBA - ERP and DB Technology Engineering=20
Cardinal Health, Medical Products and Services
(847)-578-5673 scott.gamble@xxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
