Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: February 14, 2022 - 2:39 am UTC

Last updated: September 05, 2022 - 5:47 am UTC

Version: 19.13

Viewed 1000+ times

You Asked

Team,

got an Oracle 19c (19.13) on Exacc platform to play with Auto Index (AI) feature.
one question that i had myself is how many (repeated) executions does Oracle need to create AI.

based on the below execution, can we confirm that sqls executed for one time dont benefit from AI.
However repeated sql executions are the potential candidate for AI ?
is that correct or do you see any flaw in this below test case?

Also why we got "Dynamic sampling used for this statement ( level = 11 )" in the "Notes" section of the newly created AI.

ai_demo@PDB19> select parameter_name, parameter_value from dba_auto_index_config;

PARAMETER_NAME                      PARAMETER_VALUE
----------------------------------- ------------------------------
AUTO_INDEX_COMPRESSION              ON
AUTO_INDEX_DEFAULT_TABLESPACE       TS_INDEX_DEMO
AUTO_INDEX_MODE                     IMPLEMENT
AUTO_INDEX_REPORT_RETENTION         373
AUTO_INDEX_RETENTION_FOR_AUTO       373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA                   schema IN (AI_DEMO)
AUTO_INDEX_SPACE_BUDGET             100

8 rows selected.

ai_demo@PDB19> create table demo003
  2  nologging as
  3  select rownum as id, mod(rownum,1000000)+1 as code , a.*
  4  from all_objects a ,
  5        all_users b
  6  where rownum <= 10000000;

Table created.

ai_demo@PDB19> alter table demo003
  2  add constraint demo003_pk
  3  primary key(id);

Table altered.

ai_demo@PDB19> select sum(code) from demo003 where code = 42;

SUM(CODE)
----------
       126

ai_demo@PDB19> col report for a180
ai_demo@PDB19> select dbms_auto_index.report_activity() report from dual;

REPORT
-----------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 08-FEB-2022 00:43:41
Activity end                 : 09-FEB-2022 00:43:41
Executions completed         : 59
Executions interrupted       : 0
Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                              : 3
Indexes created (visible / invisible)         : 2 (2 / 0)
Space used (visible / invisible)              : 94.37 MB (94.37 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 2
SQL statements improved (improvement factor)  : 2 (56345.8x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 56345.8x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Owner   | Table | Index                | Key  | Type   | Properties |
-----------------------------------------------------------------------
| AI_DEMO | T     | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
| AI_DEMO | T1    | SYS_AI_c792bqgaq0jrq | CODE | B-TREE | NONE       |
-----------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name  : AI_DEMO
SQL ID               : 2qrgt53b0ww5m
SQL Text             : select sum(code) from t where code = 42
Improvement Factor   : 56332.2x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
Elapsed Time (s):  4799294                       41725
CPU Time (s):      2547297                       1653
Buffer Gets:       337994                        3
Optimizer Cost:    15390                         3
Disk Reads:        0                             5
Direct Writes:     0                             0
Rows Processed:    6                             1
Executions:        6                             1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 2966233522
------------------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost  | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |      |      |       | 15390 |          |
|  1 |   SORT AGGREGATE             |      |    1 |     5 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | T    |    3 |    15 | 15390 | 00:00:01 |
------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value  : 3013999228
-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |     5 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |     5 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_41fz8aw5b9rxu |    3 |    15 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42)
-------------------------------------------------------------------------------
Parsing Schema Name  : AI_DEMO
SQL ID               : a7mquy9qsytah
SQL Text             : select sum(code) from t1 where code = 42
Improvement Factor   : 56359.3x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
Elapsed Time (s):  1816988                       5486
CPU Time (s):      1144891                       1302
Buffer Gets:       169079                        3
Optimizer Cost:    15390                         3
Disk Reads:        1                             5
Direct Writes:     0                             0
Rows Processed:    3                             1
Executions:        3                             1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 3724264953
------------------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost  | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |      |      |       | 15390 |          |
|  1 |   SORT AGGREGATE             |      |    1 |     5 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | T1   |    3 |    15 | 15390 | 00:00:01 |
------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value  : 2380736383

-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |     5 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |     5 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_c792bqgaq0jrq |    3 |    15 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42)


