jump to navigation

Clustering Factor Calculation Improvement Part II (Blocks On Blocks) May 14, 2013

Posted by Richard Foote in 11g, Clustering Factor, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes.
6 comments

My previous post on the new TABLE_CACHED_BLOCKS statistics gathering preference certainly generated some interest :) My blog hits for the week have gone off the charts !!

One of the concerns raised by this new capability was that setting such a preference might result in really unrealistic and inaccurate Clustering Factor (CF) values, especially for those tables that truly have appalling CFs. Although there are certainly some dangers, Oracle has limited the possible “abuse” by ensuring TABLE_CACHED_BLOCKS can only be set to a maximum of 255. This means Oracle will only ignore a maximum of 255 table blocks that have recently been accessed during the CF calculation. For larger tables with truly randomised data patterns, not even the maximum 255 setting if utilised will make an appreciable difference to the final CF.

A couple of examples to demonstrate.

The first table is a relatively “large” table that has a DOB column that is effectively randomised throughout the table. There are approximately 20,000 different DOB values in a 2 million row table (so each DOB occurs approximately 100 times, give or take).

SQL> create table major_tom (id number, DOB date, text varchar2(30));

Table created.

SQL> insert into major_tom select rownum,  sysdate-trunc(dbms_random.value(0, 20000)), 'DAVID BOWIE' from dual connectby level <= 2000000;

2000000 rows created.

SQL> commit;

Commit complete.

Let’s now create an index on this DOB column and have a look at the CF:

SQL> create index major_tom_dob_i on major_tom(dob);

Index created.

SQL> EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'MAJOR_TOM', estimate_percent=> null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  FROM user_tables t, user_indexes i
3  WHERE t.table_name = i.table_name AND i.index_name='MAJOR_TOM_DOB_I';

TABLE_NAME   INDEX_NAME          BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ --------------- ---------- ---------- -----------------
MAJOR_TOM    MAJOR_TOM_DOB_I       9077    2000000           1988164

So at 1,988,164, the CF is terrible. This is as expected as the DOB values are all randomised throughout the table. The index is not being used as we had hope (naively) so let’s use the new TABLE_CACHED_BLOCKS preference to now improve the calculated CF by setting it to the maximum 255 setting and recalculate the index statistics:

SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'MAJOR_TOM',
pname=>'TABLE_CACHED_BLOCKS', pvalue=>255);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'MAJOR_TOM_DOB_I', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  FROM user_tables t, user_indexes i
3  WHERE t.table_name = i.table_name AND i.index_name='MAJOR_TOM_DOB_I';

TABLE_NAME   INDEX_NAME          BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ --------------- ---------- ---------- -----------------
MAJOR_TOM    MAJOR_TOM_DOB_I       9077    2000000           1941946

We notice that although the CF has improved marginally, at whopping 1,941,946 it’s still terrible and has made no real appreciable difference. Why ?

Well let’s do some basic maths here. There are 9077 blocks in the table and the next DOB referenced in the index can potentially be in any one of them. Therefore, the chances of the next DOB being in one of the 255 previously accessed table blocks is only 255/9077 x 100 = approximately 2.8%. So in only 2.8% of the time is the CF likely to not be incremented and so the CF is only likely to drop by around this 2.8% amount.

Let’s check. (1988164 – 1941946)/1988164 x 100  indeed does equal approximately 2.8%.

So statistically with such a poor CF on such a “large” table, to limit the CF calculation if any of the last 255 table blocks are referenced is only going to improve things by 2.8% on average. Effectively of no real use at all.

Another example now, but this time with a CODE column with just 100 distinct values that are randomly distributed throughout another reasonable “large” 2 million row table. For those mathematically challenged, that means each value occurs approximately 20,000 times, give or take:

SQL> create table ziggy (id number, code number, text varchar2(30));

Table created.

SQL> insert into ziggy select rownum,  trunc(dbms_random.value(0, 100)), 'DAVID
BOWIE' from dual connect by level <= 2000000;

2000000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy_code_i on ziggy(code);

Index created.

SQL> EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'ZIGGY', estimate_percent=> null, cascade=> true,
method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  FROM user_tables t, user_indexes i
3  WHERE t.table_name = i.table_name AND i.index_name='ZIGGY_CODE_I';

TABLE_NAME   INDEX_NAME          BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ --------------- ---------- ---------- -----------------
ZIGGY        ZIGGY_CODE_I          7048    2000000            662962

So at 662,962 it’s what I would describe as a “poor to average” CF. It’s not particularly great with there being just  7,048 table blocks but it’s still some distance from the 2,000,000 row value.

The index is not being used in SQL statements as we (naively) wish, so let’s try and improve things by lowering the index CF by setting the new TABLE_CACHED_BLOCKS preference to the maximum 255 setting:

SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'ZIGGY',
pname=>'TABLE_CACHED_BLOCKS', pvalue=>255);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'ZIGGY_CODE_I',
estimate_percent=>null);

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  FROM user_tables t, user_indexes i
3  WHERE t.table_name = i.table_name AND i.index_name='ZIGGY_CODE_I';

TABLE_NAME   INDEX_NAME          BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ --------------- ---------- ---------- -----------------
ZIGGY        ZIGGY_CODE_I          7048    2000000            662962

We notice to our great disappointment (well, not really) that the CF remains completely unchanged at 662,962 !! Why ?

Again, let’s do some basic maths and consider the data distribution.

The table has some 7048 blocks but each distinct CODE value has some 20,000 occurrences on average. Therefore, each value is going to be found 20000/7048 = roughly 2 to 3 times per block. As the index is in CODE order and for each CODE in rowid order, the CF is going to increment for each CODE value for each distinct block we visit. We will therefore only go back to a previously visited table block (except for the 2 to 3 visits to the current block) when the CODE value changes but this will take us all the way back to the first block which is always going to be some 7047 blocks away from the current one. As 7047 is much greater than the 255 the CF calculation will only cater for, the CF is going to remain unchanged from the default calculation as a result.

And this is all as it should be, as the fundamental CF is indeed poor for these scenarios and even going back the maximum 255 data blocks will not reduce appreciably the manner in which the CF is calculated.

Of course, if there was no limit, then a setting of TABLE_CACHED_BLOCKS  of say 7100 would enable the CF to be recalculated as being perfect in the above scenario, which would indeed be a concern. But 255 is the limit and so limits the potential “damaged” that can be done.

More on all this to come :)

Important !! Clustering Factor Calculation Improvement (Fix You) May 8, 2013

Posted by Richard Foote in 11g, ASSM, CBO, Clustering Factor, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes.
41 comments

Believe me, this article is worth reading :)

I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now be calculated. Many thanks to Martin Decker for pointing this out to me.

As anyone who has attended my Index Seminars will know, the CF of an index is one of the most important statistics used by the Cost Based Optimizer (CBO) in determining the most efficient execution plan. As such, it has always been an issue for me that the manner in which the CF is calculated has been so flawed.

Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the table block being referenced differs from that of the previous index entry, the CF is incremented. If the table block being referenced is the same as the previous index entry, the CF is not incremented. So the CF gives an indication of how well ordered the data in the table is in relation to the index entries (which are always sorted and stored in the order of the index entries). The better (lower) the CF, the more efficient it would be to use the index as less table blocks would need to be accessed to retrieve the necessary data via the index.

However, there’s a basic flaw here. The CF calculation doesn’t take into consideration the fact the referenced table block, although maybe different from the previous one index entry, might already have recently been accessed. As such, during an index scan, the table block being accessed is almost certainly still cached in the buffer cache from the previous access, thereby not reducing the effectiveness of the index in any appreciable manner. A classic example of this would be a table with a few freelists. Although the data being inserted is not ordered precisely within the same data blocks, the data might actually be very well clustered within only a few blocks of each other.

Picture a table with 100 rows being inserted by 2 sessions simultaneously, each inserting 50 rows based on an ordered sequence. With one freelist, the data is basically inserted in one block first and then once full a second table block. The data is therefore perfectly ordered/clustered and the CF will evaluate to a value of 2 on such an indexed column. But with 2 freelists, one session could insert data into one block while the other session inserts into a second block, with the ordered sequenced values being randomly distributed among the 2 blocks.  The CF could now potentially evaluate to a value of 100 as the rows are jumbled or “toggled” across the two blocks. This is a much much worse value (2 vs. 100) that can adversely impact the CBO calculations, although the efficiency of such an index is really almost identical as both table blocks are certain to be cached during an index scan regardless.

This is also a very common scenario with Automatic Segment Space Management (ASSM) tablespaces as I’ve discussed previously, which of course is now the default these days.

OK, let’s look at an example scenario. I’ll begin by creating a simple little table, an ordered sequence and a procedure that inserts 100,000 rows into the table:


SQL> create table bowie (id number, text varchar2(30));

Table created.

SQL> create sequence bowie_seq order;

Sequence created.

SQL> CREATE OR REPLACE PROCEDURE bowie_proc AS

2  BEGIN

3     FOR i IN 1..100000 LOOP

4         INSERT INTO bowie VALUES (bowie_seq.NEXTVAL, 'ZIGGY STARDUST');

5         COMMIT;

6     END LOOP;

7  END;

8  /

Procedure created.

We note the table lives in an ASSM tablespace:


SQL> select table_name, i.tablespace_name, segment_space_management

from dba_tables i, dba_tablespaces t   where i.tablespace_name = t.tablespace_name and table_name='BOWIE';

TABLE_NAME   TABLESPACE_NAME                SEGMEN

------------ ------------------------------ ------

BOWIE        USERS                          AUTO

We next have 3 different sessions that simultaneously run the procedure to load the table. Note that an ordered sequence is used which means the 3 sessions are randomly grabbing the next sequenced value to insert. The data though is basically being inserted in order of the ID column, it’s just that the data is being distributed across a few blocks as we go along the table, rather than strictly one block after the other.


SQL> exec bowie_proc

PL/SQL procedure successfully completed.

