Re: A bit disappointed in CBO..

Ethel Aardvark wrote:

> I would also suggest spending some time studying SQL hints - they can
> make a huge difference to performance (I have seen speed-ups in the
> order of 10,000 fold)!
> 
> ETA

Hints are ok when tuning but when you have found the most efficient access path, remove the hint and tune the query and table stats to give you the same path. Hints tend to cause the plan to be stable when the data changes and can result in having to revisit the query when performance drops.

--
Andrew Allen

> 
> Andrew Allen <andrew.allen_at_handleman.com> wrote in message news:<3E4ABBD4.2070701_at_handleman.com>...
> 


>>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.

>