19C新特性研究實時統計

新特性概述

其實 oracle 的統計資訊就是儲存在資料字典裡的一組資料，從多個維度描述了 oracle 資料庫裡物件的詳細資訊。 CBO 會利用這些統計資訊來計算目標 SQL 各種可能的、不同的執行路徑成本，並從中選擇一條成本值最小的執行路徑來作為目標 SQL 的執行計劃，所以說收集統計資訊對於資料庫的 SQL 有著至關重要的作用！ oracle 資料庫會在一個固定的時間將資料庫裡的表和索引的相關統計資訊進行收集。我們可以開啟自動收集統計資訊的功能也可以透過以下命令手動收集統計資訊。

但是如今某些特定的生產資料庫每日也存在頻繁 DML 操作，為了給最佳化器提供準確的統計值，從而生成更加合理的執行計劃。從 10g 的自動收集統計資訊直到 19c 又推出了一個新的功能，實時收集資料物件的統計資訊。    顧名思義實時收集就是隻要發生 DML 操作以後， oracle 資料庫就是立即開始收集統計資訊。  

需要格外注意的是，在 19c 中，這個特性只能在 Exadata 上使用，這個特性預設是啟用的。如果沒有 Exadata 環境，可透過設定引數 "_exadata_feature_on"=true 來模擬。請不要在非 Exadta 生產環境上使用

 

透過以下檢視瞭解實時統計的資訊

DBA_TAB_COL_STATISTICS	此檢視顯示從 DBA_TAB_COLUMNS.  實時統計資料由 STATS_ON_CONVENTIONAL_DML 在 NOTES 列和 SHARED 在 SCOPE 列
DBA_TAB_STATISTICS	此檢視顯示從 DBA_TAB_COLUMNS.  實時統計資料由 STATS_ON_CONVENTIONAL_DML 在 NOTES 列和 SHARED 在 SCOPE 列

  檢視統計資訊

 

檢視列級統計資訊

SET PAGESIZE 5000
SET LINESIZE 200
COL COLUMN_NAME FORMAT a13 
COL LOW_VALUE FORMAT a14
COL HIGH_VALUE FORMAT a14
COL NOTES FORMAT a5
COL PARTITION_NAME FORMAT a13
 
SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'SALES'
ORDER BY 1, 5;

 
COLUMN_NAME   LOW_VALUE      HIGH_VALUE     SAMPLE_SIZE      NOTES
AMOUNT_SOLD    C10729          C2125349               5594
CHANNEL_ID       C103             C10A                  918843
CUST_ID           C103              C30B0B               5595
PROD_ID          C10E              C20231                 5593
PROMO_ID        C122              C20A64                  918843
QUANTITY_SOLD   C102              C102                    5593
TIME_ID       77C60101010101    78650C1F010101           5593

 

以上查詢結果， NOTES 欄位為空，則說明實時統計暫未收集

 

插入資料

嘗試插入資料，看看實時統計是否執行

 

INSERT INTO sales(prod_id, cust_id, time_id, channel_id, promo_id, 
                  quantity_sold, amount_sold)
  SELECT prod_id, cust_id, time_id, channel_id, promo_id, 
         quantity_sold * 2, amount_sold * 2 
  FROM   sales;
COMMIT;

從遊標中獲取執行計劃

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

 

計劃顯示 LOAD TABLE CONVENTIONAL 在步驟 1 和 OPTIMIZER STATISTICS GATHERING 在步驟 2 中，這意味著資料庫收集了實時統計在常規插入期間

再次檢視列級統計資訊

SET PAGESIZE 5000
SET LINESIZE 200
COL COLUMN_NAME FORMAT a30 
COL LOW_VALUE FORMAT a30
COL HIGH_VALUE FORMAT a30
COL NOTES FORMAT a20
COL PARTITION_NAME FORMAT a30
SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'TEST'
ORDER BY 1, 5;

 

SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'SALES'
ORDER BY 1, 5;
 
COLUMN_NAME   LOW_VALUE      HIGH_VALUE     SAMPLE_SIZE     NOTES
------------- -------------- -------------- ----------- -------------------------
AMOUNT_SOLD   C10729         C224422D              9073    STATS_ON_CONVENTIONAL_DML
AMOUNT_SOLD   C10729         C2125349              5702
CHANNEL_ID    C103           C10A                  9073       STATS_ON_CONVENTIONAL_DML
CHANNEL_ID    C103           C10A                918843
CUST_ID       C103           C30B0B                9073       STATS_ON_CONVENTIONAL_DML
CUST_ID       C103           C30B0B                5702
PROD_ID       C10E           C20231                9073       STATS_ON_CONVENTIONAL_DML
PROD_ID       C10E           C20231                5701
PROMO_ID      C122           C20A64                9073       STATS_ON_CONVENTIONAL_DML
PROMO_ID      C122           C20A64              918843
QUANTITY_SOLD C102           C103                  9073       STATS_ON_CONVENTIONAL_DML
QUANTITY_SOLD C102           C102                  5701
TIME_ID       77C60101010101 78650C1F010101        9073        STATS_ON_CONVENTIONAL_DML
TIME_ID       77C60101010101 78650C1F010101        5701

這時候我們發現 NOTES 欄位顯示 STATS_ON_CONVENTIONAL_DML ，則說明資料庫在插入完資料以後，就實時收集了統計資訊。

 

測試完畢， 19c 版本的資料庫，開啟實時統計功能後，在發生 DML 操作以後， oracle 資料庫就會立即開始收集統計資訊