Home » RDBMS Server » Performance Tuning » PB statistic CBO
PB statistic CBO [message #202539] Fri, 10 November 2006 04:45 Go to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello

I'm problem with statistics CBO.
Here is result from tkprof.

1)Table=ARTICLE with index on item ZONAPP
HERE IS statistics on table and index.

SELECT CLEART,MARQUE,FAMILLE,CATEG,LARBDE,LARBDM,SERIE,STRUC,DIAMJ,DIAMP, etc...
FINCOM,TGAP,ROWIDTOCHAR(ROWID)
FROM
ARTICLE WHERE ZONAPP>=:1 AND (ZONAPP<:2 OR ZONAPP LIKE '1756514HGA%')
ORDER BY ZONAPP


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.53 0.53 0 20957 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.53 0.53 0 20957 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 75

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 TABLE ACCESS FULL ARTICLE

2)Table=ARTICLE with index on item ZONAPP
HERE IS NO STATISTICS !

SELECT CLEART,MARQUE,FAMILLE,CATEG,LARBDE,LARBDM,SERIE,STRUC,DIAMJ,DIAMP,
CDEINTAUT,TYPART,UNITE,QCONDIT,DERACH,TC4,TEXTE1,TEXTE2,VAL4,VAL5,DEBCOM,
etc...
FINCOM,TGAP,ROWIDTOCHAR(ROWID)
FROM
ARTICLE WHERE ZONAPP>=:1 AND (ZONAPP<:2 OR ZONAPP LIKE '1756514HGA%')
ORDER BY ZONAPP


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 75

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 CONCATENATION
1 TABLE ACCESS BY INDEX ROWID ARTICLE
1 INDEX RANGE SCAN ARTICLE_ZONAPP_NDX (object id 57801)
0 TABLE ACCESS BY INDEX ROWID ARTICLE
0 INDEX RANGE SCAN ARTICLE_ZONAPP_NDX (object id 57801)

WHY acces by index here (one stats)
and not in 1) (with stats)?

Re: PB statistic CBO [message #202543 is a reply to message #202539] Fri, 10 November 2006 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because when you have no statistics, you end up using the Rule Based Optimiser.
This is a (pretty complex) rule engine that generates the execution plan from a set of decisions about the objects involved in the query.
It will almose always decide to use an index based approach if possible.
Re: PB statistic CBO [message #202572 is a reply to message #202543] Fri, 10 November 2006 07:26 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Mmmmh
Ok for complexity but why CBO is not better RBO?
Re: PB statistic CBO [message #202610 is a reply to message #202572] Fri, 10 November 2006 09:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The CBO is in general substantially better than the RBO, IF it is given the statistics it needs to work on.
The RBO will still pick an index based plan, even if it needs to access every row in the table (a case in which a Full table scan would be far quicker).

Are your tables analyzed?

Re: PB statistic CBO [message #202654 is a reply to message #202610] Fri, 10 November 2006 12:54 Go to previous message
Haler
Messages: 71
Registered: October 2004
Member
yes, tables and index are analyzed.
Previous Topic: Perfomace issues on complex query
Next Topic: Parrallel Query Problem
Goto Forum:
  


Current Time: Wed Dec 07 18:27:32 CST 2016

Total time taken to generate the page: 0.05871 seconds