Re: 9.2.0.4 buffer (sort)

From: Fuad Arshad <fuadar_at_yahoo.com>
Date: Tue, 13 Jul 2004 07:50:22 -0700 (PDT)
Message-ID: <20040713145022.12626.qmail@web80503.mail.yahoo.com>

had the exact same issue after upgrading to 9.2 still tryingto investigate. since we never had these problems in development and the volume wasnt as high as in production. a couple of parameters recommended by oracle helped us fix it . optimizer_new_join_card_computation = false also goes with setting
_optimizer_undo_cost_change = 9.0.1
_complex_view_merging=false
_unnest_subquery=false
_always_semi_join = off

also to use one single parameter you can user optimizer_features_enable=8.1.7 and that reverts all these and more to their orignal value prior to 9.2

"Gamble, Scott" <Scott.Gamble_at_cardinal.com> wrote: 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
-----------------------------------------------------------------


----------------------------------------------------------------
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:48:20 CDT