jump to navigation

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part II (Wild Is The Wind) August 10, 2020

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Clustering Factor, Oracle Indexes, Performance Tuning.
2 comments

 

In my previous post, I discussed a scenario in which Oracle Automatic Indexing refused to create a VALID index, because the resultant index was too inefficient to access the necessary rows due to the poor clustering of data within the table.

If the performance of such an SQL were critical for business requirements, there is a way to address this scenario, by re-clustering the data within the table to align itself with the index. Although the re-clustering table operation can now be very easily performed online since Oracle Database 12.2 (without having to use the dbms_redefinition process), this is NOT automatically performed within the Autonomous Database self-tuning framework (yet).

But it’s an activity we can perform manually to improve the performance of such critical SQLs as follows:

SQL> alter table nickcave add clustering by linear order(code);

Table altered.

SQL> alter table nickcave move online;

Table altered.

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='NICKCAVE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_dh8pumfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE          0           0                 0

 

With the data in the table now perfectly aligned with the index, we would ordinarily now expect the index to be more efficient method to retrieve this 1% of the data.

However, if we now re-run the previously executed SQLs each a number of times:

 

SQL> select * from nickcave where code=24;

SQL> select * from nickcave where code=42;

SQL> select * from nickcave where code=13;

And now wait until next Automatic Indexing process period:

SQL> select dbms_auto_index.report_last_activity() report from dual;

...

We notice that the Automatic Index is still NOT mentioned in the Automatic Indexing reports and still remains UNUSABLE:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='NICKCAVE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_dh8pumfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE          0           0                 0

 

So what’s going on?

In order to prevent the same SQLs from being continually re-evaluated to see if an index might be preferable, the Automatic Indexing process puts previously evaluated SQLs on a type of blacklist and therefore don’t get subsequently re-evaluated.

So although the new clustering of the data within the table would now likely warrant the creation of a new index, if we just run the some SQLs as previously, nothing changes. No Automatic Index is created and the SQLs remain in their current “sub-optimal” state.

In Part III, we’ll look at how to finally get Automatic Indexing to create these indexes and improve the performance of these queries…

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part I (Don’t Look Down) August 6, 2020

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Clustering Factor, Full Table Scans, Index Rebuild, Index statistics, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle19c, Performance Tuning.
4 comments

I’ve discussed many times the importance of data clustering in relation to the efficiency of indexes. With respect to the efficiency of Automatic Indexes including their usage within Oracle’s Autonomous Database environments, data clustering is just as important.

The following demo was run on an Oracle 19c database within the Oracle Autonomous Database Transaction Processing Cloud environment.

I begin by creating a simple table that has the key column CODE, in which data is populated in a manner where the data is very poorly clustered:

 

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

Table created.

SQL> insert into nickcave select rownum, mod(rownum, 100), 'Nick Cave and the Bad Seeds'
     from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

So we have 100 evenly distributed distinct CODE values but they’re all distributed throughout the table.

The following SQL statement is basically returning just 1% of the data and is executed a number of times:

 

SQL> select * from nickcave where code=42;

100000 rows selected.

Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     100K|  3613K|  9125   (5)| 00:00:01|
|   1 |  PX COORDINATOR              |          |         |       |            |         |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000 |     100K|  3613K|  9125   (5)| 00:00:01|
|   3 |    PX BLOCK ITERATOR         |          |     100K|  3613K|  9125   (5)| 00:00:01|
|*  4 |     TABLE ACCESS STORAGE FULL| NICKCAVE |     100K|  3613K|  9125   (5)| 00:00:01|
------------------------------------------------------------------------------------------

Without an index, the CBO currently has no choice but to use a Full Table Scan to access the table. So we wait for the next Automatic Index process to kick in:

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

 

The Automatic Indexing report makes no mention of Automatic Indexes on the NICKCAVE table…

If we look to see if any indexes have actually been created:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor 
     from user_indexes where table_name='NICKCAVE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_dh8pumfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE   10000000       20346           4158302

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='NICKCAVE'
     order by index_name, column_position;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_AI_dh8pumfww3f4r CODE                               1

 

We can see that yes, an Automatic Index (SYS_AI_dh8pumfww3f4r) has been created on the CODE column of the NICKCAVE table BUT it remains in an INVISIBLE, UNUSABLE state.

So Automatic Indexing considered an index on CODE, created it in an INVISIBLE, USABLE state but when testing it, failed in that it found it to be less efficient than the current FTS and so reverted the Automatic Index back to an UNUSABLE index.

Therefore, if we run a bunch of other similar SQL statements such as the following:

SQL> select * from nickcave where code=24;

SQL> select * from nickcave where code=42;

SQL> select * from nickcave where code=13;

 

They all use the FTS as again, the CBO has no choice with no VALID index on the CODE column available.

If we keep checking the Automatic Indexing report:

SQL> select dbms_auto_index.report_last_activity() report from dual;

 

There’s still no mention of an index on the CODE column. The existing Automatic Index remains in an UNUSABLE state:

 

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='NICKCAVE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_dh8pumfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE   10000000       20346           4158302

 

Basically, the index remains ineffective because with a Clustering Factor of 4158302, it’s just too inefficient to return the 1% (100000 rows) of the table.

Even in an Autonomous Database environment, nothing will automatically change with this scenario.

In my next post, we’ll look at how we can improve the performance of this query and get an Automatic Index to actually kick in with a USABLE index…

Oracle 19c Automatic Indexing: Common Index Creation Trap (Rat Trap) June 30, 2020

Posted by Richard Foote in 19c, 19c New Features, ASSM, Automatic Indexing, CBO, Clustering Factor, Data Clustering, Oracle Indexes, TABLE_CACHED_BLOCKS.
1 comment so far

When I go to a customer site to resolve performance issues, one of the most common issues I encounter is in relation to inefficient SQL. And one of the most common causes for inefficient SQL I encounter is because of deficiencies the default manner by which the index Clustering Factor is calculated.

When it comes to both Automatic Indexes and in relation to the Oracle Autonomous Database Cloud Services, the “flawed” default manner by which the index Clustering Factor is calculated still applies. So we need to exercise some caution when Auto Indexes are created and the impact their default statistics can have on the performance of subsequent SQL statements.

To illustrate with a simple example, I’ll first create a table with the key column being the ID column which will be effectively unique. The table will be populated via a basic procedure that just inserts 1M rows. The procedure uses an ORDER sequence, such that the ID values are generated in a monotonically increasing manner:

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

Table created.

SQL> create sequence bowie_assm_seq order;

Sequence created.

Procedure created.

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

Procedure created.

 

However crucially, the procedure is executed by 3 different session concurrently, to simulate a multi user environment inserting into a table…

 

SQL> exec pop_bowie_assm

PL/SQL procedure successfully completed.

 

We’ll now collect statistics on the table:

 

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE_ASSM';

TABLE_NAME        NUM_ROWS     BLOCKS
--------------- ---------- ----------
BOWIE_ASSM         3000000      12137

 

So the table has 3M rows and is 12137 blocks in size.

If we run an SQL a few times where we select only the one ID value:

 

SQL> select * from bowie_assm where id = 42;

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    22 |  1934   (6)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |     1 |    22 |  1934   (6)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |     1 |    22 |  1934   (6)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| BOWIE_ASSM |     1 |    22 |  1934   (6)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

4 - storage("ID"=42)
    filter("ID"=42)

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
     12138  consistent gets
         0  physical reads
         0  redo size
       707  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

 

The execution plan shows a Full Table Scan (FTS) is invoked, the only choice the CBO has without an index on the ID column. Clearly an index on the ID column would make the plan substantially more efficient with just 1 row selected from a 3M row table. Hopefully, Automatic Indexing will come to our rescue, so let’s check out the subsequent Automatic Indexing Report:

 