Notes
-----
- Dynamic sampling used for this statement ( level = 11 )


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------


ai_demo@PDB19> $ timeout /T 1800

ai_demo@PDB19> select dbms_auto_index.report_activity() report from dual;

REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 08-FEB-2022 01:13:43
Activity end                 : 09-FEB-2022 01:13:43
Executions completed         : 61
Executions interrupted       : 0
Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                              : 3
Indexes created (visible / invisible)         : 2 (2 / 0)
Space used (visible / invisible)              : 94.37 MB (94.37 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 2
SQL statements improved (improvement factor)  : 2 (56345.8x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 56345.8x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Owner   | Table | Index                | Key  | Type   | Properties |
-----------------------------------------------------------------------
| AI_DEMO | T     | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
| AI_DEMO | T1    | SYS_AI_c792bqgaq0jrq | CODE | B-TREE | NONE       |
-----------------------------------------------------------------------
-------------------------------------------------------------------------------

ai_demo@PDB19> 
ai_demo@PDB19> col owner for a10
ai_demo@PDB19> col index_name for a25
ai_demo@PDB19> col tablespace_name for a25
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2        status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO003'
  5  and owner ='AI_DEMO' ;

OWNER      INDEX_NAME                TABLESPACE_NAME           COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- ------------------------- ------------------------- ------------- -------- --------- ------- ---
AI_DEMO    DEMO003_PK                TS_INDEX_DEMO             DISABLED      VALID    VISIBLE   FULL    NO

ai_demo@PDB19> 
ai_demo@PDB19> select sum(code) from demo003 where code = 42;

SUM(CODE)
----------
       126

ai_demo@PDB19> $ timeout /T 1800
ai_demo@PDB19> select dbms_auto_index.report_activity() report from dual;

REPORT
------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 08-FEB-2022 01:43:43
Activity end                 : 09-FEB-2022 01:43:43
Executions completed         : 63
Executions interrupted       : 0
Executions with fatal error  : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                              : 4
Indexes created (visible / invisible)         : 3 (3 / 0)
Space used (visible / invisible)              : 141.56 MB (141.56 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 3
SQL statements improved (improvement factor)  : 3 (56352x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 56352x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Owner   | Table   | Index                | Key  | Type   | Properties |
-------------------------------------------------------------------------
| AI_DEMO | DEMO003 | SYS_AI_96xnrbxzh2saf | CODE | B-TREE | NONE       |
| AI_DEMO | T       | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
| AI_DEMO | T1      | SYS_AI_c792bqgaq0jrq | CODE | B-TREE | NONE       |
-------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
Parsing Schema Name  : AI_DEMO
SQL ID               : bj8ugjtmvg9vw
SQL Text             : select sum(code) from demo003 where code = 42
Improvement Factor   : 56364.5x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
Elapsed Time (s):  505320                        1437
CPU Time (s):      489916                        930
Buffer Gets:       112730                        3
Optimizer Cost:    15390                         3
Disk Reads:        0                             5
Direct Writes:     0                             0
Rows Processed:    2                             1
Executions:        2                             1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
Plan Hash Value  : 1530605218

---------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost  | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |      |       | 15390 |          |
|  1 |   SORT AGGREGATE             |         |    1 |     5 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO003 |    3 |    15 | 15390 | 00:00:01 |
---------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value  : 454853808

-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |     5 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |     5 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_96xnrbxzh2saf |    1 |     5 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42)


Notes
-----
- Dynamic sampling used for this statement ( level = 11 )


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------


ai_demo@PDB19> 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2        status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO003'
  5  and owner ='AI_DEMO' ;

OWNER      INDEX_NAME                TABLESPACE_NAME           COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- ------------------------- ------------------------- ------------- -------- --------- ------- ---
AI_DEMO    DEMO003_PK                TS_INDEX_DEMO             DISABLED      VALID    VISIBLE   FULL    NO
AI_DEMO    SYS_AI_96xnrbxzh2saf      TS_INDEX_DEMO             ADVANCED LOW  VALID    VISIBLE   FULL    YES

ai_demo@PDB19>



and Connor said...

Thanks for your patience. Took me a while to get some time on an Exa machine.

The algorithm on how we decide on an auto index is not public (I'm not "hiding it", I do not know what it is :-)) but single execution is definitely not a factor. eg

SQL> create table autind.t2 pctfree 0
  2  as select rownum r, d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 350 );

Table created.

--
-- single query
--

SQL>
SQL> select * from   autind.t2
  2  where r = 1231232;

         R OWNER                          OBJECT_NAME
---------- ------------------------------ ----------------------------------------
SUBOBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
---------- -------------- ----------------------- --------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------------------------------------------------------------------------------------------------
SHARING            E O A
------------------ - - -
DEFAULT_COLLATION                                                                                    D S CREATED_APPID
---------------------------------------------------------------------------------------------------- - - -------------
CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
------------- -------------- --------------
   1231232 PUBLIC                         AWR_PDB_METRIC_NAME

     12147                SYNONYM                 27-JAN-20 27-JAN-20 2020-01-27:22:22:26 VALID   N N N          1

METADATA LINK      N Y N
                                                                                                     N N



SQL>
SQL>
SQL>   @atab
Enter value for table_name: t2

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ------------ ----------- ---------
T2                               20320650     448435            0         150 01-APR-22

--
-- waited a while
--
SQL> select (sysdate - created)*86400
  2  from dba_objects
  3  where object_name = 'T2';

(SYSDATE-CREATED)*86400
-----------------------
                   1497

--
-- and an index came along
--

SQL> @indcol
Enter value for table_name: t2
Enter value for index_name:

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------
T2                             SYS_AI_adf7xgbu4fqh6           R


Perhaps try with your table being larger?

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

need for Dynamic Sampling level = 11

Rajeshwaran Jeyabal, April 01, 2022 - 4:37 am UTC

Thanks for clarification, but the other question i have is "why we got "Dynamic sampling used for this statement ( level = 11 )" in the "Notes" section of the newly created AI." ?

since the newly created index got all the index level statistics, what is the need for the "Dynamic sampling" during the execution using the newly created AI.
Chris Saxon
April 04, 2022 - 12:54 pm UTC

The Automatic Indexing process uses level 11 to determine which indexes to create. Also note that level 11 is a bit different to the lower levels:

The database uses adaptive dynamic sampling automatically when the optimizer deems it necessary.

How about a docs link?

Rajeshwaran Jeyabal, April 04, 2022 - 1:51 pm UTC

can you point me to the docs link, where we could get more insights about the above details?
Connor McDonald
April 05, 2022 - 5:20 am UTC

Well, its called automatic indexing for a reason :-)

But here's the docs

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html

AI for Global partitioned index...

Rajeshwaran Jeyabal, September 01, 2022 - 12:10 pm UTC

Team,

lets say we got a table like this.
create table t(x number,y date,.....)
partition by hash(x)
( partition p1, partition p2 );

when we run queries of this type 
select * from t where y = ??
- Automatic indexing will create global non-partitioned index.

when we run queries of this type 
select * from t where y = ?? and x = ??
-- Automatic indexing will create create local index.

so what kind of queries should we need to run to help Automatic indexing to create "Global Partitioned index" ?

Connor McDonald
September 05, 2022 - 5:47 am UTC

global partitioned indexes are a fairly niche requirement. I've almost never seen one exception for reducing ascending key contention.

I'd be very surprised to see AI build one because it would almost impossible to make a decision as to why it would surpass a non-partitioned equivalent without detailed knowledge of the business requirements
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.