Let’s create an index on the ID (sequenced) column and collect fresh statistics:


SQL> create index bowie_id_i on bowie(id);

Index created.

SQL> EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE',      estimate_percent=> null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor

2  FROM user_tables t, user_indexes i

3  WHERE t.table_name = i.table_name AND i.index_name='BOWIE_ID_I';

TABLE_NAME   INDEX_NAME       BLOCKS   NUM_ROWS CLUSTERING_FACTOR

------------ ------------ ---------- ---------- -----------------

BOWIE        BOWIE_ID_I         1126     300000            241465

We notice that although the data in the table in reality is actually quite well clustered/ordered on the ID column, the actual CF of the index is not reflecting this. At a massive 241,465 it’s an extremely high (bad) CF, much closer in value to rows in the table than the number of table blocks, as the CF calculation keeps flipping back and forth between differing blocks. With such a high CF, the CBO is therefore going to cost an index scan accordingly:


SQL> select * from bowie where id between 42 and 429;

388 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1845943507

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |   389 |  7780 |   310   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| BOWIE |   389 |  7780 |   310   (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"<=429 AND "ID">=42)

Statistics

----------------------------------------------------------

0  recursive calls

1  db block gets

1093  consistent gets

0  physical reads

0  redo size

4084  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

388  rows processed

Even though only approx. 0.13% of rows are being accessed and more importantly a similar low percentage of table blocks, the CBO has determined that a Full Table Scan (FTS) is the cheaper alternative. This is an all too familiar scenario, all down to the fact the CF is not accurately reflecting the true clustering of the data and subsequent efficiency of the index.

Finally, at long last, there’s now an official fix for this !!

Bug 13262857 Enh: provide some control over DBMS_STATS index clustering factor computation INDEX describes this scenario and currently has available patches that can be applied on both Exadata databases and Oracle versions 11.1.0.7, 11.2.0.2 and 11.2.0.3. The patches (eg. Patch ID 15830250) describe the fix as addressing “Index Clustering Factor Computation Is Pessimistic“. I couldn’t have described it better myself :)

Once applied (the following demo is on a patched 11.2.0.3 database), there is a new statistics collection preference that can be defined, called TABLE_CACHED_BLOCKS. This basically sets the number of table blocks we can assume would already be cached when performing an index scan and can be ignored when incrementing the CF during statistics gathering. The default is 1 (i.e. as performed presently) but can be set up to be a value between 1 and 255, meaning during the collection of index statistics, it will not increment the CF if the table block being referenced by the current index entry has already been referenced by any of the prior 255 index entries (if set to 255). It basically sets the appropriate parameter in the sys_op_countchg function used to calculate the CF value during statistic gathering to not increment the CF if the current table block has already been accessed “x” index entries previously.

The TABLE_CACHED_BLOCKS preference can be set by either the DBMS_STATS.SET_TABLE_PREFS, DBMS_STATS.SET_SCHEMA_PREFS or DBMS_STATS.SET_DATABASE_PREFS procedures.

So let’s now change the TABLE_CACHED_BLOCKS preference for this table and re-calculate the index statistics:


SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE',

pname=>'TABLE_CACHED_BLOCKS', pvalue=>42);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor

2  FROM user_tables t, user_indexes i

3  WHERE t.table_name = i.table_name AND i.index_name='BOWIE_ID_I';

TABLE_NAME   INDEX_NAME       BLOCKS   NUM_ROWS CLUSTERING_FACTOR

------------ ------------ ---------- ---------- -----------------

BOWIE        BOWIE_ID_I         1126     300000              1035

We notice that the CF has now been significantly reduced (down from 241465 to just 1035), reflecting far more accurately the true clustering of the data when considering the actual effectiveness of using the index.

If we now run the same query as before:


SQL> select * from bowie where id between 42 and 429;

388 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3472402785

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |   389 |  7780 |     4   (0)|00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BOWIE      |   389 |  7780 |     4   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | BOWIE_ID_I |   389 |       |     2   (0)|00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID">=42 AND "ID"<=429)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

6  consistent gets

0  physical reads

0  redo size

9882  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

388  rows processed

We notice the index is now being selected by the CBO. At a cost of 4 (previously the cost was somewhat greater than the 310 cost of the FTS), this much more accurately reflects the true cost of using the index (notice only 6 consistent gets are performed).

Being able to now set the TABLE_CACHED_BLOCKS preference during statistics collection finally gives us a fully supported and easy method to collect more accurate CF statistics. This in turn can only lead to more informed and accurate decisions by the CBO and ultimately better performing applications. Although available right now via the back ported patches, this will no doubt all be fully documented once the 12c database is finally released.

I can’t recommend enough the use of this new capability :)

Concatenated Bitmap Indexes Part II (Everybody’s Got To Learn Sometime) May 12, 2010

Posted by Richard Foote in Bitmap Indexes, CBO, Concatenated Indexes, Oracle Cost Based Optimizer, Oracle Indexes.
2 comments

A basic little post to conclude this discussion.

The issues regarding whether to go for single column indexes vs. concatenated indexes are similar for Bitmap indexes as they are for B-Tree indexes.
 
It’s generally more efficient to access a concatenated index as it’s only the one index with less processing and less throwaway rowids/rows to contend with.  However it’s more flexible to have single column indexes, especially for Bitmap indexes that are kinda designed to be used concurrently, as concatenated indexes are heavily dependant on the leading column being known in queries.
 
If we look at the second table from Part I which had the concatenated index being significantly larger than the sum of the single column indexes, we notice that it can still have a part to play with the CBO. When we run a query that references both columns in predicates:

SQL> select * from bowie2 where id = 42 and code = 42;
 
100 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4165488265
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   100 |  1200 |    21   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | BOWIE2     |   100 |  1200 |    21   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BOWIE2_3_I |       |       |            |          |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ID"=42 AND "CODE"=42)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        103  consistent gets
         26  physical reads
          0  redo size
       3030  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

 
    

 
The CBO favours the concatenated index with the total number of consistent gets at 103. This despite the fact the concatenated index has some 10,000 distinct entries and is somewhat larger than the sum of the single column indexes. If we now drop the concatenated index and re-run the same query:
 
  
 

SQL> drop index bowie2_3_i;
 
Index dropped.
 
SQL> select * from bowie2 where id = 42 and code = 42;
 
100 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2338088592
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   100 |  1200 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | BOWIE2     |   100 |  1200 |    22   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|   3 |    BITMAP AND                |            |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| BOWIE2_1_I |       |       |            |          |
|*  5 |     BITMAP INDEX SINGLE VALUE| BOWIE2_2_I |       |       |            |          |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("ID"=42)
   5 - access("CODE"=42)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
       3030  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
 

 

The CBO can use a BITMAP AND operation by accessing and ANDing the associated bitmap columns from both single column indexes. However this is little less efficient than using the single concatenated index (105 vs 103 consistent gets) even though the concatenated index is somewhat larger than the other 2 indexes combined as Oracle needs to access and process two Bitmap index segments, not one. However as is very common, note in both examples, most of the consistent gets are in relation to getting the 100 rows out of the table, not so much with regard to the indexes themselves.
 
However, it we just reference the CODE column in a predicate:

SQL> select * from bowie2 where code = 42;

10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2522233487

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            | 10000 |   117K|   489   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID | BOWIE2     | 10000 |   117K|   489   (1)| 00:00:03 |
|   2 |   BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BOWIE2_2_I |       |       |            |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CODE"=42)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2861  consistent gets
          0  physical reads
          0  redo size
     257130  bytes sent via SQL*Net to client
       7742  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
 

   

Providing it’s cheaper than other alternatives, the single column bitmap index can be considered and used by the CBO. However, if we only had the previous concatenated index:

SQL> drop index bowie2_1_i;

Index dropped.

SQL> drop index bowie2_2_i;

Index dropped.

SQL> create bitmap index bowie2_3_i on bowie2(id,code) pctfree 0;

Index created.

SQL> select * from bowie2 where code = 42;

10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1495904576

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   117K|   497   (6)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| BOWIE2 | 10000 |   117K|   497   (6)| 00:00:03 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CODE"=42)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3011  consistent gets
       2343  physical reads
          0  redo size
     165134  bytes sent via SQL*Net to client
       7742  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

 

As the leading column is not specified, the concatenated Bitmap index is ineffective and the CBO decides to use a FTS. So it’s a similar scenario as with B-tree indexes.

A concatenated Bitmap index can potentially use less or more space than corresponding single column Bitmap indexes, it depends on the number of index entries that are derived and the distribution of the data with the table. However regardless, a concatenated Bitmap index can still be a viable alternative if at least the leading column is specified and be the more efficient option if all columns are generally specified, even if the overall size of the index is somewhat greater than the sum of the alternative single column Bitmap indexes. Then again, it’s less flexible and may not be considered if the leading column is not referenced.

If columns are generally all specified in SQL predicates, then combining them all in a single concatenated Bitmap index is a viable option. It all depends. Understanding why it depends is of course important in making the correct decision with regard which way to go with Bitmap indexes …

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo) February 16, 2010

Posted by Richard Foote in CBO, Index Access Path, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Myths.
111 comments

I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?
 
By no changes, it means that there have been no alterations to any segments, no new indexes have been added, no changes associated  bind peeking (indeed, there may not even be any bind variables), no parameters changes, no new patches or upgrades, no new outlines or profiles, no new system stats and perhaps most prevalent of all, no changes to any CBO statistics.
 
The DBA hasn’t touched a thing and yet suddenly, for no apparent reason, execution plans suddenly change and (say) an inappropriate index is suddenly used and causes performance degradation.
 
How can this be possible ?
 
There are two key points I want to emphasise.
 
The first is that there’s a common misperception that if no new statistics are gathered (and assuming nothing else is altered in the database), that execution plans must always remain the same. That by not collecting statistics, one somehow can ensure and guarantee the database will simply perform in the same manner and generate the same execution plans.
 
