Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part III (Do Anything You Say) October 8, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Access Path, Index statistics, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Stale Statistics.trackback
In Part I of this series, we saw how Automatic Indexing will not create a viable Automatic Index if there are stale or missing statistics on the underlining segments. In Part II we saw how these SQL statements effectively become blacklisted and when segment statistics are subsequently collected, Automatic Indexing will still not create viable Automatic Indexes when the SQL statements are re-run.
So how do we get Automatic Indexing to now kick in and create necessary indexes on these problematic SQLs?
As I’ve discussed previously in relation to blacklisted SQLs, we need to run a NEW SQL statement that hasn’t been blacklist that will result in a necessary index to be created. An easy way to do this is just to include a new comment within the previous SQL to give the SQL a new signature.
If we now run the following “new” SQL statement (identical to the problematic SQL but with a comment embedded):
SQL> select /* new */ * from bowie_stale where code=42; ID CODE NAME ---------- ---------- ------------------------------------------ 1000041 42 David Bowie 6000041 42 David Bowie 41 42 David Bowie 3000041 42 David Bowie 7000041 42 David Bowie 8000041 42 David Bowie 4000041 42 David Bowie 9000041 42 David Bowie 5000041 42 David Bowie 2000041 42 David Bowie
If we now wait to see what the next Automatic Indexing task makes of things:
SQL> select dbms_auto_index.report_last_activity('text', 'ALL', 'ALL' ) report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 07-JUL-2020 06:34:49 Activity end : 07-JUL-2020 06:35:54 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) : 142.61 MB (142.61 MB / 0 B) Indexes dropped : 0 SQL statements verified : 1 SQL statements improved (improvement factor) : 1 (19787.7x) SQL plan baselines created : 0 Overall improvement factor : 19787.7x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- 1. The following indexes were created: *: invisible ------------------------------------------------------------------------------- --------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------- | BOWIE | BOWIE_STALE | SYS_AI_300kk2unp8tr0 | CODE | B-TREE | NONE | --------------------------------------------------------------------------- -------------------------------------------------------------------------------
We see that the index on the CODE column (SYS_AI_300kk2unp8tr0) has now been created.
Further down the report:
VERIFICATION DETAILS ------------------------------------------------------------------------------- The performance of the following statements improved: ------------------------------------------------------------------------------- Parsing Schema Name : BOWIE SQL ID : du6psd0xmzpg5 SQL Text : select /* new */ * from bowie_stale where code=42 Improvement Factor : 19787.7x Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 137261 2620 CPU Time (s): 84621 1769 Buffer Gets: 277028 13 Optimizer Cost: 544 13 Disk Reads: 275947 2 Direct Writes: 0 0 Rows Processed: 70 10 Executions: 7 1
A new index was indeed created because of this new SQL statement, with a performance improvement of 19787.7x.
Further down the report to the Plans Section:
PLANS SECTION --------------------------------------------------------------------------------------------- - Original ----------------------------- Plan Hash Value : 65903426 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 544 | | | 1 | TABLE ACCESS STORAGE FULL | BOWIE_STALE | 10 | 230 | 544 | 00:00:01 | ----------------------------------------------------------------------------------- Notes ----- - dop = 1 - px_in_memory_imc = no - px_in_memory = no - With Auto Indexes ----------------------------- Plan Hash Value : 2558864466 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 230 | 13 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_STALE | 10 | 230 | 13 | 00:00:01 | | * 2 | INDEX RANGE SCAN | SYS_AI_300kk2unp8tr0 | 10 | | 3 | 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("CODE"=42) Notes ----- - Dynamic sampling used for this statement ( level = 11 )
We can see that the new plan using the new Automatic Index with a much lower CBO cost.
If we now look at the status of this index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_STALE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --- --------- ------------- -------- ---------- ----------- ----------------- BOWIE_STALE_PK NO YES VISIBLE DISABLED VALID 10000000 20164 59110 SYS_AI_300kk2unp8tr0 YES NO VISIBLE ADVANCED LOW VALID 10000000 16891 10000000
We see that the index is now both VISIBLE and VALID (previously, it was INVISIBLE and UNUSABLE).
As such, the Automatic Index can now potentially be used by any SQL, including the previous problematic query.
So with a viable index now in place, if we re-run the initial problematic query:
SQL> select * from bowie_stale where code=42; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2558864466 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 230 | 14 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_STALE | 10 | 230 | 14 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_AI_300kk2unp8tr0 | 10 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODE"=42) Note ----- - automatic DOP: Computed Degree of Parallelism is 1 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 738 bytes sent via SQL*Net to client 361 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
We see that finally, the SQL uses the new Automatic Index and is indeed much more efficient as a result, with just 14 consistent gets required (when previously it was 39430 consistent gets).
So if ever you come across the scenario where an SQL does not have an Automatic Index created when clearly it should, it could be that it has been blacklisted and needs a different SQL to actually generate the necessary index.
To avoid some of these issues, make sure you do not have stale or missing statistics when reliant on Automatic Indexing. The new High Frequency Statistics Collection capability to designed to specifically avoid such a scenario.
In REPORT only, if it suggest an Automatic Index(invisible) how can we make it visible (in-use). I don’t want to use Implement option. It should be always REPORT only in the Database.
LikeLike
Hi Ashu
The answer is not easily as you can alter the visibility of an Auto Index.
An option would be to record the details, drop the Auto Index and then create the index again manually.
LikeLike