Re: SQL tuning

From: dias <ydias_at_hotmail.com>
Date: 2 May 2004 11:19:23 -0700
Message-ID: <55a68b47.0405021019.25cac25f@posting.google.com>

Thanks Jonathan,

I have used the 10053 event to see how CBO works for this query. The trace contains two parts, one for the subquery and one for the table T1 :

For the subquery :

SINGLE TABLE ACCESS PATH
...
TABLE: T2 ORIG CDN: 25046 ROUNDED CDN: 1 CMPTD CDN: 1 ...
TABLE: T3 ORIG CDN: 25048 ROUNDED CDN: 1 CMPTD CDN: 1 ...
GENERAL PLANS

---

...
Best NL cost: 2 resp: 2

The CBO calculates the cost of the subquery and then the cost to access the table T1 :

GENERAL PLANS

---

Join order[1]: T1[SYS_ALIAS_1]#0
Best so far: TABLE#: 0 CST: 7 CDN: 1252 BYTES: 3756
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 7 CDN: 1252 RSC: 7 RSP: 7 BYTES: 3756   IO-RSC: 7 IO-RSP: 7 CPU-RSC: 0 CPU-RSP: 0 The cost of the whole query is just the sum of the two costs.

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c72h2j$13m$1_at_titan.btinternet.com>...
> This looks like a bug in the optimizer calculation
> for the FILTER operation.
>
> Note in-line
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> June 2004 UK - Optimising Oracle Seminar
> July 2004 USA West Coast, Optimising Oracle Seminar
> August 2004 Charlotte NC, Optimising Oracle Seminar
> September 2004 USA East Coast, Optimising Oracle Seminar
> September2004 UK - Optimising Oracle Seminar
>
> "dias" <ydias_at_hotmail.com> wrote in message
> news:55a68b47.0405011704.3ba734c7_at_posting.google.com...
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=3)
> > 1 0 SORT (AGGREGATE)
> > 2 1 FILTER
> > 3 2 INDEX (FAST FULL SCAN) OF 'IT1' (NON-UNIQUE) (Cost=7
> > Card=1252 Bytes=3756)
> > 4 2 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=6)
> > 5 4 INDEX (RANGE SCAN) OF 'IT2' (NON-UNIQUE) (Cost=1
> > Card=1 Bytes=3)
> > 6 4 BUFFER (SORT) (Cost=1 Card=1 Bytes=3)
> > 7 6 INDEX (RANGE SCAN) OF 'IT3' (NON-UNIQUE) (Cost=1
> > Card=1 Bytes=3)
> >
>
> Note that FILTER is effectively a nested loop in this plan.
>
> Line 3 estimates a cost of 7 to return 1,252 rows.
>
> Line 4 should operate once per row returned by line 3,
> at a cost of 2 per operation.
>
> The total cost of the operation should therefore be:
> 7 + 1252 * 2 = 2511
> Instead it is
> 7 + 1 * 2 = 9
>
>
> If you put in an UNNEST hint on the subquery, you
> may find that the hash semi join that appears is just
> a little more expensive than the calculated cost of
> the FILTER.
>
>
> The really odd thing about this is that the manuals
> say that Oracle does not cost UNNEST in 9i, it
> just does it - but clearly, this example has used cost
> to decide.
Received on Sun May 02 2004 - 13:19:23 CDT