oracle 19c新特性自动索引
发布于：2021-02-09 09:05:20
自动索引是Oracle 19c中的一项新功能，它根据应用程序工作负载自动在数据库中创建、重建和删除索引。
索引管理任务现在由数据库本身通过每15分钟在后台执行一次的任务来动态执行。
自动索引任务分析当前工作负载并确定索引的候选对象。
然后，它将索引创建为不可见的索引，并计算已识别的候选SQL语句。如果性能得到改善，那么索引就变得可见，然后应用程序就可以使用它。如果性能没有改善，则索引将被标记为不可用，并在预定义的时间间隔后丢弃。
自动索引功能通过DBMS_AUTO_INDEX包进行管理。
请注意，此功能目前仅在Oracle Engineered Systems平台上可用。
让我们看一看Oracle 19c新功能的一个示例。
为演示模式启用自动索引，但创建任何新的自动索引时仅将其作为不可见索引。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','DEMO',TRUE);
PL/SQL procedure successfully completed.
我们对一个有2000万行的表运行了一些查询–该表当前没有索引。
SQL> conn demo/demo
Connected.
SQL> select * from mysales where id=4711;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4711 4712 Samsung Galaxy S7 1 711 5000
08-JUL-19 14-JAN-05
SQL> select * from mysales where id=4713;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4713 4714 Samsung Galaxy S7 3 713 5000
08-JUL-19 16-JAN-05
SQL> select * from mysales where id=4715;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4715 4716 Samsung Galaxy S7 0 715 5000
08-JUL-19 18-JAN-05
..
..
通过DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函数获取有关自动索引操作的信息。
由于已使用REPORT选项配置了自动索引，因此将索引创建为INVISIBLE索引。
SQL> SET LONG 1000000 PAGESIZE 0
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 08-JUL-2019 11:05:20
Activity end : 09-JUL-2019 11:05:20
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (0 / 1)
Space used (visible / invisible) : 394.26 MB (0 B / 394.26 MB)
Indexes dropped : 0
SQL statements verified : 14
SQL statements improved (improvement factor) : 14 (167664.6x)
SQL plan baselines created : 0
Overall improvement factor : 167664.6x
-------------------------------------------------------------------------------
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 |
----------------------------------------------------------------------
| DEMO | MYSALES | SYS_AI_bmqt0qthw74kg | ID | B-TREE | NONE |
----------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : DEMO
SQL ID : 06wuaj97jms49
SQL Text : select * from mysales where id=4713
Improvement Factor : 167667x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 379501 3634
CPU Time (s): 377495 854
Buffer Gets: 167667 4
Optimizer Cost: 45698 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 1 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3597614299
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45698 | |
| 1 | TABLE ACCESS STORAGE FULL | MYSALES | 1 | 56 | 45698 | 00:00:02 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 2047064025
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | By
tes | Cost | Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 1 |
56 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MYSALES | 1 |
56 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 |
| 3 | 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4713)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
...
...
...
通过查看生成的自动索引报告，我们现在可以配置自动索引，将任何新的自动索引创建为可见索引，以便在SQL语句中使用。
我们可以分配一个专用的表空间来存储将要创建的任何自动索引，我们还可以在表空间中规定一个配额，用于创建任何自动索引。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','TEST_IND');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
我们现在再次对2000万行MYSALES表执行相同的查询。
现在，一个新的索引已经自动创建——请注意执行计划。
索引也已在为自动索引分配的表空间中创建。
SQL> select * from mysales where id=4711;
4711 4712 Samsung Galaxy S7 1 711 5000 08-JUL-19 14-JAN-05
SQL> select * from table (dbms_xplan.display_cursor);
SQL_ID fc177w86zpdbb, child number 1
-------------------------------------
select * from mysales where id=4711
Plan hash value: 2047064025
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES | 1 | 56 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=4711)
SQL> select owner,tablespace_name from dba_indexes
where index_name='SYS_AI_bmqt0qthw74kg';
OWNER
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
DEMO
TEST_DATA
生成有关自动索引的报告（默认为过去24小时）。
现在请注意，报告显示索引已创建为可见索引。
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 08-JUL-2019 11:41:53
Activity end : 09-JUL-2019 11:41:53
Executions completed : 6
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 394.26 MB (394.26 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 14
SQL statements improved (improvement factor) : 14 (167664.6x)
SQL plan baselines created : 0
Overall improvement factor : 167664.6x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------
| DEMO | MYSALES | SYS_AI_bmqt0qthw74kg | ID | B-TREE | NONE |
----------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : DEMO
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
SQL ID : 06wuaj97jms49
SQL Text : select * from mysales where id=4713
Improvement Factor : 167667x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
---------------------------- ----------------------------
Elapsed Time (s): 379501 3634
CPU Time (s): 377495 854
Buffer Gets: 167667 4
Optimizer Cost: 45698 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 1 1
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3597614299
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45698 | |
| 1 | TABLE ACCESS STORAGE FULL | MYSALES | 1 | 56 | 45698 | 00:00:02 |
--------------------------------------------------------------------------------
- With Auto Indexes
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 2047064025
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | By
tes | Cost | Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 1 |
56 | 4 | 00:00:01 |
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MYSALES | 1 |
56 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 |
| 3 | 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4713)
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
作者介绍
