Skip scan used instead of (better?) range scan

[former_member182566]

Hello forum,

I have this simple query (this is oracle 10g):

SELECT                       
  *                          
FROM                         
  zfieligiblercvbl           
WHERE                        
  mandt = :a0 and bukrs = :a1

There are (among other) the following indexes:

UNIQUE     Index   ZFIELIGIBLERCVBL~0           
                                                
MANDT                                          1
BUKRS                                          8
BELNR                                    106.478
GJAHR                                          7
BUZEI                                          4

NONUNIQUE  Index   ZFIELIGIBLERCVBLZ2           
                                                
Column Name                     #Distinct                                                       
DAT_OFFER                                    390
BUKRS                                          8
FLG_OFFER                                      2

I expect that the primary key would be used, but strangely index Z2 is used:

SELECT STATEMENT ( Estimated Costs = 519 , Estimated #Rows = 25.841 )                                                                                
5  2 TABLE ACCESS BY INDEX ROWID ZFIELIGIBLERCVBL                      
         ( Estim. Costs = 518 , Estim. #Rows = 25.841 )                    
         Estim. CPU-Costs = 7.715.825 Estim. IO-Costs = 517                                                                                
1 INDEX SKIP SCAN ZFIELIGIBLERCVBLZ2                            
             ( Estim. Costs = 79 , Estim. #Rows = 25.841 )                 
             Search Columns: 1                                             
             Estim. CPU-Costs = 1.591.961 Estim. IO-Costs = 78

(this is too getting long; more details will come below)

[former_member182566]

If I use a hint to force ~0 then in fact a higher cost is estimated, but I don't understand why. Apparently for the part that is different (the step where the index is being used), a lower cost is estimated, but for the second step (that is apparently the same) a higher cost is estimated. Does this make any sense to you?

SELECT STATEMENT ( Estimated Costs = 1.103 , Estimated #Rows = 25.841 )                                                                                
5  2 TABLE ACCESS BY INDEX ROWID ZFIELIGIBLERCVBL                   
         ( Estim. Costs = 1.102 , Estim. #Rows = 25.841 )               
         Estim. CPU-Costs = 11.610.643 Estim. IO-Costs = 1.101                                                                                
1 INDEX RANGE SCAN ZFIELIGIBLERCVBL~0                        
             ( Estim. Costs = 30 , Estim. #Rows = 25.841 )              
             Search Columns: 2                                          
             Estim. CPU-Costs = 1.244.435 Estim. IO-Costs = 30

I have noticed that the clustering factor is higher for ~0. Would this explain it?

Both indexes were adjusted in SE14 and the clustering factor got slightly lower.

Would adjusting the table itself sort it by primary key and so recuce the primary key's clustering factor? Or... ?

Index statistics ZFIELIGIBLERCVBL~0
Last statistics date                  30.04.2010
Analyze Method               Sample 206.725 Rows
Levels of B-Tree                               2
Number of leaf blocks                      1.163
Number of distinct keys                  206.725
Average leaf blocks per key                    1
Average data blocks per key                    1
Clustering factor                         42.840

Index statistics ZFIELIGIBLERCVBLZ2
Last statistics date                  30.04.2010
Analyze Method               Sample 206.725 Rows
Levels of B-Tree                               2
Number of leaf blocks                        807
Number of distinct keys                    2.017
Average leaf blocks per key                    1
Average data blocks per key                    8
Clustering factor                         17.559

[former_member182566]

I ran the queries in sql*plus, and it seems that using ~0 is slightly faster (though it does show more consistent gets).

This question, of course, is academic: in fact the table is relatively small and the query is fast either way. Anyway, I suppose that if we can't understand why the optimizer is taking these decisions in simple cases then how can we expect to understand more complex ones...

Thanks in advance for your comments,

Rui Dantas

[former_member192616]

Hi Rui,

> I have noticed that the clustering factor is higher for ~0. Would this explain it?

most likely. The clustering factor plays an important role in range scans. I will post you

some more details tomorrow.

> Both indexes were adjusted in SE14 and the clustering factor got slightly lower.

You rebuilt the indexes and the clustering factor changed? This should never happen (not possible).

The clustering factor only changes when the table changes... so between the two statistics (from the

first index and after rebuilding the index) there must be some change in the table ... (?)

> Would adjusting the table itself sort it by primary key and so recuce the primary key's clustering factor? Or... ?

Reorganzing the table in the order of the primary key will very likely reduce the clustering factor of the primary key

yes. It might probably be taken then.

More details about the clustering factor in range scans (cost estimation) will follow tomorrow.

Kind regards,

Hermann

[former_member192616]

Hi Rui,

ok, earlier as promised. This might help to understand it:

FF = 1/Numdistinct(colum)

FF1 (=Product of the filterfactors of all conditions that restrict theamount of index leafblocks to be scanned)

FF2 (=Product of thefilterfactorsof all conditions that restrict the amount of tableblocks to be scanned)

FF1 = 1/1 * 1/8 = 0,125

FF2 = 1/1 * 1/8 = 0,125

Index Range Scan+ Table Access by Index ROWID:

(BLEVEL(index) + LEAF_BLOCKS(index) * FF1 + CLUSTERING_FACTOR(index) * FF2) * OPTIMIZER_INDEX_COST_ADJ / 100

2 + 1163 * 0,125 + 42840 * 0,125 * 20 / 100

2 + 145 + 1071 = 1218

you had 1103... (?) , i assmumee oica (optimizer_index_cost_adj) = 20

Was your posted costs in the execution plan BEFORE you rebuild the indexes (and updated their statistics)?

Index SkipScan:

max(NUM_DISTINCT(skipped_columns)) * (BLEVEL(index) + LEAF_BLOCKS(index) * FF1 + CLUSTERING_FACTOR(index) * FF2) * OPTIMIZER_INDEX_COST_ADJ / 100

390 * 2 + 807 * 0,125 + 17559 * 0,125 * 20 / 100

780 + 101 + 440 = 1321

this is much more than you had and more than the index range scan for the primary key.

With your newly built indexes you still get the skip scan?

is the estimated cost still the same as the posted or different?

With the given statistics and assuming oica = 20 the primary key should be chosen but maybe

i made a mistake... . Could you please post the recent plans, estimations and statistics, i can

follow up then... .

Kind regards,

Hermann

[Former Member]

Hi Hermann,

I'd appreciate if you can help clarify some confusion I have with your cost calculation for the index skip scan query:

Index SkipScan:
max(NUM_DISTINCT(skipped_columns)) * (BLEVEL(index) + LEAF_BLOCKS(index) * FF1 + CLUSTERING_FACTOR(index) * FF2) * OPTIMIZER_INDEX_COST_ADJ / 100

I didn't expect to see the clustering factor for calculating costs of index skip scans. E.g. if I take a simple example of an index i12 with two fields f1 (values A,B and C) and f2 (values 1, 2 and 3):

f1:               A         B         C
                / | \     / | \     / | \
f2:            1  2  3   1  2  3   1  2  3
                \ |   \ /    \ |    \ |  |
DataBlock:       B1    B2     B3     B4  B5

If I search via criteria on f2 the clustering factor of i12 seems irrelevant and I don't see how one could integrate the clustering factor into the cost calculation to achieve more accurate results. Can you please point out where my reasoning is wrong?

Cheers, harald

p.s.: Thanks for your nice book [ABAP Performance Tuning|http://www.sap-press.com/products/ABAP-Performance-Tuning.html], which I enjoyed reading (as your posts on SDN). It's always good to see valuable information...

[former_member192616]

Hi Harald,

my post from yesterday still seems to be incorrect i'm checking the formulas... .

Regarding your question:

An index skip scan is multiple index range scans isn't it?

can be multiple index unique scans as well of course but then, the clustering factor is irrelevant.

If we have a non-unique index like this:

MANDT 1

BUKRS 5

BELNR 350

POSNR 50

and a query with

mandt = ? and belnr =?

we can either do ONE big range scan on MANDT

or

FIVE (if the statistic is correct, ONE FOR EACH EXISTING BUKRS) smaller range scans

with MANDT, BUKRS, BELNR

the clustering factor has an influence in both cases.

Kind regards,

Hermann

p.s.: thanks for the credit

[Former Member]

I can follow your argument about five smaller range scans and I can see that the clustering factor is relevant within each DAT_OFFER bucket. Then finally the light went off, seeing that your formula for the index skip scan essentially is the same as for the index range scan multiplied by the number of buckets... (so just needed the additional nudge)

As a side note, it would be great if you have any good link to documentation providing insight into the CBO calculations and could share this. Maybe I didn't search properly, but I couldn't find anything specific in Oracle's documentation or metalink. I'm only aware of SAP documentation like OSS note [750631 - Approximations for cost calculation of the CBO|https://service.sap.com/sap/support/notes/750631]. I must admit though that I never searched intensively, because I simply assumed that the nitty-gritty details are secret information that database vendors don't like to share to retain the edge over their competitors...

[former_member192616]

Hi Rui,

i double checked with a colleague:

I didn't pay attention to the brackets yesterday night:

Index Range Scan+ Table Access by Index ROWID:

(BLEVEL(index) + LEAF_BLOCKS(index) * FF1 + CLUSTERING_FACTOR(index) * FF2) * OPTIMIZER_INDEX_COST_ADJ / 100

2 + (1163 * 0,125 + 42840 * 0,125) * 20 / 100

2 + (1163 * 0,125 + 42840 * 0,125) * 20 / 100

2 + (145 + 5355) * 0,2

2 + 5500 * 0,2

2 + 1100 = 1102

Index SkipScan:

max(NUM_DISTINCT(skipped_columns)) * (BLEVEL(index) + LEAF_BLOCKS(index) * FF1 + CLUSTERING_FACTOR(index) * FF2) * OPTIMIZER_INDEX_COST_ADJ / 100

390 * 2 + (807 * 0,125 + 17559 * 0,125) * 20 / 100

780 + (101 + 2195) * 0,2

780 + 2296 * 0,2

780 + 459 = 1239

But we still see a higher cost in the formula then in the execution plan.

Regarding the formulas: Since ORACLE does not divulge the precise operation of the CBO these

formulas are reverse engineered by my colleague. They are a rough approach, a rule of thumb.

We use them from time to time in order to get a better understanding of the CBO in many cases

they work quite well and can explain the behaviour clearly in this case the skip scan calculation

is obviously not ok. The optimizer obviously does assume less costs than the assumed 2 per distinct

key of the skipped column probably because the likeliness of the branch blocks being cached.

However: The clustering factor seems to be the cause for choosing the skip scan. Reorganizing

the table with the order that reflects the primary key will reduce the clustering factor of the primary

key and the primary key should be chosen.

Regarding your SQL plus tests:

The plan with more buffer gets shows faster execution times? what about disk reads of the two?

Was everything in the cache? Normally: more ressource consumption (e.g. buffer gets) results in

higher run times (more cpu usage for latches, buffer accesses, ...)

Kind regards,

Hermann

[former_member192616]

Hi Harald,

> As a side note, it would be great if you have any good link to documentation providing insight into the

> CBO calculations and could share this.

[Cost-Based ORACLE|http://www.amazon.com/Cost-Based-Oracle-Fundamentals-Experts-Voice/dp/1590596366/ref=sr_1_1?ie=UTF8&s=books&qid=1272877828&sr=8-1] is an interesting read on the topic.

In Metalink bug descriptions can contain interesting details as well... but finding and reading them is very

time consuming... .

Regarding OSS note [750631 - Approximations for cost calculation of the CBO|https://service.sap.com/sap/support/notes/750631] this part is important:

Below, we have indicated how the CBO makes rough cost calculations.

However, since Oracle does not divulge the precise operation of the CBO,

and there are also some release-dependent differences, we make no claims

that the following information is either accurate or complete.

If i remember right (read the book a few years ago) the same is true for the book. There are so many details and changes

between releases and patchlevels and ... .

Kind regards,

Hermann

[Former Member]

Thanks a lot for the quick and detailed reply. With books I always fear that information becomes outdated too quickly and in Metalink most bugs that seemed interesting didn't seem to be public...

So I guess for now my conclusion is to have a rough (or even better - detailed) understanding how the database stores and retrieves the data. Details on CBO calculations are interesting, but in the end we probably anyhow only fiddle with statistics or push the CBO with hints in more favorable directions. Oh well, in some cases maybe apply some patches...

[former_member192616]

Hi Harald,

>With books I always fear that information becomes outdated too quickly

Especially for such topics, you are right. There are lots of (online) updates

for the book (http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html)

Another information source is the optimizer trace (event 10053). Just google

for ORACLE and 10053. This guy http://www.centrexcc.com/ has some nice

information in his papers as well.

> So I guess for now my conclusion is to have a rough (or even better - detailed) understanding

> how the database stores and retrieves the data.

That is more important to know i agree.

> Details on CBO calculations are interesting, but in the end we probably anyhow only

> fiddle with statistics or push the CBO with hints in more favorable directions.

And in the end you are right. Looking at the original post. It is interesting if we understood

why the optimizer picked the index skip scan over the index range scan. But then what?

We have to compare the real execution times and ressource consumption and depending

on the outcome... hint, patch the statistics, reorganize the table or in case of bugs apply patches

if available. And this could of course be done without knowing the details or understanding

precisely why the optimizer has choosen something different.

Kind regards,

Hermann

[former_member182566]

Hi Hermann, Hi Harald,

Thanks a lot for all the answers. I am sorry I haven't kept my own thread alive, but I have been buried by other things these last couple of days and I needed some time to digest all your information and the formulas.

Some further inputs:

1) Ok, I understand rebuilding the index cannot change its clustering factor. I suppose the index statistcs were some days old, so that's why they changed after rebuilding.

2) I can confirm optimizer_index_cost_adj = 20

3) The statistics and the explain plans I showed were both after rebuilding the indexes. They have slightly changed meanwhile, but not enough to change the conclusions. With the current values I would have:

for the range scan: 2 + (1216 * 0,125 + 43698 * 0,125) * 20 / 100 = 1125 (estimated 1125 - hurray!)

for the skip scan: 392 * 2 + (896 * 0,125 + 17842 * 0,125) * 20 / 100 = 1252 (estimated 527)

(optimizer_index_cost_adj is not supposed to affect the cost of the skipped columns, right?)

4) In sql*plus I am using the following query with autotrace on (the count with a column not in any index guarantees the explain plan is similar):

SELECT count(distinct iniusr) FROM zfieligiblercvbl WHERE mandt = '121' and bukrs = 'DE66'

1st execution: 7741 consistent gets, 4039 physical reads (15.01 secs)

From 2nd execution 7741 consistent gets, 0 physical reads (0.05 secs)

With hint:

SELECT
/*+ INDEX (ZFIELIGIBLERCVBL "ZFIELIGIBLERCVBL~0") */ 
  count(distinct iniusr) FROM  sapr3.zfieligiblercvbl WHERE mandt = '121' and bukrs = 'DE66'

1st execution: 19573 consistent gets, 476 physical reads (1.00 secs)

From 2nd execution: 19573 consistent gets, 0 physical reads (0.04 sec)

I will try later running with the hint first to see how execution times compare.

5) I am not a DBA (that is probably obvious). Is it usual for customers to regularly physically sort the table by the most often used index? If so, how is this done?

Thanks again for the excellent info,

Rui Dantas

[former_member192616]

Hi Rui,

let me start with the easy ones:

> 3) The statistics and the explain plans I showed were both after rebuilding the indexes. They have slightly changed meanwhile, but not enough to change the conclusions. With the current values I would have:

>

> for the range scan: 2 + (1216 * 0,125 + 43698 * 0,125) * 20 / 100 = 1125 (estimated 1125 - hurray!)

> for the skip scan: 392 * 2 + (896 * 0,125 + 17842 * 0,125) * 20 / 100 = 1252 (estimated 527)

> (optimizer_index_cost_adj is not supposed to affect the cost of the skipped columns, right?)

oica (optimizer_index_cost_adj) influences the range scan and a skip scan is like multiple range scans here.

As per my colleague the estimated factor of 2 per distinct value of the skipped column in the formula seems to be to high here, in other cases it was ok. So there may be other factors (optimizer_index_caching ... we don't know (yet)...)

> 5) I am not a DBA (that is probably obvious). Is it usual for customers to regularly physically sort the table by the most often used index?

If we have a siginificant impact in terms of disc reads with a proven root cause due to the clustering factor we recommend sorted reorganizations in order to improve performance. We have seen cases showing quite a good improvement with this measure. Since the table soon or later come back to un unsorted state the reorganizatiions have to be done regularly as a maintanance task. e.g. 2 times a year or something like that.

> If so, how is this done?

check out SAP Note: 85558 Tables with data sorted by index

Kind regards,

Hermann

[former_member182566]

>

> > for the skip scan: 392 * 2 + (896 * 0,125 + 17842 * 0,125) * 20 / 100 = 1252 (estimated 527)
> >     (optimizer_index_cost_adj is not supposed to affect the cost of the skipped columns, right?)
> 
> oica (optimizer_index_cost_adj) influences the range scan and a skip scan is like multiple range scans here.
> 
> As per my colleague the estimated factor of 2 per distinct value of the skipped column in the formula seems to be to high here, in other cases it was ok. So there may be other factors (optimizer_index_caching ... we don't know (yet)...)
> 

What I meant was that we are multiplying the optimizer_index_cost_adj (20/100) by the right part of the expression (the columns that are in fact used) but not to the left part (the columns that are skipped). Just wanted to make sure that that's the way it should be.

[former_member192616]

Hi Rui,

for 4 we have to gather more information:

In SAP note 1438410 SQL: Script collection for Oracle you can find some nice SQL scripts.

Run these 2 for your SQL_IDs

SQL_SQL_ID_KeyFigures.txt

SQL_SQL_ID_DataCollector.txt (1257075 SQL_ID Data Collector (Oracle 10.2.0.2 or higher))

In the output of the two scrips we may find more details for the faster run time (when we have more ressource consumption).

and what if you simplify them and repeat them more often and buil averages?

SELECT iniusr...

instead of

SELECT count(distinct iniusr) ...

Kind regards,

Hermann

Edited by: Hermann Gahm on May 5, 2010 2:07 PM