peter wrote:
> 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>
> ------------------------------------------------------------------------------
> | Operation | Name | Rows | Bytes | Cost |
> -----------------------------------------------------------------------------
> | 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
I can't think of better advice than to suggest that you make the time
to read Jonathan's book. There is no better source of information.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Nov 17 2005 - 16:49:41 CST
