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.add a comment
I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issue 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 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. This number is 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 these test were run on the Dedicated Autonomous Application Transaction Processing (ATP) environment, where parallelism is common for most plans by default.
If we 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
So the new 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 SQL 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…
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
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.
