Oracle 19c Automatic Indexing: Common Index Creation Trap (Rat Trap) June 30, 2020
Posted by Richard Foote in 19c, 19c New Features, ASSM, Automatic Indexing, CBO, Clustering Factor, Data Clustering, Oracle Indexes, TABLE_CACHED_BLOCKS.1 comment so far
When I go to a customer site to resolve performance issues, one of the most common issues I encounter is in relation to inefficient SQL. And one of the most common causes for inefficient SQL I encounter is because of deficiencies the default manner by which the index Clustering Factor is calculated.
When it comes to both Automatic Indexes and in relation to the Oracle Autonomous Database Cloud Services, the “flawed” default manner by which the index Clustering Factor is calculated still applies. So we need to exercise some caution when Auto Indexes are created and the impact their default statistics can have on the performance of subsequent SQL statements.
To illustrate with a simple example, I’ll first create a table with the key column being the ID column which will be effectively unique. The table will be populated via a basic procedure that just inserts 1M rows. The procedure uses an ORDER sequence, such that the ID values are generated in a monotonically increasing manner:
SQL> create table bowie_assm (id number, code number, name varchar2(42)); Table created. SQL> create sequence bowie_assm_seq order; Sequence created. Procedure created. SQL> create or replace procedure pop_bowie_assm as 2 begin 3 for i in 1..1000000 loop 4 insert into bowie_assm values (bowie_assm_seq.nextval, mod(i,1000), 'DAVID BOWIE'); 5 commit; 6 end loop; 7 end; 8 / Procedure created.
However crucially, the procedure is executed by 3 different session concurrently, to simulate a multi user environment inserting into a table…
SQL> exec pop_bowie_assm PL/SQL procedure successfully completed.
We’ll now collect statistics on the table:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_ASSM'); PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE_ASSM'; TABLE_NAME NUM_ROWS BLOCKS --------------- ---------- ---------- BOWIE_ASSM 3000000 12137
So the table has 3M rows and is 12137 blocks in size.
If we run an SQL a few times where we select only the one ID value:
SQL> select * from bowie_assm where id = 42;
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 1934 (6)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 22 | 1934 (6)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 1 | 22 | 1934 (6)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| BOWIE_ASSM | 1 | 22 | 1934 (6)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("ID"=42)
filter("ID"=42)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
12138 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The execution plan shows a Full Table Scan (FTS) is invoked, the only choice the CBO has without an index on the ID column. Clearly an index on the ID column would make the plan substantially more efficient with just 1 row selected from a 3M row table. Hopefully, Automatic Indexing will come to our rescue, so let’s check out the subsequent Automatic Indexing Report:
REPORT SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 1 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 58.72 MB (58.72 MB / 0 B) Indexes dropped : 0 SQL statements verified : 2 SQL statements improved (improvement factor) : 1 (1.2x) SQL plan baselines created : 0 Overall improvement factor : 1.1x ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ------------------------------------------------------------------------- | BOWIE | BOWIE_ASSM | SYS_AI_2w1pss6qbdz6z | ID | B-TREE | NONE | -------------------------------------------------------------------------
So yes indeed, an Automatic Index (SYS_AI_2w1pss6qbdz6z) was created on the ID column.
If we look at the default Clustering Factor of this index:
SQL> select index_name, auto, constraint_index, visibility, status, clustering_factor from user_indexes where table_name='BOWIE_ASSM'; INDEX_NAME AUT CON VISIBILIT STATUS CLUSTERING_FACTOR -------------------- --- --- --------- -------- ----------------- SYS_AI_2w1pss6qbdz6z YES NO VISIBLE VALID 2504869
We notice the Clustering Factor is relatively high at 2504869, much higher than the 12137 number of blocks in the table.
But if the ID column in the table has been loaded via a monotonically increasing sequence, doesn’t that mean the ID values have been inserted in approximately in ID order? If so, doesn’t that mean the ID column should have a “good” Clustering Factor” as the order of the rows in the table matches the order of the indexed values in the ID index?
Clearly not.
The reason being that the table is stored in the default Automatic Segment Space Management (ASSM) tablespace type, which is designed to avoid contention by concurrent inserts from different sessions. Therefore each of the 3 sessions inserting into the table are each assigned to different table blocks, resulting in the rows not being precisely inserted in ID order. It’s very close to ID order, the the ID values clustered within a few blocks from each other, but not precisely stored in ID order.
However, by default, the Clustering Factor is calculated by reading each index entry and determining if it references a ROWID that accesses a table block different from the PREVIOUS index entry. If it does differ, it increments the Clustering Factor, if it doesn’t differ and accesses the same table block as the previous index entry, the Clustering Factor is NOT incremented.
So in theory, we could have 100 rows that reside in just 2 different table blocks, but if the odd IDs live in one block and the even IDs live in the other block, meaning that each ID is stored in a different table block to the previous, the Clustering Factor would have a value of 100 for these 100 rows, even though they only occupy 2 table blocks. The Clustering Factor is therefore much higher than in reality it should be as ultimately only 2 different table blocks are accessed within a negligible time from each other.
This is the “flaw” with how the default Clustering Factor is calculated. By noting if a table block access differs only from the previous table block accessed, it leaves the Clustering Factor calculation susceptible to exaggerated high values when the data really is relatively well clustered within the table.
If we run the same SQL as previously which only selects one ID value:
SQL> select * from bowie_assm where id = 42;
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 22 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM | 1 | 22 | 4 (0)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_2w1pss6qbdz6z | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ID"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO now uses the new Automatic Index as with just one row, the index is clearly more efficient regardless of the Clustering Factor value.
However, if we now run a query that selects a range of ID values, in this example between 42 and 4242 which represents only a relatively low 0.14% of the table:
SQL> select * from bowie_assm where id between 42 and 4242;
4201 rows selected.
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4202 | 92444 | 1934 (6)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 4202 | 92444 | 1934 (6)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 4202 | 92444 | 1934 (6)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| BOWIE_ASSM | 4202 | 92444 | 1934 (6)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("ID"<=4242 AND "ID">=42)
filter("ID"<=4242 AND "ID">=42)
Statistics
----------------------------------------------------------
8 recursive calls
4 db block gets
12138 consistent gets
0 physical reads
0 redo size
54767 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4201 rows processed
The CBO decides to use a Full Table Scan as it deems the index with the massive Clustering Factor to be too expensive, with it having to visit differing blocks for the majority of the estimated 4202 rows (note at 4201 actual rows returned, this estimate by the CBO is practically spot on).
If we force the use of the index via an appropriate hint:
SQL> select /*+ index (bowie_assm) */ * from bowie_assm where id between 42 and 4242;
4201 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4202 | 92444 | 3530 (1)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4202 | 92444 | 3530 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM | 4202 | 92444 | 3530 (1)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 4202 | | 12 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 4202 | | 12 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_2w1pss6qbdz6z | 4202 | | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ID">=42 AND "ID"<=4242)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
54767 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4201 rows processed
Note at an estimated cost of 3530, this is greater than the 1934 cost of the FTS which explains why the CBO decides the FTS is best. However, if we look at the number of Consistent Gets, it’s only 26, meaning the CBO is actually getting these costs way wrong.
Why?
Because of the grossly inflated Clustering Factor.
As I’ve discussed previously, Oracle 12.1 introduced a new TABLE_CACHED_BLOCKS preference. Rather than the default value of 1, we can set this to any value up to 255. When calculating the Clustering Factor during statistics collection, it will NOT increment the Clustering Factor if the index visits a table block again that was one of the last “x” distinct table blocks visited. So by setting TABLE_CACHED_BLOCKS to (say) 42, if the index visits a block that was one of the last 42 distinct table blocks previously visited, don’t now increment the Clustering Factor. This can therefore generate a much more “accurate” Clustering Factor which can be significantly smaller than previously. This in turn makes the index much more efficient to the CBO because it then estimates far fewer table blocks need be accessed during a range scan.
So let’s change the TABLE_CACHED_BLOCKS value for this table to 42 (don’t increment now the Clustering Factor value when collecting statistics if we visit again any of the last 42 differently accessed table blocks) and recollect the segment statistics:
SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE_ASSM', pname=>'TABLE_CACHED_BLOCKS', pvalue=>42); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_ASSM', cascade=>true); PL/SQL procedure successfully completed.
If we now examine the new Clustering Factor value:
SQL> select index_name, auto, constraint_index, visibility, status, clustering_factor from user_indexes where table_name='BOWIE_ASSM'; INDEX_NAME AUT CON VISIBILIT STATUS CLUSTERING_FACTOR -------------------- --- --- --------- -------- ----------------- SYS_AI_2w1pss6qbdz6z YES NO VISIBLE VALID 11608
We can see that at just 11608 it’s substantially less than the previous 2504869.
If we now rerun the previous range scan SQL without the hint:
SQL> select * from bowie_assm where id between 42 and 4242;
4201 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4202 | 92444 | 30 (4)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4202 | 92444 | 30 (4)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ASSM | 4202 | 92444 | 30 (4)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 4202 | | 12 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 4202 | | 12 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_2w1pss6qbdz6z | 4202 | | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ID">=42 AND "ID"<=4242)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
54767 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4201 rows processed
We can see the CBO now automatically uses the new Automatic Index. At a new cost of just 30, it’s substantially less than the previous index cost of 3530 and now much less than the 1934 for the FTS and so why the index is now automatically chosen by the CBO.
When Automatic Indexes are created, it’s usually a good idea to check on the Clustering Factor and because default ASSM tablespaces have a tendency to significantly escalate the values of index Clustering Factors, to look at recalculating them with an non-default setting of the TABLE_CACHED_BLOCKS statistics collection preference.
Of course, not only is this a good idea for Automatic Indexes, but for manually created indexes as well.
Although no doubt Autonomous Database Cloud services will look at these issues in the future, such self-tuning capabilities are not currently available. You will need to go in there and make these changes as necessary to fix the root issues with such inefficient SQL statements…
Oracle 19c Automatic Indexing: A More Complex Example (How Does The Grass Grow) June 16, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO.add a comment
In this post I’m going to put together in a slightly more complex SQL example a number of the concepts I’ve covered thus far in relation to the current implementation of Oracle Automatic Indexing.
I’ll begin by creating three tables, a larger TABLE1 and two smaller TABLE2 and TABLE3 lookup tables. Each table is created with only a Primary Key, Unique index and currently have no secondary indexes (this demo was run in an ATP Autonomous Cloud environment hence the odd parallel execution plans):
SQL> create table table1 (id number not null, code1 number not null, grade1 number not null, name1 varchar2(42)); Table created. SQL> insert into table1 select rownum, mod(rownum, 1000)+1, mod(rownum, 200000)+1, 'David Bowie '|| rownum from dual connect by level <=1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> create unique index table1_id_i on table1(id); Index created. SQL> alter table table1 add primary key(id); Table altered. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE1'); PL/SQL procedure successfully completed. SQL> create table table2 (id number not null, stuff number not null, name2 varchar2(42)); Table created. SQL> insert into table2 select rownum, mod(rownum, 100)+1, 'Ziggy Stardust ' || rownum from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create unique index table2_id_i on table2(id); Index created. SQL> alter table table2 add primary key(id); Table altered. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE2'); PL/SQL procedure successfully completed. SQL> create table table3 (id number not null, code3 number not null, name3 varchar2(42)); Table created. SQL> insert into table3 select rownum, mod(rownum, 500)+1, 'Thin White Duke ' || rownum from dual connect by level <=1000; 1000 rows created. SQL> commit; Commit complete. SQL> create unique index table3_id_i on table3(id); Index created. SQL> alter table table3 add primary key(id); Table altered. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE3'); PL/SQL procedure successfully completed.
I’ll next run the following “complex” query a number of times:
SQL> select code1, grade1, name1, name2 from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=42);
10 rows selected.
Execution Plan
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 600 | 857 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 600 | 857 (7)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 600 | 857 (7)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 600 | 857 (7)| 00:00:01 |
|* 5 | HASH JOIN | | 10 | 360 | 852 (7)| 00:00:01 |
| 6 | JOIN FILTER CREATE | :BF0000 | 2 | 16 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL | TABLE3 | 2 | 16 | 2 (0)| 00:00:01 |
| 8 | JOIN FILTER USE | :BF0000 | 1000K| 26M| 833 (5)| 00:00:01 |
| 9 | PX BLOCK ITERATOR | | 1000K| 26M| 833 (5)| 00:00:01 |
|* 10 | TABLE ACCESS STORAGE FULL | TABLE1 | 1000K| 26M| 833 (5)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | TABLE2_ID_I | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | TABLE2 | 1 | 24 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TABLE1"."GRADE1"="TABLE3"."ID")
7 - storage("TABLE3"."CODE3"=42)
filter("TABLE3"."CODE3"=42)
10 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
11 - access("TABLE1"."CODE1"="TABLE2"."ID")
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
5777 consistent gets
0 physical reads
0 redo size
1224 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
We note there are three predicates listed in which a column access could potentially benefit from an index being created:
table1.code1=table2.id (note that table2.id already has a unique index defined)
table1.grade1 in (Select…)
table3.code3=42
Let’s have a look at the corresponding Automatic Indexing report to see what the Oracle Automatic Indexing process made of this example:
REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 26-JUN-2019 08:56:08 Activity end : 26-JUN-2019 08:56:53 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 2 (2 / 0) Space used (visible / invisible) : 18.94 MB (18.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 2 SQL statements improved (improvement factor) : 1 (192.7x) SQL plan baselines created : 0 Overall improvement factor : 170.2x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0
We note there are 3 index candidates that were considered, BUT only 2 new indexes were actually created. Overall, the created indexes resulted in an estimated 192.7x improvement in the above SQL performance.
If we look further on in the report and at the actual indexes created:
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
------------------------------------------------------------------------
| BOWIE | TABLE1 | SYS_AI_0p5nn18dt9bn1 | GRADE1 | B-TREE | NONE |
| BOWIE | TABLE3 | SYS_AI_b4ntgxt6pdbfh | CODE3 | B-TREE | NONE |
------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 21v8yqs9jrnzm
SQL Text : select code1, grade1, name1, name2 from table1, table2
where table1.code1=table2.id and table1.grade1 in (select table3.id from table3 where table3.code3=42)
Improvement Factor : 192.7x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 299396 2170
CPU Time (s): 289510 1036
Buffer Gets: 28912 43
Optimizer Cost: 857 27
Disk Reads: 0 4
Direct Writes: 0 0
Rows Processed: 50 10
Executions: 5 1
We note the report states the two new indexes are created on the TABLE1.GRADE1 and TABLE3.CODE3 columns.
If we look at all the indexes that have been created on these tables:
SQL> select table_name, index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name like 'TABLE%'; TABLE_NAME INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------ ---------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- TABLE1 TABLE1_ID_I NO NO VISIBLE DISABLED VALID 1000000 2088 5202 TABLE1 SYS_AI_85dcbcnkgj0w0 YES NO INVISIBLE DISABLED UNUSABLE 1000000 2171 415525 TABLE1 SYS_AI_0p5nn18dt9bn1 YES NO VISIBLE DISABLED VALID 1000000 2221 1000000 TABLE2 TABLE2_ID_I NO NO VISIBLE DISABLED VALID 10000 20 44 TABLE3 TABLE3_ID_I NO NO VISIBLE DISABLED VALID 1000 2 5 TABLE3 SYS_AI_b4ntgxt6pdbfh YES NO VISIBLE DISABLED VALID 1000 3 999 SQL> select table_name, index_name, column_name, column_position from user_ind_columns where table_name like 'TABLE%' order by table_name, index_name, column_position; TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------ ---------------------- --------------- --------------- TABLE1 SYS_AI_0p5nn18dt9bn1 GRADE1 1 TABLE1 SYS_AI_85dcbcnkgj0w0 CODE1 1 TABLE1 TABLE1_ID_I ID 1 TABLE2 TABLE2_ID_I ID 1 TABLE3 SYS_AI_b4ntgxt6pdbfh CODE3 1 TABLE3 TABLE3_ID_I ID 1
We note that both indexes listed as created in the Auto Indexing report on the TABLE1.GRADE1 (and TABLE3.CODE3 columns are both listed as being VISIBLE and VALID. Both of these indexes have been proven to improve the performance of the SQL statement by reducing the number of logical reads.
However, there is also an Auto Indexed defined on the other potential indexed column table1.code1, called “SYS_AI_85dcbcnkgj0w0” that has been left in an INVISIBLE, UNUSABLE state. This index has been shown to be ineffective in improving SQL performance and has been converted back to an UNUSABLE state.
If we run the query again and look at the resultant execution plan:
SQL> select code1, grade1, name1, name2
from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=24);
10 rows selected.
Execution Plan
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 600 | 15 (0)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 600 | 15 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 600 | 15 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 600 | 15 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 10 | 360 | 10 (0)| 00:00:01 |
| 6 | PX BLOCK ITERATOR | | | | | |
|* 7 | TABLE ACCESS STORAGE FULL | TABLE3 | 2 | 16 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 5 | 140 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SYS_AI_0p5nn18dt9bn1 | 5 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | TABLE2_ID_I | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TABLE2 | 1 | 24 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - storage("TABLE3"."CODE3"=24)
filter("TABLE3"."CODE3"=24)
9 - access("TABLE1"."GRADE1"="TABLE3"."ID")
10 - access("TABLE1"."CODE1"="TABLE2"."ID")
Statistics
----------------------------------------------------------
10 recursive calls
4 db block gets
59 consistent gets
0 physical reads
0 redo size
1122 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
We note the new execution plan now uses both newly created Automatic Indexes and at just 59 consistent gets, is significantly more efficient than it was previously where it required 5777 consistent gets.
So the Auto Indexing process can create any number of possible indexes for a particular query and may independently ultimately determine different states for the various candidate indexes and so only create and keep the necessary indexes to sufficiently improve an SQL.
We now have an SQL statement that automatic runs much more efficiently without human intervention thanks to these automatically created indexes…
Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon) May 12, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Index, Index Rebuild, Oracle Indexes.add a comment
Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future).
Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would be worth checking out.
For a number of reasons (which I’ll cover in some detail in upcoming articles, but for now using Automatic Indexing in REPORT ONLY mode is but one reason), you can easily be left with an Automatic Index that might get in the way of things and you may want to drop it.
However, as we’ll see, you can’t easily drop an Automatic Index. The only “supported” manner to drop an Automatic Index is to wait for the Automatic Index Retention period to be exceeded (which is by default some 373 days and assumes the index is not used during this period).
Julian has come up with an alternate strategy.
By way of a demo, I currently have the following Automatic Index (SYS_AI_5zjkc60knz9zp):
SQL> select index_name, auto, status, visibility from user_indexes where table_name='CRACKED_ACTOR'; INDEX_NAME AUT STATUS VISIBILIT ------------------------------ --- -------- --------- CRACKED_ACTOR_CODE1_CODE2_I NO VALID VISIBLE SYS_AI_5zjkc60knz9zp YES VALID INVISIBLE SQL> select index_name, column_name, column_position from user_ind_columns where index_name='SYS_AI_5zjkc60knz9zp'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------------ --------------- SYS_AI_5zjkc60knz9zp ID 1
So I have an Automatic Index on the ID column of the CRACKED_ACTOR table, BUT it’s currently INVISIBLE and so can’t be used be default by the CBO.
If I run the following query:
SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 786009234
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CRACKED_ACTOR | 1 | 24 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7042 consistent gets
0 physical reads
0 redo size
789 bytes sent via SQL*Net to client
401 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO uses a Full Table Scan because the available Automatic Index on the ID column is current invisible.
If I try to convert it to being VISIBLE:
SQL> alter index "SYS_AI_5zjkc60knz9zp" visible; alter index "SYS_AI_5zjkc60knz9zp" visible * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
I can’t, because you can’t alter an Automatic Index to be Visible/Invisible.
If I try to just drop the Automatic Index:
SQL> drop index "SYS_AI_5zjkc60knz9zp"; drop index "SYS_AI_5zjkc60knz9zp" * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
Again, I can’t just simply drop an Automatic Index.
However, I am allowed to Rebuild (or Coalesce or Shrink) an Automatic Index. Therefore, I can create a new dummy tablespace and rebuild the Automatic Index to reside in this particular tablespace:
SQL> alter index "SYS_AI_5zjkc60knz9zp" rebuild tablespace bowie_stuff; Index altered.
I can then drop this tablespace including all its contents (and hence drop the Automatic Index contained within):
SQL> drop tablespace bowie_stuff including contents and datafiles; Tablespace dropped.
The problematic Automatic Index is now gone:
SQL> select index_name, auto, status, visibility from user_indexes where table_name='CRACKED_ACTOR'; INDEX_NAME AUT STATUS VISIBILIT ------------------------------ --- -------- --------- CRACKED_ACTOR_CODE1_CODE2_I NO VALID VISIBLE
I can now either manually create the necessary index or wait for the Automatic Index to now hopefully create a new, visible Automatic Index to address my query:
SQL> create index cracked_actor_id_i on cracked_actor(id); Index created. SQL> select index_name, auto, status, visibility from user_indexes where table_name='CRACKED_ACTOR'; INDEX_NAME AUT STATUS VISIBILIT ------------------------------ --- -------- --------- CRACKED_ACTOR_CODE1_CODE2_I NO VALID VISIBLE CRACKED_ACTOR_ID_I NO VALID VISIBLE
I’ve now addressed the problematic query:
SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 4160941723
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CRACKED_ACTOR | 1 | 24 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CRACKED_ACTOR_ID_I | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
793 bytes sent via SQL*Net to client
401 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Thanks Julian for the cool tip 🙂
Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay) May 6, 2020
Posted by Richard Foote in 19c, 19c New Features, Add Column To Existing Index, Automatic Indexing, Oracle Indexes.add a comment
In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index.
I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a new index can be used effectively for both previous and new workloads.
In this post, how will Automatic Indexing handle the scenario if a previously manually created index could also potentially be improved by adding a new column.
I’ll start by creating a table similar to my previous post but with more distinct values for the CODE3 column such that the test query will be more selective and so make the CBO favour the use of an index (Note: all examples are run on the OLTP Autonomous Database Cloud Service and hence the odd parallel execution plans):
SQL> create table major_tom6 (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into major_tom6 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 100)), 'David Bowie' from dual connect by level = 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'MAJOR_TOM6'); PL/SQL procedure successfully completed.
I’ll now manually create an index for BOTH combinations of the CODE2, CODE3 columns:
SQL> create index major_tom6_code2_code3_i on major_tom6(code2, code3); Index created. SQL> create index major_tom6_code3_code2_i on major_tom6(code3, code2); Index created. SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM6_CODE2_CODE3_I NO NO VISIBLE DISABLED VALID 10000000 23697 9890973 MAJOR_TOM6_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 23697 9890973
If I now run the following query:
SQL> select * from major_tom6 where code2=42 and code3=42;
983 rows selected.
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 28000 | 997 (1)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1000 | 28000 | 997 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6 | 1000 | 28000 | 997 (1)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 1000 | | 5 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1000 | | 5 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | MAJOR_TOM6_CODE2_CODE3_I | 1000 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CODE2"=42 AND "CODE3"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
971 consistent gets
0 physical reads
0 redo size
27836 bytes sent via SQL*Net to client
1303 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
983 rows processed
The CBO favours the use of an index as with just 983 rows returned from a 10M row table, the index is the cheaper access method.
If I now run the following SQL which also includes the more selectively CODE1 column predicate as well (which returns just 1 row):
SQL> select * from major_tom6 where code1=42 and code2=42 and code3=42;
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 997 (1)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 28 | 997 (1)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6 | 1 | 28 | 997 (1)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 1000 | | 5 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1000 | | 5 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | MAJOR_TOM6_CODE2_CODE3_I | 1000 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CODE1"=42)
8 - access("CODE2"=42 AND "CODE3"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
971 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO again uses the same index based on columns CODE2, CODE3 as this has already been proven to be more efficient than a FTS. However, an index that also included the CODE1 column would be even more efficient as the CBO could simply use this index to fetch just the row(s) of interest, without having to perform the unnecessary filtering on the CODE1 column.
So what does Automatic Indexing do in this scenario? If we look at the corresponding Auto Indexing Report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible --------------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------- | BOWIE | MAJOR_TOM6 | SYS_AI_4nc6g08bw8db7 | CODE2,CODE3,CODE1 | B-TREE | NONE | ---------------------------------------------------------------------------------------
We notice Auto Indexing has created a new index based on columns CODE2, CODE3, CODE1, however it has NOT dropped any indexes.
If we look at the Verification section of the Auto Indexing Report:
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 93zw1kj4n43n9
SQL Text : select * from major_tom6 where code1=42 and code2=42 and code3=42
Improvement Factor : 972.2x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 159122 1240
CPU Time (s): 70379 1320
Buffer Gets: 10698 4
Optimizer Cost: 997 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 11 1
Executions: 11 1
We can see the index was created because of a 972.2x improvement in the performance of the SQL query I ran.
If we look at the details of the indexes that now exist on the table:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM6_CODE2_CODE3_I NO NO VISIBLE DISABLED VALID 10000000 23556 9890973 MAJOR_TOM6_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 24029 9890973 SYS_AI_4nc6g08bw8db7 YES NO VISIBLE DISABLED VALID 10000000 29125 9999444 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM6' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------- -------------------- --------------- MAJOR_TOM6_CODE2_CODE3_I CODE2 1 MAJOR_TOM6_CODE2_CODE3_I CODE3 2 MAJOR_TOM6_CODE3_CODE2_I CODE3 1 MAJOR_TOM6_CODE3_CODE2_I CODE2 2 SYS_AI_4nc6g08bw8db7 CODE2 1 SYS_AI_4nc6g08bw8db7 CODE3 2 SYS_AI_4nc6g08bw8db7 CODE1 3
We notice a couple of key points.
Firstly, even though the previously created manual index on the columns (CODE2, CODE3) is now totally redundant because it has the same column list as the leading columns of the newly created Auto Index based on the columns (CODE2, CODE3, CODE1), Auto Indexing does NOT automatically drop the manually created index.
Auto Indexing ONLY automatically drops and logically recreates Auto Indexes.
Secondly, Auto Indexing is certainly aware of the previous workload because it has created the new Auto Index with the column list (CODE2, CODE3, CODE1) and NOT in the default CODE1, CODE2, CODE3 column order (as defined in the table definition).
This suggests Auto Indexing is indeed trying to create a new index that is able to cater for all known SQL workloads (predicates on just the CODE2, CODE3 columns and predicates on columns CODE1, CODE2, CODE3).
However, Auto Indexing does not (yet) have the capability to logically modify or drop obviously redundant manually created indexes (it can only do so on previously created Auto Indexes). This is likely one of the reasons why Oracle has provided us with the DROP_SECONDARY_INDEXES procedure in order to get rid of all those annoying manually created secondary indexes that can get in the way of an optimal indexing strategy.
More on DROP_SECONDARY_INDEXES in future posts.
Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part I (I Can’t Read) April 21, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Clustering Factor, Mixing Auto and Manual Indexes, Oracle Indexes.1 comment so far
In previous articles, I discussed how Automatic Indexing has the capability to add columns or reorder the column list of previously created Automatic Indexes. However, how does Automatic Indexing handle these types of scenarios with regard to existing manually created indexes?
To investigate, let’s create a table identical to the table I created in my previous blog post where Automatic Indexing created an index that was ultimately not used by the CBO because although Automatic Indexing finds the new index more efficient, the CBO costs it as being too expensive and ignores it.
SQL> create table major_tom5 (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into major_tom5 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)), 'David Bowie' from dual connect by level = 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'MAJOR_TOM5'); PL/SQL procedure successfully completed.
However, in this demo, I’m going to first create a manual index, but with the column list in CODE3, CODE2 order. This is the opposite order in which a default Automatic Index would be created (CODE2, CODE3 order) as this is the order of the columns in the table definition:
SQL> create index major_tom5_code3_code2_i on major_tom5(code3, code2); Index created. SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor <span style="color:var(--color-text);">from user_indexes where table_name='MAJOR_TOM5';</span> INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM5_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 24181 8974538
The resultant index has a terrible Clustering Factor of 8974538 on a 10M row table.
If we run the following query with filtering predicates on these 2 indexed columns:
SQL> select * from major_tom5 where code3=4 and code2=42;
10051 rows selected.
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 272K| 7355 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 9982 | 272K| 7355 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 9982 | 272K| 7355 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM5 | 9982 | 272K| 7355 (7)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE2"=42 AND "CODE3"=4)
filter("CODE2"=42 AND "CODE3"=4)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
45888 consistent gets
68 physical reads
5256 redo size
149822 bytes sent via SQL*Net to client
610 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10051 rows processed
The CBO decides to NOT use the available index as it deems it too expensive, especially with such a poor Clustering Factor, to return the resultant 10,051 rows.
But what will Automatic Indexing do now. If we wait the 15 minute period until the next Automatic Indexing period and look at the resultant Automatic Indexing report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible --------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------- | BOWIE | MAJOR_TOM5 | SYS_AI_2ajmncxsmg189 | CODE2,CODE3 | B-TREE | NONE | --------------------------------------------------------------------------------- VERIFICATION DETAILS ------------------------------------------------------------------------------- The performance of the following statements improved: ------------------------------------------------------------------------------- Parsing Schema Name : BOWIE SQL ID : fmpwux2ptvasq SQL Text : select * from major_tom5 where code2=42 and code3=4 Improvement Factor : 5.1x
Automatic Indexing has created a new index based on the column list CODE2, CODE3, because it considers such an index would improve performance of the query by a factor of 5.1x.
However, it has not recognised that the existing manual index based the column list CODE3, CODE2 would have done precisely the same job.
If we look further on in the Automatic Indexing report:
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 993225 26436
CPU Time (s): 963727 22535
Buffer Gets: 137756 9000
Optimizer Cost: 7355 9069
Disk Reads: 0 26
Direct Writes: 0 0
Rows Processed: 30153 10051
Executions: 3 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 2129981950
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7355 | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 280000 | 7355 | 00:00:01|
| 3 | PX BLOCK ITERATOR | | 10000 | 280000 | 7355 | 00:00:01|
| 4 | TABLE ACCESS STORAGE FULL | MAJOR_TOM5 | 10000 | 280000 | 7355 | 00:00:01|
---------------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 459198994
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10159 | 284452 | 9069 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM5 | 10159 | 284452 | 9069 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_2ajmncxsmg189 | 10051 | | 27 | 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE2"=42 AND "CODE3"=4)
We notice the new execution plan using the newly created Automatic Index actually has a greater CBO cost than the previous FTS execution plan.
As we discussed in the previous post on when Automatic Indexing creating indexes that are not ultimately used by the CBO, although Automatic Indexing has indeed created this index because it has determined it’s going to be more efficient by a factor of 5.1x due to the reduction in Buffer Gets (137756 buffer gets old plan / 3 executions = 45,919 / 9000 buffer gets with index = 5.1), the CBO considers the execution plan using the Automatic Index to have a larger cost at 9069 than the previous FTS cost at just 7355.
Again just as with the existing, logically equivalent manually created index, the reason why the new Automatic Index is deemed too expensive by the CBO is because it likewise has the same terrible Clustering Factor:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM5'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------ --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM5_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 24181 8974538 SYS_AI_2ajmncxsmg189 YES NO VISIBLE DISABLED VALID 10000000 23697 8974538
If we re-run the initial query again with the newly created Visible/Valid Automatic Index:
SQL> select * from major_tom5 where code3=4 and code2=42;
10051 rows selected.
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 272K| 7355 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 9982 | 272K| 7355 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 9982 | 272K| 7355 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM5 | 9982 | 272K| 7355 (7)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE2"=42 AND "CODE3"=4)
filter("CODE2"=42 AND "CODE3"=4)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
45888 consistent gets
68 physical reads
5256 redo size
149822 bytes sent via SQL*Net to client
610 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10051 rows processed
The CBO ignores the newly created Automatic Index as it did the logically equivalent manually created index and uses the previous, cheaper FTS execution plan.
Automatic Indexing was NOT able to recognise that we already had an equivalent manually created index and so now we have TWO indexes that the CBO simply ignores as being too expensive…
More on mixing Automatic and Manual Indexes on my next post.
Oracle 19c Automatic Indexing: Adding Columns To Existing Automatic Indexes (2+2=5) April 7, 2020
Posted by Richard Foote in 19c, 19c New Features, Add Column To Existing Index, Automatic Indexing, Oracle Indexes.2 comments
In my previous post, I discussed how when the following query is run:
select * from major_tom3 where code3=4 and code2=42;
the Automatic Indexing process will create an index on (CODE2, CODE3) but ultimately not use the index as the CBO considers the corresponding index based execution plan too expensive.
I’m going to expand on the demo and run now the following SQL for the first time (note these examples are run on the OLTP Autonomous Cloud service which explains the odd default parallel based execution plans):
SQL> select * from major_tom3
where code1=42 and code3=4 and code2=42;
10 rows selected.
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 280 | 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 280 | 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10 | 280 | 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM3 | 10 | 280 | 7354 (7)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE1"=42 AND "CODE2"=42 AND "CODE3"=4)
filter("CODE1"=42 AND "CODE2"=42 AND "CODE3"=4)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
45853 consistent gets
0 physical reads
0 redo size
1032 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
This query has a predicate that includes both CODE2 and CODE3 filtering columns as previously, but now also a new filtering column on CODE1. This now makes the resultant SQL much more selective than the previous SQL, returning just 10 rows where the previous SQL returned 9968 rows.
The current explain plan still uses the previous Full Table Scan, as the only index available is the Automatic Index created previously based on CODE2, CODE3 which has already been shown to be too expensive to return the necessary rows. The additional filtering predicate based on CODE1 doesn’t make the index any more efficient, it still has to access the 9968 rows that match the CODE2, CODE3 predicates and then filter out most of the rows less the 10 that are actually required.
So what does the Automatic Index process do in this scenario?
Let’s look at the resultant Automatic Index report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------- | BOWIE | MAJOR_TOM3 | SYS_AI_cy8rs2dqb0nrp | CODE2,CODE3,CODE1 | B-TREE | NONE | --------------------------------------------------------------------------------------- The following indexes were dropped: ------------------------------------------------------------------------------- --------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties| --------------------------------------------------------------------------------- | BOWIE | MAJOR_TOM3 | SYS_AI_bnyacywycxx8b | CODE2,CODE3 | B-TREE | NONE | --------------------------------------------------------------------------------- -------------------------------------------------------------------------------
So the first thing to note is that Oracle first creates a new index based on columns CODE2,CODE3,CODE1.
It then drops the previously created index based on the CODE2,CODE3 columns.
If we look at the Verification Details section of the report:
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 1hv3d685x2cy4
SQL Text : select * from major_tom3 where code1=43 and code3=4 and code2=42
Improvement Factor : 45853.6x
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 97by2q15zprgc
SQL Text : select * from major_tom3 where code1=42 and code3=4 and code2=42
Improvement Factor : 45856.2x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 2815446 1046
CPU Time (s): 2741134 1013
Buffer Gets: 596135 13
Optimizer Cost: 7354 13
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 130 10
Executions: 13 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 2354969370
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7354 | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 280 | 7354 | 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10 | 280 | 7354 | 00:00:01 |
| 4 | TABLE ACCESS STORAGE FULL | MAJOR_TOM3 | 10 | 280 | 7354 | 00:00:01 |
--------------------------------------------------------------------------------
Notes
-----
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 2892362571
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 280 | 13 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM3 | 10 | 280 | 13 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_cy8rs2dqb0nrp | 10 | | 3 | 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE2"=42 AND "CODE3"=4 AND "CODE1"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
So what’s going on here?
Oracle has correctly determined that an index based on all 3 columns would enable a much more efficient access path for the new SQL, by a factor of 45856.2x no less.
In order to service BOTH known queries that the Automatic Index process has determined would benefit from an index based on predicates (CODE2=42 and CODE3=4) and (CODE2=42 and CODE3=4 and CODE1=42), a single index based on CODE2,CODE3,CODE1 would suffice.
As such, the existing index based on just CODE2,CODE3 is redundant as the new index has the same leading columns. Therefore, the existing index can be safely dropped.
If we look at the definition of the indexes on this table:
SQL> select index_name, auto, constraint_index, visibility, status compression from user_indexes where table_name='MAJOR_TOM3'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS -------------------- --- --- --------- ------------- -------- SYS_AI_cy8rs2dqb0nrp YES NO VISIBLE DISABLED VALID SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM3' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- -------------------- --------------- SYS_AI_cy8rs2dqb0nrp CODE2 1 SYS_AI_cy8rs2dqb0nrp CODE3 2 SYS_AI_cy8rs2dqb0nrp CODE1 3
We notice we now have just the new index, which is both VISIBLE and VALID, based on columns in CODE2,CODE3,CODE1 order.
So Automatic Indexing is clever enough to recognise the scenario where a new index can replace an existing index by adding additional columns to cater for new SQL workloads.
Now that’s rather impressive…
Much more on Automatic Indexing to come.
Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young) March 30, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Oracle Indexes.3 comments
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.2 comments
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…
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.3 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
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 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.4 comments
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…
