jump to navigation

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.
add a comment

 

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…

 

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 Database 19c Automatic Indexing – Indexed Column Reorder (What Shall We Do Now?) December 18, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Index Column Reorder.
1 comment so far

 

I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table.

But what if there are “other factors” based on new workloads and the original index column order is no longer optimal or appropriate ?

I’ll begin by creating a table, with the following key column defined in CODE1, CODE2, CODE3 order:

 

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

Table created.

SQL> insert into major_tom select rownum, mod(rownum, 10)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), '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_TOM');

PL/SQL procedure successfully completed.

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

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                        9914368 1.0086E-07
CODE1                          10  .00000005
CODE2                         100  .00000005
CODE3                       1000        .001
NAME                           1           1

If I now run the following query with predicates based on these three columns:

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

15 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_TOM |    10 |   280 |  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

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

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

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

        34 recursive calls
         5 db block gets
     45861 consistent gets
         0 physical reads
      1044 redo size
      1087 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)
        15 rows processed

 

After the default 15 minutes period in which the Automatic Index task is run, if we look at what Automatic Index has been created:

 

INDEX DETAILS

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

  1. The following indexes were created:

--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------

VERIFICATION DETAILS

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

Parsing Schema Name  : BOWIE
SQL ID               : ayuj12hggwrvc
SQL Text             : select * from major_tom where code3=42 and code2=42 and code1=4
Improvement Factor   : 45853.8x

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

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

Elapsed Time (s):  3103394                       946
CPU Time (s):      3092860                       1017
Buffer Gets:       596102                        18
Optimizer Cost:    7354                          18
Disk Reads:        0                             2
Direct Writes:     0                             0
Rows Processed:    195                           15
Executions:        13                            1

 

We can see Oracle has indeed created an Automatic Index (SYS_AI_9mrs058nrg9d5) in the default CODE1, CODE2, CODE3 order.

But if we now run a new query, based on a predicate on just the CODE3 column:

 

SQL> select * from major_tom where code3=42; 

9961 rows selected.

Execution Plan

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

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

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

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

 

We can see the CBO has NOT used the index, as the leading column of the existing index is not mentioned in the SQL predicate and the CBO deems an Index Skip Scan as too expensive:

If we now run an SQL with predicates based on just the CODE2 and CODE3 columns:

 

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

101 rows selected.

Execution Plan

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

Predicate Information (identified by operation id):

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

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

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

         6 recursive calls
         0 db block gets
     45853 consistent gets
         0 physical reads
         0 redo size
      2281 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)
       101 rows processed

 

The existing Automatic Index is again not used as the important CODE1 column which is the leading column of the index is not mentioned in the SQL predicates and the CBO deems an Index Skip Scan as too expensive.

I know from experience many DBAs would simply create a new index with CODE3, CODE2 as the leading columns. But what does Automatic Indexing do in the scenario?

 

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

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

Activity start               : 21-JUN-2019 02:39:32
Activity end                 : 21-JUN-2019 02:40:18
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)       : 243.27 MB (243.27 MB / 0 B)
Indexes dropped (space reclaimed)      : 1 (243.27 MB)
SQL statements verified                : 1
SQL statements improved                : 0
SQL plan baselines created             : 0
Overall improvement factor             : 0x

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

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

 

We notice in the Automatic Indexing report it’s stating that one new index has been created BUT that one index has also been dropped.

 

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

         1. The following indexes were created:

-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_00hxxxkgb821n | CODE3,CODE2,CODE1 | B-TREE | NONE       |
--------------------------------------------------------------------------------------
 
 	1. The following indexes were dropped: 

-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------
 


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

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

 

Automatic Indexing has created a new index (SYS_AI_00hxxxkgb821n), with the columns in CODE3, CODE2, CODE1 order, as this index is able to service all currently known SQL predicate combinations:

WHERE CODE3 = x AND CODE2 = y AND CODE1= z

WHERE CODE3 = x AND CODE2 = y

WHERE CODE3 = x

as the leading column in the index is listed in all three current scenarios.

 

This means the previous index in CODE1, CODE2, CODE3 order is now redundant as the new index can fully service all know SQL predicate combinations. As a result, Automatic Indexing drops the redundant index.

This is a really nice capability of Automatic Indexing, the ability to effectively reorder the columns within an index based on new workloads.

But what if subsequent new SQL workloads means the new index is not able on its own to service all such workloads. I’ll discuss this scenario in my next post.

Oracle Database 19c Automatic Indexing: Index Compression (Ghosteen) December 9, 2019

Posted by Richard Foote in 19c, 19c New Features, Advanced Index Compression, Automatic Indexing, AUTO_INDEX_COMPRESSION, Index Column Order, Index Compression, Index Internals.
add a comment

 

 

In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented.

By default, Automatic Indexing does NOT use index compression. However, if you have access to the Advanced Compression option, you have the choice to turn on index compression in the following manner:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

PL/SQL procedure successfully completed.

Note: the AUTO_INDEX_COMPRESSION parameter is not actually documented, which could be a documentation bug or that Oracle is not yet ready to release this capability. The above will enable Automatic Indexes to be created with Compress Advanced Low, which is the “no-brain” index compression option as it will compress (deduplicate) safely with negligible CPU overheads. However, index column order is still critical to ensure effective compression as we shall see…

