LoginSignup
3
0

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Last updated at Posted at 2016-04-27

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

データ作成スクリプト

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

3
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
0