Re: A bit disappointed in CBO..

NetComrade wrote:
> 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

Have a look at these two initSID.ora parameters. They may be causing you trouble and need to be tuned to your specific environment. - optimizer_index_caching - percentage of blocks Oracle expects to find in the buffer cache during an index access. default ( 0 ) implies that every logical IO will result in a physial IO. Try raising this until you get more reasonabel execution plans.

• optimizer_index_cost_adj - represents relative cost of physical IO's for indexed access vs full scan. Default ( 100 ) tells the optimizer that an indexed access is just as costly as a full access.

You can play with these settings using the ALTER SESSION command. Try it and see how your explain plan is effected. Also, check out this paper ( http://www.evdbt.com/SearchIntelligenceCBO.doc ) for an interesting discussion of the topic and some more detailed help on tuning the parameters than we can give in this NG.

--
Anderw Allen