Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> A bit disappointed in CBO..
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
Received on Wed Feb 12 2003 - 15:06:58 CST