REPORT

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                              : 1
Indexes created (visible / invisible)         : 1 (1 / 0)
Space used (visible / invisible)              : 58.72 MB (58.72 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 2
SQL statements improved (improvement factor)  : 1 (1.2x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 1.1x

-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------

The following indexes were created:
-------------------------------------------------------------------------
| Owner | Table      | Index                | Key | Type   | Properties |
-------------------------------------------------------------------------
| BOWIE | BOWIE_ASSM | SYS_AI_2w1pss6qbdz6z | ID  | B-TREE | NONE       |
-------------------------------------------------------------------------

So yes indeed, an Automatic Index (SYS_AI_2w1pss6qbdz6z) was created on the ID column.

If we look at the default Clustering Factor of this index:

 

SQL> select index_name, auto, constraint_index, visibility, status, clustering_factor from user_indexes where table_name='BOWIE_ASSM';

INDEX_NAME           AUT CON VISIBILIT STATUS   CLUSTERING_FACTOR
-------------------- --- --- --------- -------- -----------------
SYS_AI_2w1pss6qbdz6z YES NO  VISIBLE   VALID              2504869

 

We notice the Clustering Factor is relatively high at 2504869, much higher than the 12137 number of blocks in the table.

But if the ID column in the table has been loaded via a monotonically increasing sequence, doesn’t that mean the ID values have been inserted in approximately in ID order? If so, doesn’t that mean the ID column should have a “good” Clustering Factor” as the order of the rows in the table matches the order of the indexed values in the ID index?

Clearly not.

The reason being that the table is stored in the default Automatic Segment Space Management (ASSM) tablespace type, which is designed to avoid contention by concurrent inserts from different sessions. Therefore each of the 3 sessions inserting into the table are each assigned to different table blocks, resulting in the rows not being precisely inserted in ID order. It’s very close to ID order, the the ID values clustered within a few blocks from each other, but not precisely stored in ID order.

However, by default, the Clustering Factor is calculated by reading each index entry and determining if it references a ROWID that accesses a table block different from the PREVIOUS index entry. If it does differ, it increments the Clustering Factor, if it doesn’t differ and accesses the same table block as the previous index entry, the Clustering Factor is NOT incremented.

So in theory, we could have 100 rows that reside in just 2 different table blocks, but if the odd IDs live in one block and the even IDs live in the other block, meaning that each ID is stored in a different table block to the previous, the Clustering Factor would have a value of 100 for these 100 rows, even though they only occupy 2 table blocks. The Clustering Factor is therefore much higher than in reality it should be as ultimately only 2 different table blocks are accessed within a negligible time from each other.

This is the “flaw” with how the default Clustering Factor is calculated. By noting if a table block access differs only from the previous table block accessed, it leaves the Clustering Factor calculation susceptible to exaggerated high values when the data really is relatively well clustered within the table.

If we run the same SQL as previously which only selects one ID value:

 

SQL> select * from bowie_assm where id = 42;

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |     1 |    22 |     4   (0)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             |     1 |    22 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM           |     1 |    22 |     4   (0)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      |     1 |       |     3   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             |     1 |       |     3   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | SYS_AI_2w1pss6qbdz6z |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("ID"=42)

Statistics
----------------------------------------------------------
        12  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       707  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
         1  rows processed

 

The CBO now uses the new Automatic Index as with just one row, the index is clearly more efficient regardless of the Clustering Factor value.

However, if we now run a query that selects a range of ID values, in this example between 42 and 4242 which represents only a relatively low 0.14% of the table:

 

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

4201 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  4202 | 92444 |  1934   (6)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |  4202 | 92444 |  1934   (6)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |  4202 | 92444 |  1934   (6)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| BOWIE_ASSM |  4202 | 92444 |  1934   (6)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

4 - storage("ID"<=4242 AND "ID">=42)
    filter("ID"<=4242 AND "ID">=42)

Statistics
----------------------------------------------------------
         8  recursive calls
         4  db block gets
     12138  consistent gets
         0  physical reads
         0  redo size
     54767  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
      4201  rows processed

 

The CBO decides to use a Full Table Scan as it deems the index with the massive Clustering Factor to be too expensive, with it having to visit differing blocks for the majority of the estimated 4202 rows (note at 4201 actual rows returned, this estimate by the CBO is practically spot on).

If we force the use of the index via an appropriate hint:

 

SQL> select /*+ index (bowie_assm) */ * from bowie_assm where id between 42 and 4242;

4201 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |  4202 | 92444 |  3530   (1)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             |  4202 | 92444 |  3530   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM           |  4202 | 92444 |  3530   (1)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      |  4202 |       |    12   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             |  4202 |       |    12   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |         INDEX RANGE SCAN              | SYS_AI_2w1pss6qbdz6z |  4202 |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("ID">=42 AND "ID"<=4242)

Statistics
----------------------------------------------------------
        12  recursive calls
         0  db block gets
        26  consistent gets
         0  physical reads
         0  redo size
     54767  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
      4201  rows processed

 

Note at an estimated cost of 3530, this is greater than the 1934 cost of the FTS which explains why the CBO decides the FTS is best. However, if we look at the number of Consistent Gets, it’s only 26, meaning the CBO is actually getting these costs way wrong.

Why?

Because of the grossly inflated Clustering Factor.

As I’ve discussed previously, Oracle 12.1 introduced a new TABLE_CACHED_BLOCKS preference. Rather than the default value of 1, we can set this to any value up to 255. When calculating the Clustering Factor during statistics collection, it will NOT increment the Clustering Factor if the index visits a table block again that was one of the last “x” distinct table blocks visited. So by setting TABLE_CACHED_BLOCKS to (say) 42, if the index visits a block that was one of the last 42 distinct table blocks previously visited, don’t now increment the Clustering Factor. This can therefore generate a much more “accurate” Clustering Factor which can be significantly smaller than previously. This in turn makes the index much more efficient to the CBO because it then estimates far fewer table blocks need be accessed during a range scan.

So let’s change the TABLE_CACHED_BLOCKS value for this table to 42 (don’t increment now the Clustering Factor value when collecting statistics if we visit again any of the last 42 differently accessed table blocks) and recollect the segment statistics:

 

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_table_stats(ownname=>null, tabname=>'BOWIE_ASSM', cascade=>true);

PL/SQL procedure successfully completed.

 

If we now examine the new Clustering Factor value:

 

SQL> select index_name, auto, constraint_index, visibility, status, clustering_factor from user_indexes

where table_name='BOWIE_ASSM';

INDEX_NAME           AUT CON VISIBILIT STATUS   CLUSTERING_FACTOR
-------------------- --- --- --------- -------- -----------------
SYS_AI_2w1pss6qbdz6z YES NO  VISIBLE   VALID                11608

 

We can see that at just 11608 it’s substantially less than the previous 2504869.

If we now rerun the previous range scan SQL without the hint:

 

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

4201 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |  4202 | 92444 |    30   (4)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             |  4202 | 92444 |    30   (4)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM           |  4202 | 92444 |    30   (4)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      |  4202 |       |    12   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             |  4202 |       |    12   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | SYS_AI_2w1pss6qbdz6z |  4202 |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("ID">=42 AND "ID"<=4242)

Statistics
----------------------------------------------------------
        12  recursive calls
         0  db block gets
        26  consistent gets
         0  physical reads
         0  redo size
     54767  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
      4201  rows processed

 

We can see the CBO now automatically uses the new Automatic Index. At a new cost of just 30, it’s substantially less than the previous index cost of 3530 and now much less than the 1934 for the FTS and so why the index is now automatically chosen by the CBO.

When Automatic Indexes are created, it’s usually a good idea to check on the Clustering Factor and because default ASSM tablespaces have a tendency to significantly escalate the values of index Clustering Factors, to look at recalculating them with an non-default setting of the TABLE_CACHED_BLOCKS statistics collection preference.

Of course, not only is this a good idea for Automatic Indexes, but for manually created indexes as well.

Although no doubt Autonomous Database Cloud services will look at these issues in the future, such self-tuning capabilities are not currently available. You will need to go in there and make these changes as necessary to fix the root issues with such inefficient SQL statements…

Oracle 19c Automatic Indexing: A More Complex Example (How Does The Grass Grow) June 16, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO.
3 comments

In this post I’m going to put together in a slightly more complex SQL example a number of the concepts I’ve covered thus far in relation to the current implementation of Oracle Automatic Indexing.

I’ll begin by creating three tables, a larger TABLE1 and two smaller TABLE2 and TABLE3 lookup tables. Each table is created with only a Primary Key, Unique index and currently have no secondary indexes (this demo was run in an ATP Autonomous Cloud environment hence the odd parallel execution plans):

SQL> create table table1 (id number not null, code1 number not null, grade1 number not null, name1 varchar2(42));

Table created.

SQL> insert into table1 select rownum, mod(rownum, 1000)+1, mod(rownum, 200000)+1, 'David Bowie '|| rownum
from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table1_id_i on table1(id);

Index created.

SQL> alter table table1 add primary key(id);

Table altered.

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

PL/SQL procedure successfully completed.


SQL> create table table2 (id number not null, stuff number not null, name2 varchar2(42));

Table created.

SQL> insert into table2 select rownum, mod(rownum, 100)+1, 'Ziggy Stardust ' || rownum from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table2_id_i on table2(id);

Index created.

SQL> alter table table2 add primary key(id);

Table altered.

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

PL/SQL procedure successfully completed.


SQL> create table table3 (id number not null, code3 number not null, name3 varchar2(42));

Table created.

SQL> insert into table3 select rownum, mod(rownum, 500)+1, 'Thin White Duke ' || rownum from dual connect by level <=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table3_id_i on table3(id);

Index created.

SQL> alter table table3 add primary key(id);

Table altered.

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

PL/SQL procedure successfully completed.

 

I’ll next run the following “complex” query a number of times:

 

SQL> select code1, grade1, name1, name2 from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=42);

10 rows selected.

Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |    10 |   600 |     857  (7)| 00:00:01 |
|   1 |  PX COORDINATOR                  |             |       |       |             |          |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000    |    10 |   600 |     857  (7)| 00:00:01 |
|   3 |    NESTED LOOPS                  |             |    10 |   600 |     857  (7)| 00:00:01 |
|   4 |     NESTED LOOPS                 |             |    10 |   600 |     857  (7)| 00:00:01 |
|*  5 |      HASH JOIN                   |             |    10 |   360 |     852  (7)| 00:00:01 |
|   6 |       JOIN FILTER CREATE         | :BF0000     |     2 |    16 |       2  (0)| 00:00:01 |
|*  7 |        TABLE ACCESS STORAGE FULL | TABLE3      |     2 |    16 |       2  (0)| 00:00:01 |
|   8 |       JOIN FILTER USE            | :BF0000     |  1000K|    26M|     833  (5)| 00:00:01 |
|   9 |        PX BLOCK ITERATOR         |             |  1000K|    26M|     833  (5)| 00:00:01 |
|* 10 |   TABLE ACCESS STORAGE FULL      | TABLE1      |  1000K|    26M|     833  (5)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN           | TABLE2_ID_I |     1 |       |       0  (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID  | TABLE2      |     1 |    24 |       1  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

      5 - access("TABLE1"."GRADE1"="TABLE3"."ID")
      7 - storage("TABLE3"."CODE3"=42)
          filter("TABLE3"."CODE3"=42)
     10 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
          filter(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
     11 - access("TABLE1"."CODE1"="TABLE2"."ID")

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
      5777  consistent gets
         0  physical reads
         0  redo size
      1224  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

 

We note there are three predicates listed in which a column access could potentially benefit from an index being created:

table1.code1=table2.id (note that table2.id already has a unique index defined)

table1.grade1 in (Select…)

table3.code3=42

 

Let’s have a look at the corresponding Automatic Indexing report to see what the Oracle Automatic Indexing process made of this example:

 

REPORT

--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start                  : 26-JUN-2019 08:56:08
Activity end                    : 26-JUN-2019 08:56:53
Executions completed            : 1
Executions interrupted          : 0
Executions with fatal error     : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------

Index candidates                                : 3
Indexes created (visible / invisible)           : 2 (2 / 0)
Space used (visible / invisible)                : 18.94 MB (18.94 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 2
SQL statements improved (improvement factor)    : 1 (192.7x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 170.2x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

 

We note there are 3 index candidates that were considered, BUT only 2 new indexes were actually created. Overall, the created indexes resulted in an estimated 192.7x improvement in the above SQL performance.

If we look further on in the report and at the actual indexes created:

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
------------------------------------------------------------------------
| Owner | Table  | Index                | Key    | Type   | Properties |
------------------------------------------------------------------------
| BOWIE | TABLE1 | SYS_AI_0p5nn18dt9bn1 | GRADE1 | B-TREE | NONE       |
| BOWIE | TABLE3 | SYS_AI_b4ntgxt6pdbfh | CODE3  | B-TREE | NONE       |
------------------------------------------------------------------------

VERIFICATION DETAILS

-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 21v8yqs9jrnzm
SQL Text             : select code1, grade1, name1, name2 from table1, table2
                       where table1.code1=table2.id and table1.grade1 in (select table3.id from table3 where table3.code3=42)
Improvement Factor   : 192.7x

Execution Statistics:
-----------------------------

                                Original Plan                 Auto Index Plan
                                ----------------------------  ----------------------------

Elapsed Time (s):               299396                        2170
CPU Time (s):                   289510                        1036
Buffer Gets:                    28912                         43
Optimizer Cost:                 857                           27
Disk Reads:                     0                             4
Direct Writes:                  0                             0
Rows Processed:                 50                            10
Executions:                     5                             1

 

We note the report states the two new indexes are created on the TABLE1.GRADE1 and TABLE3.CODE3 columns.

 

If look down further in the report and compare the before and after execution plans in the PLANS SECTION:

 

PLANS SECTION

- Original

-----------------------------
Plan Hash Value  : 1597150496
------------------------------------------------------------------------------------------------
| Id | Operation                          | Name        | Rows    | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |             |         |          |  857 |          |
|  1 |  PX COORDINATOR                    |             |         |          |      |          |
|  2 |    PX SEND QC (RANDOM)             | :TQ10000    |      10 |      600 |  857 | 00:00:01 |
|  3 |     NESTED LOOPS                   |             |      10 |      600 |  857 | 00:00:01 |
|  4 |      NESTED LOOPS                  |             |      10 |      600 |  857 | 00:00:01 |
|  5 |       HASH JOIN                    |             |      10 |      360 |  852 | 00:00:01 |
|  6 |        JOIN FILTER CREATE          | :BF0000     |       2 |       16 |    2 | 00:00:01 |
|  7 |         TABLE ACCESS STORAGE FULL  | TABLE3      |       2 |       16 |    2 | 00:00:01 |
|  8 |        JOIN FILTER USE             | :BF0000     | 1000000 | 28000000 |  833 | 00:00:01 |
|  9 |         PX BLOCK ITERATOR          |             | 1000000 | 28000000 |  833 | 00:00:01 |
| 10 |  TABLE ACCESS STORAGE FULL         | TABLE1      | 1000000 | 28000000 |  833 | 00:00:01 |
| 11 |       INDEX UNIQUE SCAN            | TABLE2_ID_I |       1 |          |    0 |          |
| 12 |      TABLE ACCESS BY INDEX ROWID   | TABLE2      |       1 |       24 |    1 | 00:00:01 |
------------------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 2014256176
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |   10 |   600 |   27 | 00:00:01 |
|   1 |   NESTED LOOPS                           |                      |   10 |   600 |   27 | 00:00:01 |
|   2 |    NESTED LOOPS                          |                      |   10 |   600 |   27 | 00:00:01 |
|   3 |     NESTED LOOPS                         |                      |   10 |   360 |   17 | 00:00:01 |
|   4 |      TABLE ACCESS BY INDEX ROWID BATCHED | TABLE3               |    2 |    16 |    3 | 00:00:01 |
| * 5 |       INDEX RANGE SCAN                   | SYS_AI_b4ntgxt6pdbfh |    2 |       |    1 | 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED | TABLE1               |    5 |   140 |    7 | 00:00:01 |
| * 7 |       INDEX RANGE SCAN                   | SYS_AI_0p5nn18dt9bn1 |    5 |       |    2 | 00:00:01 |
| * 8 |     INDEX UNIQUE SCAN                    | TABLE2_ID_I          |    1 |       |    0 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID           | TABLE2               |    1 |    24 |    1 | 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

* 5 - access("TABLE3"."CODE3"=42)
* 7 - access("TABLE1"."GRADE1"="TABLE3"."ID")
* 8 - access("TABLE1"."CODE1"="TABLE2"."ID")

 

We can see that the new plan uses both the new automatic indexes and has a new improved cost of 27 (down from 857).

 

If we look at all the indexes that have been created on these tables:

 

SQL> select table_name, index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name like 'TABLE%';

TABLE_NAME   INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------ ---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
TABLE1       TABLE1_ID_I            NO  NO  VISIBLE   DISABLED      VALID       1000000        2088              5202
TABLE1       SYS_AI_85dcbcnkgj0w0   YES NO  INVISIBLE DISABLED      UNUSABLE    1000000        2171            415525
TABLE1       SYS_AI_0p5nn18dt9bn1   YES NO  VISIBLE   DISABLED      VALID       1000000        2221           1000000
TABLE2       TABLE2_ID_I            NO  NO  VISIBLE   DISABLED      VALID         10000          20                44
TABLE3       TABLE3_ID_I            NO  NO  VISIBLE   DISABLED      VALID          1000           2                 5
TABLE3       SYS_AI_b4ntgxt6pdbfh   YES NO  VISIBLE   DISABLED      VALID          1000           3               999

SQL> select table_name, index_name, column_name, column_position from user_ind_columns
where table_name like 'TABLE%' order by table_name, index_name, column_position;

TABLE_NAME   INDEX_NAME             COLUMN_NAME     COLUMN_POSITION
------------ ---------------------- --------------- ---------------
TABLE1       SYS_AI_0p5nn18dt9bn1   GRADE1                        1
TABLE1       SYS_AI_85dcbcnkgj0w0   CODE1                         1
TABLE1       TABLE1_ID_I            ID                            1
TABLE2       TABLE2_ID_I            ID                            1
TABLE3       SYS_AI_b4ntgxt6pdbfh   CODE3                         1
TABLE3       TABLE3_ID_I            ID                            1

 

We note that both indexes listed as created in the Auto Indexing report on the TABLE1.GRADE1 (and TABLE3.CODE3 columns are both listed as being VISIBLE and VALID. Both of these indexes have been proven to improve the performance of the SQL statement by reducing the number of logical reads.

 

However, there is also an Auto Indexed defined on the other potential indexed column table1.code1, called “SYS_AI_85dcbcnkgj0w0” that has been left in an INVISIBLE, UNUSABLE state. This index has been shown to be ineffective in improving SQL performance and has been converted back to an UNUSABLE state.

If we run the query again and look at the resultant execution plan:

 

SQL> select code1, grade1, name1, name2
from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=24);

10 rows selected.

Execution Plan
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   1 |  PX COORDINATOR                          |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                    | :TQ10000             |    10 |   600 |    15   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                          |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                         |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                        |                      |    10 |   360 |    10   (0)| 00:00:01 |
|   6 |       PX BLOCK ITERATOR                  |                      |       |       |            |          |
|*  7 |        TABLE ACCESS STORAGE FULL         | TABLE3               |     2 |    16 |     2   (0)| 00:00:01 | 
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1               |     5 |   140 |     4   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN                  | SYS_AI_0p5nn18dt9bn1 |     5 |       |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN                   | TABLE2_ID_I          |     1 |       |     0   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID          | TABLE2               |     1 |    24 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   7 - storage("TABLE3"."CODE3"=24)
       filter("TABLE3"."CODE3"=24)
   9 - access("TABLE1"."GRADE1"="TABLE3"."ID")
  10 - access("TABLE1"."CODE1"="TABLE2"."ID")

Statistics
----------------------------------------------------------
        10  recursive calls
         4  db block gets
        59  consistent gets
         0  physical reads
         0  redo size
      1122  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

 

We note the new execution plan now only uses one of newly created Automatic Indexes (SYS_AI_0p5nn18dt9bn1) and at just 59 consistent gets, is significantly more efficient than it was previously where it required 5777 consistent gets.

However, the other VISIBLE automatic index (SYS_AI_b4ntgxt6pdbfh on the BOWIE3 table) is NOT actually used in the new plan. Why?

Because as discussed previously, it’s entirely possible for the Auto Indexing process to consider a new plan with Auto Index to be more efficient because it uses less consistent gets BUT the CBO not use the plan because the plan has a higher cost. The plan generated by the Auto Index process has a cost of 27 and uses 43 buffer gets but the CBO uses the plan without the second Auto Index because it has a reduced cost of only 15, even though it uses 59 consistent gets.

So the Auto Indexing process can create any number of possible indexes for a particular query and may independently ultimately determine different states for the various candidate indexes and so only create and keep the necessary indexes to sufficiently improve an SQL.

We now have an SQL statement that automatic runs much more efficiently without human intervention thanks to these automatically created indexes…

Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon) May 12, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Index, Index Rebuild, Oracle Indexes.
3 comments

reality

 

Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future).

Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would be worth checking out.

For a number of reasons (which I’ll cover in some detail in upcoming articles, but for now using Automatic Indexing in REPORT ONLY mode is but one reason), you can easily be left with an Automatic Index that might get in the way of things and you may want to drop it.

However, as we’ll see, you can’t easily drop an Automatic Index. The only “supported” manner to drop an Automatic Index is to wait for the Automatic Index Retention period to be exceeded (which is by default some 373 days and assumes the index is not used during this period).

Julian has come up with an alternate strategy.

By way of a demo, I currently have the following Automatic Index (SYS_AI_5zjkc60knz9zp):

SQL> select index_name, auto, status, visibility from user_indexes
where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
SYS_AI_5zjkc60knz9zp           YES VALID    INVISIBLE

SQL> select index_name, column_name, column_position from user_ind_columns
where index_name='SYS_AI_5zjkc60knz9zp';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_AI_5zjkc60knz9zp           ID                                           1

 

So I have an Automatic Index on the ID column of the CRACKED_ACTOR table, BUT it’s currently INVISIBLE and so can’t be used be default by the CBO.

If I run the following query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 786009234

-----------------------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)| Time        |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |               |    1 |    24 |       3 (0)| 00:00:01    |
|* 1 | TABLE ACCESS FULL| CRACKED_ACTOR |    1 |    24 |       3 (0)| 00:00:01    |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

     1 - filter("ID"=42)
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
      7042 consistent gets
         0 physical reads
         0 redo size
       789 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

The CBO uses a Full Table Scan because the available Automatic Index on the ID column is current invisible.

If I try to convert it to being VISIBLE:

SQL> alter index "SYS_AI_5zjkc60knz9zp" visible;
alter index "SYS_AI_5zjkc60knz9zp" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

I can’t, because you can’t alter an Automatic Index to be Visible/Invisible.

If I try to just drop the Automatic Index:

SQL> drop index "SYS_AI_5zjkc60knz9zp";
drop index "SYS_AI_5zjkc60knz9zp"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Again, I can’t just simply drop an Automatic Index.

However, I am allowed to Rebuild (or Coalesce or Shrink) an Automatic Index. Therefore, I can create a new dummy tablespace and rebuild the Automatic Index to reside in this particular tablespace:

SQL> alter index "SYS_AI_5zjkc60knz9zp" rebuild tablespace bowie_stuff;

Index altered.

 

I can then drop this tablespace including all its contents (and hence drop the Automatic Index contained within):

SQL> drop tablespace bowie_stuff including contents and datafiles;

Tablespace dropped.

 

The problematic Automatic Index is now gone:

SQL> select index_name, auto, status, visibility from user_indexes
where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID     VISIBLE

I can now either manually create the necessary index or wait for the Automatic Index to now hopefully create a new, visible Automatic Index to address my query:

SQL> create index cracked_actor_id_i on cracked_actor(id);

Index created.

SQL> select index_name, auto, status, visibility from user_indexes where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
CRACKED_ACTOR_ID_I             NO  VALID    VISIBLE

 

I’ve now addressed the problematic query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 4160941723

----------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name               | Rows | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                    |    1 |    24 |       2 (0)| 00:00:01    |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED| CRACKED_ACTOR      |    1 |    24 |       2 (0)| 00:00:01    |
|* 2 | INDEX RANGE SCAN                   | CRACKED_ACTOR_ID_I |    1 |       |       1 (0)| 00:00:01    |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

      2 - access("ID"=42)
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         3 consistent gets
         0 physical reads
         0 redo size
       793 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

Thanks Julian for the cool tip 🙂

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay) May 6, 2020

Posted by Richard Foote in 19c, 19c New Features, Add Column To Existing Index, Automatic Indexing, Oracle Indexes.
2 comments

stay single

 

In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index.

I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a new index can be used effectively for both previous and new workloads.

In this post, how will Automatic Indexing handle the scenario if a previously manually created index could also potentially be improved by adding a new column.

I’ll start by creating a table similar to my previous post but with more distinct values for the CODE3 column such that the test query will be more selective and so make the CBO favour the use of an index (Note: all examples are run on the OLTP Autonomous Database Cloud Service and hence the odd parallel execution plans):

SQL> create table major_tom6 (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom6 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 100)), 'David Bowie' from dual connect by level = 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