We begin by creating a simple table, that has four columns of interest, CODE1, CODE2, CODE3 and STATUS. They are defined in this order within the table, but CODE1 has the most number of distinct values (5000000 distinct values), then CODE2 (1000), then CODE3 (10) and finally STATUS which only has the 1 distinct value.

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

Table created.

SQL> insert into bowie_compress select rownum, mod(rownum, 5000000)+1, mod(rownum, 1000)+1, mod(rownum, 10)+1, 'COMPLETED’,

'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=>'BOWIE_COMPRESS');

PL/SQL procedure successfully completed.

 

In terms of being the most efficient from a compression perspective, it would be better to have the index defined in STATUS, CODE3, CODE2, CODE1 order, so that the leading columns in the index have the most duplicated values that enable effective deduplication and hence index compression. I’ve discussed the importance of index column for effective index compression a number of times previously. Arguably, it would be better not to actually index the STATUS column at all as with just 1 distinct value, provides no effective filtering benefits.

Having the CODE1 column as the leading column however with so many distinct values would effectively make the index non-compressible (with LOW compression), as the leading column would have too many distinct values to benefit much from compression.

So how does Automatic Indexing handle this scenario? Does it keep the same default index column order or does it alter the index column order to provide better index compression benefits?

Let’s run the following SQL with all four columns in the predicates and see what index Automatic Indexing creates…

 

SQL> select * from bowie_compress where code1=42 and code2=42 and code3=2 and status='COMPLETED';

Execution Plan

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

Predicate Information (identified by operation id):

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

