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

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@x.washington.edu
(replace x with u to respond)

Can you please provide with the table structure and index structures ?

amit

On Thu, 17 Nov 2005 10:56:32 -0800, peter wrote:

> I do have Jonathan Lewis's latest
> book, but I haven't

Where to heck did you get it? B&N apologized for the delay and will ship
it today and Bookpool lists it as "Out of Stock". How many did they print?
Only 10 pieces? I am desperately trying to get the book myself.

--
http://www.mgogala.com

Sure.
I don't want to give you the current stats because we have jobs that
re-generate stats so if the table
data has changed, the stats will be different, but the structure of
the table is pretty simple.

LINK_ 427037565

USER_PROF_ID NUMBER NOT NULL
PRODUCT_ID NUMBER NOT NULL,
AFFILIATE_ID NUMBER NOT NULL,
DATE_SUB DATE,
DATE_CONF DATE,
CONFIM VARCHAR2(1),
RCODE VARCHAR2(21) -- this is almost always
null.

3 indexes on the table.
LINK_427037565_PUC_U - unique on (PRODUCT_ID,USER_PROF_ID,CONFIM)
compress 1
LINK_427037565_UP_U - unique on (USER_PROF_ID,PRODUCT_ID)
LINK_427037565_D - non-unique on (DATE_CONF DESC)

Some Sample Data - all columns concatenated by '|'
================================================== =
302817134|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|
317909533|430657811|427037568|11/06/05 11:11|11/06/05 11:11|0|
430672053|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|
194057776|430657811|427037568|11/06/05 11:11|11/06/05 11:11|0|
256408509|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|
247974322|438075481|427037568|11/06/05 11:11|11/06/05 11:11|0|
439599713|430657811|427037568|11/06/05 11:11|11/06/05 11:11|0|
263760465|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|
430672059|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|
430672060|430657811|427037568|10/17/05 17:56|10/17/05 17:56|0|

--peter

I got it about 2 weeks ago without any problems.
It's got some really interesting stuff in there, I can't wait to
actually sit down and
workout some of the examples myself.

--peter

"peter" <p_msantos@yahoo.com> wrote in message
news:1132253792.850268.258580@g44g2000cwa.googlegr oups.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.
>

The cardinality is determined by applying
the standard rules to the columns referenced,
although there may be a sanity check that
uses the distinct_keys count from the index
instead of the product of the column cardinalities
in some circumstances. (That's just a recent thought,
isn't something I've checked, and may be version
dependent).

The cost is calculated in the same way as a tablescan
cost, but using the LEAF_BLOCKS statistic for the
index where you would otherwise use the BLOCKS
for a table.


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Mladen Gogala wrote:
> On Thu, 17 Nov 2005 10:56:32 -0800, peter wrote:
>
>
>>I do have Jonathan Lewis's latest
>>book, but I haven't
>
>
> Where to heck did you get it? B&N apologized for the delay and will ship
> it today and Bookpool lists it as "Out of Stock". How many did they print?
> Only 10 pieces? I am desperately trying to get the book myself.

Maybe one of the lucky few at UKOUG.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

So the cost makes sense..

(LEAF_BLKS/MBRC) * (MREADTIM/SREADTIM)
+
#CPUCycles / (cpuspeed * sreadtim_in_microseconds)

* I obtained #CPUCycles from the plan_table.cpu_cost by doing the
explain plan cmd... as you mentioned in your book.

IOCOST = (39550/125) * (26.806/4.245) = 1997.97842
+
CPUCOST= 281652952 / (198 * (4.245*1000)) = 335.097681
= 2333.0761 which is very close to 2335 as calculated by the
optimizer.

I'm still having some difficulties calculating the cardinality though.
This is probably since I have a histogram on the product_id column.
I've checkout
chapter 7 on histograms, but can't make the numbers add up.

This is what's currently in my user_tab_histograms view. There
are only 2 values for product_id in that table.

ENDPOINT_NUMBER|ENDPOINT_VALUE
---------------|--------------
1354519| 430657811
1943369| 438075481

if you have a moment, any additional hints would be greatly
appreciated.
I think I'm still not sure of what are the standard rules for
calculating the
cardinality on columns with frequency histograms.

thanks again
--peter

"peter" <p_msantos@yahoo.com> wrote in message
news:1132383166.310808.77440@g47g2000cwa.googlegro ups.com...
> So the cost makes sense..
>
>
> I'm still having some difficulties calculating the cardinality though.
> This is probably since I have a histogram on the product_id column.
> I've checkout
> chapter 7 on histograms, but can't make the numbers add up.
>
> This is what's currently in my user_tab_histograms view. There
> are only 2 values for product_id in that table.
>
> ENDPOINT_NUMBER|ENDPOINT_VALUE
> ---------------|--------------
> 1354519| 430657811
> 1943369| 438075481
>
> if you have a moment, any additional hints would be greatly
> appreciated.
> I think I'm still not sure of what are the standard rules for
> calculating the
> cardinality on columns with frequency histograms.
>
> thanks again
> --peter
>

The result depends on the predicate,
which includes considerations of whether
you have a type mismatch, or whether
you have applied a function to it.

Are the product types real numeric
types with the values that show up
in the histogram.

If your predicate is simply:
column = {actual recorded value}
then I would expect the relevant count
to be correct. In the case of a frequency
histogram, the endpoint_value records your
actual values, the endpoint_number records
the cumulative count up to that value. So
you appear to have:
1354519 rows with the value 430657811
and
1943369 - 1354519 rows =
588850 rows with the value 438075481


There are anomalies if the column stats or
table stats get out of synch with the histograms -
which is probably only going to happen if you
do unusual things with the analyze command
or dbms_stats package.

You also have to question the cardinality when
you use explain plan to check the cardinality of
a query that uses a bind variable - as (a) the bind
type is unknown, and (b) an actual value is unknown
so Oracle uses the num_distinct, num_nulls, and
num_rows. (And in some cases - version dependent -
uses the density rather than the num_distinct).

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005