I’ll now manually create an index for BOTH combinations of the CODE2, CODE3 columns:

 

SQL> create index major_tom6_code2_code3_i on major_tom6(code2, code3);

Index created.

SQL> create index major_tom6_code3_code2_i on major_tom6(code3, code2);

Index created.

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6';

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM6_CODE2_CODE3_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       23697           9890973
MAJOR_TOM6_CODE3_CODE2_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       23697           9890973

 

If I now run the following query:

SQL> select * from major_tom6 where code2=42 and code3=42;

983 rows selected.

Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |  1000 | 28000 |   997   (1)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                          |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001                 |  1000 | 28000 |   997   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6               |  1000 | 28000 |   997   (1)| 00:00:01 |
|   4 |     BUFFER SORT                       |                          |       |       |            |          |
|   5 |      PX RECEIVE                       |                          |  1000 |       |     5   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000                 |  1000 |       |     5   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                          |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | MAJOR_TOM6_CODE2_CODE3_I |  1000 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
     8 - access("CODE2"=42 AND "CODE3"=42)

Statistics
----------------------------------------------------------
        12  recursive calls
         0  db block gets
       971  consistent gets
         0  physical reads
         0  redo size
     27836  bytes sent via SQL*Net to client
      1303  bytes received via SQL*Net from client
        67  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
       983  rows processed

 