4 - storage("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')
     filter("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')

Statistics
----------------------------------------------------------
        6 recursive calls
        0 db block gets
    63562 consistent gets
        0 physical reads
        0 redo size
     1038 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)
        2 rows processed

 

If we look at the Automatic Indexing report for the period in which the above SQL was run:

 

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

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 18-JUL-2019 00:18:35
Activity end                 : 18-JUL-2019 00:19:58
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)                : 293.6 MB (293.6 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 1
SQL statements improved (improvement factor)    : 1 (63563.9x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 63563.9x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
--------------------------------------------------------------------------------------------------
| Owner | Table          | Index                | Key                      | Type   | Properties |
--------------------------------------------------------------------------------------------------
| BOWIE | BOWIE_COMPRESS | SYS_AI_bkdhrsd29vd4f | CODE1,CODE2,CODE3,STATUS | B-TREE | NONE       |
--------------------------------------------------------------------------------------------------

 

We see that Automatic Index has created the index with all four columns from the SQL predicate in again the default column order as the column order as defined in the table (CODE1, CODE2, CODE3, STATUS). Even though Automatic Index Compression was enabled, it hasn’t considered the column cardinalities in its determination of best index column order.

Automatic Indexing has the tendency to index ALL columns specified in SQL predicates, regardless of whether all such columns provide filtering benefits AND does not consider the best column order from a compression perspective when determining index column order. So definitely room for improvement here methinks.

If we look at the definition and size of the resultant Automatic Index:

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

INDEX_NAME                   AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_bkdhrsd29vd4f         YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       35451          10000000

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

INDEX_NAME                   COLUMN_NAME     COLUMN_POSITION
---------------------------- --------------- ---------------
SYS_AI_bkdhrsd29vd4f         CODE1                         1
SYS_AI_bkdhrsd29vd4f         CODE2                         2
SYS_AI_bkdhrsd29vd4f         CODE3                         3
SYS_AI_bkdhrsd29vd4f         STATUS                        4

 

We note the index has 35451 leaf blocks.

If we were to create the index manully in a more appropriate manner from a compression perspective, with the index columns defined in reverse order and also with another index without the redundant STATUS column:

SQL> create index bowie_compress_best_order_i on bowie_compress(status, code3, code2, code1) compress advanced low;

Index created.

SQL> create index bowie_compress_best_order2_i on bowie_compress(code3, code2, code1) compress advanced low;

Index created.

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

INDEX_NAME                   AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_bkdhrsd29vd4f         YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       35451          10000000
BOWIE_COMPRESS_BEST_ORDER_I  NO  NO  VISIBLE   ADVANCED LOW  VALID      10000000       23509          10000000
BOWIE_COMPRESS_BEST_ORDER2_I NO  NO  VISIBLE   ADVANCED LOW  VALID      10000000       23462          10000000

 

We notice the resultant indexes are substantially smaller, at just 23509 and 23462 leaf blocks respectively.

It could well be that Index Compression is not yet documented because Oracle appreciates that more work yet needs to be done to create indexes optimally from a compression perspective…

Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !! September 12, 2019

Posted by Richard Foote in 19c, Automatic Indexing, Exadata X8, Let's Talk Database.
add a comment

I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019.

I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, but you MUST register to attend.

Dates, locations and registration links are as follows (Note the Sydney location is NOT the Oracle office in North Ryde):

Canberra:     22 October – Registration Link (Oracle Canberra Office)

Sydney:       23 October – Registration Link (Stone and Chalk, York St)

Melbourne: 24 October – Registration Link (Oracle Melbourne Office)

Brisbane:     29 October – Registration Link (Oracle Brisbane Office)

Auckland:    30 October – Registration Link (Level 13, AMP Centre, 29 Customs Street West)

Wellington: 31 October – Registration Link (Oracle Wellington Office)

 

Session details as follows:

Oracle Database 19c New Features

The latest Oracle Database Release 19c has introduced many exciting new features and enhanced capabilities that will be of much interest to both DBAs and Developers. This session will discuss in some detail a number of these new features with practical examples on how they can assist Oracle professionals maximize the benefits of the Oracle Database, especially in relation to Oracle Cloud and Oracle Engineered Systems deployments. New features discussed include Hybrid Table Partitions, Active Standby DML Redirect, Real Time Statistics, High-Frequency Automatic Optimizer Statistics Collection, SQL Quarantine,  new JSON Enhancements, DISTINCT option for LISTAGG aggregate and the most exciting new feature for some time, Automatic Indexing.

 

Oracle Exadata X8

The Oracle Exadata X8 Database Machine is the latest release in Oracle’s engineered systems platform designed specifically to deliver dramatically better performance, cost effectiveness, and availability for Oracle databases. This session will discuss various expanded and new capabilities introduced with Oracle Exadata X8 such as the new Exadata Extended Storage server, automated CPU, Memory and Network monitoring, advanced intrusion detection and Docker support. The session also examines why the Exadata Platform is so critical for the Autonomous Database Cloud Services and the unique Oracle Database19c capabilities such as Memoptimized Rowstore, Automatic Indexing, Real-Time Statistics and SQL Quarantine that are supported on Exadata.

 

So plenty of exciting Oracle database stuff to discuss. Hope to catch you at one of these events !!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend) September 11, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Clustering Factor, Index Column Order.
2 comments

In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with.

A point worth making is that if all columns of an index are specified within SQL equality predicates, then the ordering of columns within an index is of little consequence. I’ve discussed this point a number of times previously.

Let’s explore if perhaps the resultant Clustering Factor of an index might be a factor in the default Automatic Index column order.

I begin by creating a table that has two columns of interest, CODE1 which is poorly clustered and CODE2 which is very well clustered:

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

Table created.

SQL> create sequence muse_seq;

Sequence created.

SQL> create or replace procedure pop_muse as
begin
  for code1_value in 1..10000 loop
     for i in 1..100 loop
        insert into muse values (muse_seq.nextval, ceil(dbms_random.value(0,100)), code1_value, 'Back Holes');
     end loop;
   end loop;
   commit;
end;
/

Procedure created.

SQL> exec pop_muse

PL/SQL procedure successfully completed.

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

 

We then run the following query in which to hopefully create an Automatic Index on both CODE1 and CODE2 columns:

 

SQL> select * from muse where code1=406 and code2=83;

15 rows selected.

 

If we wait for the Automatic Index to be created and check out the Automatic Index report:

 

INDEX DETAILS
-------------------------------------------------------------------------------
  1 The following indexes were created:
*: invisible

----------------------------------------------------------------------------
| Owner | Table | Index                | Key         | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | MUSE  | SYS_AI_c1m8fkukj1368 | CODE2,CODE1 | B-TREE | NONE       |
----------------------------------------------------------------------------

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

Parsing Schema Name  : BOWIE

SQL ID               : 0pdqsvpggupnz

SQL Text             : select * from muse where code1=406 and code2=83

Improvement Factor   : 4092.8x

 

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

INDEX_NAME             COLUMN_NAME          COLUMN_POSITION
---------------------- -------------------- ---------------
SYS_AI_c1m8fkukj1368   CODE2                              1
SYS_AI_c1m8fkukj1368   CODE1                              2

 

We notice the index is created in CODE2, CODE1 column order.

If we create a manual index with the column order reversed:

 

SQL> create index muse_code1_code2_i on muse(code1, 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='MUSE';

INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_c1m8fkukj1368   YES NO  VISIBLE   DISABLED      VALID       1000000        2506            362900
MUSE_CODE1_CODE2_I     NO  NO  VISIBLE   DISABLED      VALID       1000000        2510            129878

 

We notice that the manual index has the better resultant Clustering Factor. So the Clustering Factor doesn’t appear to be a factor in Automatic Index column order (no pun intended).

If we re-create the initial table in Part I, but this time with the columns defined in the table in reverse order:

 

SQL> create table major_tom3 (id number, code3 number, code2 number, code1 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  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

If we again run the following query:

 

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

...

 

And wait for the Automatic Index to be created and look at the resultant report:

 

INDEX DETAILS

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

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

Parsing Schema Name  : BOWIE

SQL ID               : 22kts3uwj7kma

SQL Text             : select * from major_tom3 where code3=4 and code2=42 and code1=42

Improvement Factor   : 45854.1x

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM3'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_g6sw030tg5ba9 CODE3                         1         1000
SYS_AI_g6sw030tg5ba9 CODE2                         2          100
SYS_AI_g6sw030tg5ba9 CODE1                         3           10

 

We notice that the resultant Automatic Index has been created in CODE3, CODE2, CODE1 order.

After creating many many Automatic Indexes under all sorts of different scenarios, the DEFAULT behaviour is for Oracle to create Automatic Indexes in Column ID order (the order in which they are defined in the table definition).

Of course as we’ll see in future posts, if there are several conflicting SQL predicates, there are various other factors that govern a more appropriate Automatic Index order, but the fact that Oracle creates Automatic Indexes in Column ID order in the absence of other factors is useful to know.

As I said previously, if all indexed columns are specified in SQL equality predicates, index column order has little consequence. But as we’ll see in the next post, there are scenarios where index column order can be very important and this default index column order may not be the most optimal…

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere) September 2, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Oracle Indexes.
1 comment so far

pin ups

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column order be dependent on the SQL predicates running in the database and I’ll discuss all that in future posts, but what is the default behaviour here with regard index column order based (for now) on a single SQL predicate.

I could come up with a number of possible options that Oracle might adopt when determining the default index column order such as:

  • Column Name Order
  • Column ID Order
  • (Reverse) Column Cardinality Order
  • Best Clustering Factor
  • Other (Random even)

So to investigate this, I started with a basic table with 3 columns (CODE1, CODE2, CODE3) that had differing levels of cardinality:

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

Table created.

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

Commit complete.

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

PL/SQL procedure successfully completed.

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

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                        9914368 1.0086E-07
CODE1                          10  .00000005
CODE2                         100  .00000005
CODE3                        1000       .001
NAME                            1          1

I then ran the following query with a predicate based on the 3 columns CODE1, CODE2 and CODE3:

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

15 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_TOM |    10 |   280 |  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

If we look at the resultant Automatic Index:

INDEX DETAILS

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

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_9mrs058nrg9d5 CODE1                         1           10
SYS_AI_9mrs058nrg9d5 CODE2                         2          100
SYS_AI_9mrs058nrg9d5 CODE3                         3         1000

 

We notice that the Automatic Index is in CODE1, CODE2, CODE3 order.

If we create a similar table, but this time have the columns with a different order of cardinality:

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

Table created.

SQL> insert into major_tom2 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;

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

15 rows selected.

 

We notice that the resultant automatic index is still in the same CODE1, CODE2 and CODE3 order:

INDEX DETAILS

-------------------------------------------------------------------------------
1. The following indexes were created:
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM2 | SYS_AI_7w9t3tt9u171r | CODE1,CODE2,CODE3 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM2'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_7w9t3tt9u171r CODE1                         1         1000
SYS_AI_7w9t3tt9u171r CODE2                         2          100
SYS_AI_7w9t3tt9u171r CODE3                         3           10

 

So we can eliminate column cardinality as being a contributing factor in Oracle deciding in which manner to order the indexed columns.

Which is unfortunate as we’ll see in a future post when we decide to implement Oracle Index Compression with Automatic Indexing.

In the next post, we’ll explore further other considerations and confirm how Oracle does indeed decide to order columns within an Automatic Index by default.

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot) August 29, 2019

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

One shot single

One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered?

To find out, I create the following table:

SQL> create table bowie_one (id number constraint bowie_one_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie_one select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I then ran the following query just once and checked to see if the Automatic Indexing task would pick this execution up and consider building a new index:

SQL> select * from bowie_one where code=42;

10 rows selected.

Execution Plan

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

Predicate Information (identified by operation id):

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

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

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

   12  recursive calls
    0  db block gets
39000  consistent gets
    0  physical reads
  132  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
    0  sorts (memory)
    0  sorts (disk)
   10  rows processed

The following Automatic Indexing report detailed the following:

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

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 26-JUN-2019 13:03:30
Activity end                 : 26-JUN-2019 21:13:06
Executions completed         : 24
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)             : 184.55 MB (184.55 MB / 0 B)
Indexes dropped                              : 0
SQL statements verified                      : 3
SQL statements improved (improvement factor) : 1 (19500x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 6.9x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

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

So an index was indeed created. Later in the report:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------

-------------------------------------------------------------------------
| Owner | Table     | Index                | Key  | Type   | Properties |
-------------------------------------------------------------------------
| BOWIE | BOWIE_ONE | SYS_AI_5tabfu6wtkbdh | CODE | B-TREE | NONE       |
-------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS

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

Parsing Schema Name  : BOWIE
SQL ID               : 9n89axkwrvw4b
SQL Text             : select * from bowie_one where code=42
Improvement Factor   : 19500x

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

                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
Elapsed Time (s):   198342                        961
CPU Time (s):       187768                        1112
Buffer Gets:        39000                         13
Optimizer Cost:     6208                          14
Disk Reads:         0                             2
Direct Writes:      0                             0
Rows Processed:     10                            10
Executions:         1                             1

So the above details that an index on the CODE column of the BOWIE_ONE table was indeed created after just 1 execution.

For those wondering, yes the Elaspsed and CPU times are actually in Microseconds (1 millionth of a second) and not in seconds as stated…

The final section of the report details:

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

Plan Hash Value  : 227986582
------------------------------------------------------------------------------------
| Id | Operation                      | Name      | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |           |      |       | 6208 |          |
|  1 |  PX COORDINATOR                |           |      |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000  |   10 |   230 | 6208 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |           |   10 |   230 | 6208 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | BOWIE_ONE |   10 |   230 | 6208 | 00:00:01 |
------------------------------------------------------------------------------------

Notes
-----

- dop_op_reason = scan of object BOWIE.BOWIE_ONE
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------

Plan Hash Value  : 2734060610
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |  10   |  230 |   14 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_ONE            |  10   |  230 |   14 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_5tabfu6wtkbdh |  10   |      |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

* 2 - access("CODE"=42)

Notes
-----

- Dynamic sampling used for this statement ( level = 11 )

It details that indeed, a new plan using the newly Automatic Index would  be substantially more efficient.

If we look at details of the new Automatic Index:

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

INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_ONE_PK           NO  YES VISIBLE   DISABLED      VALID      10000000       19642             57523
SYS_AI_5tabfu6wtkbdh   YES NO  VISIBLE   DISABLED      VALID      10000000       22285          10000000

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

INDEX_NAME             COLUMN_NAME     COLUMN_POSITION
---------------------- --------------- ---------------
BOWIE_ONE_PK           ID                            1
SYS_AI_5tabfu6wtkbdh   CODE                          1

The newly created Automatic Index is both Valid and Visible and so can be used globally within the database.

If I now re-run the original query:

SQL> select * from bowie_one where code=42;

10 rows selected.

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

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

8 - access("CODE"=42)

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

 12  recursive calls
  0  db block gets
 13  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)
 10  rows processed

The CBO now uses the newly created Automatic Index.

So it only potentially takes just the one execution of an SQL statement for an Automatic Index to be created.

Therefore some caution needs to be exercised in environments where there may be a very large number of ad-hoc queries where specific indexes may not be necessary for once only executed predicate combinations.

That said, the Automatic Indexing process is highly efficient in building only the bare minimum of column indexed combinations to cater for all known SQL predicates.

More on this in a future post.

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners) August 19, 2019

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