This is fundamentally not true. In fact, quite the opposite can be true. One might need to collect fresh statistics to make sure vital execution plans don’t change. It’s the act of not refreshing statistics that can cause execution plans to suddenly change.
 
The second point is that when one goes through all the things that might have changed in the database, two important aspects are often overlooked.
 
The first thing that does usually change within most databases is the actual data within the database. Those damn users log on and keep adding new data and modifying data all the time. It might not be a database change as such but the fact the data changes within a database is a critical change that can directly influence CBO behaviour. When pointing the finger at what might have caused an execution plan to change, many simply ignore the fact the data is constantly changing in the background.
 
The other aspect that always changes is time. People have tried but it’s very difficult to stop time. When things worked well, it was at a different point in time than now when things have suddenly gone wrong.
 
So some things do change that are not in direct control of the DBA.
 
But if we don’t collect fresh statistics, even though the data might have changed, won’t those data changes be effectively invisible to the CBO? Won’t the statistics not reflect any possible data changes and if the CBO doesn’t think the data has changed, doesn’t that mean it can’t suddenly change how it determines an execution plan ?
 
Not true. It’s quite possible that because the statistics haven’t changed, the CBO is forced into makings changes in how it costs and determines an execution plan.
 
A very simple example follows, a classic case of why not refreshing statistics has caused the CBO to suddenly change an execution plan for no apparent reason.
 
I’ll begin by creating a simple little table and populate it with approximately 5 years worth of data.

 
SQL> create table muse (id number, muse_date date, name varchar2(10));
 
Table created.
 
SQL> declare
  2  v_count  number;
  3  begin
  4  v_count:=0;
  5  for i in 1..1830 loop
  6     for j in 1..1000 loop
  7     v_count:= v_count+1;
  8     insert into muse values (v_count, sysdate-i, 'MUSE');
  9     end loop;
 10  end loop;
 11  commit;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
SQL> create index muse_i on muse(muse_date);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'MUSE', casca
de=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.

So the procedure basically populates the table, setting the MUSE_DATE column with approximately 5 years worth of data, with 1000 rows for each day so the data is evenly distributed across those 5 years.

Note that I’ve collected statistics on the table and index and they’re fully up to date.

The following query is a typical query in our application, where we’re only interested in looking at the previous year’s worth of data. It simply selects all data that is only a year old. This is a query that’s run all the time and only looks at a “moving window” of data, that being just those rows that were inserted up to a year ago.


 
SQL> select * from muse where muse_date > sysdate - 365;
 
364000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2738706195
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   363K|  6390K|  1330  (11)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| MUSE |   363K|  6390K|  1330  (11)| 00:00:07 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("MUSE_DATE">SYSDATE@!-365)
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5992  consistent gets
       5912  physical reads
          0  redo size
    3638996  bytes sent via SQL*Net to client
       1188  bytes received via SQL*Net from client
         74  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     364000  rows processed
 

Notice how the CBO has decided to use a Full Table Scan (FTS) as a year is quite a chunk of the table and is more effectively accessed in this manner. Notice also how the CBO has got the cardinality spot on and has correctly predicted the number of rows to be returned. If the CBO gets the selectivity and so the cardinality of the query correct, we have some confidence that it has indeed come up with the most efficient execution plan. Indeed, the users are perfectly happy with the performance of the query, the DBAs are happy and because we don’t really want to risk the CBO suddenly changing things, we decide to not collect statistics on this table any more.

However, more data is pumped into the table each and every day by the end-users.

The following procedure will add another years worth of data into the table to simulate how the table will be populated in a year’s time …

SQL> declare
  2  v_count  number;
  3  begin
  4  v_count:=1830000;
  5  for i in 1..365 loop
  6     for j in 1..1000 loop
  7     v_count:= v_count+1;
  8     insert into muse values (v_count, sysdate+i, 'MUSE');
  9     end loop;
 10  end loop;
 11  commit;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.

Note that we have NOT collected any new statistics.

OK, let’s now fast track ourselves one year into the future and run the same query again. Note in a year’s time, we will be 365 days past the current sysdate. So we’ll mimic running the identical query by simply adding 365 days to the sysdate and again querying for the latest year’s worth of data:


 
SQL> select * from muse where muse_date > (sysdate+365) - 365;
 
365000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1682432684
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   944 | 16992 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MUSE   |   944 | 16992 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MUSE_I |   944 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MUSE_DATE">SYSDATE@!+365-365)
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4005  consistent gets
       1301  physical reads
     134192  redo size
    4024147  bytes sent via SQL*Net to client
       1188  bytes received via SQL*Net from client
         74  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365000  rows processed
 

We notice that the execution plan has now changed !!

It’s now suddenly starting to use an index where previously it was using a FTS. Notice also that the CBO has got the cardinalty estimate way wrong, predicting only 944 rows will be returned. Instead of estimating it will get a year’s worth of data, the CBO is estimating only approximately 1 days worth of data or the selectivity based on one distinct value. If the CBO get’s this so terribly wrong, it’s a good chance it has also got the execution plan terribly wrong as well.

The query is effectively the same query that would be run in a year’s time, the statistics have not been changed and yet the execution plan has indeed changed. The CBO suddenly using this index may be a terrible thing, resulting in a really inefficient execution plan and a massive increase in LIOs.

Why has the plan changed when the statistics have not ?

The key issue here is that the CBO thinks the maximum date in the table was from a year ago when the statistics were last calculated. However, the query is attempting to select data that is beyond the range of values known to the CBO. How can it now know the estimated cardinality of the query, the number of expected rows to be returned when we’re only interested in rows that are beyond its maximum known range of data ?

The answer is that it can’t. Not accurately anyway.

The CBO has to guess and depending on the version of Oracle and the type of query being parsed, it can guess in a number of different ways. Because the query is effectively after data that is greater than the maximum known value, the CBO is basically “guessing” there will only be a days worth of data greater than its maximum known value, not the full years worth that’s really in the table. The CBO having to guess is not a good thing, especially when it’s more than likely to get the guess hopelessly wrong.

Note this change will have occurred suddenly one day into the future when the CBO  starts to consider there are so few days worth returning that the index suddenly becomes the best and cheapest option.

How do we fix this inefficient execution plan ?

Rather than having the CBO guess how many rows might be returned, let it actually know. Simply collect fresh statistics and let the CBO know that we actually have a full year’s worth of data since the statistics were previously collected:

SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

 

If we run the same query again now …


 
SQL> select * from muse where muse_date > (sysdate+365) - 365;
 
365000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2738706195
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   364K|  6413K|  1652  (14)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| MUSE |   364K|  6413K|  1652  (14)| 00:00:09 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("MUSE_DATE">SYSDATE@!+365-365)
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7205  consistent gets
       6709  physical reads
          0  redo size
    4024147  bytes sent via SQL*Net to client
       1188  bytes received via SQL*Net from client
         74  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365000  rows processed

 

We now notice the CBO has got the cardinality spot on again and choses to use the efficient FTS.

So yes, an execution plan can change even if we don’t make any changes to the database, including not collecting fresh statistics. If you think by not collecting statistics, things will simply remain the same, one day when you least expect it, things might suddenly go terribly wrong.

Solving such issues can be extremely different if you try to do so by looking at what might have changed, especially if you don’t know what you’re looking for …

The CPU Costing Model: A Few Thoughts Part V (Reality) January 13, 2010

Posted by Richard Foote in CBO, Multiblock Reads, Oracle Cost Based Optimizer, Richard's Musings, System Statistics.
5 comments

There’s plenty more I could talk about regarding the CBO CPU costing model and system statistics but I’ll make this my final little comment on this subject for now.
 
As previously discussed, the CPU costing model basically takes the time it takes to perform the all necessary I/O related activities and all the time it takes to perform all necessary CPU related activities and adds them together to get the overall time to complete a task. The CBO then takes this total and divides it by the average time to perform a single block I/O so that it expresses the overall costs in units of single block I/Os.
 
There are two advantages with expressing CBO costs in this manner.
 
Firstly, it makes the move from the old I/O costing model a little easier in that the “units” of cost under both CBO costing models is very similar.
 
With the I/O costing model, the unit of cost was also basically the number of I/Os. It’s just that the CBO made no (automatic) distinction between the I/O costs associated with single and multiblock reads. The cost was simply the expected total number of I/Os for a given execution plan, with single block and multiblock I/Os being consider the same (unless the optimiser_index_cost_adj parameter kicked in).
 
With the CPU costing modelling, the costs are expressed specifically in units of single block I/Os. However, the CBO automatically takes into consideration and differentiates the relative costs associated with multiblock I/Os (and CPU operations) and incorporates them automatically into the final cost.

The other nice advantage is that one can use the actual cost values as an indication of how long an operation or execution plan is likely to take. The overall execution times of the plan are divided by the average time of a single block I/O when using the CPU costing formula. Therefore by multiplying these cost values out again by the average time of a single block I/O (SREADTIM system statistic), one can have an indicative idea of the overall expected execution time.
 
The overall execution times as estimated by the CBO using the CPU costing model is therefore basically = cost of execution plan multiplied by SREADTIM system statistic.
 
Using my previous example with the FTS where the overall cost of the execution plan was 70, and the SREADTIM system statistic was 5:
 
the overall execution time as estimated by the CBO is approximately 70 x 5 = 350 ms.
 
Now this of course is only an indicative value. As all system related statistics are simply averages, there could obviously be discrepancies with how long specific I/Os take to actually perform, the size and number of specific multiblock read operations, etc. There may also be caching characteristics of objects that may influence the actual number of physical reads and associated wait times, it doesn’t take into consideration time taken to actually return data to the “client”, etc. etc. etc.
 
However, it provides one with a rough “ballpark figure”. If the actual executions times in the above example were (say) 20 seconds, then it’s a strong indication that the CBO may have got it wrong, that it may have calculated the wrong cost and maybe as a result the wrong execution plan. Somewhere, something such as the segment statistics, the system statistics, optimizer parameters, etc. may be inaccurate and is causing the CBO to get its costings incorrect.
 
