中文字幕一区二区人妻电影,亚洲av无码一区二区乱子伦as ,亚洲精品无码永久在线观看,亚洲成aⅴ人片久青草影院按摩,亚洲黑人巨大videos

oracle 19c新特性自動索引

發(fā)布于：2021-02-09 09:05:20

0

189

0

oracle oracle 19c 自動索引 數(shù)據(jù)庫

自動索引是Oracle 19c中的一項新功能，它根據(jù)應用程序工作負載自動在數(shù)據(jù)庫中創(chuàng)建、重建和刪除索引。

索引管理任務現(xiàn)在由數(shù)據(jù)庫本身通過每15分鐘在后臺執(zhí)行一次的任務來動態(tài)執(zhí)行。

自動索引任務分析當前工作負載并確定索引的候選對象。

然后，它將索引創(chuàng)建為不可見的索引，并計算已識別的候選SQL語句。如果性能得到改善，那么索引就變得可見，然后應用程序就可以使用它。如果性能沒有改善，則索引將被標記為不可用，并在預定義的時間間隔后丟棄。

自動索引功能通過DBMS_AUTO_INDEX包進行管理。

請注意，此功能目前僅在Oracle Engineered Systems平臺上可用。

讓我們看一看Oracle 19c新功能的一個示例。

為演示模式啟用自動索引，但創(chuàng)建任何新的自動索引時僅將其作為不可見索引。

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函數(shù)獲取有關自動索引操作的信息。

由于已使用REPORT選項配置了自動索引，因此將索引創(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 )

...
...
...

通過查看生成的自動索引報告，我們現(xiàn)在可以配置自動索引，將任何新的自動索引創(chuàng)建為可見索引，以便在SQL語句中使用。

我們可以分配一個專用的表空間來存儲將要創(chuàng)建的任何自動索引，我們還可以在表空間中規(guī)定一個配額，用于創(chuà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)在再次對2000萬行MYSALES表執(zhí)行相同的查詢。

現(xiàn)在，一個新的索引已經自動創(chuàng)建——請注意執(zhí)行計劃。

索引也已在為自動索引分配的表空間中創(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

生成有關自動索引的報告（默認為過去24小時）。

現(xiàn)在請注意，報告顯示索引已創(chuàng)建為可見索引。

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 )