### Oracle19C 自動索引初探

##### Automatic indexing 提供如下功能
- 定期在預定義的時間間隔內在後臺執行自動索引過程
- 分析應用程式工作負載，並根據分析報告相應地建立必要的新的索引，並刪除現有效能不佳的索引，以提高資料庫效能
- 重構由於表分割槽維護操作(比如ALTER table MOVE)而被標記為不可用的索引(unusable index)
- 提供PL/SQLAPIs，用於配置資料庫中的自動索引和生成與自動索引操作相關的報告。

##### 說明：
- 當前版本Auto indexes 是local B-tree 索引
- 支援分割槽和非分割槽表，不支援臨時表。

#### Automatic indexing 是如何工作的

自動索引基於手動SQL調優的常用方法，它不斷評估執行的SQL和基礎表，以確定要建立哪些索引以及可能刪除哪些索引。它透過專家系統完成此任務，該系統驗證索引可能做出的改進，並在建立之後驗證所做的假設。然後它使用強化學習來確保它不再犯同樣的錯誤。最重要的是，隨著資料模型和訪問路徑的變化，Oracle資料庫19c能夠隨著時間的推移進行調整。

#####  自動索引過程以後臺服務程式每隔15分鐘執行一次，並執行如下任務：
- 識別自動索引候選項

> 透過捕獲SQL歷史到SQL repository（包括SQL，執行計劃，繫結變數，統計資訊等），識別SQL語句中用到列的使用情況來標識自動索引候選項。

- 為自動索引候選項建立invisible的自動索引

>  Invisible Indexes是11g中提出的新功能，預設對最佳化器是不可見的，也就是說不會影響到使用者既有的SQL語句。

- 根據SQL語句驗證不可見的自動索引

> 如果透過使用這些自動索引提高了SQL語句的效能，那麼會將這些索引配置為可見索引，以便在SQL語句中使用它們。
如果使用這些索引不能提高SQL語句的效能，則將這些索引置為unusable的索引，並將對應的SQL語句列入“黑名單”。unusable的索引稍後會被自動索引過程刪除。被列入“黑名單”的SQL語句將來不允許使用自動索引。

- 清除unused auto indexes

>  對於長時間不使用的auto indexes會自動進行清除工作。預設是373天，可以使用DBMS_AUTO_INDEX.CONFIGURE過程來配置資料庫中保留未使用的自動索引的時間段。

#### 在資料庫中配置AutomaticIndexing

> 注：Automatic indexing特性對於on-prem環境僅支援Oracle Exadata平臺，對於Oracle Cloud各個Cloud Edition都支援。

需要設定隱含引數，否則會報錯

```
--報錯如下：
SQL>  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;

*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9450
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 295
ORA-06512: at line 1

--設定隱含引數，並重啟資料庫
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate
startup
```

##### 啟用和禁用Automatic Indexing特性

```
--啟用自動索引，並將任何新的自動索引建立為可見索引(visible indexes)，以便在SQL語句中使用
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
 
--啟用自動索引，但將任何新的自動索引建立為不可見索引(invisible indexes)，所以不能在SQL語句中使用
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

--禁用自動索引，這樣就不會建立新的自動索引，並禁用現有的自動索引。這也是預設模式。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
```

##### 指定哪些Schemas可以使用Automatic Indexing

當在上一步啟用Automatic Indexing特性後，預設情況下所有的Schemas都可以使用Automatic Indexing。管理員可以根據需要允許哪些schema使用自動索引。eg:

```
--eg: SH
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);

--後期如果又想讓某個使用者使用自動索引，比如HR，可以透過如下語句來設定
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

--恢復到預設（也就是所有Schema都可以使用自動索引）
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);
```

##### 為unused auto indexes指定保留期

```
--下面的語句將未使用的自動索引的保留期設定為90天。 
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', ' 90');

--下面的語句將未使用的自動索引的保留期重置為預設值373天。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

```

##### 為unused 非自動索引（non-auto indexes）指定保留期
就是為我們手動建立的unused的索引指定一個保留期，預設情況下，Automatic Indexing Process(自動索引過程)不會刪除unused手動建立的索引。