The CBO cost value doesn’t compare well to reality and so is perhaps worthy of further investigation.
 
The cost values associated with CPU costing model is not some random, ambiguous, mysterious number but a value that can often be derived and which can be most useful in determining and resolving problematic SQL statements and execution plans.

The CPU Costing Model: A Few Thoughts Part IV (Map of the Problematique) January 7, 2010

Posted by Richard Foote in CBO, Oracle Cost Based Optimizer, System Statistics.
10 comments

It’s called the CPU Costing model because among other things, it includes the time associated with performing CPU operations.
 
The CPU Costing model formula once again:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
 

So the portion detailing the sum of all required CPU cycles divided by the CPU cycles per second can potentially contribute a significant proportion of the overall costs.
 
When I previously discussed the costs associated with the CPU model between using an index and a FTS, the FTS example I used had an overall cost of 70 but I calculated that the I/O component costs were only 67. Therefore the costs directly related to CPU operations with the FTS example was 3.
 
However, these CPU specific costs in this example may vary from database to database, although the FTS might be identical as might the required CPU cycles. However, a variable in all this is the CPU cycles per second system statistic (CPUSPEED) associated with a particular database environment.
 
Obviously, the faster the CPUs, the quicker it can perform the necessary CPU operations associated with the FTS (or any operation for that matter). Conversely, the slower the CPUs, the longer it will take to complete the necessary CPU related operations. The CPU costing model formula automatically takes all this into consideration.
 
In the previous example, the CPUSPEED system statistic was 1745.
 
Let’s now run the identical FTS but this time with a faster and a slower CPU and see how this might adjust the overall related costs when using the CPU costing model.
 
One can simulate a “faster” CPU by simply adjusting the CPUSPEED system statistic. Let’s make the CPUs appear 10 times faster:
 

SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>17450);
 
PL/SQL procedure successfully completed.
 

OK, let’s now see how this impacts the cost of the FTS:
 
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
1000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 910563088
 
——————————————————————-
|Id|Operation         |Name       |Rows|Bytes|Cost (%CPU)|Time    |
——————————————————————-
| 0|SELECT STATEMENT  |           |1000|18000|   67   (0)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000|   67   (0)|00:00:01|
——————————————————————-
 

We notice that the overall cost has reduced from 70 down to 67. The cost of 3 that was previously attributed to just the CPU related costs have all disappeared and the costs are now just the 67 in relation to the I/O component.
 
The CPU is now so fast that it can effectively perform all the necessary operations in a negligible amount of time. An even faster CPU will not further improve the costs associated with this FTS as the costs now only include the I/O related components.

The (%CPU) value of (0) gives us this information if you didn’t follow how I derived the I/O cost of 67 in my previous post.

If we go the other way and now make the CPU about 1/10 the speed of the original example:
 
 
SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>175);
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
1000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 910563088
 
——————————————————————-
|Id|Operation         |Name       |Rows|Bytes|Cost (%CPU)|Time    |
——————————————————————-
| 0|SELECT STATEMENT  |           |1000|18000|   93  (28)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000|   93  (28)|00:00:01|
——————————————————————-
 

We now notice the overall costs have jumped up considerably up from 70 up 93.
 
The costs associated directly with CPU activities have now increased up from 3 to 26. The CPU component is in the ballpark of 10 times as expensive/significant when you take into account rounding errors (the original 3 value was rounded accordingly). Remember also that these figures are times expressed in units of time it takes to perform a single block I/O.
 
The CPUs are now so slow that it takes a considerable amount of time to complete all the required CPU operations.
 
Note that the (%CPU) value is now a significant (28%) of the overall costs as derived from the following formula:

round(cpu related cost/total cost) x 100 = round(26/93 x 100) = 28.
 
So having a faster (or possibly slower) CPU when performing a hardware upgrade/change can result in potentially different execution plan costings (and as such different execution plans) when using the CPU CBO costing model.
 
It’s called the CPU costing model for a reason and as one would indeed hope, the speed of said CPU(s) can directly impact the associated costs and decisions made by the CBO.

The CPU Costing Model – A Few Thoughts Part II December 14, 2009

Posted by Richard Foote in CBO, OPTIMIZER_INDEX_COST_ADJ, Oracle Cost Based Optimizer, Oracle Indexes, System Statistics.
8 comments

As previously discussed, the formula used by the CBO using the CPU costing model is basically:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
 

When determining the multiblock I/Os costs associated with a FTS, the CBO basically:
 
 - determines the number of multiblock operations (blocks in dba_tables / mbrc system statistic)
 
 - then multiplies this out by the average wait time of a multiblock I/O (mreadtim system statistic)
 
to determine the total wait time for all expected multiblock read operations.
 
  -This total wait time of all multiblock read operations is then finally divided by the average wait time for a single block I/O (sreadtim system statistic) to express the final cost in units of single block I/Os.
 
 
Remember these average wait times associated with both single and multiblock I/Os are actual wait times for these events as experienced in the specific database environment and captured during the collection of system statistics.
 
Therefore, the formula automatically takes into consideration and incorporates into the calculations any discrepancies and differences in wait times between a single and a multiblock I/O.
 
For example, if a multiblock I/O actually takes (say) 10ms to perform on average, while a single block I/O only takes (say) 5ms to perform on average, then the formula will automatically make the costs of performing multiblock reads to be twice as expensive as the costs associated with performing the single block reads as performed by index scans.
 
These discrepancies in costs and trying to make a level playing field when comparing the multiblock I/Os costs associated with FTS vs. the single block I/Os costs associated with index scan is precisely what the optimizer_index_cost_adj parameter was designed to addressed.
 
Rather than treat both types of I/Os as being the same, which is the default behaviour with the I/O costing model, the optimizer_index_cost_adj parameter is designed to adjust the single block read costs to ensure that they are indeed costed as being (say) 1/2 the cost as that of a typical multiblock I/O.
 
However, when using the CPU costing model, the optimizer_index_cost adj parameter is effectively redundant as the necessary adjustments are already incorporated into the final costs. The total time required to perform a multiblock read operation is divided by the time it takes on average to perform a single block read operation. Using the optimizer_index_cost_adj parameter, although supported and permissible, will likely result in the final CBO costs being adjusted inappropriately as the index related single block I/Os will “double-dip” and potentially reduce both as a result of the system statistic differences between sreadtim and mreadtim and also as a result of the optimizer_index_cost_adj parameter as well.
 
The system stats are much preferred provided they’re accurate and kept reasonably up to date, because one doesn’t need to “manually” change any associated database parameter.
 
Not only are the comparative differences between sreadtim and mreadtim maintained, but so are other useful system statistics such as the mbrc statistic to be discussed next.
 
So in summary, when using the CPU costing model, do not set the optimizer_index_cost_adj parameter at all. Leave it alone, collect representative system statistics and let the system statistics look after the comparative costs between single and multiblock I/Os for you automatically.

The CPU Costing Model: A Few Thoughts Part I December 8, 2009

Posted by Richard Foote in CBO, Oracle Cost Based Optimizer, System Statistics.
4 comments

In the coming days, I’ll post a series of little entries highlighting a specific point in relation to the use of system statistics and the CPU cost model. In my previous post, we looked at how the cost of a FTS is calculated using the CPU costing model and how this generally results in an increase in the associated FTS cost over the I/O costing model.

The table in my demo though had an index with an appalling clustering factor and even though the cost of the FTS increased substantially from 33 to 70, this cost was still significantly less than the large cost associated with using such an inefficient index. So in that specific example, the change of FTS costs as introduced with the CPU costing model made no difference to the final execution plan.
 
The key point I want to emphasise with this post,  is that by increasing FTS costs as is common with the CPU costing model over the I/O costing model, this can of course potentially result in entirely different execution plans, especially if a candidate index has a reasonable clustering factor. Substantially increasing the associated costs of a FTS can be very significant, especially where the difference in costs between a FTS and an index can be much narrower for well clustered indexes.
 
In this previous I/O Costing Model example using the BOWIE_STUFF2 table, the index had an excellent clustering factor. However the query resulted in a FTS as the cost of 65 was just a little less than using an associated index:

SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.

 
Execution Plan
———————————————————-
Plan hash value: 573616353
——————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————
| 0 | SELECT STATEMENT | | 10000 | 175K| 65 |
|* 1 | TABLE ACCESS FULL| BOWIE_STUFF2 | 10000 | 175K| 65 |
——————————————————————

Remember, this was “addressed” and the CBO started using the index, by manually adjusting the optimizer_index_cost_adj parameter from its default value to a value of 75 as explained in this previous post on the effects of the optimizer_index_cost_adj parameter.

However, with system stats and the use of the CPU costing model, the extra FTS cost can have a direct impact on the resultant execution plan. Running the same query again, but this time without changing any optimizer parameters and using the same system stats as in my last post on the CPU Costing Model:

PNAME    PVAL1
-------- -----
SREADTIM     5
MREADTIM    10
CPUSPEED  1745
MBRC        10

SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
 
10000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 2964430066
 
