Re: A bit disappointed in CBO..

The fact that you have to switch to ALL_ROWS to make the CBO kick in is a cleare indication that you do not have any statistics on any of the tables in the query. Consequently, the CBO is using default values, which are not suitable for your tables.

NB - if the optimizer mode in the plan from autotrace shows "optimizer=CHOOSE", and there are no items like "(Cost=15 Card=11 Bytes=187)" in the output, then you have no stats and are optimising through RBO. BTW
> We are running 8.1.7.4, did things improve significantly in 9i?
> We use Oracle 8.1.7.3 on Solaris 2.7 boxes

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


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


NetComrade wrote in message
<3e4aa9cd.621980441_at_nyc.news.speakeasy.net>...


>We have been running RBO forever.

>However, a lot of the queries aren't written optimally (e.g. driving

>tables were not written in the right order)

>So I started experimenting with some of the queries, analyzing the

>tables, looking at execution paths.

>Initial signs were encouraging, so I decided to analyze a two

>relatively small schemas and watch..

>(dbms_stats.gather_schema_stats(ownname=>'my_schema', cascade=>TRUE)

>

>However, even though a couple of queries have improved

>(siginificantly), an equal number became bad pretty bad.

>

>Oracle started to pick the wrong tables as driving tables.. Is that

>normal?

>

>We are running 8.1.7.4, did things improve significantly in 9i?

>

>Given that oracle swears that there will be no more RBO, we'd like to

>start moving away from it, but I don't want to be on query watch

every


>day.

>

>example:

>

>  1  SELECT M.USER_ID,NVL(M.TOTAL_SW_POINTS,0)

>  2  FROM MANAGER_STATS M,USER_GROUP UG

>  3  WHERE M.USER_ID = UG.USER_ID  AND UG.GROUP_ID =  (SELECT

GROUP_ID


>  4  FROM USER_GROUP

>  5  WHERE USER_ID = 100 AND ROWNUM = 1 )

>  6* ORDER BY NVL(M.TOTAL_SW_POINTS,0) DESC

>

>

>cost-based (for whatever reason i had to set optimizer_goal to

>all_rows in order to get CBO, all tables are analyzed, user_group is

a


>view on team_group)

>

>   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=11

Bytes=1


>87)

>   1    0   SORT (ORDER BY) (Cost=15 Card=11 Bytes=187)

>   2    1     FILTER

>   3    2       NESTED LOOPS (Cost=13 Card=11 Bytes=187)

>   4    3         INDEX (FAST FULL SCAN) OF 'TEAM_GROUP_U1' (UNIQUE)

(


>          Cost=2 Card=11 Bytes=88)

>   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'MANAGER_STATS'

(Co


>          st=1 Card=3828 Bytes=34452)

>   6    5           INDEX (UNIQUE SCAN) OF 'MANAGER_STATS_PK'

(UNIQUE)


>   7    2       COUNT (STOPKEY)

>   8    7         TABLE ACCESS (BY INDEX ROWID) OF 'TEAM_GROUP'

(Cost=


>          2 Card=1 Bytes=8)

>   9    8           INDEX (RANGE SCAN) OF 'TEAM_GROUP_TEAM_ID'

(NON-UN


>          IQUE) (Cost=1 Card=1)

>

>

>Statistics

>----------------------------------------------------------

>         14  recursive calls

>          4  db block gets

>       7676  consistent gets

>          0  physical reads

>          0  redo size

>       1376  bytes sent via SQL*Net to client

>        537  bytes received via SQL*Net from client

>          3  SQL*Net roundtrips to/from client

>          4  sorts (memory)

>          0  sorts (disk)

>         20  rows processed

>

>

>   0      SELECT STATEMENT Optimizer=CHOOSE

>   1    0   SORT (ORDER BY)

>   2    1     NESTED LOOPS

>   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TEAM_GROUP'

>   4    3         INDEX (RANGE SCAN) OF 'TEAM_GROUP_GROUP_ID'

(NON-UNI


>          QUE)

>

>   5    4           COUNT (STOPKEY)

>   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'TEAM_GROUP'

>   7    6               INDEX (RANGE SCAN) OF 'TEAM_GROUP_TEAM_ID'

(NO


>          N-UNIQUE)

>

>   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'MANAGER_STATS'

>   9    8         INDEX (UNIQUE SCAN) OF 'MANAGER_STATS_PK' (UNIQUE)

>

>Statistics

>----------------------------------------------------------

>          0  recursive calls

>          0  db block gets

>         72  consistent gets

>          0  physical reads

>          0  redo size

>       1372  bytes sent via SQL*Net to client

>        537  bytes received via SQL*Net from client

>          3  SQL*Net roundtrips to/from client

>          1  sorts (memory)

>          0  sorts (disk)

>         20  rows processed

>

>.......

>We use Oracle 8.1.7.3 on Solaris 2.7 boxes

>remove NSPAM to email