```
--指定一個unused手動索引保留期，比如60天，那麼到期後，將會被Automatic Indexing Process(自動索引過程)清除。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

--恢復到預設值（也就是不給unused 手動索引指定保留期，Automatic Indexing Process不會刪除這些索引）
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO',NULL);     
```

##### 為automatic indexing logs指定保留期

```
--預設是31天，如下示例表示保留60天
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');

--恢復到預設
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);
```

> 注意：Automatic indexing reports的生成依賴於automatic indexing logs。所以，當過了automatic indexing logs的保留期後，相應的automatic indexing reports就不能生成了

##### 指定一個表空間存放Auto Indexes

```
-- 預設情況下，使用當前資料庫的預設持久表空間。可以透過如果配置指定為其他表空間：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'MYAUTOTBS');

--對於使用預設持久表空間存放Auto Indexes的情況，可以透過類似如下過程來指定一個空間限額（可以使用到20%），預設是50%：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '20');
```

##### 指定一個臨時表空間存放臨時Auto Indexes結構

```
--預設情況下，使用當前資料庫的預設TEMP表空間。可以透過如果配置指定為其他表空間：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TEMP_TABLESPACE', 'MYAUTOTBS_TEMP');
```

#### 生成Automatic Indexing報告

透過DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函式可以生成AutomaticIndexing Reports。

比如生成指定時間範圍的自動索引報告:

```
set serveroutput on

declare  
       report clob := null;
   begin
       report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                   activity_start => TO_TIMESTAMP('2019-02-17', 'YYYY-MM-DD'),
                    activity_end => TO_TIMESTAMP('2019-02-17', 'YYYY-MM-DD'),
                              type => 'HTML',          --支援TEXT、HTML、XML 3中格式，預設是TEXT
                          section => 'SUMMARY',    
                              level => 'BASIC');

dbms_output.put_line(report);
   end;

/
```

生成最近一次的自動索引報告：

```
set serveroutput on

declare
       report clob := null;
   begin
       report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
                               type => 'HTML',
                           section => 'SUMMARY +INDEX_DETAILS +ERRORS',
                               level => 'BASIC');

dbms_output.put_line(report);
  end;

/
```

Automatic Indexing相關的資料字典:

```
DBA_AUTO_INDEX_CONFIG                          --19.1新增檢視，描述當前自動索引的配置
   DBA_INDEXES/ALL_INDEXES/USER_INDEXES   --新增加的AUTO列標識是自動索引(YES)還是手動索引(NO)

DBA_AUTO_INDEX_EXECUTIONS                  --顯示歷史自動索引任務執行
  DBA_AUTO_INDEX_STATISTICS                   --顯示與自動索引相關的統計資訊

DBA_AUTO_INDEX_IND_ACTIONS                --顯示在自動索引上執行的操作

DBA_AUTO_INDEX_SQL_ACTIONS                --顯示在SQL上執行的驗證自動索引的操作
```

#### Automatic Indexing初體驗

##### 檢查自動索引預設配置

```
SQL> /

PARAMETER_NAME				 PARAMETER_VALUE		LAST_MODIFIED					   MODIFIED_BY
---------------------------------------- ------------------------------ -------------------------------------------------- --------------------------------------------------
AUTO_INDEX_COMPRESSION			 OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE 			 IMPLEMENT			08-JUN-20 03.08.17.000000 PM			   SYS
AUTO_INDEX_REPORT_RETENTION		 31
AUTO_INDEX_RETENTION_FOR_AUTO		 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 		 50

8 rows selected.

SQL> 1
  1* select * from dba_auto_index_config
SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

```

##### 準備測試資料

```
alias mytest='sqlplus mytest/test@mypdb'
[oracle@rac19c ~]$ mytest

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 8 15:29:20 2020
Version 19.6.0.0.0

Last Successful login time: Wed Apr 15 2020 16:17:43 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select *from test;

72545 rows created.

SQL> /

145090 rows created.

SQL> /

290180 rows created.

SQL> /

580360 rows created.

SQL> /

1160720 rows created.

SQL> commit;

Commit complete.

SQL> update test set object_id=rownum;

2321440 rows updated.

SQL> commit;

Commit complete.

```

##### 啟用Automatic Indexing