———————————————————————————————–
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————–
|   0 | SELECT STATEMENT             |                | 10000 |   175K|       69(2)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   | 10000 |   175K|       69(2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | BOWIE_STUFF2_I | 10000 |       |       25(0)| 00:00:01 |
———————————————————————————————–
 

We notice that the CBO has now chosen the index automatically, without having to make any changes to the optimizer_index_cost_adj parameter at all.
 
Previously, the FTS costs were 65. However, the current costs for a FTS are at least:
 
(ceil(blocks/mbrc) x mreadtime) / sreadtime = (ceil(659/10) x 10) / 5 = 132.
 
132 is already way greater than the 69 cost associated with using the above index and the 132 cost doesn’t even take into consideration any additional costs related to CPU usage.
 
So in general, using the CPU costing model will likely increase the associated costs of FTS, making indexes automatically more “attractive” to the CBO as a result. This change alone in how the FTS in particular is costed using the CPU costing model can have a major impact in execution plans chosen by the CBO. This is but one of the key reasons why things can change so dramatically when moving from 9i to 10g where the CPU costing model is the default.

OPTIMIZER_INDEX_CACHING Parameter September 1, 2009

Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle Indexes.
8 comments

As previously discussed, the CBO assumes the actual costs and overheads associated with all I/Os to be the same, regardless of the type of I/O, unless told otherwise via the optimizer_index_cost_adj parameter.
 
Another key assumption the CBO makes by default is that all I/Os will be physical I/Os (PIO) and so be relatively expensive to perform and “worthy” of being costed.
 
However, this of course is not always the case with many of the blocks being requested and accessed by Oracle already cached in the buffer cache. In specific scenarios, the CBO can take the likely caching characteristics of indexes into consideration and reduce the cost of an index related execution path accordingly. Note however this only applies for I/Os associated for index specific blocks in specific scenarios where the same index is repeatedly accessed.
 
For example, in the case of a nested loop join where the inner table is typically accessed via an index look-up, the same index may repeatedly access the table many times within the loop. Many of the blocks associated with this index are therefore quite likely to be cached as the index structure is being continually accessed. Same scenario for an index look-up process as a result of an IN list condition. For each element in the IN list, an index is often used to look-up the corresponding value in the table, thereby accessing the specific index again and again for each element in the IN list. As the index is continually being accessed, many of its associated blocks are likely to already be cached in memory.
 
The purpose of the optimizer_index_caching parameter is to tell the CBO what percentage of index related blocks are likely to already be cached in the buffer cache during these types of operations and so should not be considered in the overall costings associated with the index related execution path. The default is 0 which means by default Oracle doesn’t consider any index blocks to ever be cached and all I/Os associated with an index during an index access path need to treated as PIOs and costed accordingly. If however the optimizer_index_caching parameter is set to say 25, it means that the CBO will consider 25% of all I/Os associated directly with index blocks are likely to already be cached and will therefore reduce the overall cost of index block I/Os by 25%.
 
As discussed previously, the CBO I/O based costing formula is:
 
basic index range scan cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

The optimizer_index_caching parameter adjusts the formula in the following manner by reducing just the index accesses portion of the formula:

basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)

but only for specific index scans such as nested loop joins and IN list conditions where an index is likely to be continually accessed within the same execution path.

 

So if we were to go back to the example I covered in the single predicate demo in the first CBO and Indexes Introduction post as shown below:

SQL> select * from bowie_stuff2 where id = 420;

2000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 134336835
——————————————————————————
|Id| Operation                   | Name           | Rows  | Bytes | Cost  |
——————————————————————————
|0| SELECT STATEMENT            |                |  2000 | 36000 |    18 |
|1|  TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  2000 | 36000 | 18 |
|*2|   INDEX RANGE SCAN          | BOWIE_STUFF2_I |  2000 |       |     9 |
——————————————————————————

 

we notice that the cost of the execution plan is 18.

If we now change the optimizer_index_caching parameter to say 75, meaning that 75% of all index blocks are now likely to be cached and rerun the query:
 
SQL> alter system set optimizer_index_caching=75;
 
System altered.
 
SQL> select * from bowie_stuff2 where id = 420;
 
2000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 134336835
 
——————————————————————————
|Id| Operation                   | Name           | Rows  | Bytes | Cost  |
——————————————————————————
|0| SELECT STATEMENT            |                |  2000 | 36000 |    18 |
|1|  TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  2000 | 36000 | 18 |
|*2|   INDEX RANGE SCAN          | BOWIE_STUFF2_I |  2000 |       |     9 |
——————————————————————————

 

we notice that the cost remains unchanged at 18 and parameter has had no effect, as the query was based on a single table equality predicate and did not have processing involving either a nest loop or IN list condition.
 
 
However, if we run the second IN list predicate demo involving an IN list condition as shown below (first resetting the optimizer_index_caching parameter back to 0):
 
 
SQL> alter system set optimizer_index_caching=0;
 
System altered.
 
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
 
6000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 2964430066
 
——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|    49 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 49 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |    23 |
——————————————————————————-
 

We note we had a cost of 49. Remember, the cost of 49 was calculated in the following manner as we have 3 elements in the IN list condition:

cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

= 2 + 3 x ceil(0.01 x 602) + ceil(0.03 x 852)

= 2 + 3×7 + 26

= 2 + 21 + 26

= 23 + 26 = 49

 

If we now alter the optimizer_index_caching parameter to 75 and rerun the same IN list query:

SQL> alter system set optimizer_index_caching=75;
 
System altered.
 
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
 
6000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 2964430066
 
——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|    32 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 32 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |     6 |
——————————————————————————-
 

 we notice the cost has been reduced from 49 down to 32. How has the optimizer_index_caching set to 75 changed the costs:
 

basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)

= ceil((2 + 3 x ceil(0.01 x 602)) x (1-0.75)) + ceil(0.03 x 852)
 
= ceil((2 + (3×7)) x 0.25) + 26
 
= ceil((2 + 21) x 0.25)+ 26
 
= 6 + 26 = 32

 
So whereas previously there were 23 index block I/Os, this has been reduced down to just 6. Note that the I/Os and associated costs with accessing the actual table blocks within the index scan remains unaltered.

So how to set this parameter in a database ?  Well, there are a number of issues with it all.

Firstly, as with the optimizer_index_cost_adj parameter, there’s only the one “global” parameter (for the system or session) which means any value needs to be averaged out for all indexes and for all situations in which this parameter can have an effect. However, some indexes may for example be quite small and heavily accessed and as such quite likely to have most index blocks cached at any point in time (including leaf blocks) whereas other indexes may be quite huge and rarely and randomly accessed which means perhaps only the branch level blocks are likely to be cached even during a (say) IN list operation. As discussed previously, with all averages there will likely be examples where the value is appropriate, too high or too low depending the the characteristics of specific indexes.

Secondly, the poor table related blocks don’t have an equivalent parameter and so Oracle always assumes not only the table blocks within an index scan will be PIOs, but a FTS will only ever consist of PIOs, which conversely might not always be the case. So while we might make a reasonable guesstimate of the likelihood of an index block being cached (say via the buffer cache hit ratio, a study of the v$bh view, etc.), the CBO makes so such allowances for the possible caching characteristics of table related blocks. Yes, index blocks are more likely to be cached, especially during the specific scenarios in which the optimizer_index_caching parameter has an effect, but that doesn’t mean table blocks will always be PIOs. Therefore simply setting this parameter to what might appear a reasonable generalistic index caching value might still run the risk of favouring indexes unduly, even though it only impacts the index accessed blocks in the costing formula, as the CBO doesn’t make any such cost allowances for table blocks that might be cached in a FTS.

In the ideal world, we would have some idea of the caching characteristics of all individual indexes and tables and based on the segments being accessed and their associated caching characteristics, have the CBO make the necessary adjustments to it’s costing estimates in an execution path. Until we reach such an ideal world (which might not be that far away BTW), I basically recommend not to set this parameter at all and again simply ensure you use accurate system statistics and have accurate enough segment statistics.

I recommend setting this parameter if and when you find the CBO is commonly not choosing appropriate indexes for the above mentioned scenarios when perhaps it should and a slight “nudge” of costs in the right direction is sufficient to address the issues. The optimizer_index_caching parameter is not quite as overly “dangerous” if set incorrectly as the optimizer_index_cost_adj parameter can be, as it only impacts the “half” of the formula relating directly to index block I/Os and not the table block I/Os, which often constitute the greater proportion of overall I/Os in many index range scan operations (although as my example above shows, this depends as well).

However, with both of the optimizer_index parameters set, they can both have a hand in reducing the overall costs of an index related execution plan. The optimizer_index_caching parameter first impacts the cost of just the half of the formula relating to index block I/Os as shown above and then the optimizer_index_cost_adj parameter further impacts the overall resultant cost. So if we were to run the IN list query again, but this time also set the optimizer_index_cost_adj to say 25 as well as leaving the optimizer_index_caching to 75:

SQL> alter system set optimizer_index_cost_adj=25;

System altered.

SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);

6000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066

——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|     8 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 8 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |     2 |
——————————————————————————-

 

We note the the cost of the execution plan has further reduced down from 32 to just 8. Basically it’s just the previous cost of 32 x optimizer_index_cost_adj = 32 x 0.25 = 8.

However, rather than setting either of these parameters, I would simply recommend the appropriate use of system statistics and the CPU costing model as I’ll discuss later.

The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part II July 22, 2009

Posted by Richard Foote in OPTIMIZER_INDEX_COST_ADJ, Oracle Cost Based Optimizer, Oracle Indexes.
13 comments

In OPTIMIZER_INDEX_COST_ADJ Part I,  I discussed how this parameter can significantly impact the overall cost of an index related execution path by “adjusting” the overall costs by the percentage denoted in the parameter. An OPTIMIZER_INDEX_COST_ADJ value of 25 for example will result in the costs of an index access path being reduced to just 25% of the calculated total.

Reducing the effective costs of using an index will obviously increase the likelihood of an index being chosen by the CBO over a Full Table Scan (FTS). This may be a good thing when an appropriate index is not being used by the CBO. However, this may also be a very bad thing if it results in an inappropriate index being chosen by the CBO over a more efficient FTS …

There are typically 3 different methods by which this parameter is set.

Method One: Set it to a really low value such that indexes are typically preferred over a FTS.

This is such a common piece of advice. I wish I was given a dollar every time I read someone suggest set (or reduce further) the OPTIMIZER_INDEX_COST_ADJ to be a really low value in order for a specific piece of SQL to start using an index. Classic example right here where someone suggests setting this parameter to 12 in order to get the CBO to use an index.

Why 12 ?

Perhaps because they once had a SQL statement that used a FTS instead of an index and setting the OPTIMIZER_INDEX_COST_ADJ to 12 made the CBO use the index and so setting it to 12 obviously works, right ? Perhaps because they were born on the 12th and 12 has always been a lucky number ? Who knows ?

