jump to navigation

Rebuilding Indexes: Danger With Clustering Factor Calculation (Chilly Down) July 17, 2018

Posted by Richard Foote in CBO, Clustering Factor, Data Clustering, Index Rebuild, Oracle Indexes, TABLE_CACHED_BLOCKS.
add a comment

chilly down

Let me start by saying if you don’t already following Jonathan Lewis’s excellent Oracle blog, do yourself a favour. In a recent article, Jonathan highlighted a danger with rebuilding indexes (or indeed creating an index) when used in relation to collecting index statistics with the TABLE_CACHED_BLOCKS preference.

I’ve discussed the importance of the TABLE_CACHED_BLOCKS statistics collection preference a number of times previously, but the issue discussed by Jonathan is worth repeating here.

Let me start by repeating a demo I’ve used previously, by creating a table stored in an ASSM tablespace with data that is well clustered, but reported as being badly clustered due to how the Clustering Factor (CF) is calculated by default.

Firstly, I create a simple table and sequence and run a procedure that populates the table with a monotonically increasing ID column  populated via the sequence. But importantly, the procedure is executed concurrently from 3 separate sessions such that the monotonically increasing ID values are not stored in the table in precisely ID order as each of the 3 sessions inserts rows into different sets of table blocks:

SQL> create table bowie_assm (id number, name varchar2(42));

Table created.

SQL> create sequence bowie_assm_seq order;

Sequence created.

SQL> create or replace procedure pop_bowie_assm as
2 begin
3 for i in 1..100000 loop
4 insert into bowie_assm values (bowie_assm_seq.nextval, 'DAVID BOWIE');
5 commit;
6 end loop;
7 end;
8 /

Procedure created.

The following is executed concurrently in 3 different sessions:

SQL> exec pop_bowie_assm

PL/SQL procedure successfully completed.

If you can imagine 3 different blocks within the table, block one has rows with ID values 1,4,7,10,13,16…, block two has rows with ID values 2,5,8,11,14,17… and block three has rows with ID values 3,6,9,12,15,18…

So the data is well clustered in that the data for a large number of consecutive IDs are stored within a few blocks, but they’re not stored precisely in ID order within the table.

If we now create an index on the ID column and look at the Clustering Factor (CF) of the index:

SQL> create index bowie_assm_id_i on bowie_assm(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_ASSM');

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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000            219416

We note the calculated CF is extremely poor at 219416 (a value much closer to the number of index entries than the number of blocks in the table) as the default calculation notes that most index entries have a rowid that points to a different table block to the previous index entry rowid.

If we run a query that only requires a moderate number of rows (approx. 0.13% of the table) to be returned:

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

388 rows selected.

Execution Plan
--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU) |  Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |            |  389 |  6613 |    282 (11) |  00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE_ASSM |  389 |  6613 |    282 (11) |  00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
 974 consistent gets
   0 physical reads
   0 redo size
8869 bytes sent via SQL*Net to client
 883 bytes received via SQL*Net from client
  27 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 388 rows processed

We note the CBO decides to use a Full Table Scan (FTS) as the index is too costly and inefficient to use with such a poor CF value.

However, if say retrieving 100 rows, the CBO thinks it needs to visit many more table blocks than the 3 blocks that in actual fact contain the 100 rows of interest.

The TABLE_CACHED_BLOCKS statistics preference allows us to modify how the CF is calculated by not incrementing the CF value if an index rowid points to a block that was visited just TABLE_CACHED_BLOCKS ago.

If we now re-calculate the CF but with the TABLE_CACHED_BLOCKS preference set to say 42:

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

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ASSM_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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000               909

We notice the CF has dropped significantly, down to just 909 from its previous 219416 value.

If we now re-run the same query as before:

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

388 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name            | Rows | Bytes | Cost (%CPU) | Time      |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                 |  389 |  6613 |       4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_ASSM      |  389 |  6613 |       4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ASSM_ID_I |  389 |       |       2 (0) |  00:00:01 |
-------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
   6  consistent gets
   0  physical reads
   0  redo size
8734  bytes sent via SQL*Net to client
 608  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 CBO now automatically decides to use the index and more importantly, that at just 6 consistent gets, the query is now much more efficient as a result.

The index was always the more efficient access method, but because of the poor CF that was previously calculated, the CBO got it wrong. Now that a more “accurate” CF is calculated, all is now well.

However, if we now decide to rebuild this index:

alter index bowie_assm_id_i rebuild;

Index altered.

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

388 rows selected.

Execution Plan
--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU) | Time      |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |            |  389 |  6613 |    282 (11) |  00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE_ASSM |  389 |  6613 |    282 (11) |  00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   3 recursive calls
   0 db block gets
 956 consistent gets
   0 physical reads
   0 redo size
4094 bytes sent via SQL*Net to client
 608 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 388 rows processed

So we’re back to the less efficient FTS. Why ? A look at the CF reveals the problem:

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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000            219416

