Automatic Indexing: Potential Locking Issues Part II (“Don’t Stop”) December 5, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes.add a comment
In my previous post, I highlighted how a long transaction can potentially cause the creation of an Automatic Index to hang due to the inability of the Automatic Indexing process to obtain the necessary locks.
However, these locks can have a much wider consequence, as it’s the entire Automatic Indexing process that is forced to hang, not just the creation of a specific index. This is due to the fact that Automatic Indexing works in a serial fashion, working on one index at a time, in order to put the brakes on the amount of resources that Automatic Indexing can potentially consume.
Therefore, it’s not just the creation of the specifically locked automatic index that is impacted, but the subsequent creation of all Automatic Indexes. No other Automatic Index can be created until the locking issue is resolved.
To highlight, I’m going to create and populate other table:
SQL> create table david_bowie (id number, code number, name varchar2(42)); Table created. SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, '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=>'DAVID_BOWIE'); PL/SQL procedure successfully completed.
I’ll next run an SQL several times that is forced to perform a Full Table Scan because of a missing index:
SQL> select * from david_bowie where code=42; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1390211489 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 | | * 1 | TABLE ACCESS FULL | DAVID_BOWIE | 10 | 230 | 6714 (2)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("CODE"=42) filter("CODE"=42) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 48130 consistent gets 38657 physical reads 0 redo size 885 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed However, if we look at the current Automatic Indexing report: SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 01-DEC-2022 07:12:31 Activity end : 05-DEC-2022 12:15:42 Executions completed : 0 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- ERRORS -------------------------------------------------------------------------------- ------------- No errors found. -------------------------------------------------------------------------------- -------------
We can see that the Automatic Indexing process is STILL hanging days later from the still uncommitted transaction. Therefore, it’s impossible for an Automatic Index to be created for this new workload, or indeed ANY new workload, until the locking issue is resolved, with the completion of the associated locking transaction.
We can easily see the troublesome lock:
SQL> select * from dba_waiters;
WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- -------------- --------------- -------------- ----------- --------- -------------- ---------- ----------
164 3 167 3 Transaction Exclusive Share 327694 10623
As a consequence, no new Automatic Index can be created for this new workload:
SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks from user_indexes where table_name='DAVID_BOWIE'; no rows selected
And the existing workload remains inefficient:
SQL> select * from david_bowie where code=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | DAVID_BOWIE | 10 | 230 | 6714 (2)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48130 consistent gets
38657 physical reads
0 redo size
885 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
Once the locking transaction is finally completed:
SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust'); 1 row created. SQL> commit; Commit complete.
The Automatic Indexing process can again resume and the new Automatic Indexes can finally be created as necessary:
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 05-DEC-2022 12:30:30 Activity end : 05-DEC-2022 12:31:22 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 2 (0 / 2) Space used (visible / invisible) : 287.31 MB (0 B / 287.31 MB) Indexes dropped : 0 SQL statements verified : 3 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- 1. The following indexes were created: ------------------------------------------------------------------------------- --------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------- | BOWIE | BOWIE_BUSY | SYS_AI_8pkdh6q096qvs | CODE | B-TREE | NONE | | BOWIE | DAVID_BOWIE | SYS_AI_czmkjhqr21732 | CODE | B-TREE | NONE | --------------------------------------------------------------------------- ------------------------------------------------------------------------------- ERRORS -------------------------------------------------------------------------------- ------------- No errors found. -------------------------------------------------------------------------------- -------------
If you find that the Automatic Indexing process has hung, check to make sure there are no long locks on associated underlying tables that could be causing the whole Automatic Index process to freeze…
NOTE: This post is dedicated to the memory of Christine McVie, who recently passed away…
Automatic Indexing: Potential Locking Issues Part I (“Rattle That Lock”) December 1, 2022
Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Database, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.1 comment so far
I’ve discussed previously locking issues associated with the creation of indexes. Although things have changed and improved over the years, even with the ONLINE option currently, an index creation process still requires (albeit brief and non-escalating) locks on the underlining table.
Basically, there needs to be a brief period where there isn’t an active transaction on the underlining table for the index creation process to complete, else it will forced to wait and hang. Oracle requires a table lock on the underlining table at the start of the CREATE or REBUILD process (to guarantee data dictionary information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).
So how do these index lock requirements potentially impact the Automatic Indexing process?
To investigate, I’ll create and populate a basic table with a highly selective CODE column:
SQL> create table bowie_busy (id number constraint bowie_busy_pk primary key, code number, name varchar2(42)); Table created. SQL> insert into bowie_busy select rownum, mod(rownum, 1000000)+1, '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_BUSY'); PL/SQL procedure successfully completed.
In a second session, I’ll insert a new row but NOT commit the change, thereby creating a extended transaction:
SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust'); 1 row created.
Back in the original session, I’ll run the following SQL numerous times:
SQL> select * from bowie_busy where code=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | BOWIE_BUSY | 10 | 230 | 6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
71423 consistent gets
38657 physical reads
0 redo size
885 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
Without an associated index in place, the CBO currently has no choice but to perform a Full Table Scan. But with the SQL only returning 10 rows from the 10M table, clearly an index would be beneficial.
But how does the existing transaction and associated locks on table impact the Automatic Indexing process?
There’s nothing magical here. With the current transaction in place on the underlying table, the index creation process simply can’t be completed. If we look at the status of the Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks from user_indexes where table_ name='BOWIE_BUSY'; INDEX_NAME AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS ------------------------------ --- --- --------- -------- ---------- ----------- BOWIE_BUSY_PK NO YES VISIBLE VALID 10000000 19856 SYS_AI_8pkdh6q096qvs YES NO INVISIBLE UNUSABLE 10000000 23058
It remains in its initial INVISIBLE/USABLE state.
If we look at the Automatic Indexing monitoring report, some 6 HOURS after the initial running of the Automatic Index process for this index:
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 01-DEC-2022 07:12:31 Activity end : 01-DEC-2022 13:05:53 Executions completed : 0 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- ERRORS -------------------------------------------------------------------------------- ------------- No errors found. -------------------------------------------------------------------------------- -------------
We notice that the whole Automatic Indexing process has been locked out and left in a hanging state (the times between the activity start/end times just keep climbing, with 0 executions of the Automatic Indexing process completed).
Without a VISIBLE/USABLE automatic index in place, if we re-run the SQL again:
SQL> select * from bowie_busy where code=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | BOWIE_BUSY | 10 | 230 | 6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
71423 consistent gets
38657 physical reads
0 redo size
885 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
The CBO has again no choice but to still perform the highly inefficient Full Table Scan.
And the required Automatic Index won’t be able to be created until the existing transaction on the underlying table has completed.
HOWEVER, as we’ll see in Part II, the possible ramifications of this locking transaction goes way past the impact it has on just this SQL or specific automatic index…
Automatic Indexing 21c: Non-Equality Predicate Anomaly (“Strangers When We Meet”) July 14, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Exadata X8, Full Table Scans, Index Column Order, Invisible Indexes, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning, Richard Foote Training, Richard's Blog.3 comments
I’m currently putting together some Exadata related training for a couple of customers and came across a rather strange anomaly with regard the status of Automatic Indexes, when created in part on unselective, non-equality predicates.
As discussed previously, Oracle Database 21c now allows the creation of Automatic Indexes based on non-equality predicates (previously, Automatic Indexes were only created on equality-based predicates).
But one appears to get rather odd resultant Automatic Indexes in the scenario where the non-equality predicate is not particularly selective but other predicates are highly selective.
To illustrate, I’ll create a basic table that has two columns (ID and CODE) that are both highly selective:
SQL> create table ziggy_new (id number, code number, name varchar2(42)); Table created. SQL> insert into ziggy_new select rownum, mod(rownum, 1000000)+1, '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=>'ZIGGY_NEW'); PL/SQL procedure successfully completed.
So there are currently no indexes on this table.
I’ll next run the following SQL (and others similar) a number of times:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Without any indexes, the CBO currently has no choice but to use a Full Table Scan.
But only 1 row is returned. The first equality predicate on the CODE column is highly selective and on its own would only return 10 rows out of the 10M row table. The second, non-equality range-based predicate on the ID column is nowhere near as selective and offers limited additional filtering.
The CBO stops calculating index related costs after a non-equality predicate column (as subsequent column values could exist anywhere within the preceding range), and so the more effective index here is one based on (CODE, ID) with the non-equality predicate column second, or potentially just on the CODE column only, as the ID range offers minimal filtering benefits.
So what does Automatic Indexing make of things?
If we look at the subsequent Automatic Indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 209.72 MB (0 B / 209.72 MB) Indexes dropped : 0 SQL statements verified : 44 SQL statements improved (improvement factor) : 12 (64.7x) SQL plan baselines created : 0 Overall improvement factor : 1.6x ------------------------------------------------------------------------------- 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 | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE | ----------------------------------------------------------------------------
So Automatic Indexing has indeed created an index based on CODE,ID (a common Automatic Indexing trait appears to be to create an index based on all available predicates).
BUT the index is created as an INVISIBLE Index and so can not generally be used by database sessions.
SQL> select index_name, auto, visibility, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY_NEW';
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --------- -------- ---------- ----------- -----------------
SYS_AI_75j16xff1ag3j YES INVISIBLE VALID 10000000 25123 10000000
SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='ZIGGY_NEW';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------ ---------------
SYS_AI_75j16xff1ag3j CODE 1
SYS_AI_75j16xff1ag3j ID 2
So re-running the previous SQL statements continues to use a Full Table Scan:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Automatic Indexing appears to only create Invisible indexes when there is an inefficient non-equality predicate present. It won’t create the index as a Visible index, even though it would significantly benefit these SQL statements that caused its creation. And Automatic Indexing won’t create an index on just the highly selective CODE equality predicate, which would also be of much benefit to these SQL statements.
If we now run similar queries, but with much more selective non-equality predicates, such as:
SQL> select * from ziggy_new where code=1 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=1 AND "ID"<=10 AND "ID">=1)
filter("CODE"=1 AND "ID"<=10 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38604 consistent gets
38600 physical reads
0 redo size
503 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Again, with no (Visible) index present, the CBO currently has no choice but to use the Full Table Scan.
But during the next cycle, after Automatic Indexing kicks in again:
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 5
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 209.72 MB (209.72 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 89
SQL statements improved (improvement factor) : 31 (71.9x)
SQL plan baselines created : 0
Overall improvement factor : 1.7x
-------------------------------------------------------------------------------
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 | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : d4znwcu4h52ca
SQL Text : select * from ziggy_new where code=42 and id between 1 and 10
Improvement Factor : 38604x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 3398605 68
CPU Time (s): 3166824 68
Buffer Gets: 463250 3
Optimizer Cost: 6738 4
Disk Reads: 463200 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 12 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3165184525
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6738 | |
| 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 | 00:00:01 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 1514586396
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
But this time, the index on the CODE,ID columns is created as a Visible index.
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --------- -------- ---------- ----------- ----------------- SYS_AI_75j16xff1ag3j YES VISIBLE VALID 10000000 25123 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='ZIGGY_NEW'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------ --------------- SYS_AI_75j16xff1ag3j CODE 1 SYS_AI_75j16xff1ag3j ID 2
So this index can be generally used, both by the newer SQLs that generated the now Visible index:
SQL> select * from ziggy_new where code=42 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
And also used by the SQLs with the unselective non-equality predicates, that Automatic Indexing would only create as Invisible indexes:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=100000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
729 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Automatic Indexing appears to currently not quite do the right thing with SQL statements that have unselective non-equality predicates, by creating such indexes as only Invisible Indexes, inclusive of the unselective columns.
Although an edge case, I would recommend looking through the list of created Automatic Indexes to see if any such Invisible/Valid indexes exists, as it can suggest there are current inefficient SQL statements that could benefit from such indexes being Visible.
Oracle 19c Automatic Indexing: Invisible/Valid Automatic Indexes (Bowie Rare) August 31, 2021
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Index Access Path, Index statistics, Invisible Indexes, Invisible/Valid Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Statistics, Oracle19c, Unusable Indexes.1 comment so far
In my previous post, I discussed how newly created Automatic Indexes can have one of three statuses, depending the selectivity and effectiveness of the associated Automatic Index.
Indexes that improve performance sufficiently are created as Visible/Valid indexes and can be subsequently considered by the CBO. Indexes that are woeful and have no chance of improving performance are created as Invisible/Unusable indexes. Indexes considered potentially suitable but ultimately don’t sufficiently improve performance, are created as Invisible/Valid indexes.
Automatic Indexes are created as Visible/Valid indexes when shown to improve performance (by the _AUTO_INDEX_IMPROVEMENT_THRESHOLD parameter). But as I rarely came across Invisible/Valid Automatic Indexes (except for when Automatic Indexing is set to “Report Only” mode), I was curious to determine approximately at what point were such indexes created by the Automatic Indexing process.
To investigate things, I created a table with columns that contain data with various levels of selectivity, some of which should fall inside and outside the range of viability of any associated index, based on the cost of the associated Full Table Scan.
The following table has 32 columns of interest, each with a slight variation of distinct values giving small differences in overall column selectivity:
SQL> create table bowie_stuff1 (id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, code21 number, code22 number, code23 number, code24 number, code25 number, code26 number, code27 number, code28 number, code29 number, code30 number, code31 number, code32 number, name varchar2(42));
Table created.
SQL> insert into bowie_stuff1
select rownum,
mod(rownum, 900)+1,
mod(rownum, 1000)+1,
mod(rownum, 1100)+1,
mod(rownum, 1200)+1,
mod(rownum, 1300)+1,
mod(rownum, 1400)+1,
mod(rownum, 1500)+1,
mod(rownum, 1600)+1,
mod(rownum, 1700)+1,
mod(rownum, 1800)+1,
mod(rownum, 1900)+1,
mod(rownum, 2000)+1,
mod(rownum, 2100)+1,
mod(rownum, 2200)+1,
mod(rownum, 2300)+1,
mod(rownum, 2400)+1,
mod(rownum, 2500)+1,
mod(rownum, 2600)+1,
mod(rownum, 2700)+1,
mod(rownum, 2800)+1,
mod(rownum, 2900)+1,
mod(rownum, 3000)+1,
mod(rownum, 3100)+1,
mod(rownum, 3200)+1,
mod(rownum, 3300)+1,
mod(rownum, 3400)+1,
mod(rownum, 3500)+1,
mod(rownum, 3600)+1,
mod(rownum, 3700)+1,
mod(rownum, 3800)+1,
mod(rownum, 3900)+1,
mod(rownum, 4000)+1,
'THE RISE AND FALL OF ZIGGY STARDUST'
from dual connect by level >=10000000;
10000000 rows created.
SQL> commit;
Commit complete.
As always, it’s important that statistics be collected for Automatic Indexing to function properly:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_STUFF1', estimate_percent=>null); PL/SQL procedure successfully completed.
So on a 10M row table, I have 32 columns with the number of distinct values varying by only 100 values per column (or by a selectivity of just 0.001%):
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='BOWIE_STUFF1' order by num_distinct; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM ------------ ------------ ---------- --------------- NAME 1 .00000005 FREQUENCY CODE1 900 .001111 HYBRID CODE2 1000 .001 HYBRID CODE3 1100 .000909 HYBRID CODE4 1200 .000833 HYBRID CODE5 1300 .000769 HYBRID CODE6 1400 .000714 HYBRID CODE7 1500 .000667 HYBRID CODE8 1600 .000625 HYBRID CODE9 1700 .000588 HYBRID CODE10 1800 .000556 HYBRID CODE11 1900 .000526 HYBRID CODE12 2000 .0005 HYBRID CODE13 2100 .000476 HYBRID CODE14 2200 .000455 HYBRID CODE15 2300 .000435 HYBRID CODE16 2400 .000417 HYBRID CODE17 2500 .0004 HYBRID CODE18 2600 .000385 HYBRID CODE19 2700 .00037 HYBRID CODE20 2800 .000357 HYBRID CODE21 2900 .000345 HYBRID CODE22 3000 .000333 HYBRID CODE23 3100 .000323 HYBRID CODE24 3200 .000312 HYBRID CODE25 3300 .000303 HYBRID CODE26 3400 .000294 HYBRID CODE27 3500 .000286 HYBRID CODE28 3600 .000278 HYBRID CODE29 3700 .00027 HYBRID CODE30 3800 .000263 HYBRID CODE31 3900 .000256 HYBRID CODE32 4000 .00025 HYBRID ID 10000000 0 HYBRID
I’ll next run the below queries (based on a simple equality predicate on each column) several times each in batches of 8 queries, so as to not swamp the Automatic Indexing process with potential new index requests (the ramifications of which I’ll discuss in another future post):
SQL> select * from bowie_stuff1 where code1=42; SQL> select * from bowie_stuff1 where code2=42; SQL> select * from bowie_stuff1 where code3=42; SQL> select * from bowie_stuff1 where code4=42; SQL> select * from bowie_stuff1 where code5=42; ... SQL> select * from bowie_stuff1 where code31=42; SQL> select * from bowie_stuff1 where code32=42;
If we now look at the statuses of the Automatic Indexes subsequently created:
SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='BOWIE_STUFF1' order by visibility, status; INDEX_NAME COLUMN_NAME AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- ------------ --- --- --------- -------- ---------- ----------- ----------------- SYS_AI_5rw9j3d8pc422 CODE5 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_48q3j752csn1p CODE4 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_9sgharttf3yr7 CODE3 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_8n92acdfbuh65 CODE2 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_brgtfgngu3cj9 CODE1 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_1tu5u4012mkzu CODE11 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_34b6zwgtm86rr CODE12 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_gd0ccvdwwb4mk CODE13 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_7k7wh28n3nczy CODE14 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_67k2zjp09w101 CODE15 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_5fa6k6fm0k6wg CODE10 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_4624ju6bxsv57 CODE9 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_bstrdkkxqtj4f CODE8 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_39xqjjar239zq CODE7 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_6h0adp60faytk CODE6 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_5u0bqdgcx52vh CODE16 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_0hzmhsraqkcgr CODE22 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_4x716k4mdn040 CODE21 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_6wsuwr7p6drsu CODE20 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_b424tdjx82rwy CODE19 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_3a2y07fqkzv8x CODE18 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_8dp0b3z0vxzyg CODE17 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_d95hnqayd7t08 CODE23 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_fry4zrxqtpyzg CODE24 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_920asb69q1r0m CODE25 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_026pa8880hnm2 CODE31 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_96xhzrguz2qpy CODE32 YES NO VISIBLE VALID 10000000 15368 10000000 SYS_AI_3dq93cc7uxruu CODE29 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_5nbz41xny8fvc CODE28 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_fz4q9bhydu2qt CODE27 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_0kwczzg3k3pfw CODE26 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_4qd5tsab7fnwx CODE30 YES NO VISIBLE VALID 10000000 15367 10000000
We can see we indeed have the 3 statuses of Automatic Indexes captured:
Columns with a selectivity equal or worse to that of COL5 with 1300 distinct values are created as Invisible/Unusable indexes. Returning 10M/1300 rows or a cardinality of approx. 7,693 or more rows is just too expensive for such indexes on this table to be viable. This represents a selectivity of approx. 0.077%.
Note how the index statistics for these Invisible/Unusable indexes are not accurate. They all have an estimated LEAF_BLOCKS of 21702 and a CLUSTERING_FACTOR of 4272987. However, we can see from the other indexes which are physically created that these are not correct and are substantially off the mark with the actual LEAF_BLOCKS being around 15364 and the CLUSTERING_FACTOR actually much worse at around 10000000.
Again worthy of a future post to discuss how Automatic Indexing processing has to make (potentially inaccurate) guesstimates for these statistics in its analysis of index viability when such indexes don’t yet physically exist.
Columns with a selectivity equal or better to that of COL23 which has 3100 distinct values are created as Visible/Valid indexes. Returning 10M/3100 rows or a cardinality of approx. 3226 or less rows is cheap enough for such indexes on this table to be viable. This represents a selectivity of approx. 0.032%.
So in this specific example, only those columns between 1400 and 3000 distinct values meet the “borderline” criteria in which the Automatic Indexing process creates Invisible/Valid indexes. This represents a very very narrow selectivity range of only approx. 0.045% in which such Invisible/Valid indexes are created. Or for this specific example, only those columns that return approx. between 3,333 and 7,143 rows from the 10M row table.
Now the actual numbers and total range of selectivities for which Invisible/Valid Automatic Indexes are created of course depends on all sorts of factors, such as the size/cost of FTS of the table and not least the clustering of the associated data (which I’ve blogged about ad nauseam).
The point I want to make is that the range of viability for such Invisible/Valid indexes is relatively narrow and the occurrences of such indexes relatively rare in your databases. As such, the vast majority of Automatic Indexes are likely to be either Visible/Valid or Invisible/Unusable indexes.
It’s important to recognised this when you encounter such Invisible/Valid Automatic Indexes (outside of “REPORT ONLY” implementations), as it’s an indication that such an index is a borderline case that is currently NOT considered by the CBO (because of it being Invisible).
However, this Invisible/Valid Automatic Index status should really change to either of the other two more common statuses in the near future.
I’ll expand on this point in a future post…
Oracle 19c Automatic Indexing: The 3 Possible States Of Newly Created Automatic Indexes (“Don’t Sit Down”) August 24, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Clustering Factor, Exadata, Invisible Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Indexes, Oracle Statistics.2 comments
As I discussed way back in February 2021 (doesn’t time fly!!), I discussed some oddity cases in which Automatic Indexes were being created in an Invisible/Valid state. At the time, I described it as unexpected behaviour as this wasn’t documented and seemed an odd outcome, one which I had only expected to find when Automatic Indexing was set in “REPORT ONLY” mode.
After further research and discussions with folks within Oracle, Automatic Indexes created in this state is indeed entirely expected, albeit in relatively rare scenarios. So I thought I’ll discuss the 3 possible states in which an Automatic Index can be created and explore things further in future blog posts.
The follow demo illustrates the 3 different states in which Automatic Indexes can be created.
I start by creating a table with 3 columns of note:
- CODE1 which is highly selective and very likely to be used by the CBO if indexed
- CODE2 which is relatively selective BUT likely NOT quite enough so to be used by the CBO if indexed
- CODE3 which is very unselective and almost certainly won’t be used by the CBO if indexed
SQL> create table david_bowie (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, mod(rownum, 5000)+1, mod(rownum, 100)+1, 'THE RISE AND FALL OF ZIGGY STARDUST' from dual connect by level >=10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID_BOWIE'); PL/SQL procedure successfully completed.
Note that in an Autonomous Database, these columns will all now have histograms (as previously discussed):
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='DAVID_BOWIE'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- ID 9705425 0 HYBRID CODE1 971092 .000001 HYBRID CODE2 4835 .000052 HYBRID CODE3 100 .00000005 FREQUENCY NAME 1 4.9460E-08 FREQUENCY
I’ll now run the following simple queries a number of times, using predicates on each of the 3 columns:
SQL> select * from david_bowie where code1=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 1076 (9) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 10 | 540 | 1076 (9) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE1"=42)
filter("CODE1"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
783 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from david_bowie where code2=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 109K | 1083 (10) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 | 109K | 1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE2"=42)
filter("CODE2"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
32433 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
SQL> select * from david_bowie where code3=42;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 5273K | 1090 (10) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 100K | 5273K | 1090 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE3"=42)
filter("CODE3"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
1984026 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
Obviously with no indexes in place, they all currently use a FTS.
If we wait though until the next Automatic Indexing reporting period and look at the next Automatic Indexing report:
SQL> select dbms_auto_index.report_last_activity() from dual; SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 2 (1 / 1) Space used (visible / invisible) : 276.82 MB (142.61 MB / 134.22 MB) Indexes dropped : 0 SQL statements verified : 2 SQL statements improved (improvement factor) : 1 (83301.1x) SQL plan baselines created : 0 Overall improvement factor : 2x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 -------------------------------------------------------------------------------
We notice Automatic Indexing stated there were 3 index candidates, but has created 2 new indexes, one VISIBLE and one INVISIBLE.
Further down the report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | DAVID_BOWIE | SYS_AI_48d67aycauayj | CODE1 | B-TREE | NONE | | BOWIE | DAVID_BOWIE | SYS_AI_cpw2p477wk6us | CODE2 | B-TREE | NONE | ---------------------------------------------------------------------------- -------------------------------------------------------------------------------
We see that one index was created on the CODE1 column and the other on the CODE2 column (note: in the current 19.12.0.1.0 version of the Transaction Processing Autonomous Database, the * to denote invisible indexes above is no longer present).
No index is listed as being created on the very unselective CODE3 column.
If we continue down the report:
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 6vp85adas9tq3
SQL Text : select * from david_bowie where code1=42
Improvement Factor : 83301.1x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 246874 1248
CPU Time (s): 139026 694
Buffer Gets: 749710 13
Optimizer Cost: 1076 13
Disk Reads: 749568 2
Direct Writes: 0 0
Rows Processed: 90 10
Executions: 9 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 1390211489
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1076 | |
| 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 10 | 540 | 1076 | 00:00:01 |
-----------------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 3510800558
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 13 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE | 10 | 540 | 13 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_48d67aycauayj | 10 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE1"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We see that the Visible Index was actually created on the CODE1 column, thanks to the perceived 83301.1x performance improvement.
If we look at the status of all indexes now on our table:
SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.compression, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='DAVID_BOWIE'; INDEX_NAME COLUMN_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- ----------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_48d67aycauayj CODE1 YES NO VISIBLE ADVANCED LOW VALID 10000000 16891 10000000 SYS_AI_cpw2p477wk6us CODE2 YES NO INVISIBLE ADVANCED LOW VALID 10000000 15369 10000000 SYS_AI_c8bkc2z4bxrzp CODE3 YES NO INVISIBLE ADVANCED LOW UNUSABLE 10000000 20346 4173285
We see indexes with 3 different statuses:
- CODE1 index is VISIBLE/VALID
- CODE2 index is INVISIBLE/VALID
- CODE3 index is INVISIBLE/UNUSABLE
The logic appears to be as follows:
If an index will demonstrably improve performance sufficiently, then the index is created as a VISIBLE and VALID index and can be subsequently used by the CBO.
If an index is demonstrably awful and has very little chance of ever being used by the CBO, it’s left INVISIBLE and put in an UNUSABLE state. It therefore takes up no space and will eventually be dropped. It will likely never be required, so no loss then if it doesn’t physically exist.
Interestingly, if an index is somewhat “borderline”, currently not efficient enough to be used by the CBO, but close enough perhaps that maybe things might change in the future to warrant such as index, then it is physically created as VALID but is not readily available to the CBO and remains in an INVISIBLE state. This index won’t have to be rebuilt in the future if indeed things change subsequently to enough to warrant future index usage.
It should of be noted that little of this is clearly documented and that it’s subject to change without notice. One of the key points of Automatic Indexing is that we can off-hand all this to Oracle and let Oracle worry about things. That said, it might be useful to understand why you might end up with indexes in different statuses and the subsequent impact this might make.
If we re-run the first query based on the CODE1 predicate:
SQL> select * from david_bowie where code1=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3510800558
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 14 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE | 10 | 540 | 14 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_48d67aycauayj | 10 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE1"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
The CBO will indeed use the newly created Automatic Index.
But if we re-run either of the other 2 queries based on the CODE2 and CODE3 predicates:
SQL> select * from david_bowie where code2=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 109K | 1083 (10) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 | 109K | 1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE2"=42)
filter("CODE2"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
32433 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
The CBO will not use an index as no VISIBLE/VALID indexes exist on these columns.
In future blog posts I’ll explore what is meant by “borderline” and what can subsequently happen to any such INVISIBLE/VALID Automatic Indexes…
Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud) February 3, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Automatic Table Statistics, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Histograms, Invisible Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Statistics, Oracle19c.2 comments
There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article.
The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has the key column CODE with a selectivity that would likely make it too expensive to be accessed via an associated index.
SQL> create table pink_floyd (id number, code number, create_date date, name varchar2(42)); Table created. SQL> insert into pink_floyd select rownum, ceil(dbms_random.value(0, 5000)), sysdate-mod(rownum, 50000)+1, 'Dark Side of the Moon' from dual connect by level <=10000000; 10000000 rows created. SQL> commit; Commit complete.
Importantly, I’ll next collect statistics on this table using all the default attributes, including allowing Oracle to decide the merits of any column histogram:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'PINK_FLOYD'); PL/SQL procedure successfully completed.
Note I’ve yet to run a single query against this table. And yet, if we look at the details of each of these columns:
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='PINK_FLOYD'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- ID 9705425 0 HYBRID CODE 4835 .00005 HYBRID CREATE_DATE 50357 .00002 HYBRID NAME 1 4.9639E-08 FREQUENCY
All the columns have a histogram !! This despite the columns not meeting either criteria normally required for a histogram, that the column be used in a SQL predicate AND for the column to have an uneven distribution of values.
None of these columns have yet to be used in a filtering predicate and none of these columns have a uneven distribution of values, even the CODE column as highlighted by looking at the minimum and maximum number of occurrences:
SQL> select min(code_count), max(code_count) from (select count(*) code_count from pink_floyd group by code);
MIN(CODE_COUNT) MAX(CODE_COUNT)
--------------- ---------------
1845 2163
So it’s very odd for these histograms to be present.
If we run the following query with a filtering predicate based on the CODE column:
SQL> select * from pink_floyd where code=42;
2012 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1152280033
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
63655 consistent gets
63645 physical reads
0 redo size
38575 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2012 rows processed
The CBO currently has no choice but to use a FTS with no index currently present. But what will Automatic Indexing make of things? If we look at the next automatic indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 2 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 134.22 MB (0 B / 134.22 MB) Indexes dropped : 0 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 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | PINK_FLOYD | * SYS_AI_dp2t0j12zux49 | CODE | B-TREE | NONE | ---------------------------------------------------------------------------- -------------------------------------------------------------------------------
We notice that Oracle has created an Automatic Index, but it’s an INVISIBLE index !!
If we look at the details of this Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='PINK_FLOYD'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dp2t0j12zux49 YES NO INVISIBLE ADVANCED LOW VALID 10000000 15369 9845256
The index is in an INVISIBLE/VALID state, not the usual INVISIBLE/UNUSABLE state for an index for which Automatic Indexing decides an index is not efficient enough to be implement.
This is NOT expected behaviour.
Usually INVISIBLE/VALID indexes are created when Automatic Indexing is in “REPORT ONLY” mode, although I have come across this scenario when statistics are stale or missing. But in this case, Automatic Indexing is in “IMPLEMENT” mode and the table has recently collected statistics, albeit with odd histograms present (hence why I think these issues to be related).
If we run the same query again:
SQL> select * from pink_floyd where code=42;
2012 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1152280033
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
63655 consistent gets
63645 physical reads
0 redo size
38575 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2012 rows processed
The CBO has again no option but to use the FTS as Invisible indexes can not be considered by the CBO. However, it’s important to note that such an index would not be used by the CBO anyways as it would be deemed too expensive to use than the current FTS.
If you’re relying on Automatic Indexing and have it in Implement mode, I would recommend checking for any indexes in this INVISIBLE/VALID state as they’re an indication that something has very likely gone wrong…
Oracle 19c Automatic Indexing: DDL Statements With Auto Indexes (No Control) September 1, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Drop Automatic Indexing, Drop Index, Index Coalesce, Index Rebuild, Index Shrink, Invisible Indexes, Online DDL, Oracle Indexes.2 comments
I’ve had a number of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic Indexes.
Many DDL commands are NOT supported with Automatic Indexes, such as making indexes (IN)VISIBLE and (UN)USABLE and changing storage attributes:
SQL> alter index "SYS_AI_600vgjmtqsgv3" invisible; alter index "SYS_AI_600vgjmtqsgv3" invisible * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes SQL> alter index "SYS_AI_600vgjmtqsgv3" unusable; alter index "SYS_AI_600vgjmtqsgv3" unusable * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes SQL> ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5; ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5 * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
You also can’t drop indexes with the DDL statement:
SQL> drop index "SYS_AI_600vgjmtqsgv3"; drop index "SYS_AI_600vgjmtqsgv3" * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
Although as discussed in my last post, you can now drop Automatic Indexes by using DBMS_AUTO_INDEX.DROP_AUTO_INDEXES.
You can however potentially improve the structure of an Automatic Index by using the REBUILD, COALESCE or SHRINK (SPACE) options:
SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online; Index altered. SQL> alter index "SYS_AI_600vgjmtqsgv3" coalesce; Index altered. SQL> alter index "SYS_AI_600vgjmtqsgv3" shrink space; Index altered.
Interestingly, if Oracle considers an Automatic Index but decides it’s not efficient enough to be created, the Automatic Indexing process can leave a new Automatic Index in UNUSABLE / INVISIBLE state (as previously discussed), which can be subsequently rebuilt:
SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3'; INDEX_NAME STATUS VISIBILIT ------------------------------ -------- --------- SYS_AI_600vgjmtqsgv3 UNUSABLE INVISIBLE SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online; Index altered. SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3'; INDEX_NAME STATUS VISIBILIT ------------------------------ -------- --------- SYS_AI_600vgjmtqsgv3 VALID INVISIBLE
So the index is now VALID and actually physically created. But you can’t subsequently make it VISIBLE, which means it can’t ordinarily be used by the CBO:
SQL> alter index "SYS_AI_600vgjmtqsgv3" visible; alter index "SYS_AI_600vgjmtqsgv3" visible * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
When you rebuild an Automatic Index, you can however change the manner in which it’s compressed:
SQL> select index_name, status, visibility, compression from user_indexes where index_name='SYS_AI_600vgjmtqsgv3'; INDEX_NAME STATUS VISIBILIT COMPRESSION ------------------------------ -------- --------- ------------- SYS_AI_600vgjmtqsgv3 VALID INVISIBLE ADVANCED LOW SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild nocompress; Index altered. SQL> select index_name, status, visibility, compression from user_indexes where index_name='SYS_AI_600vgjmtqsgv3'; INDEX_NAME STATUS VISIBILIT COMPRESSION ------------------------------ -------- --------- ------------- SYS_AI_600vgjmtqsgv3 VALID INVISIBLE DISABLED
And no, you can’t rename an Automatic Index:
SQL> alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX; alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes
So the answer is it depends on what one can and can’t do currently with an Automatic Index, which of course is subject to change in the future…
Indexing Foreign Key Constraints With Invisible Indexes (Invisible People) April 22, 2014
Posted by Richard Foote in 12c, Block Dumps, Foreign Keys, Invisible Indexes, Oracle Indexes.1 comment so far
In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks.
Last year, I discussed how it was possible in Oracle Database 12c to have multiple indexes on the same column list.
Quite some time ago, I discussed how so-called Invisible Indexes can indeed still be visible in various scenarios, including when policing FK constraints.
Well, lets put all these three topics together 🙂
First, let use the same basic setup as the last post:
SQL> create table bowie_dad (id number, dad_name varchar2(30)); Table created. SQL> insert into bowie_dad values (1, 'DAVID BOWIE'); 1 row created. SQL> insert into bowie_dad values (2, 'ZIGGY STARDUST'); 1 row created. SQL> insert into bowie_dad values (3, 'MAJOR TOM'); 1 row created. SQL> insert into bowie_dad values (4, 'THIN WHITE DUKE'); 1 row created. SQL> commit; Commit complete. SQL> create table bowie_kid (id number, kid_name varchar2(30), dad_id number); Table created. SQL> insert into bowie_kid select rownum, 'ALADDIN SANE', mod(rownum,3)+2 from dual connect by level <=1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> alter table bowie_dad add primary key(id); Table altered. SQL> alter table bowie_kid add constraint bowie_kid_fk foreign key(dad_id) references bowie_dad(id); Table altered.
We’re now going to create two indexes concurrently on the FK constraint on the DAD_ID column, a Bitmap Index and an invisible B-Tree Index as is now possible since Oracle Database 12c:
SQL> create bitmap index bowie_kid_fk_i on bowie_kid(dad_id); Index created. SQL> create index bowie_kid_fk2_i on bowie_kid(dad_id) invisible; Index created.
Oracle Database 12c allows us to now create multiple indexes on the same column list, providing only one index is visible at a time.
Let’s look at a partial block dump of the first leaf block of each index. First the Bitmap Index:
Block header dump: 0x0180805c
Object id on Block? Y
seg/obj: 0x16f45 csc: 0x00.36bc54 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1808058 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0036bc54
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[4499] flag: ——-, lock: 0, len=3537
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 00
col 2; len 6; (6): 01 80 80 2c 00 3f
col 3; len 3516; (3516):
cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92
24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
49 92 24 49 cb 92 24 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24
49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49
92 24 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49
Note the indexed value is c1 03, denoting the lowest DAD_ID=2 currently in the table.
Now the partial block dump of the invisible B-Tree Index:
Block header dump: 0x0181b724
Object id on Block? Y
seg/obj: 0x16f46 csc: 0x00.36bc78 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x181b720 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0036bc78
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 01
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 04
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 07
Again as expected the first index entry is C1 03.
With only a visible Bitmap Index in place, does that mean we’ll have table locking issues if we delete a parent row with current transactions in place ? Let’s check it out.
In one session, we have a current transaction on the child table:
SQL> insert into bowie_kid values (1000001, 'LOW', 4); 1 row created.
In another session, we attempt to delete a parent row (with an ID = 1 which doesn’t currently exist with the child table):
SQL> delete bowie_dad where id = 1; 1 row deleted.
We note the DML was successful and didn’t hang. This means the B-Tree index is clearly being used to police this constraint, even though it’s currently invisible.
In a third session, we now attempt to insert a child row using a FK value that’s in the process of being deleted:
SQL> insert into bowie_kid values (1000003, 'HEROES', 1);
As expected, it hangs as it’s currently effectively waiting on the row level lock made possible by the index entry in the B-Tree index as invisible indexes are still maintained behind the scenes. If we look at a fresh block dump of both indexes, beginning with the Bitmap Index:
Block header dump: 0x0180805c
Object id on Block? Y
seg/obj: 0x16f45 csc: 0x00.36bc54 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1808058 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0036bc54
Leaf block dump
===============
header address 402948708=0x18048264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[4499] flag: ——-, lock: 0, len=3537
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 00
col 2; len 6; (6): 01 80 80 2c 00 3f
col 3; len 3516; (3516):
cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92
24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
49 92 24 49 cb 92 24 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24
49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49
92 24 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49
We note the Bitmap Index has not been updated. It still lists the C1 03 value as the minimum indexed value.
However, if we look at the invisible B-Tree index:
Block header dump: 0x0181b724
Object id on Block? Y
seg/obj: 0x16f46 csc: 0x00.36bc78 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x181b720 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0008.015.00000b86 0x014316ab.01c5.42 —- 1 fsc 0x0000.00000000
Leaf block dump
===============
header address 402948708=0x18048264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 514
kdxcofbo 1064=0x428
kdxcofeo 1868=0x74c
kdxcoavs 804
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[1868] flag: ——-, lock: 2, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 81 b6 f3 00 00
row#1[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 01
row#2[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 7f d3 00 04
row#3[8000] flag: ——-, lock: 0, len=12
It has been updated and lists a new index entry C1 02 as the minimum value now in the index.
So the B-Tree index can be used to successfully police the FK index and prevent the possible table level locking issues associated with deleting parent rows, even though it’s invisible and there is an equivalent visible Bitmap index in place. Invisible indexes are simply not considered as viable execution paths by the Cost Based Optimizer, but may still be “visible” in a variety of scenarios such as quietly policing constraints behind the scenes.
Do I recommend creating two such indexes in Oracle Database 12c. Well, no as the costs of maintaining both indexes need to be considered. But I certainly do caution simply making indexes invisible and expecting the database to behave in exactly the same manner if the index were to be subsequently dropped.
Because rolling back all the above and then dropping the invisible index:
SQL> drop index bowie_kid_fk2_i; Index dropped. SQL> insert into bowie_kid values (1000001, 'LOW', 4); 1 row created.
Means in another session the parent delete operation will now hang without the B-Tree index being in place:
SQL> delete bowie_dad where id = 1;
12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation) February 17, 2014
Posted by Richard Foote in 12c, Drop Index, Invisible Indexes, Online DDL, Oracle Indexes, Unusable Indexes.6 comments
In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.
12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:
DROP INDEX ONLINE
ALTER INDEX UNUSABLE ONLINE
So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:
SQL> create table radiohead (id number, code number, name varchar2(30)); Table created. SQL> insert into radiohead select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> create index radiohead_code_i on radiohead(code); Index created.
If we now insert a new row in one session but not commit:
SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST'); 1 row created.
And then attempt any of the following DDL commands in another session:
SQL> drop index radiohead_code_i; drop index radiohead_code_i * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i invisible; alter index radiohead_code_i invisible * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i unusable; alter index radiohead_code_i unusable * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
They all get the well-known “ORA-00054: resource busy” error.
If on the other hand, one of these DDL statements is already running in a session:
SQL> alter index radiohead_code_i unusable;
All DML statements in other sessions will hang until the DDL completes:
SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');
Once the index is finally made unusable:
SQL> alter index radiohead_code_i unusable; Index altered. SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I'; INDEX_NAME STATUS ------------------------------ -------- RADIOHEAD_CODE_I UNUSABLE SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I'; no rows selected
We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.
So these commands prior to the Oracle 12c Database previously had locking related issues.
If we now perform the same setup in 12c and again have an outstanding transaction in a session:
SQL> drop index radiohead_code_i online;
The Drop Index command doesn’t now get the Ora-00054: resource busy, but rather hangs until all prior transactions complete.
However, while the Drop Index command hangs, it doesn’t in turn lock out transactions within other sessions. In another session:
SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE'); 1 row created.
And in yet other session:
SQL> delete radiohead where id = 42; 1 row deleted. SQL> commit; Commit complete.
These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.
SQL> drop index radiohead_code_i online; Index dropped.
Another more subtle difference in behaviour with 12c. If there’s an existing transaction when you decide to make an index unusable:
SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST'); 1 row created.
SQL> alter index radiohead_code_i unusable online;
As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:
SQL> commit; Commit complete. SQL> alter index radiohead_code_i unusable online; Index altered. SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I'; INDEX_NAME STATUS ------------------------- -------- RADIOHEAD_CODE_I UNUSABLE SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I'; SEGMENT_NAME BLOCKS EXTENTS ---------------- ---------- ---------- RADIOHEAD_CODE_I 2176 32
We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.
With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.
Visible Invisible Indexes (The Invisible Band) November 20, 2008
Posted by Richard Foote in 11g, Invisible Indexes.9 comments
After spending the last few weeks traveling throughout Europe, it’s about time I wrote a new post on the Blog !!
I’ve previously posted a simple demo on Invisible Indexes, a new 11g feature that allows you to quickly make an index “invisible” to the CBO.
Recently, a comment on the above post by Michael Sorensen mentions a nice post by Christian Antognini in which he demonstrates how so-called Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance related issues when delete/update operations are performed on the parent records.
I previously posted how Index Monitoring on FK indexes doesn’t pick up the fact so-called “Unused Indexes” can actually be “used” by Oracle for exactly this reason, quickly determining whether there’s an existing FK record that needs to prevent the update or delete operation on the parent record from proceeding.
I also previously discussed how the 11g CBO can now use index statistics to determine the appropriate cardinality of a query and how Index Monitoring doesn’t detect an index being “used” in this context as well.
It should therefore come as no surprise that Invisible Indexes might not be so invisible after all when it comes to the CBO potentially using the index statistics of an Invisible Index to determine the correct cardinality of a query.
This demo on Invisible Indexes and Index Statistics clearly shows that Oracle can still use the statistics of an index, even after they’ve been made invisible, to determine a more accurate cardinality for a query.
This means you need to be very careful how you use and apply Invisible Indexes and have a clear understanding on how Invisible Indexes are implemented by Oracle and the purpose for their use.
As I explain in my Indexing Seminar, Invisible Indexes are primarily intended to be applied when an application has suddenly and inexplicably started to use an index inappropriately and the database has significant performance issues as a result. The scenario is such that the performance of the database or application is so bad, it has caused the drain of resources to such an extent that normal business functions can’t continue and drastic action is required to address the issue.
The drastic action is to make the problematic index invisible so that the CBO can no longer see and use the index within an execution plan. This is drastic because it means other queries that could currently be using the index appropriately will also be prevented from using the index but performance is so bad that such a consequence can’t make matters any worse. By making the index invisible at least the resource drain can be prevented and hopefully most of the other business activities can now continue within the database.
By making the index invisible rather than simply dropping it, it means some sessions can investigate what the problem might be that’s causing the inappropriate use of the index (by altering sessions to OPTIMIZER_USE_INVISIBLE_INDEXES = true). Once the issue has been solved and addressed, the index can quickly be made visible again, rather than having to recreate the index from scratch.
Invisible indexes could prove to be a handy option in such scenarios.
Invisible indexes are also often viewed as being a useful method of determining whether it’s safe to drop an existing index that may not actually be used by an application. An unused index is a waste of both storage and resources in maintaining the thing but it’s often difficult to determine what the ramifications might be if an index were to be dropped. By making an index invisible, it may be possible to “test the waters” so to speak and determine how an application may suddenly behave without the index.
However, making an index invisible may not necessarily provide an accurate determination of the ramifications of dropping such an index because as has already been demonstrated, there are a number of important and key areas in which invisible indexes are still being used by Oracle. Dropping such an index is not the same as simply making an index invisible and may result in the application suddenly behaving in an unexpected manner that could cause significant performance issues.
Invisible indexes are not entirely invisible if you see what I mean 😉
Invisible Indexes December 11, 2007
Posted by Richard Foote in 11g, Index Access Path, Invisible Indexes, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.15 comments
New in 11g are “Invisible Indexes”, which are basically indexes that exist and are maintained by Oracle but are “invisible” to the CBO. Specific sessions can be set to see these invisible indexes as necessary.
Potentially useful if one has a problematic (and very large) index causing performance issues that you want to make invisible until the specific issue is addressed without the expensive of having to drop and latter recreate the index. Also useful if you want to introduce a new index but want it to be invisible until it’s been given a workout first in a specific “test” session.
Here’s a bit of a demo: Invisible Indexes
Richard Foote's Oracle Blog 