However, simply setting the OPTIMIZER_INDEX_COST_ADJ to a low value for no specific reason is generally a very dangerous and ill advised thing to do.

Remember this key point. The purpose of the OPTIMIZER_INDEX_COST_ADJ is not to ensure indexes are favoured over a FTS. As I discussed in Part I, the purpose is to more accurately reflect any discrepancies between the actual costs of a single block I/O associated with an index access path versus the actual costs of a multiblock I/O associated with a FTS. Therefore, the OPTIMIZER_INDEX_COST_ADJ should as accurately as possible reflect any such discrepancies so that the comparative costs are true and reflective of the real corresponding costs.

The parameter should attempt to set a even playing field between the use of an index and a FTS, not simply just adjust things in favour of indexes generally.

Setting this parameter to an arbitrarily low number may indeed make a specific SQL statement suddenly use an index when previously it used a FTS. Perhaps this SQL statement might indeed benefit from now using the index.

However, this parameter is a global parameter in the sense that it impacts all SQL statements for the entire system or session. Although it might indeed make the CBO use indexes appropriately in places, unfortunately if set incorrectly, it might also make the CBO start to use indexes inappropriately as well. Perhaps the use of a FTS is actually the way to go, is actually the most efficient and less costly option for other SQL statements, but by making this parameter so low as to favour indexes by so much, it might suddenly make the CBO choose indexes that are less efficient and much more costly than the alternative FTS.

Because there are of course many many occasions when a FTS is actually the desired access method as it’s simply the cheaper and less costly alternative. In fact, an index that’s selected inappropriately, an index that’s used instead of the cheaper FTS can actually be far more damaging to overall database performance than the potential damage caused by an inappropriate FTS.

Consider this. In the worst case scenario, the maximum number of logical (or physical) I/Os that a FTS will generate is the number of blocks in the table. The number of actual I/Os is likely to be far fewer than the number of blocks in the table as a FTS will generally perform multiblock I/Os and read all the table blocks in larger “chunks” at a time. However, even with a poorly configured db_file_multiblock_read_count set to effectively 1, the other great advantage of a FTS is that Oracle only needs to access a specific block the once. So reading all the table blocks once, one at a time, is effectively as bad as a FTS can get.

In a 10,000,000 row table housed in say 100,000 table blocks, the maximum number of LIOs is effectively 100,000for a FTS.

However, the worse case scenario for an index range scan can potentially be much much worse. In a poorly clustered index, it’s actually possible to have to read not only each and every leaf block in the index structure (plus a few branch blocks to get to the first index leaf block of interest) but in addition also perform as many logical (or physical) I/Os as there are rows in the table.

In a 10,000,000 row table housed in 100,000 table blocks with say 25,000 leaf blocks in a corresponding index, the maximum number of LIOs could potentially be as high as 10,025,003 for an index range scan, in the order of 100 times worse than the corresponding worse case scenario with the FTS. Remember of course that a FTS is likely to actually have far fewer actual LIOs than the worse case scenario, assuming the db_file_multiblock_read_count is set to something reasonable. So it could quite conceivably be in the order of 1000 times worse than the corresponding FTS.

An index scan you see may need to visit a specific table block many times because the indexed data in the table may be randomly distributed throughout the table (the index has a very bad clustering factor in other words) and so only accesses individual rows within a table block at differing times during the index range scan. As each index entry is read, the corresponding rowids keep referencing different table blocks from those recently accessed. If a specific table block contains say 100 rows, an index range scan may need to access this same block as many as a 100 different times during the index range scan operation.

A very simple example to illustrate this point.

Here, I create a table with10,000,000 rows, with an ID column that has values that are randomly distributed throughout the table:

SQL> create table bowie (id number, text varchar2(80));
 
Table created.
 
SQL> insert into bowie select ceil(dbms_random.value(0, 10000)), ‘This is just a piece of text designed to make a row a reasonable size’ from dual connect by level <= 10000000;
 
10000000 rows created.
 
SQL> commit;
 
Commit complete.

 

I now create an index on this ID column and collect 100% accurate statistics:

SQL> create index bowie_id on bowie(id);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
 
PL/SQL procedure successfully completed.

 

I now run a select statement that selects just 10%of rows in the table. Many would expect the index to be used by the CBO as 90% of all rows are of no interest:

SQL> select * from bowie where id between 1 and 1000;
 
1000873 rows selected.
 
Elapsed: 00:01:49.75
 
Execution Plan
———————————————————-
Plan hash value: 1845943507

—————————————————————————
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT  |       |  1000K|    69M| 22068   (2)| 00:01:51 |
|*  1 |  TABLE ACCESS FULL| BOWIE |  1000K|    69M| 22068   (2)| 00:01:51 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(“ID”<=1000 AND “ID”>=1)
 

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
     108986  consistent gets
     108672  physical reads
          0  redo size
    8920558  bytes sent via SQL*Net to client
       2596  bytes received via SQL*Net from client
        202  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000873  rows processed

 

However, we note the CBO has actually selected a FTS and the query is a little slow at nearly 1 minute and 50 seconds.

Hummmm, I know, let’s change the OPTIMIZER_INDEX_COST_ADJ parameter to a really low figure and see if the CBO now decides “to do the right thing” and use the index.

I’m going to pick the value of, uuuummmmm let me think, oh I know, let’s use a value of 2 because I scored 2 goals yesterday in football and 2 has always been somewhat lucky for me. That should do the trick, let’s make the index appear to be only 2% of it’s original cost and really give this index a good chance of being selected by the CBO

SQL> alter session set optimizer_index_cost_adj = 2;
 
Session altered.
 
SQL> select * from bowie where id between 1 and 1000;
 
1000873 rows selected.
 
Elapsed: 02:16:44.42

Execution Plan
———————————————————-
Plan hash value: 4117205494
 
—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |  1000K|    69M| 19970   (1)| 00:01:40 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOWIE    |  1000K|    69M| 19970   (1)| 00:01:40 |
|*  2 |   INDEX RANGE SCAN          | BOWIE_ID |  1000K|       |    42   (0)| 00:00:01 |
—————————————————————————————-
 

Predicate Information (identified by operation id):
—————————————————
 
   2 – access(“ID”>=1 AND “ID”<=1000)
 

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
     998652  consistent gets
     916134  physical reads

          0  redo size
    5034208  bytes sent via SQL*Net to client
       2596  bytes received via SQL*Net from client
        202  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000873  rows processed

 

Well the good news is that the CBO is now using the index.

However, the bad news is that the elapsed time has gone from 1 min 50 seconds to a massive 2 hours 16 minutes and 44 seconds.

Changing the OPTIMIZER_INDEX_COST_ADJ to an arbitrarily “low” value has suddenly caused some critical SQL statements to now perform appallingly.

And remember, this for a query that was only returning 10% of the data …

Why are things so bad now ?

Because the ID values are randomly distributed throughout the table, the index on the ID column has as a result a terrible/awful clustering factor. Therefore, as the index range scan is performed, Oracle is forced to keep visiting different table blocks with each new index key, potentially returning to the same table block time and time again. The number of logical I/Os is going to be massive and has increased significantly from that of the FTS but the CBO is effectively only costing 2% of all these I/Os in it’s calculations because of the OPTIMIZER_INDEX_COST_ADJ value.

It was giving the index an unfair advantage and severely underestimating the true costs associated with using the index vs. the FTS.

Worse, as it’s using an index, all these massive numbers of blocks are likely going to cause more performance issues in the buffer cache due to the more favourable manner in which such blocks are cached over blocks accessed via a FTS. 

I might also add that in this specific example, the CBO was using the CPU costing model (which I’ll discuss later). So the OPTIMIZER_INDEX_COST_ADJ parameter still has an impact even when the CBO in theory should already have a good idea on the comparative costs of a single vs a multiblock I/O.

So simply setting the OPTIMIZER_INDEX_COST_ADJ parameter to an arbitrarily low value is a very dangerous tuning technique due to the risk of inappropriate index access paths being selected by the CBO.

I’ll next discuss two better techniques in how to set the OPTIMIZER_INDEX_COST_ADJ parameter to a more appropriate value.

The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part I July 8, 2009

Posted by Richard Foote in CBO, Index Access Path, OPTIMIZER_INDEX_COST_ADJ, Oracle Cost Based Optimizer, Oracle Indexes.
17 comments

In the previous entry regarding The CBO and Indexes, we saw how the CBO with a query that selected 5 distinct values in an IN list, representing 5% of the data, decided to use a FTS because the costs of doing so were less than that of using a corresponding index. These costs (using the I/O costing model) represented the number of expected I/Os and the FTS was basically going to perform fewer I/Os than the index. Less I/Os, less cost and so the FTS was selected as the preferred access path.
 
However, by default, the CBO when determining these costs via the I/O costing model makes two very important assumptions which may not necessarily be true.
 
Assumption one is that all I/Os are likely to be “physical I/Os” which all need to be costed and taken into account.
 
Assumption two is that all I/Os are costed equally, even though the size of a multiblock I/O performed typically during a FTS is larger and so potentially more costly than a single block I/O usually associated with an index access.
 
Today, I’m only going to focus on this second assumption. 

Now, when performing and processing data from a multiblock I/O as performed during a FTS operation, it’s typical for such operations to be more resource intensive than that of a single block I/O as performed during an index range scan, as the associated overheads are likely be greater such as having to read more actual data off the disk, having to transfer more data into the SGA, having to process more data in each associated block, etc.
 
Therefore, not all I/Os are equal. However, by default the CBO ignores all these possible differences and costs all I/Os associated with a FTS (multiblock) and an index (single block) as being equivalent or the same.
 
Now, this hardly seems fair or indeed accurate and desirable when determining the true cost differences between an index and a FTS. Shouldn’t the fact that a single block I/O is likely to be less resource intensive and take less elapsed time to process be taken into consideration when determining these relative costs ? 

