Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> A bit disappointed in CBO..

A bit disappointed in CBO..

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 12 Feb 2003 21:06:58 GMT
Message-ID: <3e4aa9cd.621980441@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 Received on Wed Feb 12 2003 - 15:06:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US