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.3 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 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 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…
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…
Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part III (“Loaded”) April 28, 2022
Posted by Richard Foote in 19c, Advanced Index Compression, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Data Clustering, Exadata, Index Access Path, Index Column Order, Index Compression, Oracle, Oracle 21c, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Overloading.add a comment
In my previous two posts, I’ve discussed scenarios where Automatic Indexing (AI) does not currently created automatic indexes and you may need to manually create the necessary indexes.
In this post, I’ll discuss a third scenario where AI will create an index, but you may want to manually create an even better one…
I’ll start by creating a relatively “large” table, with 20+ columns:
SQL> create table bowie_overload (id number, code1 number, code2 number, stuff1 varchar2(42), stuff2 varchar2(42), stuff3 varchar2(42), stuff4 varchar2(42), stuff5 varchar2(42), stuff6 varchar2(42), stuff7 varchar2(42), stuff8 varchar2(42), stuff9 varchar2(42), stuff10 varchar2(42), stuff11 varchar2(42), stuff12 varchar2(42), stuff13 varchar2(42), stuff14 varchar2(42), stuff15 varchar2(42), stuff16 varchar2(42), stuff17 varchar2(42), stuff18 varchar2(42), stuff19 varchar2(42), stuff20 varchar2(42), name varchar2(42)); Table created. SQL> insert into bowie_overload select rownum, mod(rownum, 1000)+1, '42', 'David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke', 'David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke','David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke','David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke', 'The Spiders From Mars' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_OVERLOAD'); PL/SQL procedure successfully completed.
The main columns to note here are CODE1 which contains 1000 distinct values (and so is kinda selective on a 10M row table, but not spectacularly so, especially with a poor clustering factor) and CODE2 which always contains the same value of “42” (and so will compress wonderfully for maximum effect).
I’ll next run the following query a number of times:
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1883860831
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 74817 (1) | 00:00:03 |
| * 1 | TABLE ACCESS STORAGE FULL | BOWIE_OVERLOAD | 10000 | 70000 | 74817 (1) | 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE1"=24)
filter("CODE1"=24)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
869893 consistent gets
434670 physical reads
0 redo size
183890 bytes sent via SQL*Net to client
7378 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
Without an index, the CBO currently has no choice but to perform a FTS. An index on the CODE1 column would provide the necessary filtering to fetch and return the required rows.
BUT, if this query was important enough, we could improve things further by “Overloading” this index with the CODE2 column, so we could use the index exclusively to get all the necessary data, without having to access the table at all. Considering an index on just the CODE1 column would need to fetch a reasonable number of rows (10000) and would need to visit a substantial number of different table blocks due to its poor clustering, overloading the index in this scenario would substantially reduce the necessary workloads of this query.
So what does AI do in this scenario, is overloading an index considered?
If we look at the AI report:
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 28-APR-2022 12:15:45
Activity end : 28-APR-2022 12:16:33
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) : 134.22 MB (134.22 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 2 (47.1x)
SQL plan baselines created : 0
Overall improvement factor : 47.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 | BOWIE_OVERLOAD | SYS_AI_aat8t6ad0ux0h | CODE1 | B-TREE | NONE |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : bh5cuyv8ga0bt
SQL Text : select code1, code2 from bowie_overload where code1=42
Improvement Factor : 46.9x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 42619069 241844
CPU Time (s): 25387841 217676
Buffer Gets: 12148771 18499
Optimizer Cost: 74817 10021
Disk Reads: 6085380 9957
Direct Writes: 0 0
Rows Processed: 140000 10000
Executions: 14 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 1883860831
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74817 | |
| 1 | TABLE ACCESS FULL | BOWIE_OVERLOAD | 10000 | 70000 | 74817 | 00:00:03 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 2541132923
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9281 | 64967 | 10021 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_OVERLOAD | 9281 | 64967 | 10021 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_aat8t6ad0ux0h | 10000 | | 18 | 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE1"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We see that an automatic index on just the CODE1 column was created.
SQL> select index_name, auto, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_OVERLOAD'; INDEX_NAME AUT VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_aat8t6ad0ux0h YES VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_OVERLOAD' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------- --------------- --------------- SYS_AI_aat8t6ad0ux0h CODE1 1
If we now re-run the query (noting in Oracle21c after you invalidate the current cursor):
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2541132923
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 10021 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_OVERLOAD | 10000 | 70000 | 10021 (1)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_aat8t6ad0ux0h | 10000 | | 18 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE1"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10021 consistent gets
0 physical reads
0 redo size
50890 bytes sent via SQL*Net to client
63 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
The query now uses the newly created automatic index.
BUT, at 10021 consistent gets, it’s still doing a substantial amount to work here.
If we manually create another index that overloads the only other column (CODE2) required in this query:
SQL> create index bowie_overload_code1_code2_i on bowie_overload(code1,code2) compress advanced low; Index created.
I’m using COMPRESS ADVANCED LOW as used by the automatic index, noting that CODE2 only contains the value “42” for all rows, making it particularly perfect for compression and a “best case” scenario when it comes to the minimal overheads potentially associated with overloading this index (I’m trying yo give AI every chance here):
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_OVERLOAD'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_aat8t6ad0ux0h YES NO VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 BOWIE_OVERLOAD_CODE1_CODE2_I NO NO VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_OVERLOAD' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- BOWIE_OVERLOAD_CODE1_CODE2_I CODE1 1 BOWIE_OVERLOAD_CODE1_CODE2_I CODE2 2 SYS_AI_aat8t6ad0ux0h CODE1 1
In fact, my manually created index is effectively the same size as the automatic index, with the same number (15363) of leaf blocks.
So I’m giving AI the best possible scenario in which it could potentially create an overloaded index.
But I’ve never been able to get AI to create overloaded indexes. Only columns in filtering predicates are considered for inclusion in automatic indexes.
If I now re-run my query again:
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1161047960
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 18 (0)| 00:00:01 |
| * 1 | INDEX RANGE SCAN | BOWIE_OVERLOAD_CODE1_CODE2_I | 10000 | 70000 | 18 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CODE1"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
50890 bytes sent via SQL*Net to client
63 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
We notice the CBO now uses the manually created index without any table access path, as it can just use the index to access the necessary data.
The number of consistent gets as a result has reduced significantly, down to just 21, a fraction of the previous 10021 when the automatic index was used.
So the scenario an of overloaded index that could significantly reduce database resources, which is currently not supported by AI, is another example of where may want to manually create a necessary index.
As always, this may change in the future releases…
Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part II (“Ragazzo Solo, Ragazza Sola” April 27, 2022
Posted by Richard Foote in 19c, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Constraints, Exadata, Foreign Keys, Full Table Scans, Index Internals, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Performance Tuning.1 comment so far
In my last post, I discussed how Automatic Indexing doesn’t create an automatic index in the scenario where the minimum or maximum of a column is required.
Another scenario when an automatic index is not created is when we hit issues associated with a missing index on a Foreign Key (FK) constraint.
As I’ve discussed many times previously, if you delete a parent record without an index on the dependant FK constraints, you hit a number of issues including having to perform a (potentially expensive and problematic) Full Table Scan (FTS) on the child tables and the associated locking problems.
To illustrate, I’ll first create a small parent table:
SQL> create table daddy (id number constraint daddy_pk primary key , name varchar2(42)); Table created. SQL> insert into daddy select rownum, 'David Bowie '|| rownum from dual connect by level <= 10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DADDY'); PL/SQL procedure successfully completed.
And then a somewhat larger child table, with no index on the associated foreign key constraint:
SQL> create table kiddy (id number constraint kiddy_pk primary key , code1 number constraint daddy_fk references daddy(id), code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into kiddy select rownum, mod(rownum,1000)+1000 , mod(rownum, 10000)+1, mod(rownum, 100000)+1, 'Ziggy Stardust '|| rownum from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'KIDDY'); PL/SQL procedure successfully completed.
If we delete a number of parent rows, for example:
SQL> delete from daddy where id = 101;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=101)
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
117462 consistent gets
22292 physical reads
4645500 redo size
204 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)
1 rows processed
We notice that even though we only delete one row from a relatively small table, we perform a large number of consistent gets (117462) due to the necessary FTS on the child table, as Oracle is forced to check the table for any possible FK violations. Without an index on the child CODE1 column, Oracle has no choice but to perform the relatively expensive FTS.
Additionally, if we have an existing transaction of a child table (in Session 1):
SQL> insert into kiddy values (10000001,1042,1042,1042,'Iggy Pop'); 1 row created.
And then in another session attempt to delete a parent row (in Session 2):
SQL> delete from daddy where id = 112;
The delete hangs in a locked state due to the child transaction in Session 1. This can lead to further locking issues in other sessions (Session 3):
insert into kiddy values (10000002,1042,1042,1042,'Iggy Pop');
The FTS on the child table and these associated locks can all be avoided by having an index on the FK constraint, as the index can then be used to effectively police the constraint during such delete operations.
What does AI do in this scenario?
Currently, nothing.
I’ve been unable to ever get AI to create a usable automatic index in this scenario. In Oracle Database 19c, I’ve not been able to get an AI created at all. In Oracle Database 21c, the best I’ve seen has been a Unusable/Invisible AI:
SQL> select index_name, index_type, auto, constraint_index, visibility, status, num_rows from user_indexes where table_n ame='KIDDY'; INDEX_NAME INDEX_TYPE AUT CON VISIBILIT STATUS NUM_ROWS ------------------------------ --------------------------- --- --- --------- -------- ---------- KIDDY_PK NORMAL NO YES VISIBLE VALID 10000004 SYS_AI_31thttf8v6r35 NORMAL YES NO INVISIBLE UNUSABLE 10000004 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='KIDDY'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- KIDDY_PK ID 1 SYS_AI_31thttf8v6r35 CODE1 1
So you may need to manually create such an index on the FK constraint to improve performance and eliminate these locking issues:
SQL> create index kiddy_code1_i on kiddy(code1);
Index created.
SQL> delete from daddy where id = 142;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=142)
Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
2 consistent gets
2 physical reads
132 redo size
204 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)
1 rows processed
Consistent gets have dropped off massively (down to just 8) as Oracle can now use the index to avoid the FTS search on the child table. The associated locking issues are eliminated as well.
Note: As always, this AI behaviour can always change in the future…
Oracle 19c Automatic Indexing: Invisible/Valid Automatic Indexes (Bowie Rare) August 31, 2021
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Index Access Path, Index statistics, Invisible Indexes, Invisible/Valid Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Statistics, Oracle19c, Unusable Indexes.1 comment so far
In my previous post, I discussed how newly created Automatic Indexes can have one of three statuses, depending the selectivity and effectiveness of the associated Automatic Index.
Indexes that improve performance sufficiently are created as Visible/Valid indexes and can be subsequently considered by the CBO. Indexes that are woeful and have no chance of improving performance are created as Invisible/Unusable indexes. Indexes considered potentially suitable but ultimately don’t sufficiently improve performance, are created as Invisible/Valid indexes.
Automatic Indexes are created as Visible/Valid indexes when shown to improve performance (by the _AUTO_INDEX_IMPROVEMENT_THRESHOLD parameter). But as I rarely came across Invisible/Valid Automatic Indexes (except for when Automatic Indexing is set to “Report Only” mode), I was curious to determine approximately at what point were such indexes created by the Automatic Indexing process.
To investigate things, I created a table with columns that contain data with various levels of selectivity, some of which should fall inside and outside the range of viability of any associated index, based on the cost of the associated Full Table Scan.
The following table has 32 columns of interest, each with a slight variation of distinct values giving small differences in overall column selectivity:
SQL> create table bowie_stuff1 (id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, code21 number, code22 number, code23 number, code24 number, code25 number, code26 number, code27 number, code28 number, code29 number, code30 number, code31 number, code32 number, name varchar2(42));
Table created.
SQL> insert into bowie_stuff1
select rownum,
mod(rownum, 900)+1,
mod(rownum, 1000)+1,
mod(rownum, 1100)+1,
mod(rownum, 1200)+1,
mod(rownum, 1300)+1,
mod(rownum, 1400)+1,
mod(rownum, 1500)+1,
mod(rownum, 1600)+1,
mod(rownum, 1700)+1,
mod(rownum, 1800)+1,
mod(rownum, 1900)+1,
mod(rownum, 2000)+1,
mod(rownum, 2100)+1,
mod(rownum, 2200)+1,
mod(rownum, 2300)+1,
mod(rownum, 2400)+1,
mod(rownum, 2500)+1,
mod(rownum, 2600)+1,
mod(rownum, 2700)+1,
mod(rownum, 2800)+1,
mod(rownum, 2900)+1,
mod(rownum, 3000)+1,
mod(rownum, 3100)+1,
mod(rownum, 3200)+1,
mod(rownum, 3300)+1,
mod(rownum, 3400)+1,
mod(rownum, 3500)+1,
mod(rownum, 3600)+1,
mod(rownum, 3700)+1,
mod(rownum, 3800)+1,
mod(rownum, 3900)+1,
mod(rownum, 4000)+1,
'THE RISE AND FALL OF ZIGGY STARDUST'
from dual connect by level >=10000000;
10000000 rows created.
SQL> commit;
Commit complete.
As always, it’s important that statistics be collected for Automatic Indexing to function properly:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_STUFF1', estimate_percent=>null); PL/SQL procedure successfully completed.
So on a 10M row table, I have 32 columns with the number of distinct values varying by only 100 values per column (or by a selectivity of just 0.001%):
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='BOWIE_STUFF1' order by num_distinct; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM ------------ ------------ ---------- --------------- NAME 1 .00000005 FREQUENCY CODE1 900 .001111 HYBRID CODE2 1000 .001 HYBRID CODE3 1100 .000909 HYBRID CODE4 1200 .000833 HYBRID CODE5 1300 .000769 HYBRID CODE6 1400 .000714 HYBRID CODE7 1500 .000667 HYBRID CODE8 1600 .000625 HYBRID CODE9 1700 .000588 HYBRID CODE10 1800 .000556 HYBRID CODE11 1900 .000526 HYBRID CODE12 2000 .0005 HYBRID CODE13 2100 .000476 HYBRID CODE14 2200 .000455 HYBRID CODE15 2300 .000435 HYBRID CODE16 2400 .000417 HYBRID CODE17 2500 .0004 HYBRID CODE18 2600 .000385 HYBRID CODE19 2700 .00037 HYBRID CODE20 2800 .000357 HYBRID CODE21 2900 .000345 HYBRID CODE22 3000 .000333 HYBRID CODE23 3100 .000323 HYBRID CODE24 3200 .000312 HYBRID CODE25 3300 .000303 HYBRID CODE26 3400 .000294 HYBRID CODE27 3500 .000286 HYBRID CODE28 3600 .000278 HYBRID CODE29 3700 .00027 HYBRID CODE30 3800 .000263 HYBRID CODE31 3900 .000256 HYBRID CODE32 4000 .00025 HYBRID ID 10000000 0 HYBRID
I’ll next run the below queries (based on a simple equality predicate on each column) several times each in batches of 8 queries, so as to not swamp the Automatic Indexing process with potential new index requests (the ramifications of which I’ll discuss in another future post):
SQL> select * from bowie_stuff1 where code1=42; SQL> select * from bowie_stuff1 where code2=42; SQL> select * from bowie_stuff1 where code3=42; SQL> select * from bowie_stuff1 where code4=42; SQL> select * from bowie_stuff1 where code5=42; ... SQL> select * from bowie_stuff1 where code31=42; SQL> select * from bowie_stuff1 where code32=42;
If we now look at the statuses of the Automatic Indexes subsequently created:
SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='BOWIE_STUFF1' order by visibility, status; INDEX_NAME COLUMN_NAME AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- ------------ --- --- --------- -------- ---------- ----------- ----------------- SYS_AI_5rw9j3d8pc422 CODE5 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_48q3j752csn1p CODE4 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_9sgharttf3yr7 CODE3 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_8n92acdfbuh65 CODE2 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_brgtfgngu3cj9 CODE1 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_1tu5u4012mkzu CODE11 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_34b6zwgtm86rr CODE12 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_gd0ccvdwwb4mk CODE13 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_7k7wh28n3nczy CODE14 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_67k2zjp09w101 CODE15 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_5fa6k6fm0k6wg CODE10 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_4624ju6bxsv57 CODE9 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_bstrdkkxqtj4f CODE8 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_39xqjjar239zq CODE7 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_6h0adp60faytk CODE6 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_5u0bqdgcx52vh CODE16 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_0hzmhsraqkcgr CODE22 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_4x716k4mdn040 CODE21 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_6wsuwr7p6drsu CODE20 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_b424tdjx82rwy CODE19 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_3a2y07fqkzv8x CODE18 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_8dp0b3z0vxzyg CODE17 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_d95hnqayd7t08 CODE23 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_fry4zrxqtpyzg CODE24 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_920asb69q1r0m CODE25 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_026pa8880hnm2 CODE31 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_96xhzrguz2qpy CODE32 YES NO VISIBLE VALID 10000000 15368 10000000 SYS_AI_3dq93cc7uxruu CODE29 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_5nbz41xny8fvc CODE28 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_fz4q9bhydu2qt CODE27 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_0kwczzg3k3pfw CODE26 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_4qd5tsab7fnwx CODE30 YES NO VISIBLE VALID 10000000 15367 10000000
We can see we indeed have the 3 statuses of Automatic Indexes captured:
Columns with a selectivity equal or worse to that of COL5 with 1300 distinct values are created as Invisible/Unusable indexes. Returning 10M/1300 rows or a cardinality of approx. 7,693 or more rows is just too expensive for such indexes on this table to be viable. This represents a selectivity of approx. 0.077%.
Note how the index statistics for these Invisible/Unusable indexes are not accurate. They all have an estimated LEAF_BLOCKS of 21702 and a CLUSTERING_FACTOR of 4272987. However, we can see from the other indexes which are physically created that these are not correct and are substantially off the mark with the actual LEAF_BLOCKS being around 15364 and the CLUSTERING_FACTOR actually much worse at around 10000000.
Again worthy of a future post to discuss how Automatic Indexing processing has to make (potentially inaccurate) guesstimates for these statistics in its analysis of index viability when such indexes don’t yet physically exist.
Columns with a selectivity equal or better to that of COL23 which has 3100 distinct values are created as Visible/Valid indexes. Returning 10M/3100 rows or a cardinality of approx. 3226 or less rows is cheap enough for such indexes on this table to be viable. This represents a selectivity of approx. 0.032%.
So in this specific example, only those columns between 1400 and 3000 distinct values meet the “borderline” criteria in which the Automatic Indexing process creates Invisible/Valid indexes. This represents a very very narrow selectivity range of only approx. 0.045% in which such Invisible/Valid indexes are created. Or for this specific example, only those columns that return approx. between 3,333 and 7,143 rows from the 10M row table.
Now the actual numbers and total range of selectivities for which Invisible/Valid Automatic Indexes are created of course depends on all sorts of factors, such as the size/cost of FTS of the table and not least the clustering of the associated data (which I’ve blogged about ad nauseam).
The point I want to make is that the range of viability for such Invisible/Valid indexes is relatively narrow and the occurrences of such indexes relatively rare in your databases. As such, the vast majority of Automatic Indexes are likely to be either Visible/Valid or Invisible/Unusable indexes.
It’s important to recognised this when you encounter such Invisible/Valid Automatic Indexes (outside of “REPORT ONLY” implementations), as it’s an indication that such an index is a borderline case that is currently NOT considered by the CBO (because of it being Invisible).
However, this Invisible/Valid Automatic Index status should really change to either of the other two more common statuses in the near future.
I’ll expand on this point in a future post…
Oracle 19c Automatic Indexing: Function-Based Indexes? Part II (If You Can See Me) February 5, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Function Based Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Virtual Columns.1 comment so far
In my previous post, I discussed how Automatic Indexing does not currently support creating an index based on a function or expression predicate, even if it’s an equality predicate. You must manually create the associated function-based index.
However, if you have access to the application, there’s a better strategy when frequently searching on a function-based predicate. That’s to create a Virtual Column and use this column in your searching criteria (as mentioned by Connor McDonald in this comment).
To illustrate, I’m going to drop the previously manually created function-based index and hence the associated hidden virtual column, as Oracle quite rightly doesn’t allow you to have two virtual columns based on the same expression in the same table.
SQL> drop index david_upper_name_i; Index dropped.
Since Oracle 11g, Oracle has supported the use of Visible Virtual Columns, a column that doesn’t physically exist, but defines a function/expression that can be easily accessed and populated when queried.
I’ll next create a Virtual Column called UPPER_NAME that is defined not based on a Data Type, but on the result on the UPPER function on the previously defined NAME column:
SQL> alter table david add (upper_name as (upper(name))); Table altered.
Regardless of size of table, this column is added virtually instantly (pun fully intended), as no data is physically stored in the table itself. I view it (yep, another pun) as a “mini-view”, that can be used to hide complexity from the developer, with the actual data derived at run-time when the column is accessed in an SQL.
After I generate fresh statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID', estimate_percent=>null); PL/SQL procedure successfully completed. SQL> select column_name, hidden_column, virtual_column, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME HID VIR NUM_DISTINCT DENSITY HISTOGRAM -------------------- --- --- ------------ ---------- --------------- NAME NO NO 10000000 0 HYBRID MORE_STUFF9 NO NO 1 .00000005 FREQUENCY MORE_STUFF8 NO NO 1 .00000005 FREQUENCY MORE_STUFF7 NO NO 1 .00000005 FREQUENCY MORE_STUFF6 NO NO 1 .00000005 FREQUENCY MORE_STUFF5 NO NO 1 .00000005 FREQUENCY MORE_STUFF4 NO NO 1 .00000005 FREQUENCY MORE_STUFF3 NO NO 1 .00000005 FREQUENCY MORE_STUFF2 NO NO 1 .00000005 FREQUENCY MORE_STUFF10 NO NO 1 .00000005 FREQUENCY MORE_STUFF1 NO NO 1 .00000005 FREQUENCY ID NO NO 10000000 0 HYBRID CODE NO NO 10000 .0001 HYBRID UPPER_NAME NO YES 10000000 0 HYBRID
Note how the UPPER_NAME virtual column is NOT hidden and now has up to date statistics.
We can now run this simplified query based on the new UPPER_NAME column, which does not need to include the potentially complex function expression:
SQL> select * from david where upper_name='DAVID BOWIE 42';
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2426813604
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 3349 (6) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID | 1 | 200 | 3349 (6) | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("UPPER_NAME"='DAVID BOWIE 42')
filter("UPPER_NAME"='DAVID BOWIE 42')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
263469 consistent gets
263452 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If we look at portions of the subsequent Automatic Indexing report:
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 360.71 MB (360.71 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 2 (263476.8x)
SQL plan baselines created : 0
Overall improvement factor : 263476.8x
-------------------------------------------------------------------------------
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 | DAVID | SYS_AI_4k4mkgkw049ht | UPPER_NAME | B-TREE | NONE |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 7tfqh3pu526mt
SQL Text : select * from david where upper_name='DAVID BOWIE 42'
Improvement Factor : 263484.7x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 1471249 1414
CPU Time (s): 300584 986
Buffer Gets: 3161816 4
Optimizer Cost: 3349 4
Disk Reads: 3161432 3
Direct Writes: 0 0
Rows Processed: 12 1
Executions: 12 1
PLANS SECTION
--------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 2426813604
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3349 | |
| 1 | TABLE ACCESS STORAGE FULL | DAVID | 1 | 200 | 3349 | 00:00:01 |
-----------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- cardinality_feedback = yes
- With Auto Indexes
-----------------------------
Plan Hash Value : 1447691372
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_4k4mkgkw049ht | 1 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("UPPER_NAME"='DAVID BOWIE 42')
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We see from the report that Automatic Indexing has now created the associated, implicitly created function-based index (SYS_AI_4k4mkgkw049ht) based on the virtual UPPER_NAME column:
SQL> select index_name, index_type, auto, constraint_index, visibility, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='DAVID'; INDEX_NAME INDEX_TYPE AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR -------------------- --------------------------- --- --- --------- -------- ---------- ----------- ----------------- SYS_AI_4k4mkgkw049ht FUNCTION-BASED NORMAL YES NO VISIBLE VALID 10000000 43104 2136839 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='DAVID' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- -------------------- --------------- SYS_AI_4k4mkgkw049ht UPPER_NAME 1
If we now re-run the SQL query:
SQL> select * from david where upper_name='DAVID BOWIE 4242';
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1447691372
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_4k4mkgkw049ht | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UPPER_NAME"='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1334 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO now uses the new Automatic Index to significantly improve the performance of the query.
So not only is using a user defined Virtual Column a cleaner solution with respect to the frequent use of a function-based expressions, but has the added advantage of being supported with Automatic Indexing.
Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan) February 4, 2021
Posted by Richard Foote in 19c, 19c New Features, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Function Based Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Virtual Columns.3 comments
I previously discussed how Automatic Indexing only currently supports Equality based predicates.
The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look.
The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column:
SQL> create table david (id number, code number, name varchar2(42), more_stuff1 varchar2(42), more_stuff2 varchar2(42), more_stuff3 varchar2(42), more_stuff4 varchar2(42), more_stuff5 varchar2(42), more_stuff6 varchar2(42), more_stuff7 varchar2(42), more_stuff8 varchar2(42), more_stuff9 varchar2(42), more_stuff10 varchar2(42)); Table created. SQL> insert into david select rownum, mod(rownum, 10000)+1, 'David Bowie '|| rownum, 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust' from dual connect by level <=10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID', estimate_percent=>null); PL/SQL procedure successfully completed.
If we look at the current details of the table columns:
SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID
We notice the same oddity of my previous post that all columns have histograms…
Let’s run the following query with an UPPER function-based predicate that returns only the one row:
SQL> select * from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2426813604
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 17M | 3350 (6) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID | 100K | 17M | 3350 (6) | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(UPPER("NAME")='DAVID BOWIE 4242')
filter(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
263469 consistent gets
263452 physical reads
0 redo size
1256 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
What does Automatic Indexing make of this scenario?
Basically, it does nothing. Currently, Automatic Indexing does NOT support such function-based indexes, even with equality based predicates (as of at least version 19.5.0.0.0). If we look at the next Automatic Indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 2 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 -------------------------------------------------------------------------------
No such function-based index is ever created by Automatic Indexing:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='DAVID'; no rows selected
To improve the performance of this query, one has to manually create the necessary function-based index:
SQL> create index david_upper_name_i on david(upper(name)); Index created.
If we now re-run the query:
SQL> select name from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2675555529
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 4199K | 3175 (1) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 100K | 4199K | 3175 (1) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | DAVID_UPPER_NAME_I | 40000 | | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
369 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The query now uses the function-based index to significantly improve the performance of this query, with just 5 consistent gets.
Note however as with all function-based indexes, by default the estimated cardinality estimate and associated CBO costs are way off (100K rows are estimated, not the 1 row that is actually returned). This is due to the CBO having no real idea of the number and distribution of values coming out of the “black box” function-based predicate.
This is why Oracle automatically creates an hidden virtual column by which to store the necessary statistics associated to the function (in this case the SYS_NC00014$ column):
SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID SYS_NC00014$ NONE
But we need to first collect statistics on this hidden virtual column for the statistics to be populated:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID', no_invalidate=> false, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1'); SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID SYS_NC00014$ 9947366 0 HYBRID
Now the CBO has the necessary statistics by which to determine a much more accurate cardinality estimate for the function-based predicate and so potentially a more efficient execution plan:
SQL> select * from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2675555529
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | DAVID_UPPER_NAME_I | 1 | | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1256 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
With the virtual column statistics in place, the CBO now has the cardinality estimate of 1 and associated costs spot on, which is always a good thing.
This requirement to collect the necessary statistics on the associated virtual column created as a result of the function-based index to ensure the index is costed and used effectively is perhaps but one reason why function-based indexes are currently not supported by Automatic Indexing.
As always, this can always change in the future…
Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud) February 3, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Automatic Table Statistics, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Histograms, Invisible Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Statistics, Oracle19c.2 comments
There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article.
The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has the key column CODE with a selectivity that would likely make it too expensive to be accessed via an associated index.
SQL> create table pink_floyd (id number, code number, create_date date, name varchar2(42)); Table created. SQL> insert into pink_floyd select rownum, ceil(dbms_random.value(0, 5000)), sysdate-mod(rownum, 50000)+1, 'Dark Side of the Moon' from dual connect by level <=10000000; 10000000 rows created. SQL> commit; Commit complete.
Importantly, I’ll next collect statistics on this table using all the default attributes, including allowing Oracle to decide the merits of any column histogram:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'PINK_FLOYD'); PL/SQL procedure successfully completed.
Note I’ve yet to run a single query against this table. And yet, if we look at the details of each of these columns:
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='PINK_FLOYD'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- ID 9705425 0 HYBRID CODE 4835 .00005 HYBRID CREATE_DATE 50357 .00002 HYBRID NAME 1 4.9639E-08 FREQUENCY
All the columns have a histogram !! This despite the columns not meeting either criteria normally required for a histogram, that the column be used in a SQL predicate AND for the column to have an uneven distribution of values.
None of these columns have yet to be used in a filtering predicate and none of these columns have a uneven distribution of values, even the CODE column as highlighted by looking at the minimum and maximum number of occurrences:
SQL> select min(code_count), max(code_count) from (select count(*) code_count from pink_floyd group by code);
MIN(CODE_COUNT) MAX(CODE_COUNT)
--------------- ---------------
1845 2163
So it’s very odd for these histograms to be present.
If we run the following query with a filtering predicate based on the CODE column:
SQL> select * from pink_floyd where code=42;
2012 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1152280033
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
63655 consistent gets
63645 physical reads
0 redo size
38575 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2012 rows processed
The CBO currently has no choice but to use a FTS with no index currently present. But what will Automatic Indexing make of things? If we look at the next automatic indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 2 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 134.22 MB (0 B / 134.22 MB) Indexes dropped : 0 SQL statements verified : 1 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | PINK_FLOYD | * SYS_AI_dp2t0j12zux49 | CODE | B-TREE | NONE | ---------------------------------------------------------------------------- -------------------------------------------------------------------------------
We notice that Oracle has created an Automatic Index, but it’s an INVISIBLE index !!
If we look at the details of this Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='PINK_FLOYD'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dp2t0j12zux49 YES NO INVISIBLE ADVANCED LOW VALID 10000000 15369 9845256
The index is in an INVISIBLE/VALID state, not the usual INVISIBLE/UNUSABLE state for an index for which Automatic Indexing decides an index is not efficient enough to be implement.
This is NOT expected behaviour.
Usually INVISIBLE/VALID indexes are created when Automatic Indexing is in “REPORT ONLY” mode, although I have come across this scenario when statistics are stale or missing. But in this case, Automatic Indexing is in “IMPLEMENT” mode and the table has recently collected statistics, albeit with odd histograms present (hence why I think these issues to be related).
If we run the same query again:
SQL> select * from pink_floyd where code=42;
2012 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1152280033
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
63655 consistent gets
63645 physical reads
0 redo size
38575 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2012 rows processed
The CBO has again no option but to use the FTS as Invisible indexes can not be considered by the CBO. However, it’s important to note that such an index would not be used by the CBO anyways as it would be deemed too expensive to use than the current FTS.
If you’re relying on Automatic Indexing and have it in Implement mode, I would recommend checking for any indexes in this INVISIBLE/VALID state as they’re an indication that something has very likely gone wrong…
Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning) January 27, 2021
Posted by Richard Foote in 19c, 19c New Features, Advanced Index Compression, Autonomous Database, Autonomous Transaction Processing, AUTO_INDEX_COMPRESSION, Exadata, Index Column Order, Index Compression, Oracle, Oracle Blog, Oracle General, Oracle Indexes, Oracle19c.add a comment
I was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in need of a couple of amendments.
Initially when Automatic Indexing was released, the ability to set Advanced Compression was NOT included in the official documentation, although the EXEC DBMS_AUTO_INDEX.CONFIGURE( ‘AUTO_INDEX_COMPRESSION‘ , ‘ON’); option was readily accessible. This has now been fixed and the associated doco on setting Advanced Compression for Automatic Indexes can be found here.
The other significant change is that Advanced Compression Low is now the default behaviour when Automatic Indexes are created in the Oracle ATP Autonomous Database Cloud environment. This makes sense in that if you have access to the Advanced Compression option, setting all indexes to Advanced Compression Low is the no-brainer setting as I’ve discussed previously. So several of my more recent posts show how Automatic Indexes have been created with Advanced Compression Low set.
What hasn’t changed however is how Automatic Indexing does NOT consider the efficiency of an index in relation to Index Compression when deciding how to order the columns within the index.
The default order of columns within an index (when other SQL predicates are not a consideration) is simply the order by which the columns appear within the table. Even though an index could be significantly smaller thanks to Index Compression if columns with more repeated values are ordered first within an index, this is not something Automatic Indexing currently considers.
The demo in my original piece still works exactly the same in the current 19c database versions of the ATP Autonomous Cloud environments. Manually created indexes can be significantly smaller if index columns are reordered or dropped entirely if they don’t provide filtering benefits.
When reading my blog, please do take note of the date of blog piece, especially in relation to Automatic Indexing. Things are only accurate as at time of publication and may change subsequently.
I thank Rajeshwaran for getting me to pull my finger out and update my blog accordingly…
Richard Foote's Oracle Blog 