DBMS_STATS.SEED_COL_USAGEプロシージャ と DBMS_STATS.CREATE_EXTENDED_STATSファンクション で 拡張統計のエントリを(半)自動作成してみる。

表題の検証をやってみるやで彡(ﾟ)(ﾟ) まずデータ作成します。実行スクリプトは下記の通りです。

**データ作成スクリプト**

```sql
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
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**

```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
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$)と拡張統計の採集状況を確認してみます。

**確認スクリプト**

```sql
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
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**

```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
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ファンクション を実行すると……

**実行スクリプト**

```sql
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
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
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