absolute beginners

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below:

I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index.

To start, I create a typically simple little table:

SQL> create table bowie (id number constraint bowie_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

INDEX_NAME                  AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_PK                    NO  YES VISIBLE   DISABLED        VALID      10000000         19429               58133

The key column here is CODE, which is highly selective with just 10 rows on average per CODE value.

If I run the following query a number of times:

SQL> select * from bowie where code=42;

10 rows selected.

Execution Plan

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

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

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

Statistics
----------------------------------------------------------
    6  recursive calls
    0  db block gets
39026  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
    0  sorts (memory)
    0  sorts (disk)
   10  rows processed

The query runs slowly as it performs a Full Table Scan of a 10M row table when returning just 10 runs.

Perhaps an index would be a good idea…

With Auto Indexing, we just wait approx. 15 minutes until the Auto Index task completes, or manually run exec dbms_auto_index_internal.task_proc(true);

As discussed in my previous blog post, the Auto Indexing task will look at the workload over the past 15 minutes and determine if a new index might be warranted to improve the performance of an SQL.

We can query the results of the last Auto Index task by running the following:

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

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start                  : 20-JUN-2019 08:12:15
Activity end                    : 20-JUN-2019 08:12:55
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)                : 184.55 MB (184.55 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 2
SQL statements improved (improvement factor)    : 1 (39044.8x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 6.9x
-------------------------------------------------------------------------------

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

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

At this point in the report, we can see Oracle has verified 2 SQL statements and has created 1 new, visible index using 184.55 MB of space. It has improved 1 SQL statement by a factor of 39044.8x and improved things overall by a factor of 6.9x. (we’ll look at how Oracle determines these values in a later post).

The report continues with the Index Details section:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
---------------------------------------------------------------------
| Owner | Table | Index                | Key  | Type   | Properties |
---------------------------------------------------------------------
| BOWIE | BOWIE | SYS_AI_600vgjmtqsgv3 | CODE | B-TREE | NONE       |
---------------------------------------------------------------------

Oracle has decided to create a new indexed called “SYS_AI_600vgjmtqsgv3” on the CODE column of the BOWIE table. Notice the mixed case naming convention for the new Auto Index, oh what fun and games to be had…

Next the Verification Details section:

VERIFICATION DETAILS

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

Parsing Schema Name  : BOWIE

SQL ID               : dd5gzx7skf6as
SQL Text             : select * from bowie where code=42
Improvement Factor   : 39044.8x

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

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

Elapsed Time (s):    3241698                       108
CPU Time (s):        3174021                       108
Buffer Gets:         663764                        13
Optimizer Cost:      6204                          14
Disk Reads:          0                             0
Direct Writes:       0                             0
Rows Processed:      170                           10
Executions:          17                            1

So the SQL we previously ran has an improvement factor of 39044.8x with the new plan that uses the newly created Auto Index. These numbers are a little nonsensical as we’ll see in a later post, but it does sound kinda impressive…

Finally, we get to the Plans Section of the report:

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

Plan Hash Value  : 3567883234
-------------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows   | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |          |        |       | 6204 |          |
|  1 |  PX COORDINATOR                |          |        |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000 |    425 | 20825 | 6204 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |          |    425 | 20825 | 6204 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | BOWIE    |    425 | 20825 | 6204 | 00:00:01 |
-------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 493118340

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   10 |   230 |   14 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE                |   10 |   230 |   14 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_600vgjmtqsgv3 |   10 |       |    3 | 00:00:01 |

Here Oracle compares the original plan with the new plan that uses the new index. The new plan is much more efficient and so the index is created as a Valid, Visible index.

Note: the vast majority of my test cases were run on the Dedicated Autonomous Application Transaction Processing (ATP) environment, where parallelism is common for most plans by default.

Let’s look at details of the newly created Automatic Index:

 

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

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_PK                  NO  YES VISIBLE   DISABLED      VALID      10000000       19429             58133
SYS_AI_600vgjmtqsgv3      YES NO  VISIBLE   DISABLED      VALID      10000000       22419          10000000

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

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
BOWIE_PK                  ID                                 1
SYS_AI_600vgjmtqsgv3      CODE                               1

 

There is new column column called AUTO in DBA_INDEXES to denote where an index has been automatically created by Oracle.

So the new SYS_AI_600vgjmtqsgv3 Automatic Index on the CODE column is both VISIBLE and VALID in this case,  meaning it can be globally used within the database. As we’ll see if future posts, this is not always the case with Automatic Indexes.

If we now re-run the initial SQL query I ran and look at the execution plan:

 

SQL> select * from bowie where code=42;

10 rows selected.

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

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

8 - access("CODE"=42)

Statistics
----------------------------------------------------------
 12  recursive calls
  0  db block gets
 13  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)
 10  rows processed

 

We notice the new Automatic Index has been used by the CBO to substantially improve the performance of the query (just 13 consistent gets).

OK, so that’s the end of the Intro. In the next blog article, we’ll start looking at some of the specifics of how Auto Indexing works behind the covers and why it’s important to understand some of these workings…

Oracle 19c Automatic Indexing: Configuration (All I Need) July 29, 2019

Posted by Richard Foote in Automatic Indexing, AUTO_INDEX_COMPRESSION, AUTO_INDEX_DEFAULT_TABLESPACE, AUTO_INDEX_MODE, AUTO_INDEX_REPORT_RETENTION, AUTO_INDEX_RETENTION_FOR_AUTO, AUTO_INDEX_RETENTION_FOR_MANUAL, AUTO_INDEX_SCHEMA, AUTO_INDEX_SPACE_BUDGET, DBA_AUTO_INDEX_CONFIG, DBMS_AUTO_INDEX.CONFIGURE, Oracle Indexes, Oracle19c, SMB$CONFIG.
add a comment

In Rainbows

In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c.

The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a switch that just turns the feature on and that all necessary indexes then simply be created/modified/dropped as required. It’s not quite there yet, but it’ll no doubt get closer with each new release.

By default, Automatic Indexing is turned OFF. To turn on these capabilities, you simply run the following using the DBMS_AUTO_INDEX.CONFIGURE procedure:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE‘,’IMPLEMENT‘);

