jump to navigation

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…

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People) March 22, 2019

Posted by Richard Foote in 18c, 19c, 19c New Features, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Oracle Indexes.
4 comments

automatic for the people

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this.

Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous Databases are positioned as “self-driving” Cloud- Based database environments, that automatically address many of the tasks usually performed by DBAs such as patching, backups, security and what is of most interest to me, database tuning.

The first of these cloud environments, the Oracle Autonomous Data Warehouse Cloud Service  (ADW) was first released in March 2018, with the Oracle Autonomous Transaction Processing Cloud Service (ATP) released in August 2018.

So key point number one. These are all Oracle Autonomous Database Cloud Services, there are no actual Autonomous Databases as such. These environments currently consist of the following key components:

  • Oracle Database 18c (and above)
  • Oracle Cloud Environment
  • Oracle Exadata Infrastructure
  • Oracle Policy-Driven Automation

So the Oracle Database alone is not an autonomous database. An Oracle 18c database running on your On-Premises Exadata is still not enough to be an autonomous database platform. It requires all the above components, which is why this is only available on the Oracle Cloud environment (and will likely be soon available on the Oracle Cloud at Customer environment, where all these components can also be replicated).

Now having a database environment is which many of the mundane DBA tasks (such as database provisioning, patching, taking backups, etc.) can be automated can only be a good thing and the Autonomous Database Cloud services delivers on all these. Creating an Autonomous Database environment truly just takes a few minutes and I could easily connect via SQL*PLUS and SQL Developer on my laptop a few minutes later.

However, my key interest here is in database tuning (hey, I’m a database tuning guy) and the capability of the Autonomous Database Cloud Services in being able to self-tune and self-repair itself when database performance issues and inefficiencies are encountered.

So, are we there yet?

So my second key point is that is many ways, we already have a “self-tuning” database with Oracle and have had so for many many years. I’m old enough to remember the Rule-Based Optimizer, when the structure of the SQL was critical to performance. I remember tuning Rollback Segments and manually setting extent sizes to make sure no segment got too close to 121 extents else it couldn’t grow any further. I remember manually setting Freelists, I remember having to write my own jobs to run maintenance tasks and setting tablespaces to be in backup mode etc. etc. etc.

The Oracle Database has evolved over the years, where Oracle DBAs don’t have to worry about these things. If you wish, you can now configure the Oracle database to also automatically adjust memory components, automatically collect necessary schema statistics, automatically create baselines and to tune SQL queries, etc. etc. etc.

All of these Oracle database capabilities are crucial in the new Oracle autonomous database environments, as are new Oracle 18c features and as will be new Oracle 19c features (especially in relation to self-tuning the Autonomous Transaction Processing Cloud Service). The newer autonomous database capabilities are just part of this Oracle database self-tuning evolution, with in the future some new policy-driven based automation thrown into the mix.

So are we indeed there yet with a completely self-tuning database with these new autonomous database services? In a word, no.

Which brings me to my next key point. This is all very very new. All these autonomous database services are effectively at “Edition One” status. This will all take time to eventually evolve to be truly, fully self-tuning database environments. There’ll be some new cool capabilities and features which will assist in some areas but be initially deficient in other areas. But clearly this is the future and clearly in future releases, more and more self-tuning capabilities will be added that will make things easier to both manage and tune Oracle database environments.

Note Oracle Corporation itself (depending on who you talk to) is quite clear that it isn’t there yet, with the web-page on the Autonomous Transaction Processing Cloud services clearly stating that “Workload Optimization* (coming soon)“, but with lots of clues on what’s to come with features such as “Database tunes itself (indexes, memory, partitions, SQL plans, parallelism, optimizer stats) for the incoming workload as data changes over time“.

Do many of these upcoming features sound familiar? If you’re not sure, check out the Oracle Database 19c New Features manual.

Which brings me to my final key point here. Even if you’re not particularly interested in the Cloud, if you view managing On-Premises environments as being your foreseeable future, some the best things that has happened to you in relation to the Oracle Database comes courtesy to you as a result of Oracle’s strategic direction with the cloud. Many of the best new features introduced in the past few Oracle Database releases, especially in relation to the CBO and much of the online stuff such moving tables and partitions online, moving data files online, converting tables to be partitioned online, converting partitioned tables differently online, merging/splitting partitions online, etc. etc. are clearly going to be critical in a self-managing/tuning database. As a DBA of an On-Premises database environment, you can also take advantage of these new capabilities.

It will enable Oracle in its autonomous environments for example to automatically convert that table to be partitioned in this specific manner to improve overall performance, all behind the scenes, without anyone necessarily knowing it’s done so.

Is it there yet? No. Is it coming? You bet.

That said, some newer Oracle Database 19c features that will clearly be critical to a self-tuning autonomous databases moving forward such as Real-Time Statistics, SQL Quarantine and Automatic Indexing will only be available in the Oracle Cloud and Exadata platforms. So take note…

Which brings me to indexing.

When the first Oracle Autonomous Data Warehouse cloud service was announced in March 2018, one of the key “features” of the new autonomous platform was that indexing was disabled (as were other traditional Data Warehouse database capabilities such as Partitioning, Materialized Views, etc.). But how can you effectively “tune” a database when you take away some of the key tuning capabilities? The short answer is that you can’t, unless the database has somewhat simplistic data workloads and data structures.

Remember, this was “Version One” with the first autonomous database environment and the initial strategy was to make the Oracle database not smarter, but dumber. Simplify it such that DBAs can’t easily “break” things and by simply making the Exadata platform do all the heavy lifting with regards to database tuning. A more simplified environment makes things a little easier to “self-tune” as there are fewer moving parts to worry about.

For more simplistic Data Warehouse environments, this might all be adequate. For those of you who follow my blog and my previous discussions on indexing on Exadata, dropping all indexes on Exadata, even on Data Warehouse environments was generally a bad idea. So providing an Oracle database platform, even an autonomous one running on an Exadata platform, where all indexing was effectively disabled, was always going to have performance shortfalls in some scenarios. Try running ad-hoc queries on the supplied SSB LINEORDER table for example. Smart Scans, Storage Indexes, HCC, Result Caches, etc. will only take you so far.

So as I said, Oracle evolves and improves and now allows database indexes to be created in the Autonomous Data Warehouse cloud service.

Which will be the focus on upcoming blog posts, so stay tuned.