9.2.0.4 buffer (sort)

From: Gamble, Scott <Scott.Gamble_at_cardinal.com>
Date: Tue, 13 Jul 2004 09:16:07 -0500
Message-ID: <A208F793ACBE00439E6FB2AB0E8B57D601716D58@mpgexch01.cahapps.net>

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_at_cardinal.com

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 13 2004 - 09:14:22 CDT