PL/SQL procedure successfully completed.

That’s it. Automatic Indexing is now enabled and as discussed in the previous blog post on the Automatic Indexing Methodology, every 15 minutes, the SYS_AUTO_INDEX_TASK background task will kickoff and automatically create/replace/drop any database indexes as necessary.

Another option, is to enable Automatic Indexing in ‘REPORT ONLY‘ mode:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY‘);

PL/SQL procedure successfully completed.

This will only create new indexes as INVISIBLE indexes, which are not considered by default by the CBO. The intent here is that the DBA can investigate the newly created Automatic Indexes and decide whether turning on this feature for real would be a good idea. Need to exercise some caution with this option though, as the limited options regarding how to subsequently administer the created Invisible Automatic Indexing can be problematic. I’ll discuss all this is more detail in a future post.

To turn off Automatic Indexing, simply set the AUTO_INDEX_MODE to ‘OFF’:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF‘);

PL/SQL procedure successfully completed.

Note here the documentation states “the existing auto indexes are disabled” which is incorrect. New Automatic Indexes will no longer be created, but existing Automatic Indexes will still be both Visible and Valid and available to the CBO for continued use.

By default, Automatic Indexing considers all tables in all “user created” schemas. However, this can be controlled with the AUTO_INDEX_SCHEMA option within the DBMS_AUTO_INDEX.CONFIGURE procedure. You can control which schemas to either explicitly include or exclude from Automatic Indexing considerations.

