Re: Query CBO Cost

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 8 Apr 2005 22:45:39 +0000 (UTC)
Message-ID: <d371ij$5gd$1@sparta.btinternet.com>

<bdbafh_at_gmail.com> wrote in message
news:1112995246.090664.288030_at_f14g2000cwb.googlegroups.com...
> Anurag,
>
> Why not run a 10053 trace while executing the statement and observe
> what costs the CBO actually comes up with?
> Costs across parsings don't necessarily mean anything.
>
> http://www.centrexcc.com/ - papers -
> "A Look under the Hood of CBO: The 10053 Event"
>
> -bdbafh
>

Original Post lost at ISP - but I tracked back to metalink and found that the starting point to the question didn't give the full story. The delete statement in question looked more like this:

DELETE
FROM grandparent g
WHERE NOT EXISTS (
  SELECT 0
  FROM
   parent p,
   child c
  WHERE

   p.small_vc_p = 'xx'
  AND p.id_gp = g.id
  AND c.id_gp = p.id_gp
  and c.id_p = p.id

)
AND NOT EXISTS (
  SELECT 0
  FROM
   parent p,
   child c
  WHERE

   p.small_vc_p = 'yy'
  AND p.id_gp = g.id
  AND c.id_gp = p.id_gp
  and c.id_p = p.id

)
AND (
    EXISTS (
  SELECT 0
  FROM
   parent p
  WHERE
   p.small_vc_p = 'xx'
  AND p.id_gp = g.id
  )
OR EXISTS (
  SELECT 0
  FROM
   parent p
  WHERE
   p.small_vc_p = 'yy'
  AND p.id_gp = g.id
  )
)
;

And here's a funny one. With all tables analyzed to compute statistics, I get this plan:

Execution Plan

0 DELETE STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3) 1 0 DELETE OF 'GRANDPARENT'

   2    1     FILTER
   3    2       INDEX (FULL SCAN) OF 'GP_PK' (UNIQUE) (Cost=3 Card=1 
Bytes=3)
   4    3         NESTED LOOPS (Cost=5 Card=1 Bytes=22)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=16)
   6    5             INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
   7    4           INDEX (RANGE SCAN) OF 'C_PK' (UNIQUE) (Cost=1 Card=1 
Bytes=6)
   8    3         NESTED LOOPS (Cost=5 Card=1 Bytes=22)
   9    8           TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=16)
  10    9             INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
  11    8           INDEX (RANGE SCAN) OF 'C_PK' (UNIQUE) (Cost=1 Card=1 
Bytes=6)
  12    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=13)
  13   12         INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
  14    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=13)
  15   14         INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)

Note the total cost of the delete plan, and the INDEX FULL SCAN on line 3 When I add a bitmap index on one of the columns on grandparent, look what happens to the plan:

Execution Plan

0 DELETE STATEMENT Optimizer=CHOOSE (Cost=50 Card=1 Bytes=13) 1 0 DELETE OF 'GRANDPARENT'

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'GRANDPARENT' (Cost=40 Card=1 
Bytes=13)
   4    2       NESTED LOOPS (Cost=5 Card=1 Bytes=22)
   5    4         TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=16)
   6    5           INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
   7    4         INDEX (RANGE SCAN) OF 'C_PK' (UNIQUE) (Cost=1 Card=1 
Bytes=6)
   8    2       NESTED LOOPS (Cost=5 Card=1 Bytes=22)
   9    8         TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=16)
  10    9           INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
  11    8         INDEX (RANGE SCAN) OF 'C_PK' (UNIQUE) (Cost=1 Card=1 
Bytes=6)
  12    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=13)
  13   12         INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)
  14    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=1 
Bytes=13)
  15   14         INDEX (RANGE SCAN) OF 'P_PK' (UNIQUE) (Cost=2 Card=2)

It doesn't use the index - but the plan changes, and the cost changes.

(9.2.0.6, no funny init.ora parameters, no tricks - just an optimizer anomaly).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005

Received on Fri Apr 08 2005 - 17:45:39 CDT

Re: Query CBO Cost - Hints