```
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

SQL> set lines 200
set pages 999
col parameter_name for a40
col parameter_value for a15
col last_modified for a40
col modified_by for a20
select * from dba_auto_index_config;SQL> SQL> SQL> SQL> SQL> SQL>

PARAMETER_NAME				 PARAMETER_VALUE LAST_MODIFIED				  MODIFIED_BY
---------------------------------------- --------------- ---------------------------------------- --------------------
AUTO_INDEX_COMPRESSION			 OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE 			 IMPLEMENT	 08-JUN-20 04.11.50.000000 PM		  MYTEST
AUTO_INDEX_REPORT_RETENTION		 31
AUTO_INDEX_RETENTION_FOR_AUTO		 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 		 50

8 rows selected.

```

##### 執行測試SQL

```
select object_name from test where object_id=1;
select object_type from test where object_id=123;
select created from test where object_id=345;
```

##### 檢查Automatic Indexing Process的執行情況（15分鐘以後）

```
--檢視自動索引任務執行情況
col EXECUTION_NAME for a40
col ERROR_MESSAGE for a20
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select * from dba_auto_index_executions;

--檢視該任務與自動索引相關的統計資訊
select * from dba_auto_index_statistics where execution_name='SYS_AI_2020-06-08/16:42:12';

EXECUTION_NAME				 STAT_NAME			    VALUE
---------------------------------------- ----------------------------- ----------
SYS_AI_2020-06-08/16:42:12		 Index candidates			3
SYS_AI_2020-06-08/16:42:12		 Indexes created (visible)		1
SYS_AI_2020-06-08/16:42:12		 Indexes created (invisible)		0
SYS_AI_2020-06-08/16:42:12		 Indexes dropped			0
SYS_AI_2020-06-08/16:42:12		 Space used in bytes		 45088768
SYS_AI_2020-06-08/16:42:12		 Space reclaimed in bytes		0
SYS_AI_2020-06-08/16:42:12		 SQL statements verified		3
SYS_AI_2020-06-08/16:42:12		 SQL statements improved		3
SYS_AI_2020-06-08/16:42:12		 SQL statements managed by SPM		0
SYS_AI_2020-06-08/16:42:12		 SQL plan baselines created		0
SYS_AI_2020-06-08/16:42:12		 Improvement percentage 	      100

--檢視在自動索引上執行的操作
col index_name for a20
col table_name for a20
col command for a20
col statement for a80
set pages 999
select index_name,table_name,command,statement from dba_auto_index_ind_actions where execution_name='SYS_AI_2020-06-08/16:42:12' order by action_id;

--eg:
INDEX_NAME	     TABLE_NAME 	  COMMAND	       STATEMENT
-------------------- -------------------- -------------------- --------------------------------------------------------------------------------
SYS_AI_6q50typ711dvz TESTA_DEP1 	  CREATE INDEX	       CREATE INDEX "DEP1"."SYS_AI_6q50typ711dvz"   ON "DEP1"."TESTA_DEP1"("C1") TABLES
SYS_AI_7371mg6j5bawt TESTB_DEP1 	  CREATE INDEX	       CREATE INDEX "DEP1"."SYS_AI_7371mg6j5bawt"   ON "DEP1"."TESTB_DEP1"("C1") TABLES
SYS_AI_ap6rzrnxmm8k5 TEST		  CREATE INDEX	       CREATE INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   ON "MYTEST"."TEST"("OBJECT_ID") T
SYS_AI_ap6rzrnxmm8k5 TEST		  REBUILD INDEX        ALTER INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   REBUILD  ONLINE
SYS_AI_ap6rzrnxmm8k5 TEST		  ALTER INDEX VISIBLE  ALTER INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   VISIBLE

--檢視測試表上是否建立了索引
select index_name,index_type,tablespace_name,status,auto from dba_indexes where owner='MYTEST' and table_name='TEST';

--eg:
INDEX_NAME	     INDEX_TYPE 		 TABLESPACE_NAME		STATUS	 AUT
-------------------- --------------------------- ------------------------------ -------- ---
SYS_AI_ap6rzrnxmm8k5 NORMAL			 USERS				VALID	 YES

--檢視執行計劃
set autotrace on exp
select object_name from test where object_id=1234;
```

#### 檢查下Automatic Indexing 報告