Enter the optimizer_index_cost_adj parameter.
 
The purpose of this parameter is simply to “adjust” the corresponding costs associated with an index to (hopefully) more accurately reflect the relative I/O costs between using an index and a FTS. If for example a single block I/O only takes 1/2 the time and resources to perform compared to a multiblock I/O, shouldn’t these associated I/O cost differences be reflected when determining whether or not to use an index and perhaps reduce the index related costs by 1/2 as a result ?
 
This parameter has a very simple impact on how the CBO costs the use of an index based access path. It takes the value of the optimizer_index_cost_adj as a percentage and adjusts the cost of an index related range scan access path to only be the corresponding percentage of the total index cost. By default, it has a value of 100 meaning that a single block I/O is 100% when compared to that of a multiblock I/O which in turn means that the index related I/O costs are treated the same as that of a multiblock FTS I/O. A default value of 100 therefore has no effect on the overall cost of using an index related access path.
 
However, if the optimizer_index_cost_adj only has a value of (say) 25, it means that all single block I/O are only 25% as costly as that of a multiblock I/O and so index related range scan costs are adjusted to be only 25% of that of the total index access path cost.
 
Going back to the previous demo where the FTS was selected, I calculated the cost of using the index when retrieving the 5% of data to be:

index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854) = 2 + 5 x 7 + 43 = 37 + 43 = 80.
 
The cost of using a FTS was calculated as being only 65. A cost of 65 for the FTS is less than a cost of 80 for the index and so the FTS was selected.

This time, the linked demo sets the optimizer_index_cost_adj = 25 before running the exact same query again.

We notice of couple of key differences. The first obvious difference is that the plan has changed and that the CBO has now decided to use the index. The second difference is the associated cost relating to the use of the index. Previously, it was calculated as being 80 but now it only has a cost of 20. The maths is pretty simple as with an optimizer_index_cost_adj = 25, we need only mutliply the previous total with 0.25:

(2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854)) x 0.25 = (2 + 5 x 7 + 43) x 0.25 = 80 x 0.25 = 20.

Note also that just the index range scan cost component was previously 2 + 5 x ceil(0.01 x 602) = 37, but is now also adjusted to 37 x 0.25 which rounds to 9.

Basically by setting the optimizer_index_cost_adj = 25, we have effectively reduced the overall cost of using the index based execution path down from 80 to just 20, to just 25% of the previous total index cost.
 
The cost of the FTS remains unchanged at 65. The index access path at just 20 is now less than the FTS alternative and so the index is now chosen by the CBO.

Yes, all these numbers and costs make sense when one understands how the CBO performs its calculations and the effect of setting the optimizer_index_cost_adj parameter to a non-default value.

The  optimizer_index_cost_adj parameter can therefore obviously have a very significant impact in the behaviour and subsequent performance of the database as the CBO will reduce (or maybe increase) the actual costs of index related access paths by the percentage denoted in the optimizer_index_cost_adj parameter. It can potentially dramatically increase (or decrease) the likelihood of an index access path being chosen over a FTS.
 
There are typically 3 very different ways in which this parameter is set, which I’ll list in increasing order of preference.
 
1) Set it arbitrarily to a very low figure such that indexes reign supreme as their associated costs get adjusted to such a low figure by the CBO that a FTS access path has little chance of being chosen (for example, here’s a suggestion to set it to a magical value of 12). Generally a very bad thing to do in any database …
 
2) Set it to a value that the DBA determines is an approximate percentage of the costs associated with a single block I/O when compared to a multiblock I/O. An improvement of option 1), but I still prefer the next option 3) …
 
3) Leave it at the default value of 100 such that it has no impact and the CBO does not use it to adjust the cost of an index access path

 

I’ll explain in Part II a sensible approach in setting the optimizer_index_cost_adj parameter and why option 3 is the preferred option with any currently supported version of Oracle.

Function-Based Indexes and Missing Statistics (No Surprises) December 4, 2008

Posted by Richard Foote in Function Based Indexes, Oracle Cost Based Optimizer, Oracle Indexes.
7 comments

Thought I might mention a common trap associated with Function-Based Indexes following a recent question on the OTN Database Forum.

First of all, considered this “simple” little scenario. You have a column in a table that’s a standard VARCHAR2 field and it has 100 distinct values. Therefore, if you select just one value, assuming even distribution of data, you’ll select approximately 1% of the data.

However, this table can potentially allow mixed case fields, with values such as ‘Bowie’, ‘bowie’, ‘BowiE’, etc.

Therefore, you decide to write a query that first converts all the fields to UPPER case such that you can now compare all possible values of a specific name to say ‘BOWIE’.

Remembering you previously had a selectivity of 1%, what’s the selectively of the column after you’ve converted everything to upper case ?

Ummmm, actually, there’s no easy answer to that. It all depends on how many actual different names you have in your data. In theory, you may now have just 1 distinct value or you may have 100 distinct values, you can’t really tell.

However, what if the function did something more complex and converted the name to some numerical value based on the sum of all the characters via some weird formula. In theory, you could have no distinct values in that everything could get converted to a NULL or you could possibly have as many distinct values as there are rows in the table.

Again, Oracle can’t really tell how many rows may get selected based on the determination of such a function.

When a predicate is used with a function call, Oracle has great difficulty in determining the correct selectivity. Therefore when you create a Function-Based Index, which Oracle assumes you may wish to access, Oracle behind the scenes creates a hidden virtual column on the parent table in order to capture the data characteristics of the function so that the CBO can make an accurate determination of the selectivity associated with using the function.

If Oracle knows the low value, the high value and the number of distinct values of the data associated with the function on a column, it can then accurately determine the associated selectivity and cardinality when the function is used on the column (assuming even distribution of data) and hence calculate an accurate cost and determine whether the use of the function-based index is appropriate.

However, and here comes the trap, when a function-based index is created, Oracle will now (since 10g) automatically calculate the statistics associated with the index (such as the blevel, number of leaf blocks, clustering factor, etc.) but it will NOT calculate the statistics associated with the hidden virtual column as these statistics are associated with the parent table, not directly with the index itself.

Here I create a new function-based index on a table:

SQL> create index pink_floyd_upp_tab_name_i on pink_floyd(upper(table_name));

 

Index created.

 

But there are still no statistics on the hidden virtual column created by Oracle behind the scenes.

 

SQL> select column_name, num_distinct, hidden_column, virtual_column from dba_tab_cols where table_name=’PINK_FLOYD’;

 

COLUMN_NAME     NUM_DISTINCT HID VIR
--------------- ------------ --- ---
OWNER                     74 NO  NO
TABLE_NAME              5739 NO  NO
NUM_ROWS                 886 NO  NO
BLOCKS                   152 NO  NO
SYS_NC00005$                 YES YES

Therefore, even after you’ve created the function-based index, Oracle still has no idea on the selectivity associated the function because the necessary virtual column statistics are still missing. Oracle simply takes a “guess” and as with most guesses, it’s quite likely to be wrong which means the selectivity is likely to be wrong which means the costings is likely to be wrong which means the execution plan could very well be wrong as well.

What should you do after you create a function-based index ? You should collect the statistics on the hidden virtual column so that the CBO can accurately determine the cardinality associated with using the function and so make correct decisions regarding the most appropriate execution plan.

The ‘FOR ALL HIDDEN COLUMNS’ method_opt option with DBMS_STATS is one way of collecting just the necessary table statistics on these hidden virtual columns:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘PINK_FLOYD’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL HIDDEN COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, hidden_column, virtual_column from dba_tab_cols where table_name=’PINK_FLOYD’;

COLUMN_NAME     NUM_DISTINCT HID VIR
--------------- ------------ --- ---
OWNER                     74 NO  NO
TABLE_NAME              5739 NO  NO
NUM_ROWS                 886 NO  NO
BLOCKS                   152 NO  NO
SYS_NC00005$            5739 YES YES

This demo on missing virtual column statistics asscociated with function-based indexes goes through this whole issue.

My early Christmas wish to Oracle would be for the CBO to at least consider the DISTINCT_KEYS index statistic when determining the correctly selectivity when hidden virtual column statistics are missing on function-based indexes, rather than take it’s 0.4% / 1% guess …

More on virtual columns, hidden and otherwise, in the future …

Index Monitoring and Index Statistics (The Great Gig In The Sky) September 16, 2008

Posted by Richard Foote in 11g, Concatenated Indexes, Extended Statistics, Index Monitoring, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes.
8 comments

I write this post whilst listening to Pink Floyd’s masterpiece “The Dark Side Of The Moon” while sadly lamenting the passing away of Richard Wright. RIP and thank you for all the great musical gifts you’ve given me over the years.

In my last post I highlighted an example of where Index Monitoring doesn’t show how indeed Oracle does indeed use an index when checking for the existence of Foreign Key values. Thought I might discuss yet another example of where Oracle does indeed use an index but it’s again not picked up by index monitoring, this time with a slight 11g flavour.

This specific example involves using the statistics associated with the indexes to provide the CBO with useful additional information, although the index itself is not used directly within the execution plan. Dropping the index means losing this information which could possibly result in a different, non optimal execution plans.