To add the BOWIE schema to an “Inclusion” list of schemas, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘,’BOWIE’, TRUE);

PL/SQL procedure successfully completed.

 

To add the BOWIE schema to an “Exclusion” list of schemas, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘,’BOWIE’, FALSE);

PL/SQL procedure successfully completed.

 

To remove the BOWIE schema from whichever list it belongs, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘, ‘BOWIE’, NULL);

PL/SQL procedure successfully completed.

 

To remove all schemas from the “Inclusion” list, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘, NULL, TRUE);

PL/SQL procedure successfully completed.

 

You can configure a tablespace to be the tablespace in which all Automatic Indexes are to now be created by running the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE‘,’INDEX_TS’);

PL/SQL procedure successfully completed

The INDEX_TS tablespace is now the location of all newly created Automatic Indexes.

You can also control how much of the configured Automatic Indexing tablespace is to be reserved for use by Automatic Indexes (default 50%) by running the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SPACE_BUDGET‘, ’42’);

PL/SQL procedure successfully completed.

Now, only 42% of the INDEX_TS tablespace can be used by Automatic Indexes.

 

You can control the number of days (the default is 373 days) in which if an Automatic Index has been deemed NOT to have been used, it will be automatically dropped. The following command:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO‘, ’42’);

PL/SQL procedure successfully completed.

will automatically drop any Automatic Index that is deemed not to have been used in the last 42 days.

Note: Oracle uses the new Indexing Tracking feature introduced in Oracle 12.2 to determine if an index has/has not been used, which has limitations that could potentially result in an Automatic Index that has been “lightly” used during the retention period being dropped. This will be discussed in more detail in a future post.

A similar retention configuration can be implemented for manually created indexes. The following command:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’,’42’);

