oracle 19c新特性自动索引

发布于：2021-02-09 09:05:20

0

165

0

oracle oracle 19c 自动索引 数据库

自动索引是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 )

作者介绍

城南花已开

博客

31

获赞

1

浏览

11300

热门博客推荐