Prior to 11g, Oracle can have a hard time of accurately determining the correct selectively where there is a correlation between two (or more) columns in a table. By default, Oracle assumes the selectivity of two distinct columns to be the density of both columns multiplied together. So for example, if one column (say “A”) had 10 distinct values and the other column (say “B” also had 10 distinct values, Oracle assumes the selectivity of both columns combined to be 10 x 10 = 100 distinct values. A predicate such as:

WHERE A = 5 and B = 2

would assume 1% of data would be retrieved if both columns A and B both had 10 distinct possible values.

However, what if there’s a special relationship between the columns and the actual number of distinct combinations was somewhat different ? What if B always equals 2 when A equals 5, what if instead of the theoretical 100 different combinations there were only 10 combinations (or some such) because most of the other possible combination don’t actually exist …

This 9i and 10g demo shows how there is indeed only 10 distinct values for each of two different columns, however there is a direct relationship between these columns such that there is actually only 10 distinct combinations of both these columns (and not the 100 combinations which are possible and which Oracle assumes in it’s selectivity calculations).

Instead of the actual 10,000 rows (or 10% of all data) being selected, Oracle is incorrectly assuming only 1000 rows (or 1%) will be selected. This is a significant error by a order of magnitude which in many cases can result in a less efficient execution plan.

With 11g, Oracle can use the statistics associated with an index to give Oracle some vital extra information. Because if there’s an index based on the two columns, then the number of distinct key values recorded for the index can provide Oracle with a much more accurate estimation of the true selectivity based on the two columns. If a concatenated index based on the two columns only has say 10 distinct values, then Oracle can assume that a specific combination of the two columns is likely to also retrieve 1/10 of all the values and not the 1/100 that are theoretically possible.

This identical 11g demo to the one above shows by having an index on the two columns that have a correlation, Oracle is using the DISTINCT_KEYS statistic for the index to determine the correct selectivity and associated cardinality for the query.

However, the demo clearly shows index monitoring is still not showing the index as being “USED”. If you were to hence drop the index, the CBO loses potentially vital information and the cardinality estimates revert back to being the product of the two column densities as with pre 11g.

By dropping the index which appears to not be used, we can potentially impact other execution plans, even though they don’t directly use the index within the execution plan. The correct cardinality estimates of a table can for example potential drive the order in which the table is subsequently joined or the manner in which it’s joined.

This demo on the possible impact of dropping an “unused” index shows how an execution plan can change to be sub-optimal, even though neither execution actually directly uses the associated index. It’s not a particularly “clever” example, but it does illustrate the potential impact of dropping these so called unused indexes.

Of course, with 11g, we now have the capability of collecting extended statistics. We can potentially determine these same level of statistics by generating statistics on both columns combined. Oracle can determine the actual distinct combinations of columns that are somehow correlated and produce more accurate and detailed statistics with hence make the CBO determine more accurate and reliable cardinality estimates.

This final demo on extended statistics shows how we can recreate the more efficient execution plan and provide the CBO with more detailed extended statistics so that it can accurately determine the correct cardinality estimates without the need to recreate the “unused” index.

Extended statistics can be extremely useful in determining correct cardinality values for column combinations that exist however it still falls somewhat short when it attempts to estimate the expected cardinality for combinations that don’t actually exist, even with histograms.

But that’s a tale for another day.

Now it’s time for “Wish You Were Here” …

Partition Pruning – Some Examples (Knives Out) September 3, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Partitioning.
4 comments

Following on from my last post where I looked at the use of partition pruning vs. the use of an index, I thought it might be worthwhile to just go through a few additional basic examples of where partition pruning can come into play.

I’ll also include a little example of how execution plans may not favour indexes as much with partitioned tables as the resultant cost of a Full Table Scan can be reduced significantly due to partition pruning.

All the examples use exactly the same tables as created in the previous post. For a full listing of the examples and resultant executions plans, look at this Partition Pruning Demo. You may want to open up the demo in a different browser window to make it easier to follow.

In Example 1:

SQL> select * from big_album_sales where release_date between ’01-JUN-2003′ and ’13-APR-2004′;

the CBO can determine that all the data can only belong in Partitions 3 and 4 (as only only years that need to be referenced are 2003 and 2004) and so not have to visit any of the other partitions. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 3 – 4.

In Example 2:

SQL> select * from big_album_sales where release_date > ’01-JUN-2006′;

All the possible values must exist from partition 6 which stores all the 2006 data and onwards through to the last partition in the table which is partition 8. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 6 – 8.

In Example 3:

SQL> select * from big_album_sales where release_date between ’01-JUN-2006′ and ’30-NOV-2006′ or release_date > ’14-JUN-2008′;

The only possible partitions the data can reside in are partition 6 or partition 8 (for the years 2006 or 2008). In this case, the CBO performs the PARTITION RANGE OR step which visits only these specific partitions.

In Example 4:

SQL> select * from big_album_sales where release_date is null;

All NULL values must reside in the last partition, as the table was created with the last partition having a MAXVALUE boundary. That being the case, the CBO need only visit the last partition (8) within the table to find all possible NULL values of RELEASE_DATE.

Because partition pruning can make performing a so-called Full Table Scan much more efficient as only those partitions with possible data need only be accessed, in some rare cases this can make the CBO appear to ignore reasonable indexes that may be used and accessed in an equivalent non-partitioned table.

Note that the data includes a series of RELEASE_DATE values but many of the dates actually have the same timestamp component as all the row values were determined and loaded in a very quick manner. As a result, the CBO considers there are actually 718 rows per distinct occurrence of a RELEASE_DATE value. This fact is significant when costing an equality predicate.

In Example 5:

SQL> select * from big_album_sales where release_date = ’01-JUN-2006′;

we’re only interested in the one specific date value but Oracle still favours the Full Table Scan (FTS). Note this value can only possibly exist in partition 6 associated with data from 2006 and so the FTS need only actually access partition 6. Oracle assumes it will retrieve approximately 718 rows which is too costly via the index as partition pruning can make the FTS relatively efficient. Note that the actual cost of the FTS is 303.

In Example 6:

SQL> select /*+ index(b) */ * from big_album_sales b where release_date = ’01-JUN-2006′;

a hint is used to force the use of the associated index on the RELEASE_DATE column. Note in this example, it’s actually a much cheaper option (there are far fewer consistent gets) as no rows are actually being retrieved but the cost of 722 is significantly greater than that of the FTS (303) as Oracle is expecting to retrieve and access 718 rows.

In Example 7:

SQL> select * from big_album_sales2 where release_date = ’01-JUN-2006′;

we run exactly the same statement but this time on the non-partitioned table and notice that the execution plan is totally different. Oracle is indeed using the index because in this example the FTS is so much more expensive as it needs to read the entire table and not just the one partition (or approximately 1/8 of the table) as it does with the partitioned table example.

Lots more on partitioning to come in the future …

Regular Index vs. Partitions (The Best Of Both Worlds) August 28, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle Indexes, Partitioning.
17 comments

When asking for requests and suggestions on what I could cover in the blog, Brian Tkatch asked “When is a partition better than just a regular index ?”

As it’s a very good question, I thought I might spend a few moments attempting to provide some kind of meaningful answer.

The first point I would make however, is that indexes and partitions are not at all mutually exclusive. A table of course could very well benefit from being both partitioned and indexed. Most partitioned tables have indexes and indeed indexes can in turn be partitioned.

The second point I would make is that there are two key advantages or reasons why it might be beneficial to partition a segment. They are:

  1. Better Manageability
  2. Better Performance

This post can be viewed as being but an introduction to the potential performance benefits of partitioned segments. I will leave the topic of better manageability for another time.

The point of Brian’s question is when can the performance benefits available to partitioned segments out perform and be a better option than that of a possible associated index.

As I’ve discussed a number of times, there are no magic numbers or ratios as to when an index is the most appropriate and cost effective access path. If fact, the link shows a theoretical example of when a Full Table Scan (FTS) is by far the cheaper option when selecting just 1% of data. Generally speaking though, the higher the percentage of returned rows, the more likely a FTS is going to be the more efficient option.

However, a Full Table Scan is just that, a FULL Table Scan. Oracle needs to read the entire table, each and every block below the segment High Water Mark. So if a query needed to retrieve just 5% of the data, it’s quite likely it would perform a FTS because it’s possibly a cheaper and less costly alternative than using an associated index (the above link explains why) but it would need to access 100% of the table to do so. That means it would need to effectively access 95% of the data and simply just throw away the rows so it can access the 5% of data it actually requires in a more efficient manner than possible using an index access path.

That’s potentially (say) 95% of the work and resources for no real reason other than Oracle has no choice but to access all the data when performing a FTS. That’s potentially 95% of wasted resources, 95% of wasted effort. Yes, a TFS might be more efficient than using an index but accessing unnecessarily say 95% of data sounds rather costly nonetheless.

Wouldn’t it be nice if somehow we could get the benefits of performing a FTS (multiblock reads, reading a specific block just the once, etc.) but only read the say 5% of the data that we were actually interested in (or something significantly less than the 100%)? Somehow combine the benefits of a FTS with the benefits provided by an index, that being only needing to access data that is of direct interest.

Enter Partitioning.

Partitioning provides Oracle with another level of granularity (or levels of granularity with composite partitions) when accessing a table. It’s no longer an question of using an index or reading the entire table. Oracle can now either use an index, access the entire table or with partitioning access just those partitions (or bits) of a table that can only possibly contain data of interest.

By partitioning a table, Oracle knows that data belonging to the partitioning keys must belong in a specific partition. The logical table is effectively broken down into multiple physical segments with each partition segment containing only data associated with the partitioning keys or columns.

If the say 5% of data that’s of interest only logically resides in the one or whatever number of partitions, then there’s no need for Oracle to access the data in all the other partitions, containing the other say 95% of unwanted data. The Cost based Optimizer (CBO) can automatically take advantage of “partition pruning” and only needs to access the partition or those partitions that only can contain the data of interest.

Oracle can now potentially perform a so-called “FTS” of the table, but only actually accesses and reads the 5% (or significantly less than the 100%) of data that’s potentially of direct interest by accessing just those partitions it needs to.

At a simplistic level, indexes are best when accessing a relatively “small” amount of data. A FTS is best when accessing a relatively “large” amount of the data and partitions can be extremely useful and beneficial when accessing an amount of data somewhere between the two. As such, partitioning is more likely to be beneficial in a Data Warehouse or Reporting environment where accessing a relatively high percentage of data is common.

This demo of indexes vs. a FTS vs. partition pruning shows how a FTS outperforms an index when accessing approximately 1/8 of data in a table but an appropriately partitioned table out performs both options.

More on partitioning in the future.

Follow

Get every new post delivered to your Inbox.

Join 1,818 other followers