PL/SQL procedure successfully completed.

will automatically drop any manually created index that is deemed not to have been used in the last 42 days.

Note: the same Indexing Tracking limitations means that manually created indexes lightly used during the last 42 days could also be automatically dropped.

I will discuss various undocumented implications of automatically dropping both Automatic and Manual Indexes in future posts.

 

Very importantly,  the Automatic Indexing logs on which Automatic Indexing Reports are based are only retained by default for just 31 days. To change the time before the Automatic Indexing logs are deleted, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_REPORT_RETENTION‘,’342’);

PL/SQL procedure successfully completed.

The Automatic Indexing logs are now retained for 342 days. These Automatic Indexing reports (and hence logs) are critical for understanding what the Automatic Indexing featuring is doing within the database and will be discussed extensively in future posts. I would recommend increasing the retention period from the 31 days default.

 

The final configuration option is not officially documented (yet) and controls whether or not Advanced Compression is used for Automatic Indexes. The default is that Advanced Compression is disabled, but this can be changed as follows:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_COMPRESSION‘,’ON’);

PL/SQL procedure successfully completed.

All Automatic Indexes are now automatically compressed using Advanced Low Compression.

I believe this was at some stage going to be the default behaviour, but due to licencing considerations and that many sites don’t have the Advanced Compression Option, this was subsequently changed. If you do have Advanced Compression, I would strongly recommend turning this ON, as implementing Advanced Low Compression is a relatively no-brainer beneficial decision.

I will however discuss the various undocumented implications of Advanced Compression in relation to Automatic Indexing in future posts.

The documented DBA_AUTO_INDEX_CONFIG view can be used to view the current setting for all of these configuration options:

SQL> select * from dba_auto_index_config;

PARAMETER_NAME                             PARAMETER_VALUE      LAST_MODIFIED                              MODIFIED_BY
---------------------------------------- -------------------- ---------------------------------------- --------------------
AUTO_INDEX_COMPRESSION                   OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                          REPORT ONLY           03-JUL-19 05.43.28.000000 AM            BOWIE
AUTO_INDEX_REPORT_RETENTION              31
AUTO_INDEX_RETENTION_FOR_AUTO            42                    20-JUN-19 06.32.06.000000 AM            BOWIE
AUTO_INDEX_RETENTION_FOR_MANUAL                                02-JUL-19 12.12.21.000000 AM            BOWIE
AUTO_INDEX_SCHEMA  schema IN (BOWIE)                           20-JUN-19 06.27.26.000000 AM            BOWIE
AUTO_INDEX_SPACE_BUDGET                  50

 

Additionally, you can view both the documented and undocumented settings regarding Automatic Indexing by looking at the SMB$CONFIG table:

 

SQL> select parameter_name, parameter_value
from sys.SMB$CONFIG;

PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ---------------
SPACE_BUDGET_PERCENT                                  10
PLAN_RETENTION_WEEKS                                  53
SPM_TRACING                                            0
AUTO_CAPTURE_PARSING_SCHEMA_NAME                       0
AUTO_CAPTURE_MODULE                                    0
AUTO_CAPTURE_ACTION                                    0
AUTO_CAPTURE_SQL_TEXT                                  0
AUTO_INDEX_SCHEMA                                      0
AUTO_INDEX_DEFAULT_TABLESPACE                          0
AUTO_INDEX_SPACE_BUDGET                               50
AUTO_INDEX_REPORT_RETENTION                           31
AUTO_INDEX_RETENTION_FOR_AUTO                          0
AUTO_INDEX_RETENTION_FOR_MANUAL                        0
AUTO_INDEX_MODE                                        0
_AUTO_INDEX_TRACE                                      0
_AUTO_INDEX_TASK_INTERVAL                            900
_AUTO_INDEX_TASK_MAX_RUNTIME                        3600
_AUTO_INDEX_IMPROVEMENT_THRESHOLD                     20
_AUTO_INDEX_REGRESSION_THRESHOLD                      10
_AUTO_INDEX_ABSDIFF_THRESHOLD                        100
_AUTO_INDEX_STS_CAPTURE_TASK                           0
_AUTO_INDEX_CONTROL                                    0
_AUTO_INDEX_DERIVE_STATISTICS                          0
_AUTO_INDEX_CONCURRENCY                                1
_AUTO_INDEX_SPA_CONCURRENCY                            1
_AUTO_INDEX_REBUILD_TIME_LIMIT                        30
_AUTO_INDEX_REBUILD_COUNT_LIMIT                        5
_AUTO_INDEX_REVERIFY_TIME                             30
AUTO_INDEX_COMPRESSION                                 0
AUTO_SPM_EVOLVE_TASK                                   0
AUTO_SPM_EVOLVE_TASK_INTERVAL                       3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME                    1800

 

I’ll discuss a number of these undocumented configuration options in future posts.

In my next post however, we’ll look at the Automatic Indexing feature in action with a very simple example to start with…

Oracle 19c Automatic Indexing: Methodology Introduction (After Today) July 24, 2019

Posted by Richard Foote in Automatic Indexing, Oracle Indexes, Oracle19c.
1 comment so far

young americans

For the past month or so I’ve been playing around extensively with the new Oracle 19c “Automatic Indexing” feature, so I thought it was time to start blogging about it.