```
--這裡以html格式輸出,詳細見附件
 set serveroutput on
        declare 
               report clob := null;
        begin
               report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                       activity_start => TO_TIMESTAMP('2020-06-08 16:42:12', 'yyyy-mm-ddhh24:mi:ss'),
                       activity_end => TO_TIMESTAMP('2020-06-08 16:43:10', 'yyyy-mm-dd hh24:mi:ss'),
                                 type => 'HTML', 
                             section => 'ALL', 
                                level => 'ALL');
               dbms_output.put_line(report);
end;
/
```

#### 參考

> https://blog.csdn.net/Hehuyi_In/article/details/90481080
> https://blog.csdn.net/enmotech/article/details/87870944

附：html程式碼輸出介面。

<title>
	  Automatic Index Report

</title>
	<style type="text/css">
	  body, table, input, select,
textarea
	  {font:normal normal 8pt
Verdana,Arial;text-decoration:none;color:#000000;}
	  .s8
{font-size:8pt;color:#006699}
	  .s9 {font-size:10pt;color:#006699}

.s10 {font-size:14pt;color:#006699;}
	  .s16 {border-width : 1px;
border-color : #CCCC99;
	  border-style:
solid;color:#006699;font-size:8pt;}
	  .s17 {border-width : 1px;
border-color : #CCCC99;
	  border-style: solid;font-size:8pt;}

.s27 {border-width : 1px; border-color : #CCCC99; border-style: solid;}

</style>
    
    
	
  
  
	<a name="general" rel="nofollow"></a>
	<a href="http://blog.itpub.net/29487349/viewspace-2765681/#general" style="text-decoration:none" rel="nofollow">

<font class="s10">GENERAL INFORMATION</font>
	</a>
	<hr size="3" width="550" align="left">
	<table class="ex" cellspacing="0" border="0">
	    <tr>
		<th align="left">Activity
start</th>
		<td>: 08-JUN-2020 16:42:12</td>
	    </tr>

<tr>
		<th align="left">Activity end</th>
		<td>:
08-JUN-2020 16:43:10</td>
	    </tr>
	    <tr>
		<th align="left">Executions completed</th>
		<td>: 1</td>

</tr>
	    <tr>
		<th align="left">Executions
interrupted</th>
		<td>: 0</td>
	    </tr>

<tr>
		<th align="left">Executions with fatal error</th>

<a href="http://blog.itpub.net/29487349/viewspace-2765681/#summary-auto" style="text-decoration:none" rel="nofollow">
	    <font class="s10">SUMMARY (AUTO INDEXES)</font>
	</a>
	<hr size="3" width="550" align="left">
	<table class="ex" cellspacing="0" border="0">
	    <tr>
		<th align="left">Index
candidates</th>
		<td ora_space_char="_">
      : 
      3</td>

</tr>
	    <tr>
		<th align="left">Indexes created

(visible
	  / invisible)
      </th>
		<td ora_space_char="_">
      : 
      1
	(1
	  / 
	  0)

</td>
	    </tr>
	    <tr>
		<th align="left">Space
used
	(visible
	  / invisible)
      </th>
		<td ora_space_char="_">
      : 
      45.09_MB
	(45.09_MB
	  /

0_B)
      </td>
	    </tr>
	    <tr>
		<th align="left">Indexes dropped</th>
		<td ora_space_char="_">

: 
      0</td>
	    </tr>
	    <tr>
		<th align="left">SQL statements verified</th>
		<td ora_space_char="_">
      : 
      3</td>
	    </tr>
	    <tr>

<th align="left">SQL statements improved
	(improvement factor)

</th>
		<td ora_space_char="_">
      : 
      3

(59085.3x)
      </td>
	    </tr>
	    <tr>
		<th align="left">SQL plan baselines created</th>
		<td ora_space_char="_">
      : 
      0</td>
	    </tr>
	    <tr>

<th align="left">Overall improvement factor</th>
		<td ora_space_char="_">
      : 
      59085.3x</td>
	    </tr>

</table>
	<hr size="3" width="550" align="left">
	<br>

<a name="summary-nonauto" rel="nofollow"></a>
	<a href="http://blog.itpub.net/29487349/viewspace-2765681/#summary-nonauto" style="text-decoration:none" rel="nofollow">
	    <font class="s10">SUMMARY (MANUAL
INDEXES)</font>
	</a>
	<hr size="3" width="550" align="left">

<th align="left">Unused indexes</th>
		<td ora_space_char="_">

: 
      0</td>
	    </tr>
	    <tr>
		<th align="left">Space used</th>
		<td ora_space_char="_">
      :

0_B</td>
	    </tr>
	    <tr>
		<th align="left">Unusable indexes</th>
		<td ora_space_char="_">

: 
      0</td>
	    </tr>
	</table>
	<hr size="3" width="550" align="left">
	<br>
	
  
	<a name="index_details" rel="nofollow"></a>
	<a href="http://blog.itpub.net/29487349/viewspace-2765681/#index_details" style="text-decoration:none" rel="nofollow">
	    <font class="s10">INDEX
DETAILS</font>
	</a>
	<hr size="3" width="550" align="left">

<font class="s5">  1. The following indexes were created:</font>
	<br>

*: invisible 
	<hr size="3" width="550" align="left">
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>

<th align="left">Owner</th>
		<th align="left">Table</th>

<th align="left">Index</th>
		<th align="left">Key</th>

<th align="left">Type</th>
		<th align="left">Properties</th>

</tr>
	    <tr>
		<td>MYTEST</td>

<td>TEST</td>
		<td>SYS_AI_ap6rzrnxmm8k5</td>

<td>OBJECT_ID</td>
		<td>B-TREE</td>

<td>NONE</td>
	    </tr>
	</table>
	<hr size="3" width="550" align="left">
	<br>
	
  
	<a name="verification_details" rel="nofollow"></a>
	<a href="http://blog.itpub.net/29487349/viewspace-2765681/#verification_details" style="text-decoration:none" rel="nofollow">
	    <font class="s10">VERIFICATION
DETAILS</font>
	</a>
	<hr size="3" width="550" align="left">

<font class="s5">  1. The performance of the following statements
improved:</font>
	<hr size="3" width="550" align="left">
	<table class="ex" cellspacing="0" border="0" ora_widthc="80">
	    <tr>

<th align="left">Parsing Schema Name</th>
		<td>: MYTEST</td>

</tr>
	    <tr>
		<th align="left">SQL ID</th>

<td>: 9nd4vtk5wbjbv</td>
	    </tr>
	    <tr>
		<th align="left">SQL Text</th>
		<td>: select created from test where
object_id=345</td>
	    </tr>
	    <tr>
		<th align="left">Improvement Factor</th>
		<td>: 44916x</td>

</tr>
	</table>
	<br>
	<font class="s7">
	    <b>
Execution Statistics: </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0" ora_widthc="80">
	    <tr>

</font>
		</td>
		<td>

<b>Original Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
		<td>

<b>Auto Index Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
	    </tr>
	    <tr>

<td>
		    <b>Elapsed Time (s):</b>
		</td>

</td>
		<td>219338</td>
		<td>2013</td>

</tr>
	    <tr>
		<td>
		    <b>Buffer
Gets:</b>
		</td>
		<td>44916</td>

<b>Optimizer Cost:</b>
		</td>
		<td>10360</td>

<b>Disk Reads:</b>
		</td>
		<td>0</td>

<b>Direct Writes:</b>
		</td>
		<td>0</td>

<b>Rows Processed:</b>
		</td>
		<td>1</td>

<b>Executions:</b>
		</td>
		<td>1</td>

<font class="s10">
		<br>
		
	PLANS SECTION

</font>
	</a>
	<hr size="3" width="650" align="left">
	<br>
	<font class="s8">
	    <b> -
Original </b>
	</font>
	<hr size="1" width="200" align="left">

<table border="0">
	    <tr>
		<th align="left">Plan Hash
Value </th>
		<td>: 
       1357081020</td>
	    </tr>

</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>
		<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>
	    <tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right"></td>
		<td class="s27" align="right"></td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS FULL

</td>
		<td class="s27">TEST</td>
		<td class="s27" align="right">16</td>
		<td class="s27" align="right">208</td>

Notes

-
	     optimizer_use_stats_on_conventional_dml = 
	     yes

<br>
	<br>
	<br>
	<font class="s8">
	    <b> -
With Auto Indexes </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0">
	    <tr>
		<th align="left">Plan Hash Value </th>
		<td>:

3677153392</td>
	    </tr>
	</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>

<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>

<tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right">1</td>
		<td class="s27" align="right">13</td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS BY INDEX ROWID
BATCHED
		</td>
		<td class="s27">TEST</td>

<font color="#F7F777">..</font>
		    INDEX RANGE SCAN

</td>
		<td class="s27">SYS_AI_ap6rzrnxmm8k5</td>

<br>
	Predicate Information (identified by operation id):

<hr size="1" width="290" align="left">
	<ul>
	    <li>2 -

access("OBJECT_ID"=345)
       </li>
	</ul>
	<br>
	Notes

<hr size="1" width="30" align="left">
	
	     -

optimizer_use_stats_on_conventional_dml = 
	     yes
	<br>

- Dynamic sampling used for this statement

( level = 11 )
	    
	<br>
	<br>
	<hr size="3" width="550" align="left">
	<table class="ex" cellspacing="0" border="0" ora_widthc="80">
	    <tr>
		<th align="left">Parsing
Schema Name</th>
		<td>: MYTEST</td>
	    </tr>

<tr>
		<th align="left">SQL ID</th>
		<td>:
ax5r8mcd7u1w7</td>
	    </tr>
	    <tr>
		<th align="left">SQL Text</th>
		<td>: select object_type from test
where object_id=123</td>
	    </tr>
	    <tr>
		<th align="left">Improvement Factor</th>
		<td>: 44916x</td>

</tr>
	</table>
	<br>
	<font class="s7">
	    <b>
Execution Statistics: </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0" ora_widthc="80">
	    <tr>

</font>
		</td>
		<td>

<b>Original Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
		<td>

<b>Auto Index Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
	    </tr>
	    <tr>

<td>
		    <b>Elapsed Time (s):</b>
		</td>

<td>
		    <b>Buffer Gets:</b>
		</td>

<td>
		    <b>Optimizer Cost:</b>
		</td>

<td>
		    <b>Disk Reads:</b>
		</td>

<td>
		    <b>Direct Writes:</b>
		</td>

<td>
		    <b>Rows Processed:</b>
		</td>

<td>
		    <b>Executions:</b>
		</td>

<br>
		
	PLANS SECTION
      
	    </font>

</a>
	<hr size="3" width="650" align="left">
	<br>
	<font class="s8">
	    <b> - Original </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0">
	    <tr>

<th align="left">Plan Hash Value </th>
		<td>:

1357081020</td>
	    </tr>
	</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>

<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>

<tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right"></td>
		<td class="s27" align="right"></td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS FULL

</td>
		<td class="s27">TEST</td>
		<td class="s27" align="right">16</td>
		<td class="s27" align="right">240</td>

Notes

-
	     optimizer_use_stats_on_conventional_dml = 
	     yes

<br>
	<br>
	<br>
	<font class="s8">
	    <b> -
With Auto Indexes </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0">
	    <tr>
		<th align="left">Plan Hash Value </th>
		<td>:

3677153392</td>
	    </tr>
	</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>

<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>

<tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right">2</td>
		<td class="s27" align="right">30</td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS BY INDEX ROWID
BATCHED
		</td>
		<td class="s27">TEST</td>

<font color="#F7F777">..</font>
		    INDEX RANGE SCAN

</td>
		<td class="s27">SYS_AI_ap6rzrnxmm8k5</td>

<br>
	Predicate Information (identified by operation id):

<hr size="1" width="290" align="left">
	<ul>
	    <li>2 -

access("OBJECT_ID"=123)
       </li>
	</ul>
	<br>
	Notes

<hr size="1" width="30" align="left">
	
	     -

optimizer_use_stats_on_conventional_dml = 
	     yes
	<br>

- Dynamic sampling used for this statement

( level = 11 )
	    
	<br>
	<br>
	<hr size="3" width="550" align="left">
	<table class="ex" cellspacing="0" border="0" ora_widthc="80">
	    <tr>
		<th align="left">Parsing
Schema Name</th>
		<td>: MYTEST</td>
	    </tr>

<tr>
		<th align="left">SQL ID</th>
		<td>:
csv9p5vfq6kt1</td>
	    </tr>
	    <tr>
		<th align="left">SQL Text</th>
		<td>: select object_name from test
where object_id=1</td>
	    </tr>
	    <tr>
		<th align="left">Improvement Factor</th>
		<td>: 87424x</td>

</tr>
	</table>
	<br>
	<font class="s7">
	    <b>
Execution Statistics: </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0" ora_widthc="80">
	    <tr>

</font>
		</td>
		<td>

<b>Original Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
		<td>

<b>Auto Index Plan</b>
		    <hr size="1" width="250" align="left">
		</td>
	    </tr>
	    <tr>

<td>
		    <b>Elapsed Time (s):</b>
		</td>

</td>
		<td>984250</td>
		<td>376</td>

</tr>
	    <tr>
		<td>
		    <b>Buffer
Gets:</b>
		</td>
		<td>87424</td>

<b>Optimizer Cost:</b>
		</td>
		<td>10360</td>

<b>Disk Reads:</b>
		</td>
		<td>9066</td>

<b>Direct Writes:</b>
		</td>
		<td>0</td>

<b>Rows Processed:</b>
		</td>
		<td>1</td>

<b>Executions:</b>
		</td>
		<td>1</td>

<font class="s10">
		<br>
		
	PLANS SECTION

</font>
	</a>
	<hr size="3" width="650" align="left">
	<br>
	<font class="s8">
	    <b> -
Original </b>
	</font>
	<hr size="1" width="200" align="left">

<table border="0">
	    <tr>
		<th align="left">Plan Hash
Value </th>
		<td>: 
       1357081020</td>
	    </tr>

</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>
		<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>
	    <tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right"></td>
		<td class="s27" align="right"></td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS FULL

</td>
		<td class="s27">TEST</td>
		<td class="s27" align="right">16</td>
		<td class="s27" align="right">640</td>

Notes

-
	     optimizer_use_stats_on_conventional_dml = 
	     yes

<br>
	<br>
	<br>
	<font class="s8">
	    <b> -
With Auto Indexes </b>
	</font>
	<hr size="1" width="200" align="left">
	<table border="0">
	    <tr>
		<th align="left">Plan Hash Value </th>
		<td>:

3677153392</td>
	    </tr>
	</table>
	<br>
	<table bordercolor="#000000" ora_borderstyle="headeronly">
	    <tr>

<th align="left" bgcolor="#CCCC99" class="s16">Id</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Operation</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Name</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Rows</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Bytes</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Cost</th>
		<th align="left" bgcolor="#CCCC99" class="s16">Time</th>
	    </tr>

<tr bgcolor="#F7F777" valign="bottom">
		<td class="s27" align="right">0</td>
		<td class="s27" ora_space_char=".">SELECT
STATEMENT</td>
		<td class="s27"></td>
		<td class="s27" align="right">2</td>
		<td class="s27" align="right">80</td>

<td class="s27" ora_space_char=".">
		    <font color="#F7F777">.</font>
		    TABLE ACCESS BY INDEX ROWID
BATCHED
		</td>
		<td class="s27">TEST</td>

<font color="#F7F777">..</font>
		    INDEX RANGE SCAN

</td>
		<td class="s27">SYS_AI_ap6rzrnxmm8k5</td>

<br>
	Predicate Information (identified by operation id):

<hr size="1" width="290" align="left">
	<ul>
	    <li>2 -

access("OBJECT_ID"=1)
       </li>
	</ul>
	<br>
	Notes

<hr size="1" width="30" align="left">
	
	     -

optimizer_use_stats_on_conventional_dml = 
	     yes
	<br>

- Dynamic sampling used for this statement

( level = 11 )
	    
	<br>
	<br>
	<hr size="3" width="550" align="left">
	<hr size="3" width="550" align="left">

<br>
	
  
	<a name="errors" rel="nofollow"></a>
	<a href="http://blog.itpub.net/29487349/viewspace-2765681/#errors" style="text-decoration:none" rel="nofollow">
	    <font class="s10">ERRORS</font>

</a>
	<hr size="3" width="650" align="left">
	<tr>No errors
found.</tr>
	<hr size="3" width="650" align="left">

【INDEX】Oracle19c 自動索引技術初探

相關文章