The CBO favours the use of an index as with just 983 rows returned from a 10M row table, the index is the cheaper access method.

If I now run the following SQL which also includes the more selectively CODE1 column predicate as well (which returns just 1 row):

SQL> select * from major_tom6 where code1=42 and code2=42 and code3=42;

Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |    28 |   997   (1)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                          |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001                 |     1 |    28 |   997   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6               |     1 |    28 |   997   (1)| 00:00:01 |
|   4 |     BUFFER SORT                       |                          |       |       |            |          |
|   5 |      PX RECEIVE                       |                          |  1000 |       |     5   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000                 |  1000 |       |     5   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                          |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | MAJOR_TOM6_CODE2_CODE3_I |  1000 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
     3 - filter("CODE1"=42)
     8 - access("CODE2"=42 AND "CODE3"=42)

Statistics
----------------------------------------------------------
        12  recursive calls
         0  db block gets
       971  consistent gets
         0  physical reads
         0  redo size
       867  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
         1  rows processed

 

The CBO again uses the same index based on columns CODE2, CODE3 as this has already been proven to be more efficient than a FTS. However, an index that also included the CODE1 column would be even more efficient as the CBO could simply use this index to fetch just the row(s) of interest, without having to perform the unnecessary filtering on the CODE1 column.

So what does Automatic Indexing do in this scenario? If we look at the corresponding Auto Indexing Report:

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM6 | SYS_AI_4nc6g08bw8db7 | CODE2,CODE3,CODE1 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

 

