When Does A ROWID Change? Part I (“Fearless”) December 7, 2022
Posted by Richard Foote in Autonomous Database, Block Dumps, Changing ROWID, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Table Internals, Oracle19c, Pink Floyd, Richard's Blog, ROWID.7 comments
Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing.
You can watch his excellent video on the subject here.
As the humble ROWID is a critical component of any index, I thought it worthwhile to have a deep dive discussion on when a ROWID can and can’t change and some of the key changes that have been introduced within Oracle’s Autonomous database environments.
If you’re a developer who explicitly uses the ROWID in your applications, you might want to pay extra attention to these changes.
I thought I’ll begin first though by discussing how the ROWID currently doesn’t generally change…
The ROWID is basically just a pointer stored in indexes that effectively points to the physical location of a row within a table that’s associated with the specific indexed key. It consists of the Data Object ID (if it’s a Global Index associated to a Partitioned Table, as the Relative File ID is no longer a unique value and so needs this to determine the appropriate tablespace), a Relative File ID, a Data Block ID within the Relative File and the Row Location ID within the Data Block.
Now although it has never been a completely risk free approach to manually store and directly use these ROWIDs to fetch a row from a table, it’s a technique that has been frequently used by developers for 3 very good reasons.
The first reason is that it’s one of the most efficient ways to fetch a required row, because the database can go directly to the physical location and directly access the required row, without having to even read a single index block.
The second reason is because it has always been fully supported by Oracle to do so, with the required syntax well documented. Indeed, Oracle APEX includes the base functionality to store and access rows directly via their associated ROWIDs.
The third reason is because it’s generally well understood that the ROWID doesn’t change, except for a very few (generally) well known scenarios, and so using the ROWID to access data within an application is viewed as being reasonable safe.
When a row is inserted into a table, each corresponding index on the table (generally) has a new index entry also inserted, including its associated ROWID. Now, if the row never moves from its current physical location, there is no need to ever worry about the ROWID subsequently changing.
So the big question is, when can a row physically move AND the associated ROWID change?
Logically, a scenario that springs to mind is when a row is updated and made bigger and there’s no longer room within the current data block to store the larger row. Does the row move to another block with sufficient free space and result in the ROWID to change?
To check out this scenario, I’ll create and populate a basic table, with PCTFREE set to 0, so once the blocks within the table are filled, there is precious little space for rows to subsequently grow:
SQL> create table bowie (id number, name varchar2(142)) pctfree 0 enable row movement; Table created. SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed.
For good measure, I’ve also included the ENABLE ROW MOVEMENT clause, which is usually associated with Partitioned Tables (as I’ll discuss in Part II in this series).
Let’s have a look at the ROWIDs of a few random rows:
SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id; ID ROWID ---------- ------------------ 42 AAASe4AAMAAAACHAAp 424 AAASe4AAMAAAACHAGn 4242 AAASe4AAMAAAACNAHV
If we look at a partial block dump of one of the table blocks:
tab 0, row 0, @0x1312 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 5] 42 4f 57 49 45 tab 0, row 1, @0x131e tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 5] 42 4f 57 49 45 tab 0, row 2, @0x132a tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [ 5] 42 4f 57 49 45 tab 0, row 3, @0x1336 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 05 col 1: [ 5] 42 4f 57 49 45 tab 0, row 4, @0x1342 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 06 col 1: [ 5] 42 4f 57 49 45
The above partial block dump shows the first 5 rows within the block, with the contents of the 2 table columns listed (in hex format).
If we access a row via an index:
SQL> select * from bowie where id=42;
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 66 | 2 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
702 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
We can see in this example that Oracle requires 4 consistent gets, 3 of which are accesses to the index.
If we access this table via its ROWID:
SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID | BOWIE | 1 | 10 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
698 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
We can see that this only requires just the 1 consistent get (vs. 4 when using the index) to access the row.
So we can see the appeal of using the ROWID to access a row.
If we now update the rows within the table and make them substantially larger so they can no longer fit within the currently filled blocks:
SQL> update bowie set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 10000 rows updated. SQL> commit; Commit complete.
And now look again at the ROWIDs of these selected rows:
SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id; ID ROWID ---------- ------------------ 42 AAASe4AAMAAAACHAAp 424 AAASe4AAMAAAACHAGn 4242 AAASe4AAMAAAACNAHV
We notice that the ROWIDs all remain the same.
This has always historically been the behaviour here. If we update a row and the updated row can no longer fit within the current block, the row “migrates” to another table block with sufficient free space. BUT the associated ROWIDs do NOT change and the associated indexes are NOT updated.
Rather, the row data within the updated table is replaced with a “pointer”, that points to the new physical location of the migrated row. The advantage here being that Oracle only has to update the table with this new pointer, rather having to update the associated ROWIDs of all the (possibly many) associated indexes (noting that such an update would actually result in a delete followed by a re-insert of each index entry).
The disadvantage of course is that to now access this migrated row via an index requires an extra hop, to first read the initial table block and then to follow the pointer and access the actual block that now contains the row. Note if this row is forced to be migrated again because it grows again and can’t be housed in the current block, this pointer in the initial block is updated to reflect the newer location, so at least there is only ever the one extra hop.
If we look at a new partial block dump of the previously accessed block:
tab 0, row 0, @0x1f8f tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4b tab 0, row 1, @0x1f86 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4c tab 0, row 2, @0x1f7d tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4d tab 0, row 3, @0x1f74 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4e tab 0, row 4, @0x1f6b tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000b8.0
We notice that the rows with their 2 columns have been replaced with a logical nrid pointer (consisting of a relative block address and row location within the block), that effectively points to the new physical location of the row.
Note we can still use the same, unchanged ROWID to access the same table rows:
SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID | BOWIE | 1 | 10 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
698 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
This ROWID access still works fine, except it has now increased to 2 consistent gets, one to access the initial block referenced by the ROWID and the extra consistent get to follow the pointer and access the new physical location of the row.
So historically, we haven’t had to worry about updates changing the ROWID of a row (except perhaps at looking at reducing the number of these migrated rows).
Well, except for one clear example as I’ll discuss in Part II…
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: Non-Equality Predicates Part IV (“Like A Rocket Man”) November 29, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, LIKE Predicates, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, _EXADATA_FEATURE_ON.add a comment
Forgive me, it’s been a while since I last posted, but life has so many distractions these days 🙂
I recently had a question on whether a LIKE predicate can generate an Automatic Index now that non-equality predicates are supported since Oracle Database 21c.
Now the answer I initially provided was “well, why don’t you just test it for yourself“. However, his subsequent responses highlighted to me that not everyone might necessarily know how to potentially play with many of the Exadata features, even if you don’t directly have access to an Exadata environment.
So the purpose of this post is not only to answer this question, but also to just highlight HOW to potentially test things out for yourself when you’re not lucky enough to work directly with Exadata.
One obvious manner in which to play on an Exadata environment is to simply create and connect to an Oracle Autonomous Database environment using Oracle’s Cloud services (which are all Exadata-based environments), where you can easily, FOR FREE, and WITH NO TIME RESTRICTIONS play with many Exadata database features. The “Always Free Cloud Services” is truly a fabulous resource provided by Oracle, where you can have a couple of Autonomous Database environments always at your disposal (and very very easily and quickly just drop an existing database environment and re-create a new one).
Follow the link for all the information you need on how to get started with Oracle’s Always Free Cloud Services: https://www.oracle.com/au/autonomous-database/free-trial/
If the version of Oracle Database you like to play with isn’t currently available on the Oracle Autonomous Database platforms, another option is to simply download the database version you want to play with and just make it think it’s actually on an Exadata platform, by setting the following hidden parameter:
SQL> alter system set "_exadata_feature_on"=true scope=spfile; System altered.
and restart your database.
You can now at least play and learn about many of the Exadata database features (such as Automatic Indexing), without having an actual Exadata machine on hand.
OK, now that you have an Exadata (or Exadata-like) environment on hand, you can go about answering for yourself these types of questions…
So, does Automatic Indexing now work in the case of a LIKE predicate?
First, make sure Automatic Indexing is enabled:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
Begin by creating and populating a basic table structure to test. The following table just has a few basic columns, with the MIXED_STUFF column simply populated with the rownum concatenated with a constant string:
SQL> create table aladdin_sane (id number, code1 number, grade number, mixed_stuff varchar2(42), name varchar2(42)); Table created. SQL> insert into aladdin_sane select rownum, mod(rownum,100000), mod(rownum,100), rownum || ' David Bowie ' || rownum, '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=>'ALADDIN_SANE'); PL/SQL procedure successfully completed.
I then run the following query several times with a LIKE predicate that returns just the one row from my 10M row table:
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 14805 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | ALADDIN_SANE | 1 | 57 | 14805 (2)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
169813 consistent gets
84940 physical reads
0 redo size
912 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 currently in place, the CBO has no choice but to use a FTS. But, with only 1 row returned from this 10M table, an appropriate index would almost certainly be beneficial. So what does Automatic Index do in this scenario?
Once we wait for the next running of the Automatic Indexing jobs to complete, we can check:
SQL> select dbms_auto_index.report_last_activity() report from dual;
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 29-NOV-2022 12:52:30
Activity end : 29-NOV-2022 12:53:50
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) : 452.98 MB (452.98 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 6
SQL statements improved (improvement factor) : 1 (169815.2x)
SQL plan baselines created (SQL statements) : 1 (1)
Overall improvement factor : 2x
-------------------------------------------------------------------------------
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 | ALADDIN_SANE | SYS_AI_dzhahcw1cf0mw | MIXED_STUFF | B-TREE | NONE |
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : fgvdbfsfwb9jv
SQL Text : select * from aladdin_sane where mixed_stuff like '4242%'
Improvement Factor : 169815.2x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 10869872 433
CPU Time (s): 9778626 433
Buffer Gets: 2377413 4
Optimizer Cost: 14805 4
Disk Reads: 1189160 2
Direct Writes: 0 0
Rows Processed: 14 1
Executions: 14 1
So, it appears that Automatic Indexing has indeed created a new index. We can now check out the new index details:
select index_name, auto, constraint_index, visibility, compression, status from user_indexes where table_name='ALADDIN_SANE'; 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='ALADDIN_SANE' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------------ --------------- SYS_AI_dzhahcw1cf0mw MIXED_STUFF 1
Automatic Indexing has indeed created a VALID/VISIBLE index on the MIXED_STUFF column.
If we now re-run the query:
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE | 1 | 57 | 4 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_dzhahcw1cf0mw | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
116204 consistent gets
84940 physical reads
0 redo size
912 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
We can see the newly generated plan now uses the new Automatic Index.
But due to Deferred Invalidations (which I’ve discussed previously), which in Oracle 21c delay the invalidation of SQL cursors due to new indexes, we may need to (for example) flush the shared_pool for the new plan to actually be used (a safe enough option in our play/test environment):
SQL> alter system flush shared_pool;
System altered.
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE | 1 | 57 | 4 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_dzhahcw1cf0mw | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
263 recursive calls
0 db block gets
508 consistent gets
0 physical reads
0 redo size
916 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
70 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE | 1 | 57 | 4 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_dzhahcw1cf0mw | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
916 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
We can now see that the new plan has indeed been adopted with the substantial decrease in consistent gets, down to just 5 from the previous 169813 with the FTS.
So yes, Automatic Indexing does indeed now work with LIKE predicates, but most importantly, it’s very easy for you to test and see these things for yourself.
In which case, you won’t need blogs such as this in the future to show you the way… 🙂
Costing Concatenated Indexes With Range Scan Predicates Part II (Coming Back To Life) July 27, 2022
Posted by Richard Foote in Automatic Indexing, CBO, Column Statistics, Concatenated Indexes, Explain Plan For Index, Full Table Scans, Index Access Path, Index Column Order, Index Column Reorder, Index Internals, Index statistics, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard Foote Training.add a comment
In my previous Part I post, I discussed how the CBO basically stops the index leaf block access calculations after a non-equality predicate. This means that for an index with the leading indexed column being accessed via an unselective non-equality predicate, a large percentage of the index’s leaf blocks might need to be scanned, making the index access path unviable.
In the example in Part I, an index on the ID, CODE columns was too expensive due to the unselective range-scan predicate based on the leading ID column.
To provide the CBO a potentially much more efficient access path, we need an index with the more selective CODE predicate to be the leading column:
SQL> CREATE INDEX radiohead_code_id_i ON radiohead(code, id); Index created. SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_CODE_ID_I'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ----------------------------- ---------- ----------- ----------------- RADIOHEAD_CODE_ID_I 1 265 98619
If we now re-run the previous query:
SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD | 4 | 72 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | RADIOHEAD_CODE_ID_I | 4 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
We notice the CBO is now using this new index, as the costs for this index-based plan have dropped significantly, down to just 6 (from the previous 116). This overall cost of 6 is lower than the cost of 105 for the Full Table Scan and hence the reason why this index-based plan is now chosen by the CBO.
This is all due entirely to the significant drop in costs in accessing the index itself, now just 2 (from the previous 112).
Importantly, these much lower costs are accurate as we can tell via the reduced number of consistent reads, now just 7 (from 114 from the previous index-based plan).
If we now look at the associated costings:
Effective Index Selectivity = CODE selectivity x ID selectivity
= (1/10000) x ((5000-1000)/(10000-1) + 2 x (1/10000))
= 0.0001 x ((4000/9999) + 0.0002)
= 0.0001 x 0.40024)
= 0.000040024
Effective Table Selectivity = same as Index Selectivity
= 0.000040024
The effective index selectivity of 0.000040024 is now much lower than the previous (0.40024), as the CBO can now consider the product of the selectivities of both columns).
If we now plug this improved effective index selectivity into the index path costing calculations:
Index IO Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor)
Index IO Cost = 1 + ceil(0.000040024 x 265) + ceil(0.000040024 x 99034)
= 1 + 1 + 4
= 2 + 4
= 6
Index Access Cost = IO Costs + CPU Costs (in this plan, 0% of total costs and so unchanged from the IO costs)
= 2 + 4
= 6
We can see how the respective 2 and 6 improved CBO index costings are derived.
So again, it’s important to note that Automatic Indexing is doing entirely the correct thing with these examples, when it creates an index with the equality based predicate columns as the leading columns of the index…
Costing Concatenated Indexes With Range Scan Predicates Part I (Nothing To Be Desired) July 22, 2022
Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Concatenated Indexes, Index Access Path, Index Column Order, Index Column Reorder, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning, Richard Foote Consulting, Richard Foote Training, Richard's Blog.1 comment so far
In my previous post, I discussed how Automatic Indexing ordered columns when derived from SQLs containing both equality and non-equality predicates.
I’ve since had offline questions asking why indexes are more effective with leading columns addressing the equality predicates rather than the leading columns addressing non-equality predicates. Based on the theory that for everyone who asks a question, there are likely numerous others wondering the same thing, I thought I’ll try to explain things with these posts.
I’ll start by creating the following simple table that has two columns (ID and CODE) that are both highly selective (they both have 10,000 distinct values in a 100,000 rows table, so 10 rows approximately per value):
SQL> CREATE TABLE radiohead (id NUMBER, code NUMBER, name VARCHAR2(42)); Table created. SQL> INSERT INTO radiohead SELECT mod(rownum,10000)+1, ceil(dbms_random.value(0,10000)), 'RADIOHEAD' FROM dual CONNECT BY LEVEL <= 100000; 100000 rows created. SQL> commit; Commit complete.
I’ll next create an index based on the ID, CODE columns, with importantly the ID column as the leading column:
SQL> CREATE INDEX radiohead_id_code_i ON radiohead(id, code); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'RADIOHEAD', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
When it comes to costing index accesses, some of the crucial statistics including the Blevel, Leaf_Blocks and often most crucial of all, the Clustering_Factor:
SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_ID_CODE_I'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR -------------------- ---------- ----------- ----------------- RADIOHEAD_ID_CODE_I 1 265 99034
We begin by running the following query, with an equality predicate on the ID column and a relatively large, non-selective range predicate on the CODE column:
SQL> SELECT * FROM radiohead WHERE id = 42 AND CODE BETWEEN 1000 AND 5000;
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD | 4 | 72 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | RADIOHEAD_ID_CODE_I | 4 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
As (perhaps) expected, the CBO uses the index to retrieve the small number of rows (just 5 rows).
However, if we run the following query which also returns a small number of rows (just 4 rows) BUT with the relatively unselective, non-equality predicate based on the leading indexed ID column:
SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;
Execution Plan
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 105 (11)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| RADIOHEAD | 4 | 72 | 105 (11)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
363 consistent gets
0 physical reads
0 redo size
770 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
We notice (perhaps unexpectedly) that the CBO now ignores the index and uses a Full Table Scan, even though only 4 rows are returned from a 100,000 row table.
This is a common area of confusion. Why does Oracle not use the index when both columns in the index are referenced in the SQL predicates and only a tiny number of rows are returned?
The answer comes down to the very unselective non-equality predicate (ID BETWEEN 1000 AND 5000) being serviced by the leading column (ID) of the index.
The “ID BETWEEN 1000 AND 5000” predicate basically covers 40% of all known ID values, which means Oracle must now read 40% of all Leaf Blocks within the index (one leaf block at a time), starting with ID =1000 and ending with ID = 5000. Although there are very few rows that then subsequently match up with the other (CODE = 140) predicate based on the second column (CODE) of the index, these relatively few values could exist anywhere within the 40% ID range.
Therefore, when costing the reading of the actual index, the CBO basically stops its calculations after the non-equality predicate on this leading ID column and indeed estimates that a full 40% of the index itself must be scanned.
If we force the CBO into a range scan via a basic index hint:
SQL> SELECT /*+ index(r) */ * FROM radiohead r WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 116 (4)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD | 4 | 72 | 116 (4)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | RADIOHEAD_ID_CODE_I | 4 | | 112 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
114 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
We notice that the overall cost of this index based plan is 116, greater than the 105 cost of the Full Table Scan (and hence why the Full Table Scan was selected). We also notice that the vast majority of this 116 cost can be attributed to the index scan itself in the plan, which has a cost of 112.
If you have a calculator handy, this is basically how these costs are derived.
Range Selectivity = (Max Range Value–Min Range Value)/(Max Column Value–Min Column Value)
Effective Index Selectivity = Range Selectivity + 2 x ID density (as a BETWEEN clause was used which is inclusive of Min/Max range)
= (5000-1000)/(10000-1) + 2 x (1/10000)
= 0.40004 + 0.0002
= 0.40024
Effective Table Selectivity = ID selectivity (as above) x CODE selectivity
= 0.40024 x (1/10000)
= 0.40024 x 0.0001
= 0.000040024
These selectivities are then inserted into the following index costing formula:
Index IO Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor)
Index IO Cost = 1 + ceil(0.40024 x 265) + ceil(0.000040024 x 99034)
= 1 + 107 + 4
= 108 + 4 = 112.
Index Access Cost = IO Costs + CPU Costs (in this plan, 4% of total costs)
= (108 + (112 x 0.04)) + (4 + (4 x 0.04))
= (108 + 4) + (4 + 0)
= 112 + 4
= 116
So we can clearly see how the CBO has made its calculations, come up with its costs and has decided that the Full Table Scan is indeed the cheaper alternative with the current index in place.
So Automatic Indexing is doing the right thing, by creating an index with the leading column based on the equality predicate and the second indexed column based on the unselective non-equality predicate.
I’ll expand on this point in an upcoming Part II post.
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.
Upcoming Webinar Series Now Sold Out!! (“White Light White Heat”) July 13, 2022
Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training.add a comment
My upcoming webinars now have the maximum number of attendees I’m comfortable in having in one session and are now officially sold out!!
Taking my inspiration from David Bowie’s famous 1973 farewell speech, not only is this the last webinar series of the year, but it’s likely the last webinar series I’ll ever do.
A huge thank you to everyone who has enrolled, I’m absolutely sure we’re all going to have a blast 🙂
If you were interested in attending this training, please contact me to get yourself on a waiting list (although I unfortunately have no timeframe in which I’m likely to run another series) : richard@richardfooteconsulting.com.
Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022
Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.1 comment so far
In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).
The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.
In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.
I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:
SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:
SQL> create index album_id_i on big_bowie(album_id); Index created. SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local; Index created.
If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):
SQL> alter table big_bowie move partition albums_2017; Table altered.
This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:
SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';
INDEX_NAME STATUS
------------------------------ --------
ALBUM_ID_I UNUSABLE
RELEASE_DATE_TOTAL_SALES_I N/A
SQL> select index_name, partition_name, status from user_ind_partitions
where index_name='RELEASE_DATE_TOTAL_SALES_I';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2014 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2015 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2016 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2017 UNUSABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2018 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2019 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2020 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2021 USABLE
Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:
SQL> select * from big_bowie where album_id=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 52000 | 7959 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 2000 | 52000 | 7959 (2) | 00:00:01 | 1 | 8 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 2000 | 52000 | 7959 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ALBUM_ID"=42)
- filter("ALBUM_ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48593 consistent gets
42881 physical reads
0 redo size
44289 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)
2000 rows processed
We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:
SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 986 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 986 (2) | 00:00:01 | 4 | 4 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 1 | 26 | 986 (2) | 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
- filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5573 consistent gets
0 physical reads
0 redo size
676 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
Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.
So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?
If we wait until the next AI task completes and check out the indexes on the table:
SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I N/A YES
ALBUM_ID_I UNUSABLE NO
SYS_AI_aw2825ffpus5s VALID NO
SYS_AI_2hf33fpvnqztw VALID NO
SQL> select index_name, partition_name, status from user_ind_partitions
where index_name='RELEASE_DATE_TOTAL_SALES_I';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2014 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2015 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2016 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2017 UNUSABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2018 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2019 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2020 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2021 USABLE
We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------- --------------- ALBUM_ID_I ALBUM_ID 1 RELEASE_DATE_TOTAL_SALES_I RELEASE_DATE 1 RELEASE_DATE_TOTAL_SALES_I TOTAL_SALES 2 SYS_AI_aw2825ffpus5s ALBUM_ID 1 SYS_AI_aw2825ffpus5s RELEASE_DATE 2 SYS_AI_2hf33fpvnqztw TOTAL_SALES 1 SYS_AI_2hf33fpvnqztw RELEASE_DATE 2
As we can see, AI has logically replaced both unusable indexes.
The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.
The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.
Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.
But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.
A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).
If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…
As always, this restriction may change in future releases…
Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”) May 25, 2022
Posted by Richard Foote in 18c New Features, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Tricks, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Seminar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Seminar, Performance Tuning Webinar, Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training, Richard Presentations.add a comment
The registration links for my upcoming webinars running in August are now open!!!
The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button).
(Note: Do NOT use the links if you’re an Australian resident. Please contact me at richard@richardfooteconsulting.com for additional payment info and tax invoice that includes additional GST).
Just click the below “Buy Now” buttons to book your place for these unique, highly acclaimed Oracle training events (see some of my testimonials for feedback by previous attendees to these training events):
“Oracle Indexing Internals“ Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!
“Oracle Performance Diagnostics and Tuning“ Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!
“Special Combo Price for both August 2022 Webinars” $2,750 AUD: SOLD OUT!!
The links allow you to book a place using either PayPal or a credit card. If you wish to pay via a different method or have any questions at all regarding these events, please contact me at richard@richardfooteconsulting.com.
As I mentioned previously, for those of you on my official waiting list, I will reserve a place for you for a limited time.
As this will probably be the last time I will run these events, remaining places are likely to go quickly. So please book your place ASAP to avoid disappointment…
Read below a brief synopsis of each webinar:
“Oracle Indexing Internals“
This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).
Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.
This webinar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the webinar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this webinar and is not generally available in Oracle documentation or in Oracle University courses.
For full details, see: https://richardfooteconsulting.com/indexing-seminar/
“Oracle Performance Diagnostics and Tuning“
This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning. The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.
When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.
Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.
One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.
It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).
For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/
If you have any questions about these events, please contact me at richard@richardfooteconsulting.com
Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”) May 19, 2022
Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Myth, Oracle, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Webinar, Richard Foote Seminars, Webinar.add a comment
As promised last week, I have now finalised the dates for my upcoming webinars.
They will be run as follows (UPDATED):
“Oracle Indexing Internals“ Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!
“Oracle Performance Diagnostics and Tuning“ Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!
“Special Combo Price for both August 2022 Webinars“: SOLD OUT!!
I’ll detail costings and how to register for these events in the coming days.
There is already quite a waiting list for both of these webinars and so I anticipate available places will likely go quickly. Sorry to all those who have been waiting for so long and thank you for your patience. Please note for those on the waiting list, I already have places reserved for you.
It’s highly likely these will be the last time I’ll ever run these highly acclaimed training events (yes, I’m getting old)…
So don’t miss this unique opportunity to learn important skills in how to improve the performance and scalability of both your Oracle based applications and backend Oracle databases, in the comfort of your own home or office.
Read below a brief synopsis of each webinar:
“Oracle Indexing Internals“
This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).
Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.
This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.
For full details, see: https://richardfooteconsulting.com/indexing-seminar/
“Oracle Performance Diagnostics and Tuning“
This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning. The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.
When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.
Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.
One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.
It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).
For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/
Keep an eye out in the coming days on costings and how to register for these events.
If you have any questions about these events, please contact me at richard@richardfooteconsulting.com
Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”) May 17, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Exadata, Full Table Scans, Manual Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.1 comment so far
I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index.
However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created?
In my first demo, I’ll start by creating a basic non-partitioned table:
SQL> create table bowie_stuff (id number, album_id number, country_id number, release_date date, total_sales number); Table created. SQL> insert into bowie_stuff select rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800), ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BOWIE_STUFF'); PL/SQL procedure successfully completed.
We next manually create an index on the highly selective TOTAL_SALES column:
SQL> create index bowie_stuff_total_sales_i on bowie_stuff(total_sales); Index created.
Let’s now invalidate the index by re-organising the table without the online clause:
SQL> alter table bowie_stuff move; Table altered. SQL> select index_name, status from user_indexes where table_name='BOWIE_STUFF'; INDEX_NAME STATUS ------------------------------ -------- BOWIE_STUFF_TOTAL_SALES_I UNUSABLE
So the index is now in an UNUSABLE state.
To perk up the interest of AI, I’ll run a number of queries such as the following with a predicate condition on TOTAL_SALES:
select * from bowie_stuff where total_sales=42;
18 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 910563088
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7427 (2) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE_STUFF | 20 | 520 | 7427 (2) | 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("TOTAL_SALES"=42)
filter("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42746 consistent gets
42741 physical reads
0 redo size
1392 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)
18 rows processed
Without a valid index, the CBO has no choice but to perform an expensive full table scan.
However, it doesn’t matter how long I wait or how many different queries I run similar to the above, AI currently will never rebuild an unusable index if the index was manually created.
AI will only rebuild unusable automatically created indexes.
I’ve discussed previously how automatic and manually created indexes often don’t gel well together and is one of the key reasons why Oracle recommends dropping all manually created secondary indexes if you wish to implement AI (using the DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES procedure, which I’ll discuss in a future post).
Things can get a little interesting with AI, if the underlining table is partitioned and you have manually created unusable indexes.
As I’ll discuss in my next post…
Announcement: New (And Likely Final) Dates For My Webinars Finalised Next Week !! May 12, 2022
Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Indexing Webinar, Oracle, Oracle 21c, Oracle Cloud, Oracle General, Oracle Performance Diagnostics and Tuning Webinar, Richard Foote Training.add a comment
It’s been one hell of a hectic year!!
For all those of you who have been patiently hanging on for the next series of my webinars, I finally, at long last, have some good news.
I’m currently just finalising my calendar for the upcoming months, but I shall announce the next running of my webinars next week.
I plan to run both of my webinars in the coming months (follow links for full details on each webinar):
Note: There is the very distinct possibility that I will be running these highly acclaimed training events, either as a webinar or in person as a seminar, for the very last time.
Ever!!
So these will indeed be unique opportunities to attend some quality training on how to improve the performance and scalability of both your Oracle based applications and backend Oracle databases.
Listen out next week for full details on when these webinars will finally be available to attend and how to register for the limited places available 🙂
Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”) May 11, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning, Rebuild Unusable Indexes.1 comment so far
Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”?
My answer to them both is that I’ve provided them the basic framework in the demo to check out the answer to that question for themselves (Note: a fantastic aspect of working with the Oracle Database is that it’s available for free to play around with, including the Autonomous Database environments).
But based on the principle that for every time someone asks a question, there’s probably a 100 others who potentially might be wondering the same thing, thought I’ll quickly whip up a demo to answer this for all.
I’ll begin with the same table format and data as my previous blog:
SQL> CREATE TABLE big_ziggy(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_ziggy SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');
PL/SQL procedure successfully completed.
But this time, I’ll run a number of queries similar to the following, that also has a predicate based on the partitioned key (RELEASE_DATE) of the table:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1051 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5618 consistent gets
0 physical reads
0 redo size
676 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
If we wait for the next AI task to kick in:
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 10:55:43 Activity end : 11-MAY-2022 10:56:27 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 6 SQL statements improved (improvement factor) : 3 (6670.1x) SQL plan baselines created : 0 Overall improvement factor : 2x ------------------------------------------------------------------------------- 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 | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
We can see that AI has indeed automatically created a LOCAL, partitioned index (on columns RELEASE_DATE, TOTAL_SALES) in this scenario, as we have an equality predicate based on the partitioned key (RELEASE_DATE).
Currently, all is well with the index, with all partitions in a USABLE state:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_ZIGGY';
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_6wv99zdbsy8ar YES YES VISIBLE N/A
SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
SQL> select index_name, column_name, column_position from user_ind_columns
where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_6wv99zdbsy8ar RELEASE_DATE 1
SYS_AI_6wv99zdbsy8ar TOTAL_SALES 2
But if we now do an offline reorg of a specific table partition:
SQL> alter table big_ziggy move partition albums_2017; Table altered. SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 UNUSABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
We can see we’ve now made the associated Local Index partition UNUSABLE.
If we run the following query:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 986 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
3 recursive calls
4 db block gets
5578 consistent gets
5571 physical reads
924 redo size
676 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
The CBO has no choice here but to do a full partition table scan.
If now wait again for the next AI task to strut its stuff:
SQL> select dbms_auto_index.report_last_activity() from dual; DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 11:42:42 Activity end : 11-MAY-2022 11:43:13 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 4 SQL statements improved (improvement factor) : 1 (5573x) SQL plan baselines created : 0 Overall improvement factor : 1.1x ------------------------------------------------------------------------------- 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 | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
The index partition is now automatically in a USABLE state again.
If we look at the index object data:
SQL> select object_name, subobject_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yy hh24:mi:ss’) last_ddl_time from dba_objects where object_name='SYS_AI_6wv99zdbsy8ar'; OBJECT_NAME SUBOBJECT_NAME CREATED LAST_DDL_TIME ------------------------------ -------------------- --------------------------- --------------------------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 11-May-22 10:41:33 11-May-22 10:56:14 SYS_AI_6wv99zdbsy8ar ALBUMS_2016 11-May-22 10:41:33 11-May-22 10:56:15 SYS_AI_6wv99zdbsy8ar ALBUMS_2017 11-May-22 10:41:33 11-May-22 11:42:42 SYS_AI_6wv99zdbsy8ar ALBUMS_2018 11-May-22 10:41:33 11-May-22 10:56:18 SYS_AI_6wv99zdbsy8ar ALBUMS_2019 11-May-22 10:41:33 11-May-22 10:56:19 SYS_AI_6wv99zdbsy8ar ALBUMS_2020 11-May-22 10:41:33 11-May-22 10:56:20 SYS_AI_6wv99zdbsy8ar ALBUMS_2021 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar ALBUMS_2022 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar 11-May-22 10:41:33 11-May-22 11:43:13
We can see that just the impacted index partition has been rebuilt.
The CBO can now successfully use the index to avoid the full partition table scan:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3640710173
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_ZIGGY | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_AI_6wv99zdbsy8ar | 1 | | 3 (0) | 00:00:01 | 3 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
676 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
I’ll leave it to the discernible reader to determine if this also works in the scenario where the partitioned index were to be global… 🙂
Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”) May 10, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Rebuild Unusable Indexes.2 comments
Obviously, the main feature of Automatic Indexing (AI) is for Oracle to automatically create indexes, that have been proven to improve performance, in a relatively safe and timely manner.
However, another nice and useful capability is for AI to automatically rebuild indexes that are placed in an “Unusable” state.
The documentation states that:
“Automatic indexing provides the following functionality:
Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.”
Now, when AI was initially released, I was unable to get this rebuild capability to work as advertised. I don’t know whether this was because the capability had not yet been successfully implemented or because of some failings in my testing.
However, with both the current versions of Oracle Database 19c (19.15.0.1.0 as now implemented in Autonomous Databases) and Oracle Database 21c, the following demo now works successfully.
Let’s begin by creating a simple partitioned table:
SQL> CREATE TABLE big_bowie(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
We next run a number of SQL statements such as the following:
SQL> SELECT * FROM big_bowie WHERE total_sales = 123456;
19 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7958 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 7958 (2) | 00:00:01 | 1 | 8 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 20 | 520 | 7958 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=123456)
filter("TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49573 consistent gets
42778 physical reads
0 redo size
1423 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)
19 rows processed
If we wait for the AI task to kick in, we notice is has successfully created an associated automatic index:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1 NO YES VISIBLE VALID
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_17cd4101fvrk1 TOTAL_SALES 1
As discussed previously, AI can now create a non-partitioned, Global index if deemed more efficient than a corresponding Local index.
Note that the newly created automatic index is currently VALID.
However, if we re-organise a partition within the table without using the Online clause:
SQL> alter table big_bowie move partition albums_2017; Table altered. SQL> select index_name, partitioned, auto, visibility, status from user_indexes where table_name = 'BIG_BOWIE'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_17cd4101fvrk1 NO YES VISIBLE UNUSABLE
The index as a result goes into an UNUSABLE state.
Running similar queries from this point will result in a FTS again:
SQL> select * from big_bowie where total_sales=42;
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7937 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 7937 (2) | 00:00:01 | 1 | 8 |
|* 2 | TABLE ACCESS FULL | BIG_BOWIE | 20 | 520 | 7937 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=123456)
filter("TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
126 recursive calls
0 db block gets
48962 consistent gets
42799 physical reads
0 redo size
1497 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
22 rows processed
If we now wait until the next AI task period and check out the index:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_17cd4101fvrk1 NO YES VISIBLE VALID
We notice the index is now back in a VALID state again.
Checking out the date attributes of the index confirms the index has indeed been rebuilt:
SQL> select object_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yyhh24:mi:ss’) last_ddl_time from dba_objects where object_name='SYS_AI_17cd4101fvrk1'; OBJECT_NAME CREATED LAST_DDL_TIME ------------------------------ --------------------------- --------------------------- SYS_AI_17cd4101fvrk1 18-Apr-22 11:59:36 18-Apr-22 18:37:42
Being in a VALID state again, the CBO can now use the automatic index:
SQL> select * from big_bowie where total_sales=42;
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 920768077
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 23 (0) | 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 20 | 520 | 23 (0) | 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | SYS_AI_17cd4101fvrk1 | 20 | | 3 (0) | 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48711 consistent gets
42799 physical reads
0 redo size
1497 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)
22 rows processed
Note: This scenario works the same if the table is Non-Partitioned.
In my next post, I’ll discuss a scenario where the automatic rebuild of an Unusable index will currently NOT work…
Richard Foote's Oracle Blog 