表題の検証をやってみるやで彡(ﾟ)(ﾟ) まずデータ作成します。実行スクリプトは下記の通りです。
データ作成スクリプト
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#i997460sqlset_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