We notice Auto Indexing has created a new index based on columns CODE2, CODE3, CODE1, however it has NOT dropped any indexes.

If we look at the Verification section of the Auto Indexing Report:

 

VERIFICATION DETAILS

-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 93zw1kj4n43n9
SQL Text             : select * from major_tom6 where code1=42 and code2=42 and code3=42
Improvement Factor   : 972.2x

Execution Statistics:
-----------------------------

                              Original Plan                 Auto Index Plan
                              ----------------------------  ----------------------------
Elapsed Time (s):             159122                        1240
CPU Time (s):                 70379                         1320
Buffer Gets:                  10698                         4
Optimizer Cost:               997                           4
Disk Reads:                   0                             2
Direct Writes:                0                             0
Rows Processed:               11                            1
Executions:                   11                            1

 

We can see the index was created because of a 972.2x improvement in the performance of the SQL query I ran.

If we look at the details of the indexes that now exist on the table:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6';

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM6_CODE2_CODE3_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       23556           9890973
MAJOR_TOM6_CODE3_CODE2_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       24029           9890973
SYS_AI_4nc6g08bw8db7      YES NO  VISIBLE   DISABLED      VALID      10000000       29125           9999444

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM6' order by index_name, column_position;

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
MAJOR_TOM6_CODE2_CODE3_I  CODE2                              1
MAJOR_TOM6_CODE2_CODE3_I  CODE3                              2
MAJOR_TOM6_CODE3_CODE2_I  CODE3                              1
MAJOR_TOM6_CODE3_CODE2_I  CODE2                              2
SYS_AI_4nc6g08bw8db7      CODE2                              1
SYS_AI_4nc6g08bw8db7      CODE3                              2
SYS_AI_4nc6g08bw8db7      CODE1                              3

 

We notice a couple of key points.

Firstly, even though the previously created manual index on the columns (CODE2, CODE3) is now totally redundant because it has the same column list as the leading columns of the newly created Auto Index based on the columns (CODE2, CODE3, CODE1), Auto Indexing does NOT automatically drop the manually created index.

Auto Indexing ONLY automatically drops and logically recreates Auto Indexes.

Secondly, Auto Indexing is certainly aware of the previous workload because it has created the new Auto Index with the column list (CODE2, CODE3, CODE1) and NOT in the default CODE1, CODE2, CODE3 column order (as defined in the table definition).

This suggests Auto Indexing is indeed trying to create a new index that is able to cater for all known SQL workloads (predicates on just the CODE2, CODE3 columns and predicates on columns CODE1, CODE2, CODE3).

However, Auto Indexing does not (yet) have the capability to logically modify or drop obviously redundant manually created indexes (it can only do so on previously created Auto Indexes). This is likely one of the reasons why Oracle has provided us with the DROP_SECONDARY_INDEXES procedure in order to get rid of all those annoying manually created secondary indexes that can get in the way of an optimal indexing strategy.

More on DROP_SECONDARY_INDEXES in future posts.

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part I (I Can’t Read) April 21, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Clustering Factor, Mixing Auto and Manual Indexes, Oracle Indexes.
1 comment so far

tin machine album

In previous articles, I discussed how Automatic Indexing has the capability to add columns or reorder the column list of previously created Automatic Indexes. However, how does Automatic Indexing handle these types of scenarios with regard to existing manually created indexes?

To investigate, let’s create a table identical to the table I created in my previous blog post where Automatic Indexing created an index that was ultimately not used by the CBO because although Automatic Indexing finds the new index more efficient, the CBO costs it as being too expensive and ignores it.

SQL> create table major_tom5 (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom5 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)),  'David Bowie' from dual connect by level = 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

However, in this demo, I’m going to first create a manual index, but with the column list in CODE3, CODE2 order. This is the opposite order in which a default Automatic Index would be created (CODE2, CODE3 order) as this is the order of the columns in the table definition:

SQL&gt; create index major_tom5_code3_code2_i on major_tom5(code3, code2);

Index created.

SQL&gt; select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor <span style="color:var(--color-text);">from user_indexes where table_name='MAJOR_TOM5';</span>

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS   NUM_ROWS   LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM5_CODE3_CODE2_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       24181           8974538

The resultant index has a terrible Clustering Factor of 8974538 on a 10M row table.

If we run the following query with filtering predicates on these 2 indexed columns:

SQL> select * from major_tom5 where code3=4 and code2=42;

