Re: Query CBO Cost

From: Anurag Varma <avoracle_at_gmail.com>
Date: 8 Apr 2005 15:05:30 -0700
Message-ID: <1112997930.301991.104450@g14g2000cwa.googlegroups.com>

Well I did run some tests ... and lol I was able to finally recreate the case.

and from what I see, in the simple case above, the CBO refused to look at the index plans in the non-hinted deletes (Even when I deleted
29988 out of 30000 rows .. and rebuilt the bitmap index). However in the hinted query, cost did appear as 1.

So basically the CBO never even considered the index access in my test case.
I could however, not reproduce the original posters case (who later admitted that
the query he posted was in reality different).

Here were the results (snipped) which somehow do prove my statement wrong that
hinting with an index can indeed result in lower cost. However, not sure whether I should have expected CBO to consider the index access in this special case when the consistent gets would have reduced. :

Non-Hinted:

BASE STATISTICAL INFORMATION

Table stats Table: DELTAB Alias: DELTAB   TOTAL :: CDN: 12 NBLKS: 192 AVG_ROW_LEN: 20 -- Index stats
  INDEX NAME: DELTAB_BMIDX COL#: 2
    TOTAL :: LVLS: 1 #LB: 11 #DK: 1 LB/K: 11 DB/K: 24 CLUF: 24   INDEX NAME: DELTAB_IDX COL#: 1
    TOTAL :: LVLS: 1 #LB: 13 #DK: 5311 LB/K: 1 DB/K: 1 CLUF: 24 _OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH
TABLE: DELTAB ORIG CDN: 12 ROUNDED CDN: 12 CMPTD CDN: 12 Access path: tsc Resc: 31 Resp: 31 BEST_CST: 31.00 PATH: 2 Degree: 1

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: DELTAB[DELTAB]#0
Best so far: TABLE#: 0 CST: 31 CDN: 12 BYTES: 96
Final - All Rows Plan:
JOIN ORDER: 1
CST: 31 CDN: 12 RSC: 31 RSP: 31 BYTES: 96 IO-RSC: 31 IO-RSP: 31 CPU-RSC: 0 CPU-RSP: 0 QUERY
explain plan for delete from deltab
PLAN
Cost of plan: 31
Operation...........Object name.....Options.........Id...Pid..

DELETE STATEMENT                                        0
DELETE              DELTAB                              1
TABLE ACCESS        DELTAB          FULL                2    1

Hinted:

BASE STATISTICAL INFORMATION

Table stats Table: DELTAB Alias: DELTAB   TOTAL :: CDN: 12 NBLKS: 192 AVG_ROW_LEN: 20 -- Index stats
  INDEX NAME: DELTAB_BMIDX COL#: 2
    TOTAL :: LVLS: 0 #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1   INDEX NAME: DELTAB_IDX COL#: 1
    TOTAL :: LVLS: 1 #LB: 1 #DK: 12 LB/K: 1 DB/K: 1 CLUF: 1 _OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH
TABLE: DELTAB ORIG CDN: 12 ROUNDED CDN: 12 CMPTD CDN: 12 Access path: index (no sta/stp keys)

      Index: DELTAB_BMIDX
  TABLE: DELTAB
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00   BEST_CST: 1.00 PATH: 4 Degree: 1

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: DELTAB[DELTAB]#0
Best so far: TABLE#: 0 CST: 1 CDN: 12 BYTES: 96
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1 CDN: 12 RSC: 1 RSP: 1 BYTES: 96 IO-RSC: 1 IO-RSP: 1 CPU-RSC: 0 CPU-RSP: 0 QUERY
explain plan for delete /*+ index(deltab deltab_bmidx) */ from deltab PLAN
Cost of plan: 1
Operation...........Object name.....Options.........Id...Pid..

DELETE STATEMENT                                        0
DELETE              DELTAB                              1
INDEX               DELTAB_BMIDX    FULL SCAN           2    1

......
And here are the results of the autotrace:

ORA92> delete from deltab;

12 rows deleted.

Elapsed: 00:00:01.34

Execution Plan

0 DELETE STATEMENT Optimizer=CHOOSE (Cost=31 Card=12 Bytes=96) 1 0 DELETE OF 'DELTAB'
2 1 TABLE ACCESS (FULL) OF 'DELTAB' (Cost=31 Card=12 Bytes=96)

Statistics

          0  recursive calls
         64  db block gets
        196  consistent gets
          0  physical reads
       8284  redo size
        784  bytes sent via SQL*Net to client
        784  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

ORA92> rollback;

Rollback complete.

Elapsed: 00:00:00.03
ORA92> delete /*+ index(deltab deltab_bmidx) */ from deltab;

12 rows deleted.

Elapsed: 00:00:01.33

Execution Plan

0 DELETE STATEMENT Optimizer=CHOOSE (Cost=2 Card=12 Bytes=96) 1 0 DELETE OF 'DELTAB'
2 1 INDEX (FULL SCAN) OF 'DELTAB_BMIDX' (NON-UNIQUE) (Cost=1 Card=12 Bytes=96)

Statistics

          0  recursive calls
         19  db block gets
          1  consistent gets
          0  physical reads
       4288  redo size
        784  bytes sent via SQL*Net to client
        818  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         12  rows processed

Thanks,

Anurag Received on Fri Apr 08 2005 - 17:05:30 CDT

Re: Query CBO Cost - Hints