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
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.
Received on Thu Feb 13 2003 - 02:47:15 CST