10051 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |  9982 |   272K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM5 |  9982 |   272K|  7355   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - storage("CODE2"=42 AND "CODE3"=4)
       filter("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
     45888  consistent gets
        68  physical reads
      5256  redo size
    149822  bytes sent via SQL*Net to client
       610  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     10051  rows processed

The CBO decides to NOT use the available index as it deems it too expensive, especially with such a poor Clustering Factor, to return the resultant 10,051 rows.

But what will Automatic Indexing do now. If we wait the 15 minute period until the next Automatic Indexing period and look at the resultant Automatic Indexing report:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
---------------------------------------------------------------------------------
| Owner | Table      | Index                | Key         | Type   | Properties |
---------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM5 | SYS_AI_2ajmncxsmg189 | CODE2,CODE3 | B-TREE | NONE       |
---------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : fmpwux2ptvasq
SQL Text             : select * from major_tom5 where code2=42 and code3=4
Improvement Factor   : 5.1x

Automatic Indexing has created a new index based on the column list CODE2, CODE3, because it considers such an index would improve performance of the query by a factor of 5.1x.

However, it has not recognised that the existing manual index based the column list CODE3, CODE2 would have done precisely the same job.

If we look further on in the Automatic Indexing report:

Execution Statistics:
-----------------------------
                              Original Plan                 Auto Index Plan
                              ----------------------------  ----------------------------
Elapsed Time (s):             993225                        26436
CPU Time (s):                 963727                        22535
Buffer Gets:                  137756                        9000
Optimizer Cost:               7355                          9069
Disk Reads:                   0                             26
Direct Writes:                0                             0
Rows Processed:               30153                         10051
Executions:                   3                             1

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------

Plan Hash Value  : 2129981950
---------------------------------------------------------------------------------------
| Id | Operation                      | Name       | Rows   | Bytes  | Cost  | Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |            |        |        |  7355 |         |
|  1 |  PX COORDINATOR                |            |        |        |       |         |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000   |  10000 | 280000 |  7355 | 00:00:01|
|  3 |     PX BLOCK ITERATOR          |            |  10000 | 280000 |  7355 | 00:00:01|
|  4 |      TABLE ACCESS STORAGE FULL | MAJOR_TOM5 |  10000 | 280000 |  7355 | 00:00:01|
---------------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------

Plan Hash Value  : 459198994
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10159 | 284452 | 9069 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM5           | 10159 | 284452 | 9069 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_2ajmncxsmg189 | 10051 |        |   27 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE2"=42 AND "CODE3"=4)

We notice the new execution plan using the newly created Automatic Index actually has a greater CBO cost than the previous FTS execution plan.

As we discussed in the previous post on when Automatic Indexing creating indexes that are not ultimately used by the CBO, although Automatic Indexing has indeed created this index because it has determined it’s going to be more efficient by a factor of 5.1x due to the reduction in Buffer Gets (137756 buffer gets old plan / 3 executions = 45,919 / 9000 buffer gets with index = 5.1), the CBO considers the execution plan using the Automatic Index to have a larger cost at 9069 than the previous FTS cost at just 7355.

Again just as with the existing, logically equivalent manually created index, the reason why the new Automatic Index is deemed too expensive by the CBO is because it likewise has the same terrible Clustering Factor:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM5';

INDEX_NAME               AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------ --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM5_CODE3_CODE2_I NO  NO  VISIBLE   DISABLED      VALID      10000000       24181           8974538
SYS_AI_2ajmncxsmg189     YES NO  VISIBLE   DISABLED      VALID      10000000       23697           8974538

If we re-run the initial query again with the newly created Visible/Valid Automatic Index:

SQL> select * from major_tom5 where code3=4 and code2=42;

10051 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |  9982 |   272K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM5 |  9982 |   272K|  7355   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

    4 - storage("CODE2"=42 AND "CODE3"=4)
        filter("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
     45888  consistent gets
        68  physical reads
      5256  redo size
    149822  bytes sent via SQL*Net to client
       610  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     10051  rows processed

The CBO ignores the newly created Automatic Index as it did the logically equivalent manually created index and uses the previous, cheaper FTS execution plan.

Automatic Indexing was NOT able to recognise that we already had an equivalent manually created index and so now we have TWO indexes that the CBO simply ignores as being too expensive…

More on mixing Automatic and Manual Indexes on my next post.

Oracle 19c Automatic Indexing: Adding Columns To Existing Automatic Indexes (2+2=5) April 7, 2020

Posted by Richard Foote in 19c, 19c New Features, Add Column To Existing Index, Automatic Indexing, Oracle Indexes.
2 comments

2+2=5 Single

 

In my previous post, I discussed how when the following query is run:

select * from major_tom3 where code3=4 and code2=42;

the Automatic Indexing process will create an index on (CODE2, CODE3) but ultimately not use the index as the CBO considers the corresponding index based execution plan too expensive.

I’m going to expand on the demo and run now the following SQL for the first time (note these examples are run on the OLTP Autonomous Cloud service which explains the odd default parallel based execution plans):

SQL> select * from major_tom3
where code1=42 and code3=4 and code2=42;

10 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    10 |   280 |  7354   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |    10 |   280 |  7354   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |    10 |   280 |  7354   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM3 |    10 |   280 |  7354   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

4 - storage("CODE1"=42 AND "CODE2"=42 AND "CODE3"=4)
    filter("CODE1"=42 AND "CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
         6 recursive calls
         0 db block gets
     45853 consistent gets
         0 physical reads
         0 redo size
      1032 bytes sent via SQL*Net to client
       588 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
        10 rows processed

 

This query has a predicate that includes both CODE2 and CODE3 filtering columns as previously, but now also a new filtering column on CODE1. This now makes the resultant SQL much more selective than the previous SQL, returning just 10 rows where the previous SQL returned 9968 rows.

The current explain plan still uses the previous Full Table Scan, as the only index available is the Automatic Index created previously based on CODE2, CODE3 which has already been shown to be too expensive to return the necessary rows. The additional filtering predicate based on CODE1 doesn’t make the index any more efficient, it still has to access the 9968 rows that match the CODE2, CODE3 predicates and then filter out most of the rows less the 10 that are actually required.

So what does the Automatic Index process do in this scenario?

Let’s look at the resultant Automatic Index report:

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_cy8rs2dqb0nrp | CODE2,CODE3,CODE1 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

The following indexes were dropped:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Owner | Table      | Index                | Key         | Type   | Properties|
---------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_bnyacywycxx8b | CODE2,CODE3 | B-TREE | NONE      |
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------

 

So the first thing to note is that Oracle first creates a new index based on columns CODE2,CODE3,CODE1.

It then drops the previously created index based on the CODE2,CODE3 columns.

If we look at the Verification Details section of the report:

VERIFICATION DETAILS

-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 1hv3d685x2cy4
SQL Text             : select * from major_tom3 where code1=43 and code3=4 and code2=42
Improvement Factor   : 45853.6x

-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : 97by2q15zprgc
SQL Text            : select * from major_tom3 where code1=42 and code3=4 and code2=42
Improvement Factor  : 45856.2x

Execution Statistics:
-----------------------------
                     Original Plan                Auto Index Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    2815446                      1046
CPU Time (s):        2741134                      1013
Buffer Gets:         596135                       13
Optimizer Cost:      7354                         13
Disk Reads:          0                            2
Direct Writes:       0                            0
Rows Processed:      130                          10
Executions:          13                           1
PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
Plan Hash Value : 2354969370

--------------------------------------------------------------------------------
| Id | Operation                 | Name       | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
| 0  | SELECT STATEMENT          |            |      |       | 7354 |          |
| 1  | PX COORDINATOR            |            |      |       |      |          |
| 2  | PX SEND QC (RANDOM)       | :TQ10000   |   10 |   280 | 7354 | 00:00:01 |
| 3  | PX BLOCK ITERATOR         |            |   10 |   280 | 7354 | 00:00:01 |
| 4  | TABLE ACCESS STORAGE FULL | MAJOR_TOM3 |   10 |   280 | 7354 | 00:00:01 |
--------------------------------------------------------------------------------

Notes
-----
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 2892362571

-----------------------------------------------------------------------------------------------------
|  Id | Operation                           | Name                 | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   10 |   280 |   13 | 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM3           |   10 |   280 |   13 | 00:00:01 |
| * 2 | INDEX RANGE SCAN                    | SYS_AI_cy8rs2dqb0nrp |   10 |       |    3 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE2"=42 AND "CODE3"=4 AND "CODE1"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

 

So what’s going on here?

Oracle has correctly determined that an index based on all 3 columns would enable a much more efficient access path for the new SQL, by a factor of 45856.2x no less.

In order to service BOTH known queries that the Automatic Index process has determined would benefit from an index based on predicates (CODE2=42 and CODE3=4) and (CODE2=42 and CODE3=4 and CODE1=42), a single index based on CODE2,CODE3,CODE1 would suffice.

As such, the existing index based on just CODE2,CODE3 is redundant as the new index has the same leading columns. Therefore, the existing index can be safely dropped.

If we look at the definition of the indexes on this table:

SQL> select index_name, auto, constraint_index, visibility, status compression from user_indexes where table_name='MAJOR_TOM3';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS
-------------------- --- --- --------- ------------- --------
SYS_AI_cy8rs2dqb0nrp YES NO  VISIBLE   DISABLED      VALID

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM3' order by index_name, column_position;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_AI_cy8rs2dqb0nrp CODE2                              1
SYS_AI_cy8rs2dqb0nrp CODE3                              2
SYS_AI_cy8rs2dqb0nrp CODE1                              3

 

We notice we now have just the new index, which is both VISIBLE and VALID, based on columns in CODE2,CODE3,CODE1 order.

So Automatic Indexing is clever enough to recognise the scenario where a new index can replace an existing index by adding additional columns to cater for new SQL workloads.

Now that’s rather impressive…

Much more on Automatic Indexing to come.

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young) March 30, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Oracle Indexes.
8 comments

 

scary monsters album

 

The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO.

To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and CODE3 which has only 10 distinct values. The data of both columns is very poorly clustered with data for both columns sprinkled throughout the table:

SQL> create table major_tom3 (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom3 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)), 'David Bowie'
from dual connect by level 10000000;

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

When we run the following query (a total of 4 times), it returns close to 10K rows per execution:

SQL> select * from major_tom3
where code3=4 and code2=42;

9968 rows selected.

 

When we look at the Automatic Indexing report after the next Automatic Index job run:

 

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
---------------------------------------------------------------------------------
| Owner | Table      | Index                 | Key         | Type   | Properties|
---------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_bnyacywycxx8b  | CODE2,CODE3 | B-TREE | NONE      |
---------------------------------------------------------------------------------

 

We notice that Automatic Indexing has indeed created a new index based on the columns CODE2, CODE3.

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name  : BOWIE
SQL ID               : 1h4j53jruuzht
SQL Text             : select * from major_tom3 where code3=4 and code2=42
Improvement Factor   : 5.1x
Execution Statistics:
-----------------------------
Original Plan                 Auto Index Plan
----------------------------  ----------------------------
Elapsed Time (s):    1276903                         29310
CPU Time (s):        1226240                         25905
Buffer Gets:          183493                          8954
Optimizer Cost:         7355                          8996
Disk Reads:                0                            26
Direct Writes:             0                             0
Rows Processed:        39872                           9968
Executions:                4                              1

 

The reason why the index was created was because the Automatic Indexing process has determined the query will improve by a factor of 5.1 with the new index in place.

This has been calculated by first determining the average number of consistent gets per execution:  183493 total consistent gets / 4 executions = 45873 consistent gets average per execution

This average consistent gets is then divided by the number of consistent gets with the new index 45873 / 8954 = 5.1x.

So this “Improvement Factor” is determined primarily by the ratio of improvement based on consistent gets.

If we look now at the Plans Section of the Automatic Indexing report:

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 2354969370
---------------------------------------------------------------------------------------
| Id | Operation                      | Name       | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |            |       |        | 7355 |          |
|  1 |  PX COORDINATOR                |            |       |        |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000   | 10000 | 280000 | 7355 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |            | 10000 | 280000 | 7355 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | MAJOR_TOM3 | 10000 | 280000 | 7355 | 00:00:01 |
---------------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value  : 1676847804
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10820 | 302960 | 8996 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM3           | 10820 | 302960 | 8996 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_bnyacywycxx8b |  9968 |        |   27 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

* 2 - access("CODE2"=42 AND "CODE3"=4)

 

We notice with some interest that the new “improved” plan actually has a CBO cost of 8996 which is greater than the original plan cost of just 7355.

This is because the CBO does not simply use Consistent Gets in its calculations, but a combination of anticipated I/O and CPU costs. Noting that Multi-block reads may physically read more blocks more efficiently than a fewer number of Single-block reads, it’s quite conceivable that the CBO would consider an execution plan with more consistent gets to be more efficient if the underlining I/Os are costed as being cheaper.

This is precisely what’s happening with this query…

If we look at the resultant Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, compression, status from user_indexes where table_name='MAJOR_TOM3';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS
-------------------- --- --- --------- ------------- --------
SYS_AI_bnyacywycxx8b YES NO  VISIBLE   DISABLED      VALID

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM3' order by index_name, column_position;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_AI_bnyacywycxx8b CODE2                              1
SYS_AI_bnyacywycxx8b CODE3                              2

 

We can see that the newly created Automatic Index is both VISIBLE and VALID and so can potentially be used by any SQL within the database.

If we now re-run the query:

SQL> select * from major_tom3 where code3=4 and code2=42;

9968 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            | 10045 |   274K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   | 10045 |   274K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            | 10045 |   274K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM3 | 10045 |   274K|  7355   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

4 - storage("CODE2"=42 AND "CODE3"=4)
    filter("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
        11 recursive calls
         4 db block gets
     45859 consistent gets
         0 physical reads
         0 redo size
    275084 bytes sent via SQL*Net to client
      7892 bytes received via SQL*Net from client
       666 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
      9968 rows processed

 

We notice that the execution plan remains the same as previously, with the newly available index NOT used by the CBO. This is because the index is deemed too inefficient and the result index based execution plan too expensive by the CBO.

If we now re-run the query with a hint to make the CBO use the index:

SQL> select /*+ index(major_tom3) */ * from major_tom3 where code3=4 and code2=42;

9968 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10045 |  274K |  9065   (1)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             | 10045 |  274K |  9065   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM3           | 10045 |  274K |  9065   (1)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      | 10045 |       |    27   (4)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             | 10045 |       |    27   (4)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |         INDEX RANGE SCAN              | SYS_AI_bnyacywycxx8b | 10045 |       |    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
        16 recursive calls
         4 db block gets
      8958 consistent gets
         0 physical reads
         0 redo size
    275084 bytes sent via SQL*Net to client
      7892 bytes received via SQL*Net from client
       666 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
      9968 rows processed

 

The Automatic Index is now used, but it requires a hint for the index to be used. The cost of using the Automatic Index is too great, even though the Automatic Index has only just been created and created to specifically address this query.

Having one criteria (based on Consistent Gets) used by Automatic Indexing and another criteria (based on estimated I/O and CPU costs) as used by the CBO leaves open this possibility of the Automatic Indexing thinking a new index is a great idea, while the CBO thinks not.

In my next post I’ll show you how Automatic Indexing actually does a rather clever job of improving the current index definition with the introduction of a new SQL…

 

ANNOUNCEMENT: Replacement Oracle Indexing Internals and Performance Tuning Webinars 23-26 March 2020 !! March 11, 2020

Posted by Richard Foote in Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Webinar, Webinar.
add a comment

 

London March 2020 Seminar Dates Cancelled 😦

Unfortunately, due to the Coronavirus crisis, both seminars scheduled for London during March 2020 have been cancelled. Full refunds have been processed for all attendees.

 


March 2020 Webinar Replacement Dates Announced !!

 

However, as a quick replacement primarily for those disappointed by the cancellation of the London seminars, I will now be running webinar versions of both events as replacements during the same times the seminars were previously scheduled.

Remaining places for the webinars can be purchased by anyone until all remaining places are taken. Simple click on the Buy Now button to purchase your place for this unique training opportunity.

 

23-24 March 2020 (running between 9:00 – 17:00 GMT): “Oracle Indexing Internals and Best Practices” Webinar:

25-26 March 2020 (running between 9:00 – 17:00 GMT): “Oracle Performance Diagnostics and Tuning” Webinar:

 

You can also purchase tickets to both webinars at a special combo discount:

23-26 March 2020 (running between 9:00 – 17:00 GMT)Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Webinars:

 

The cost for an individual webinar is $1,800 Australian Dollars.

The cost for the webinar combo is $3,000 Australian Dollars.

 

For full content details of “Oracle Indexing Internals and Best Practices” Webinar: https://richardfooteconsulting.com/indexing-seminar/

For full content details of “Oracle Performance Diagnostics and Tuning” Webinar: https://richardfooteconsulting.com/performance-tuning-seminar/

 

Please contact me directly if you wish to pay via invoice and direct bank transfer or if you have any questions.

 

Although I might not currently be able to run these events in London, hopefully you’ll still have the opportunity to attend this high acclaimed training 🙂

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars January 21, 2020

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard's Blog.
add a comment

 

Time is fast running out to enroll for one of my acclaimed seminars I’ll be running in London, UK in March 2020. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” Seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount (all enrollments so far have gone with this option, so it’s obviously the way to go):

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

 

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

Oracle Database 19c Automatic Indexing: Minimum Number Of Required Indexes (Low) January 20, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Oracle Indexes.
add a comment

 

As I discussed in my previous posts, Oracle Automatic Indexing will try and create as few indexes as possible to satisfy existing workloads, even if that means reordering the columns in an existing index.

To illustrate how Automatic Indexing creates as few indexes as possible, I’ll create the following table which has a number of columns with differing numbers of distinct values:

SQL> create table thin_white_duke (id number, code1 number, code2 number, code3 number, code4 number, name varchar2(42));

Table created.

SQL> insert into thin_white_duke select rownum, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), ceil(dbms_random.value(0, 10000)), ceil(dbms_random.value(0, 100000)), 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from user_tab_columns where table_name='THIN_WHITE_DUKE';

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
ID                   9914368 1.0086E-07
CODE1                    100        .01
CODE2                   1000       .001
CODE3                  10000      .0001
CODE4                 100824 9.9183E-06
NAME                       1          1

 

I then run the following workload within a 15 minute window between Automatic Index tasks:

 

SQL> select * from thin_white_duke where id=42;

SQL> select * from thin_white_duke where code1=42;

SQL> select * from thin_white_duke where code2=42;

SQL> select * from thin_white_duke where code3=42;

SQL> select * from thin_white_duke where code4=42;

SQL> select * from thin_white_duke where code1=42 and code2=42;

SQL> select * from thin_white_duke where code1=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code1=42;

SQL> select * from thin_white_duke where code2=42 and code3=42;

SQL> select * from thin_white_duke where code2=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code1=42;

SQL> select * from thin_white_duke where code3=42 and code2=42;

SQL> select * from thin_white_duke where code3=42 and code4=42;

SQL> select * from thin_white_duke where code4=42 and code1=42;

SQL> select * from thin_white_duke where code4=42 and code2=42;

SQL> select * from thin_white_duke where code4=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code4=42;

SQL> select * from thin_white_duke where code1=42 and code3=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code1=42 and code3=42;

SQL> select * from thin_white_duke where code2=42 and code1=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code3=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code1=42 and code2=42;

SQL> select * from thin_white_duke where code3=42 and code1=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code2=42 and code4=42;

SQL> select * from thin_white_duke where code4=42 and code1=42 and code2=42;

SQL> select * from thin_white_duke where code4=42 and code1=42 and code3=42;

SQL> select * from thin_white_duke where code4=42 and code2=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code3=42 and code4=42;

 

Each of these queries have no choice but to perform a Full Table Scan as there are currently no indexes defined to the table. Each query uses a different column list, so for the 30 or so SQL statements, one could potentially create 30 or so different indexes to cover each and every SQL predicate combination used above.

But how many different indexes will Automatic Indexing create?

Let’s have a look…

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 15-JUL-2019 07:46:25
Activity end                 : 15-JUL-2019 07:48:56
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                                : 7
Indexes created (visible / invisible)           : 7 (7 / 0)
Space used (visible / invisible)                : 1.8 GB (1.8 GB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 29
SQL statements improved (improvement factor)    : 29 (147.2x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 147.2x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

 

We can see that Automatic Indexing only create 7 different indexes, that’s it !!

If we look at the indexes that have been created:

 

INDEX DETAILS

-------------------------------------------------------------------------------
1.  The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Owner | Table           | Index                | Key                     | Type   | Properties |
--------------------------------------------------------------------------------------------------
| BOWIE | THIN_WHITE_DUKE | SYS_AI_0u1qx8vgtstkb | CODE4,CODE1,CODE2       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_2j5g09nzrhqsw | CODE2,CODE3,CODE4       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_4y26dtkybxq6k | CODE3,CODE4             | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_5pmdyk5pjay8a | CODE3,CODE1,CODE4       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_6uqhvvzabg5n8 | ID                      | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_bwfbc6nah6uga | CODE2,CODE4             | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_fftcb8q17yy6g | CODE1,CODE2,CODE3,CODE4 | B-TREE | NONE       |
--------------------------------------------------------------------------------------------------

 

We can see how the 7 indexes can collectively cover all 30 odd different SQL predicates within the workload, because the leading columns of at least one index has the necessary columns of each SQL predicate.

If we look at but one SQL example within the Automatic Index report, the query with the predicate on just the CODE4 column:

 

Parsing Schema Name   : BOWIE
SQL ID                 : 20h1p88d1u80r
SQL Text               : select * from thin_white_duke where code4=42
Improvement Factor     : 1200.5x

Execution Statistics:
-----------------------------
Original Plan                  Auto Index Plan
----------------------------  ----------------------------
Elapsed Time (s):     679689                          1148
CPU Time (s):         724033                           933
Buffer Gets:          162070                            91
Optimizer Cost:         8617                           103
Disk Reads:                0                             2
Direct Writes:             0                             0
Rows Processed:          264                            88
Executions:                3                             1

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 2714752625
------------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      |       | 8617 |          |
|  1 |  PX COORDINATOR                |                 |      |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000        |   99 |  3366 | 8617 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |                 |   99 |  3366 | 8617 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | THIN_WHITE_DUKE |   99 |  3366 | 8617 | 00:00:01 |
------------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 2447525579
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   88 |  2992 |  103 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | THIN_WHITE_DUKE      |   88 |  2992 |  103 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_0u1qx8vgtstkb |   99 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

 

We see it can now be serviced with the new SYS_AI_0u1qx8vgtstkb index, because it has the following columns (CODE4,CODE1,CODE2), with the CODE4 column as the leading column.

If we look at the details of all these new Automatic Indexes:

 

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes
where table_name='THIN_WHITE_DUKE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_6uqhvvzabg5n8 YES NO  VISIBLE   DISABLED      VALID      10000000       23553             53336
SYS_AI_fftcb8q17yy6g YES NO  VISIBLE   DISABLED      VALID      10000000       37322           9999819
SYS_AI_2j5g09nzrhqsw YES NO  VISIBLE   DISABLED      VALID      10000000       33154           9999815
SYS_AI_bwfbc6nah6uga YES NO  VISIBLE   DISABLED      VALID      10000000       27564           9999822
SYS_AI_5pmdyk5pjay8a YES NO  VISIBLE   DISABLED      VALID      10000000       31908           9999804
SYS_AI_4y26dtkybxq6k YES NO  VISIBLE   DISABLED      VALID      10000000       27697           9999818
SYS_AI_0u1qx8vgtstkb YES NO  VISIBLE   DISABLED      VALID      10000000       31783           9999819
         
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='THIN_WHITE_DUKE' order by index_name, column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION
-------------------- --------------- ---------------
SYS_AI_0u1qx8vgtstkb CODE4                         1
SYS_AI_0u1qx8vgtstkb CODE1                         2
SYS_AI_0u1qx8vgtstkb CODE2                         3
SYS_AI_2j5g09nzrhqsw CODE2                         1
SYS_AI_2j5g09nzrhqsw CODE3                         2
SYS_AI_2j5g09nzrhqsw CODE4                         3
SYS_AI_4y26dtkybxq6k CODE3                         1
SYS_AI_4y26dtkybxq6k CODE4                         2
SYS_AI_5pmdyk5pjay8a CODE3                         1
SYS_AI_5pmdyk5pjay8a CODE1                         2
SYS_AI_5pmdyk5pjay8a CODE4                         3
SYS_AI_6uqhvvzabg5n8 ID                            1
SYS_AI_bwfbc6nah6uga CODE2                         1
SYS_AI_bwfbc6nah6uga CODE4                         2
SYS_AI_fftcb8q17yy6g CODE1                         1
SYS_AI_fftcb8q17yy6g CODE2                         2
SYS_AI_fftcb8q17yy6g CODE3                         3
SYS_AI_fftcb8q17yy6g CODE4                         4

 

The 7 newly created Automatic Indexes are all VISIBLE and VALID and can collectively service all 30 odd different SQL predicates of the captured workload.

I just know from experience that many DBAs and Developers out there would create many more than just these 7 indexes, partly because it’s just easier to create a new index for each new SQL predicate that doesn’t currently have an appropriate index and partly because it’s not always easy to capture and know what all SQL predicate combinations might be in use by an application.

This is one of the really nice capabilities of Automatic Indexing, in that it tries to service the known workloads it captures with as few indexes as possible, that have all be proven first to indeed improve SQL performance.

In my next blog post, I’ll show another trick that Automatic Indexing can do to reduce the number of different indexes it needs to create…

Oracle Database 19c Automatic Indexing – Need Another Index (Another Brick in The Wall Part 2) January 16, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Oracle Indexes.
add a comment

I previously discussed how Automatic Indexing can effectively cleverly reorder an existing index if it means it can now use the new index to satisfy new SQL predicates. In this post, we’ll explore this example further with some new workloads.

So, we previously ran SQL queries with SQL predicates in the following combinations:

  • CODE1=42 and CODE2=42 and CODE3=42
  • CODE2=42 and CODE3=42
  • CODE3=42

 

so an index based on CODE3, CODE2, CODE1 is able to satisfy all 3 existing predicates.

If we now run a new query with the following predicates based on CODE2=42 and CODE1=42:

SQL> select * from major_tom where code2=42 and code1=42;

no rows selected

Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    28 |  7318   (6)|00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |         |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |     1 |    28 |  7318   (6)|00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |     1 |    28 |  7318   (6)|00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |     1 |    28 |  7318   (6)|00:00:01 |
-----------------------------------------------------------------------------------------

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

4 - storage("CODE1"=42 AND "CODE2"=42)
    filter("CODE1"=42 AND "CODE2"=42)

Statistics
----------------------------------------------------------
         8  recursive calls
         4  db block gets
     45853  consistent gets
         0  physical reads
         0  redo size
       645  bytes sent via SQL*Net to client
       577  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

 

The current previously re-ordered index can’t now satisfy this new predicate as the leading column of the index (CODE3) is not specified in the SQL predicate and the CBO considers an Index Skip Scan as too expensive an operation. So a Full Table Scan is chosen here by the CBO.

If we wait and see what Automatic Indexing decides to now do:

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start               : 21-JUN-2019 07:41:55
Activity end                 : 21-JUN-2019 07:42:49
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------

Index candidates                              : 1
Indexes created (visible / invisible)         : 1 (1 / 0)
Space used (visible / invisible)              : 201.33 MB (201.33 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 1
SQL statements improved (improvement factor)  : 1 (5.1x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 5.1x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

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

We notice that a new index has been created…

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
--------------------------------------------------------------------------------
| Owner | Table     | Index                | Key         | Type   | Properties |
--------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_gmhdbjx21zcr1 | CODE1,CODE2 | B-TREE | NONE       |
--------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 3y246nzwdpybv
SQL Text             : select * from major_tom where code2=42 and code1=4
Improvement Factor   : 5.1x

 

So Automatic Indexing has decided to now create a new, second index (SYS_AI_gmhdbjx21zcr1) on the table based on CODE1, CODE2, as a single index is no longer capable to address all known SQL predicates.

 

SQL> select index_name, column_name, column_position from user_ind_columns 
where table_name='MAJOR_TOM' order by index_name, column_position;

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
SYS_AI_00hxxxkgb821n      CODE3                              1
SYS_AI_00hxxxkgb821n      CODE2                              2
SYS_AI_00hxxxkgb821n      CODE1                              3
SYS_AI_gmhdbjx21zcr1      CODE1                              1
SYS_AI_gmhdbjx21zcr1      CODE2                              2

 

So we now have two indexes to cater for all known SQL predicates in which an index has been shown to improve performance.

Automatic Indexing will try and create the minimum number of indexes possible to cater for all known SQL workloads.

 

In my next post, we’ll look at a more complex example in which there could potentially be numerous different indexes that could cater for more extensive workloads and how Automatic Indexing copes rather splendidly…

“Oracle Performance Diagnostics and Tuning” Seminar: Berlin, Germany 31 March – 1 April 2020 January 14, 2020

Posted by Richard Foote in Oracle Performance Diagnostics and Tuning Seminar.
add a comment

 

I’m very excited to be running my high acclaimed “Oracle Performance Diagnostics and Tuning” Seminar in Berlin, Germany on 31 March – 1 April 2020 in conjunction with DOUG. If you enjoyed my “Oracle Indexing Internals and Best Practices” Seminar, you will simply love this seminar 🙂

This seminar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real-world scenarios and numerous actual examples and test cases, this seminar will show participants how to confidently and reliably diagnose performance issues. The seminar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database AWR reports where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

 

For all the details regarding seminar content, venue, costs and how to register, please visit: https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=589552

 

Places are already filling up so please book early to avoid disappointment !!

 

Merry Christmas and Happy New Year !! (“Heroes”) December 24, 2019

Posted by Richard Foote in Oracle Indexes.
4 comments

I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year.

My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same Bing Crosby Christmas Special in 1977.

 

Enjoy 🙂