Considering it’s only in “Version 1” status, my initial impression is very positive in that it works extremely well doing at what it’s initially designed to do. There are certainly “interesting” bits of behavior here and there and some missing functionality, but it’s a whole lot better than DB running sub-optimally due to missing indexes.

This post is purely an introduction in which I’ll just discuss the general methodology behind the Automatic Indexing (AI) capability, initially in a somewhat simplistic manner in order to convey the more important concepts. I’ll expand and discuss many of the actual complexities behind this cool feature in future posts.

The basic concept behind AI is that the DBA via a simple “switch” can turn on the AI  feature and have the Oracle Database automatically create/drop/modify any necessary database indexes as required.

The Oracle database continually captures SQL workloads and keeps track of any new column usages within SQL predicates (as well as associated plans and execution statistics) that might warrant the need for a new index. Note that currently, only equality predicates are considered when determining potential candidate Automatic Indexes.

By default, a background task is executed every 15 minutes that by default runs for up to an hour, to determine if the database can identify any new indexes that “might” be warranted. There are a number of reasons why the tasks might actually take considerably more time than the default 1 hour (I’ll expand on reasons why in future posts).

Candidate indexes that have been detected based on the previous 15 mins database workload are initially created as INVISIBLE/UNUSABLE indexes and hard parsed with the captured SQLs to determine if the index could be considered by the CBO.

If the candidate indexes are indeed viable, the indexes are then created as INVISIBLE/USABLE indexes and verified via the SQL Performance Analyzer using SQL Tuning Sets to determine if the performance of the captured SQLs have indeed improved from the existing plans when using the newly created Automatic Indexes.

If performance actually improves for all captured SQLs using a new Automatic Index, the Automatic Index is made VISIBLE and is now available for general database use. If performance is worse for all captured SQLs, the Automatic Index is made UNUSABLE again and hence not available to the CBO. If performance is better for some SQLs but worse for others, well the story gets a little complicated. In “theory”, the Automatic Index is made USABLE but SQL baselines are created for the SQLs that suffer performance degradation to not use the Automatic Index. Any such SQLs are effectively “blacklisted” and are not (easily) considered for future “new” AI deliberations.  As I’ll discuss in future posts, things are not actually quite as straightforward as that.

So depending on the scenario, Automatic Indexes can end up being in any of the following states:

  • Invisible and Unusable
  • Invisible and Valid
  • Visible and Unusable
  • Visible and Valid

When Visible and Valid, Automatic Indexes can ultimately be:

  • Used by the CBO
  • Not used by the CBO (even by the SQL that caused its creation)

As part of the identify candidate index process, Oracle will consider if a new index can be logically “merged” with an existing index and effectively replace an existing index by dropping and replacing it with a new Automatic Index.

AI will also monitor if existing (either Automatic or Manual) indexes are not currently being used within the database. If after a configurable period of time an index is deemed not to have been used within that time, the index will be automatically dropped during the AI 15 minute task.

This is the basic AI story. The actual story is a tad more complicated and which I’ll expand upon in many many future posts, so keep your questions until then 🙂

 

Importantly, AI is ONLY available on the Exadata platform or on Oracle Cloud environments. It is NOT available on standard On-Premises Oracle 19c deployments. Attempts to turn the feature on where not supported will only result in disappointment:

 

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);
BEGIN DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’); END;

*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_AUTO_INDEX_INTERNAL”, line 9180
ORA-06512: at “SYS.DBMS_AUTO_INDEX”, line 283
ORA-06512: at line 1

 

I’ll next discuss the AI configuration options available to the DBA…

 

 

Oracle Database 19c Automatic Indexing: Predicted Back In 2008 (A Better Future) March 19, 2019

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

heathen

I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones).

In the comments section, there’s an interesting discussion where I mention:

If Oracle19 does everything for you and all the various indexes structures get automatically created, used, applied, tuned, maintained, partitioned, etc. etc., then I’ll have nothing to write about 🙂.

Actually, when I think about it, it may not take as long as Oracle19 for Oracle to get there.

The fact this was stated some 10 years before the release of Oracle Database 19c with indeed the new Automatic Indexing capability is kinda funny.

I would however like to state a few points.

Firstly, congratulations for the impressive timely predication should go to Robert who made the initial reference to Oracle 19c in the comments.

Although the changing in Oracle database versioning helped in getting the predicted Oracle version correct, I did at least make the comment that I thought some form Automatic Indexing was likely before Oracle 19c.

Although not all the predicted capabilities are there yet (no auto index maintenance, no auto index partitioning, not all index structures are yet supported with auto indexing, etc.), I do predict that these will all be implemented within a few Oracle database releases (early 20s).

I stated there would be nothing to blog about if automatic indexing became a reality, which of course is not true either. The more one understands how Oracle actually works, the more one can proactively identify and address any potential issues and deficiencies. Automatic Indexing is ONLY going to be available on Exadata and the Oracle Cloud platform, so the majority of Oracle On-Premises database users will still need to manually manage indexes. Understanding how Automatic Indexing capability works means one can potentially highlight and learn how to more appropriately manage indexes manually.

So finally, yes I will of course be blogging about Automatic Indexing and about indexing in general in the new Autonomous Database cloud environments.

I’ll start with some initial thoughts and examples of indexing the Autonomous Data Warehouse Database.

Stay tuned.