Oracle Scratchpad

January 12, 2007

Buffer Sorts – 2

Filed under: CBO,Execution plans,Performance,trace files — Jonathan Lewis @ 7:26 pm GMT Jan 12,2007

Just a little follow-up on my earlier note on buffer sorts. The following is an extract from a a tkprof output showing the rowsource operation for a query. Note especially the  rows information. 

7854          MERGE JOIN CARTESIAN (cr=14 time=63688 us)
66              TABLE ACCESS FULL GTT_REB_GRP_HDLE_RATE (cr=7 time=460 us)
7854            BUFFER SORT (cr=7 time=24797 us)
119               TABLE ACCESS FULL VP_REBATE_CONTROL_DTL (cr=7 time=391 us)     

The numbers help to explan what’s going on in the buffer sort. We see a merge join cartesian (which, in this case, looks like an accident – the optimizer may have expected just one row to come out of the GTT_REB_GRP_HDLE_RATE table – the name suggests it’s a global temporary table, and they often cause problems to the optimizer). To make this join more efficient, Oracle has scanned the VP_REBATE_CONTROL_DTL just once, producing 119 rows and stuffing them into a buffer.

We then see that the buffer sort operation has produced 7,854 rows – why ? Because Oracle has scanned the buffer once for every row in the driving table. (Quick check: 66 * 119 = 7,854).

Footnote: The owner of the query is going to be using dbms_xplan, checking the estimated cardinalities and predicates, to check why Oracle came up with a Cartesian merge join.

1 Comment »

  1. […] [More on Buffer Sorts] Comments (32) […]

    Pingback by Cartesian Merge Join « Oracle Scratchpad — July 9, 2010 @ 1:18 pm BST Jul 9,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by