DBMS_STATS.SEED_COL_USAGEプロシージャ と DBMS_STATS.CREATE_EXTENDED_STATSファンクション で 拡張統計のエントリを(半)自動作成してみる。
表題の検証をやってみるやで彡(ﾟ)(ﾟ) まずデータ作成します。実行スクリプトは下記の通りです。
データ作成スクリプト
CONNECT AYSHIBAT/xxxxxxxx DROP TABLE TBL_A; CREATE TABLE TBL_A AS SELECT LEVEL AS C1 , TO_DATE('2016/04/27', 'YYYY/MM/DD')+(LEVEL/24/60/60) AS C2 , MOD(LEVEL, 26) AS C3 , CHR(ASCII('A') + MOD(LEVEL, 26)) AS C4 FROM DUAL CONNECT BY LEVEL <= 100000; EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A');
データ作成結果
SQL> CONNECT AYSHIBAT/xxxxxxxx Connected. SQL> DROP TABLE TBL_A; Table dropped. SQL> CREATE TABLE TBL_A AS 2 SELECT LEVEL AS C1 3 , TO_DATE('2016/04/27', 'YYYY/MM/DD')+(LEVEL/24/60/60) AS C2 4 , MOD(LEVEL, 26) AS C3 5 , CHR(ASCII('A') + MOD(LEVEL, 26)) AS C4 6 FROM DUAL 7 CONNECT BY LEVEL <= 100000; Table created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A'); PL/SQL procedure successfully completed.
SQL を EXPLAIN＆実行してみます。
EXPLAIN＆実行SQL
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; SET LINESIZE 300; SET PAGESIZE 100; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ALL')); SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A';
EXPLAIN と SQL の 実行結果
SQL> EXPLAIN PLAN FOR 2 SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; Explained. SQL> SET LINESIZE 300; SQL> SET PAGESIZE 100; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ALL')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------Plan hash value: 849991529 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 92 (2)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TBL_A | 148 | 740 | 92 (2)| 00:00:02 | ---------------------------------------------------------------------------- ^^^^^^^ Optimzer は 148件取得と予測(Estimate)している。 ： 25 rows selected. SQL> SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; COUNT(*) ---------- 3846 ★実際には3846件取得される。Actual=3846, Estimate=148 で大きく乖離している。
この時点で Explain の 見積件数(Estimate) と COUNT(*) の 実際の件数(Actual) には大きな乖離があります。データ作成スクリプト を 見ると解るんですが、WHERE句述語 の C3列 と C4列 には 値の内容に相関が有るからです。
＃拡張統計(列グループ統計)が有効な状況です。
DBMS_STATS.REPORT_COL_USAGEファンクション と DBA_STAT_EXTENSIONSディクショナリで、SQLワークロード(col_usage$)と拡張統計の採集状況を確認してみます。
確認スクリプト
SET LONG 1000000; SET LONGC 1000000; SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_A') FROM DUAL; SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'; EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TBL_A'); SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A';
確認スクリプト実行結果
SQL> SET LONG 1000000; SQL> SET LONGC 1000000; SQL> SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_A') FROM DUAL; DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT','TBL_A') -------------------------------------------------------------------------------- LEGEND: ....... EQ : Used in single table EQuality predicate RANGE : Used in single table RANGE predicate ： ############################################################################### COLUMN USAGE REPORT FOR AYSHIBAT.TBL_A ...................................... 1. C3 : EQ 2. C4 : EQ ############################################################################### SQL> SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'; no rows selected SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TBL_A'); PL/SQL procedure successfully completed. SQL>SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'; no rows selected
この時点では拡張統計は取得されていないし、改めて統計収集(GATHER_TABLE_STATS)してみても拡張統計は採取されません。
ここで DBMS_STATS.SEED_COL_USAGEプロシージャを実行してから、SQLを実行してみます。下記マニュアルにも記載がある通り、sqlset_nameパラメタ と owner_nameパラメタが NULL の場合は、time_limitパラメタ で設定された秒数の間に実行された SQL の 列(グループ)情報が記録されます。
SEED_COL_USAGEプロシージャ http://docs.oracle.com/cd/E57425_01/121/ARPLS/d_stats.htm#i997460 > sqlset_nameおよびowner_nameがNULLの場合、次のtime_limit秒にシステムで実行される文に関する列(グループ)使用情報が記録されます。
実行SQL
EXEC DBMS_STATS.SEED_COL_USAGE(NULL, NULL, 60); SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_A') FROM DUAL;
SQLの実行結果
SQL> EXEC DBMS_STATS.SEED_COL_USAGE(NULL, NULL, 60); PL/SQL procedure successfully completed. SQL> SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; COUNT(*) ---------- 3846 SQL> SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_A') FROM DUAL; DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT','TBL_A') -------------------------------------------------------------------------------- LEGEND: ....... EQ : Used in single table EQuality predicate RANGE : Used in single table RANGE predicate ： ############################################################################### COLUMN USAGE REPORT FOR AYSHIBAT.TBL_A ...................................... 1. C3 : EQ 2. C4 : EQ 3. (C3, C4) : FILTER ★列グループの使用情報が記録されている。 ###############################################################################
この状態で DBMS_STATS.CREATE_EXTENDED_STATSファンクション を実行すると……
実行スクリプト
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('AYSHIBAT', 'TBL_A') FROM DUAL; SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A';
実行結果
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('AYSHIBAT', 'TBL_A') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS('AYSHIBAT','TBL_A') ------------------------------------------------------------------------------- ############################################################################### EXTENSIONS FOR AYSHIBAT.TBL_A ............................. 1. (C3, C4) : SYS_STUW_5RHLX443AN1ZCLPE_GLE4 created ############################################################################### SQL> SELECT * FROM DBA_STAT_EXTENSIONS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'; OWNER TABLE_NAME EXTENSION_NAME EXTENSION ------------ ------------ ------------------------------ ----------------------- AYSHIBAT TBL_A SYS_STUW_5RHLX443AN1ZCLPE_GLE4 ("C3","C4") ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^拡張統計のエントリが作成されている。
拡張統計(今回の場合は列グループ)のエントリが作成されます。スキーマ名とテーブル名だけパラメタ指定していて、extensionパラメタを指定してないのがポイントやね！彡(^)(^)
改めて統計を取り直して、EXPLAIN＆実行すると……
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TBL_A'); PL/SQL procedure successfully completed. SQL> EXPLAIN PLAN FOR 2 SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; Explained. SQL> SET LINESIZE 300; SQL> SET PAGESIZE 100; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ALL')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- Plan hash value: 849991529 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 92 (2)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TBL_A | 3913 | 19565 | 92 (2)| 00:00:02 | ---------------------------------------------------------------------------- ^^^^ Optimizer は 3913件と予測(Estimate) ： SQL> SELECT COUNT(*) FROM TBL_A WHERE C3 = 0 AND C4 = 'A'; COUNT(*) ---------- 3846 ★実件数は3846件(Actual と Estimate の 結果が近しくなっている。Estimateの精度が上がっている。)
拡張統計(今回のケースでは列グループ統計)の効果で、Estimateの精度が上がってActualとの差が無くなったZe!!!(｀･ω･)Ъ
ポイントは CREATE_EXTENDED_STATS の実行時に extensionパラメタ を指定してないところやで彡(ﾟ)(ﾟ)
このextensionパラメタには通常 WHERE句で使用される 列グループ や 計算式 を
明示的に指定するんやけど、それを指定せずにイケるのが楽ちんやね彡(^)(^)
マニュアルも見ておくんやで彡(ﾟ)(ﾟ)
Oracle Database SQLチューニング・ガイド 12cリリース1 (12.1) B71277-05 13 オプティマイザ統計の管理: 高度なトピック ワークロードの監視中に検出された列グループの作成 http://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-783A8687-EDB3-437E-AE99-3F12369BA10A
Qiita にも書いたやで！彡(ﾟ)(ﾟ)
DBMS_STATS.SEED_COL_USAGEプロシージャ と DBMS_STATS.CREATE_EXTENDED_STATSファンクション で 拡張統計のエントリを(半)自動作成してみる。 http://qiita.com/ora_gonsuke777/items/438cf379b3459544d65d
