10g CBO and how to determine cardinality on INDEX

From: peter <p_msantos_at_yahoo.com>
Date: 17 Nov 2005 10:56:32 -0800
Message-ID: <1132253792.850268.258580@g44g2000cwa.googlegroups.com>

dear all,
  I'm trying to determine how the 10g optimizer calculates the cardinality and/or cost
  for an index fast full scan. I do have Jonathan Lewis's latest book, but I haven't
  had a chance to really dig into it, and to make matters worse his test are so controlled and
  as he mentioned little things can change how the optimizer behaves...so I thought I'd get some
  help here.

My environment:

Oracle 10.1.0.4 on Solaris 64bit.
pga_aggregate_target = 1.5 GB
workarea_size_policy = auto
db_cache_size = 2GB.
shared_pool_size = 1GB.
undo = AUTO.
optimizer_features_enable=10.1.0
optimizer_mode = 'ALL_ROWS'
optimizer_index_caching=80
optimizer_index_cost_adj=30
db_file_multiblock_read_count =128

SQL>@10053.sql - turn on 10053 tracing..

SQL> explain plan for SELECT count(*)

           from LINK_427037565
           where product_id=430657811;

The execution plan in the 10053 trace file reads <PRE>

| SELECT STATEMENT         |      |       |     |  2335 |
|  SORT AGGREGATE           |      |    1 |  7 |          |
|   INDEX FAST FULL SCAN  | LINK_427037565_PUC_U | 6614K  |   45M |

2335 |

Oracle correctly estimates the cardinality on the INDEX fast full scan, but I can't seem to come up with the formula for how it got that number.

Here is what's in my sys.aux_stats$;

CPUSPEEDNW=> 203.526389537599
IOSEEKTIM => 10
IOTFRSPEED => 4096

SREADTIM       => 4.245
MREADTIM       => 26.806
CPUSPEED      => 198
MBRC               => 125
MAXTHR           => 4876288
SLAVETHR       =>  NULL

Here is the relevant information from the 10053 trace file.

BASE STATISTICAL INFORMATION

Table stats Table: LINK_427037565 Alias: LINK_427037565 TOTAL :: CDN: 9716845 NBLKS: 59751 AVG_ROW_LEN: 47 Index stats
Index: LINK_427037565_PUC_U COL#: 2 1 6 TOTAL :: LVLS: 2 #LB: 39550 #DK: 9873590 LB/K: 1 DB/K: 1 CLUF: 6105005

SINGLE TABLE ACCESS PATH
COLUMN: PRODUCT_ID(NUMBER) Col#: 2 Table: LINK_427037565 Alias: LINK_427037565

    Size: 7 NDV: 2 Nulls: 0 Density: 5.1457e-08     Histogram: Freq #Bkts: 2 UncompBkts: 1943369 EndPtVals: 2   TABLE: LINK_427037565 Alias: LINK_427037565     Original Card: 9716845 Rounded: 6772595 Computed: 6772595.00 Non Adjusted: 6772595.00
  Access Path: table-scan Resc: 6070 Resp: 6070   Access Path: index (index-ffs)
    Index: LINK_427037565_PUC_U
    rsc_cpu: 281652952 rsc_io: 2000
    ix_sel: 0.0000e+00 ix_sel_with_filters: 1.0000e+00   Access Path: index-ffs Resc: 2335 Resp: 2335   Access Path: index (index-only)

    Index: LINK_427037565_PUC_U
    rsc_cpu: 1572700179   rsc_io: 27569
    ix_sel:  6.9700e-01    ix_sel_with_filters:  6.9700e-01

  Access Path: index (skip-scan)
    ss sel 6.9700e-01 andv 6937365
    ss cost 6937365 vs. table scan io cost 3020     Skip Scan rejected
  Access Path: index (index-only)

    Index: LINK_427037565_PUC_U
    rsc_cpu: 1572700179   rsc_io: 27569
    ix_sel:  6.9700e-01    ix_sel_with_filters:  6.9700e-01
    SORT resource      Sort statistics
      Sort width:         448 Area size:     1048576 Max Area size:
78643200
      Degree:               1
      Blocks to Sort:   17413 Row size:           21 Total Rows:
6772595
      Initial runs:         2 Merge passes:        1 IO Cost / pass:
   5660
      Total IO sort cost: 23073      Total CPU sort cost: 7353586202
      Total Temp space used: 217588000

BEST_CST: 2335.10 PATH: 14 Degree: 1

GENERAL PLANS

Join order[1]: LINK_427037565[LINK_427037565]#0 Best so far: TABLE#: 0 CST: 2335 CDN: 6772595 BYTES: 47408165
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 Final - All Rows Plan:
JOIN ORDER: 1
CST: 2335 CDN: 6772595 RSC: 2335 RSP: 2335 BYTES: 47408165 IO-RSC: 2000 IO-RSP: 2000 CPU-RSC: 281652952 CPU-RSP: 281652952 If someone could help me understand how the cardinality was calculated, I'd appreciate it.
I'd be interested in the cost as well, but that's not as important right now and hopefully with
a little bit more time and more reading I'll figure it out.

thanks again.
--peter Received on Thu Nov 17 2005 - 12:56:32 CST

10g CBO and how to determine cardinality on INDEX_FFS?