When the index is rebuilt and so when the index statistics are implicitly recalculated, the TABLE_CACHED_BLOCKS preference is ignored. This applies even if this preference is set at the schema or database level:

SQL> exec dbms_stats.set_schema_prefs(ownname=>user, pname=>'TABLE_CACHED_BLOCKS', pvalue=>42);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_database_prefs(pname=>'TABLE_CACHED_BLOCKS', pvalue=>42);

PL/SQL procedure successfully completed.

SQL> alter index bowie_assm_id_i rebuild online;

Index altered.

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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000            219416

This issue also applies when an index is newly created, any TABLE_CACHED_BLOCKS setting is ignored, until the time when statistics are again collected via DBMS_STATS:

SQL> drop index bowie_assm_id_i;

Index dropped.

SQL> create index bowie_assm_id_i on bowie_assm(id);

Index created.

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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000            219416

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ASSM_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_ASSM_ID_I';

TABLE_NAME      INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
--------------- -------------------- ---------- ---------- -----------------
BOWIE_ASSM      BOWIE_ASSM_ID_I            1000     300000               909

This is currently being investigation by Oracle as unpublished bug 28292026.

Again, another example of the dangers of blindly rebuilding indexes without a valid justification…

Advertisements

Clustering Factor Calculation Improvement Part III (Too Much Rope) June 4, 2013

Posted by Richard Foote in 11g, CBO, Clustering Factor, Index statistics, TABLE_CACHED_BLOCKS.
9 comments

In my previous post, I discussed how for larger tables that really do have randomised data, not even setting the new TABLE_CACHED_BLOCKS to its 255 maximum value is going to make any real difference to the resultant Clustering Factor (CF) of an index. The 255 maximum value here protects us somewhat from abusing this capability and setting unrealistic CF values.

However, for smaller tables in particular, we do need to exercise some caution.

In the following example, we’re only creating a relatively small table and associated index with a CODE column that is randomly distributed throughout the table:

SQL> create table bowie (id number, code number, text varchar2(30));
Table created.

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

70000 rows created.

SQL> commit;

Commit complete.

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> create index bowie_code_i on bowie(code);

Index created.

If we look at the CF of this index:

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

TABLE_NAME   INDEX_NAME       BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ ------------ ---------- ---------- -----------------
BOWIE        BOWIE_CODE_I        244      70000             22711

We notice that at 22,711, the CF it’s pretty average. The table though is quite small at only 244 blocks.

If we run a simple query:

SQL> select * from bowie where code between 42 and 44;
2050 rows selected.

Execution Plan
----------------------------------------------------------

Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  2814 | 56280 |    65   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |  2814 | 56280 |    65   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

1 - filter('CODE'<=44 AND 'CODE'>=42)

Statistics
----------------------------------------------------------

0  recursive calls
1  db block gets
254  consistent gets
0  physical reads
0  redo size
25044  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)
2050  rows processed

The CBO goes for a Full Table Scan. This is not unexpected as we likely have to visit all 244 blocks anyways to fetch the required 2050 rows due to the CODE data being so randomly distributed throughout the table. In a naive attempt to improve things, we decide to improve the CF by setting the TABLE_CACHED_BLOCKS to the maximum 255 value:

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

PL/SQL procedure successfully completed.

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
FROM user_tables t, user_indexes i WHERE t.table_name = i.table_name AND i.index_name='BOWIE_CODE_I';

TABLE_NAME   INDEX_NAME         BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------ -------------- ---------- ---------- -----------------
BOWIE        BOWIE_CODE_I          244      70000               240

Indeed, we have improved the CF, dramatically reducing it down to just 240 from the previous 22711. Even though the column value for the CODE really is randomly distributed throughout the table, the CF now suggests the data is perfectly clustered. This is because with a table with only 244 blocks, incrementing the CF if the current index entry references a table block more than 255 blocks ago is now impossible. The CF is now guaranteed to be “perfect” as each index entry can only reference one of the 244 table blocks and so is incremented only when each table block is referenced the first time.

This dramatic reduction in the CF will certainly make the index more attractive to the CBO. But is this really a good thing:

SQL> select * from bowie where code between 42 and 44;
2050 rows selected.

Execution Plan

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

Plan hash value: 1602289932
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  2814 | 56280 |    17   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOWIE        |  2814 | 56280 |    17   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BOWIE_CODE_I |  2814 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - access('CODE'>=42 AND 'CODE'<=44)

Statistics
----------------------------------------------------------

0  recursive calls
0  db block gets
681  consistent gets
0  physical reads
0  redo size
20895  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)
2050  rows processed

The CBO is now using the index, but is now performing significantly more consistent gets, 681 rather than the previous 254. This execution plan is actually much less efficient than the previous FTS execution plan. The CBO is getting this wrong now as the CF isn’t really anywhere near as good as it’s now being lead to believe.

Caution setting TABLE_CACHED_BLOCKS to a value that is anywhere close to the number of blocks in the table. This is one of the reasons for Oracle having a 1% of table blocks default value for this setting.