oracle 19c新特性自動(dòng)索引
發(fā)布于：2021-02-09 09:05:20
自動(dòng)索引是Oracle 19c中的一項(xiàng)新功能，它根據(jù)應(yīng)用程序工作負(fù)載自動(dòng)在數(shù)據(jù)庫(kù)中創(chuàng)建、重建和刪除索引。
索引管理任務(wù)現(xiàn)在由數(shù)據(jù)庫(kù)本身通過(guò)每15分鐘在后臺(tái)執(zhí)行一次的任務(wù)來(lái)動(dòng)態(tài)執(zhí)行。
自動(dòng)索引任務(wù)分析當(dāng)前工作負(fù)載并確定索引的候選對(duì)象。
然后，它將索引創(chuàng)建為不可見(jiàn)的索引，并計(jì)算已識(shí)別的候選SQL語(yǔ)句。如果性能得到改善，那么索引就變得可見(jiàn)，然后應(yīng)用程序就可以使用它。如果性能沒(méi)有改善，則索引將被標(biāo)記為不可用，并在預(yù)定義的時(shí)間間隔后丟棄。
自動(dòng)索引功能通過(guò)DBMS_AUTO_INDEX包進(jìn)行管理。
請(qǐng)注意，此功能目前僅在Oracle Engineered Systems平臺(tái)上可用。
讓我們看一看Oracle 19c新功能的一個(gè)示例。
為演示模式啟用自動(dòng)索引，但創(chuàng)建任何新的自動(dòng)索引時(shí)僅將其作為不可見(jiàn)索引。
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.
我們對(duì)一個(gè)有2000萬(wàn)行的表運(yùn)行了一些查詢–該表當(dāng)前沒(méi)有索引。
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
..
..
通過(guò)DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函數(shù)獲取有關(guān)自動(dòng)索引操作的信息。
由于已使用REPORT選項(xiàng)配置了自動(dòng)索引，因此將索引創(chuàng)建為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 )
...
...
...
通過(guò)查看生成的自動(dòng)索引報(bào)告，我們現(xiàn)在可以配置自動(dòng)索引，將任何新的自動(dòng)索引創(chuàng)建為可見(jiàn)索引，以便在SQL語(yǔ)句中使用。
我們可以分配一個(gè)專用的表空間來(lái)存儲(chǔ)將要?jiǎng)?chuàng)建的任何自動(dòng)索引，我們還可以在表空間中規(guī)定一個(gè)配額，用于創(chuàng)建任何自動(dòng)索引。
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.
我們現(xiàn)在再次對(duì)2000萬(wàn)行MYSALES表執(zhí)行相同的查詢。
現(xiàn)在，一個(gè)新的索引已經(jīng)自動(dòng)創(chuàng)建——請(qǐng)注意執(zhí)行計(jì)劃。
索引也已在為自動(dòng)索引分配的表空間中創(chuàng)建。
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
生成有關(guān)自動(dòng)索引的報(bào)告（默認(rèn)為過(guò)去24小時(shí)）。
現(xiàn)在請(qǐng)注意，報(bào)告顯示索引已創(chuàng)建為可見(jiàn)索引。
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 )
作者介紹
熱門博客推薦
