Oracle Scratchpad

January 12, 2007

Buffer Sorts – 2

Filed under: CBO,Execution plans,Performance,trace files — Jonathan Lewis @ 7:26 pm BST 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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,985 other followers