オプティマイザ統計管理の新機能(11g)
1. オプティマイザ統計のペンディングとパブリッシュ(Pending and Published Statistics)
1.1 概要
  • Oracle Database 11gリリース1(11.1)より前のバージョンでは、統計情報収集ジョブが完了すると、新しい統計情報は自動的にディクショナリ表にパブリッシュされる。
  • 11gリリース1(11.1)よりユーザーは、統計情報を収集し、すぐにはパブリッシュしないようにできるようになる。これによって、DBAは新しい統計情報をパブリッシュする前にテストできる。
1.2 ペンディングとパブリッシュの制御

デフォルトの設定ではパブリッシュされる。つまり、新しい統計情報を収集したらすぐにディクショナリー表に反映する。

PROD(SYS)> select dbms_stats.get_prefs('PUBLISH') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')
------------------------------------------------
TRUE

パブリッシュ設定の変更
  • グローバルレベル:DBMS_STATS.SET_GLOBAL_PREFSプロシージャ
  • データベースレベル:DBMS_STATS.SET_DATABASE_PREFSプロシージャ
  • スキーマレベル:DBMS_STATS.SET_SCHEMA_PREFSプロシージャ
  • 個別テーブルレベル:DBMS_STATS.SET_TABLE_PREFSプロシージャ

下記は、JASONスキーマのオブジェクトに対して新しい統計情報をすぐに反映しないように設定する。

PROD(JASON)> exec dbms_stats.set_schema_prefs('JASON', 'PUBLISH', 'FALSE');

PL/SQLプロシージャが正常に完了しました。

PROD(JASON)> select dbms_stats.get_prefs('PUBLISH','JASON') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','JASON')
-------------------------------------------------------
TRUE <-- スキーマの設定を確認したら、まだTRUEでバグ?

PROD(JASON)> select dbms_stats.get_prefs('PUBLISH','JASON','T_OBJ') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','JASON','T_OBJ')
-------------------------------------------------------
FALSE <-- 個別なテーブルを確認すれば FALSEになっている。

JASONスキーマの統計情報を集計して、すぐに反映していないかを確認
PROD(JASON)> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name = 'T_OBJ';

TABLE_NAME                                 NUM_ROWS     BLOCKS LAST_ANA
---------------------------------------- ---------- ---------- --------
T_OBJ

PROD(JASON)> exec dbms_stats.gather_schema_stats('JASON');

PL/SQLプロシージャが正常に完了しました。

PROD(JASON)> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name = 'T_OBJ';

TABLE_NAME                                 NUM_ROWS     BLOCKS LAST_ANA
---------------------------------------- ---------- ---------- --------
T_OBJ    <-- 統計情報が無いのでペンディングされた。

PROD(JASON)>

OPTIMIZER_USE_PENDING_STATISTICS初期パラメータ
SQL文をコンパイルするときにオプティマイザが保留中の統計を使用するかどうかを指定する。デフォルトはFALSE。
新しい統計情報をテストする際に、セッションレベルでOPTIMIZER_USE_PENDING_STATISTICSを有効にすれば、テストできると思う。
PROD(JASON)> show parameter OPTIMIZER_USE_PENDING_STATISTICS

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_use_pending_statistics     boolean                           FALSE
PROD(JASON)>
1.3 ペンディングされた統計情報の確認
1.4 ペンディングされた統計情報の削除
  • DBMS_STATS.DELETE_PENDING_STATSプロシージャ
PROD(JASON)> select * from user_tab_pending_stats;

TABLE_NAME                     PARTITION_NAME       SUBPARTITION_NAME      NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANA
------------------------------ -------------------- -------------------- ---------- ---------- ----------- ----------- --------
CALL_HISTORY                                                                      2         10          38           2 10-02-18
CALL_HISTORY_SYS                                                                  1          5          38           1 10-02-18
CUSTOMER_ORDERS                                                                   0          0           0           0 10-02-18
CUSTOMER_ORDER_ITEMS                                                              0          0           0           0 10-02-18
EMPLOYEE                                                                          0          0           0           0 10-02-18
T_OBJ                                                                         11978        172  91.3188345       11978 10-02-18
CALL_HISTORY                   P0                                                 0          0           0           0 10-02-18
CALL_HISTORY                   P1                                                 1          5          38           1 10-02-18
CALL_HISTORY                   SYS_P22                                            1          5          38           1 10-02-18
CALL_HISTORY_SYS               S1                                                 1          5          38           1 10-02-18
CALL_HISTORY_SYS               S2                                                 0          0           0           0 10-02-18
CALL_HISTORY_SYS               S3                                                 0          0           0           0 10-02-18
CALL_HISTORY_SYS               S4                                                 0          0           0           0 10-02-18
CALL_HISTORY_SYS               S5                                                 0          0           0           0 10-02-18
CUSTOMER_ORDERS                P_2007_JAN                                         0          0           0           0 10-02-18
CUSTOMER_ORDERS                P_BEFORE_JAN_2007                                  0          0           0           0 10-02-18
CUSTOMER_ORDER_ITEMS           P_2007_JAN                                         0          0           0           0 10-02-18
CUSTOMER_ORDER_ITEMS           P_BEFORE_JAN_2007                                  0          0           0           0 10-02-18
EMPLOYEE                       P1                                                 0          0           0           0 10-02-18
EMPLOYEE                       P2                                                 0          0           0           0 10-02-18
EMPLOYEE                       P3                                                 0          0           0           0 10-02-18

21行が選択されました。

PROD(JASON)> exec dbms_stats.delete_pending_stats('JASON');

PL/SQLプロシージャが正常に完了しました。

PROD(JASON)> select * from user_tab_pending_stats;

レコードが選択されませんでした。

PROD(JASON)>
1.5 ペンディングされた統計情報のパブリッシュ
  • DBMS_STATS.PUBLISH_PENDING_STATSプロシージャ
--# T_OBJ表の統計情報を確認
PROD(JASON)> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name = 'T_OBJ';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANA
------------------------------ ---------- ---------- --------
T_OBJ

--# スキーマに対して新しい統計情報を収集する
PROD(JASON)> exec dbms_stats.gather_schema_stats('JASON');

PL/SQLプロシージャが正常に完了しました。

--# 新しい統計情報をすぐに反映しないように設定しているから、ディクショナリーに反映していなかった。
PROD(JASON)> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name = 'T_OBJ';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANA
------------------------------ ---------- ---------- --------
T_OBJ

--# 手動でディクショナリーへ反映させる。
PROD(JASON)> exec dbms_stats.publish_pending_stats('JASON',null);

PL/SQLプロシージャが正常に完了しました。

--# 統計情報を反映されたかどうかを確認。
PROD(JASON)> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name = 'T_OBJ';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANA
------------------------------ ---------- ---------- --------
T_OBJ                               11978        172 10-02-18

PROD(JASON)>
2. 拡張統計(Extended Statistics)
2.1 概念

Oracle社マニュアル パフォーマンス・チューニング・ガイド(11.1)

2.2 複数列の統計

複数列をグループ(列グループ)として統計を収集することで、オプティマイザでは、個々の列統計に基づいて値を生成することなく、そのグループに関するより正確な選択値を特定できる。

複数列問い合わせの例

OWNER='SYSTEM' AND OBJECT_TYPE='VIEW'のグループ条件でT_OBJ表を検索

PROD(JASON)> select count(*) from t_obj where owner='SYSTEM' and object_type='VIEW';

  COUNT(*)
----------
       192

経過: 00:00:00.04

実行計画
----------------------------------------------------------
Plan hash value: 3946656183

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    12 |   261   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE              |                 |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_OBJ           |  2474 | 29688 |   261   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IDX_T_OBJ_ONWER |  8710 |       |    29   (0)| 00:00:01 |  <<- 索引レンジスキャン
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='VIEW')
   3 - access("OWNER"='SYSTEM')

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        421  consistent gets
          0  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

PROD(JASON)>

OracleはOBJECT_TYPE列の統計を知らないので、OWNER列の索引レンジスキャンを行った
複数列の統計を収集することによってパフォーマンス改善

列グループの作成  DBMS_STATS.CREATE_EXTENDED_STATS:仕様

PROD(JASON)> declare
  cg_name varchar2(30);
begin
 cg_name := dbms_stats.create_extended_stats('JASON','T_OBJ', '(OWNER,OBJECT_TYPE)');
end;
/

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.47
PROD(JASON)>

作成した列グループ名を取得 DBMS_STATS.SHOW_EXTENDED_STATS_NAME:仕様
PROD(JASON)> select sys.dbms_stats.show_extended_stats_name('JASON','T_OBJ','(OWNER,OBJECT_TYPE)') col_group_name from dual;

COL_GROUP_NAME
----------------------------------------------------------------------------------------------------
SYS_STUXJ8K0YTS_5QD1O0PEA514IY

経過: 00:00:00.01
PROD(JASON)>

複数列(列グループ)の統計情報収集 DBMS_STATS.GATHER_TABLE_STATS:仕様
PROD(JASON)> EXEC DBMS_STATS.GATHER_TABLE_STATS('JASON','T_OBJ',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (OWNER,OBJECT_TYPE) SIZE SKEWONLY');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.92
PROD(JASON)>

OWNER='SYSTEM' AND OBJECT_TYPE='VIEW'のグループ条件でT_OBJ表を再検索
PROD(JASON)> select count(*) from t_obj where owner='SYSTEM' and object_type='VIEW';

  COUNT(*)
----------
       192

経過: 00:00:00.02

実行計画
----------------------------------------------------------
Plan hash value: 3946656183

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    12 |   231   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE              |                 |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_OBJ           |   138 |  1656 |   231   (0)| 00:00:03 |
|*  3 |    INDEX RANGE SCAN          | IDX_T_OBJ_ONWER |  7675 |       |    26   (0)| 00:00:01 | <<- 実行計画変わった。
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='VIEW')
   3 - access("OWNER"='SYSTEM')

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        421  consistent gets
          0  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

PROD(JASON)>
  • レンジスキャンの範囲が少し狭くなった。適当で作ったテスト表なので、効果があまり見えないね。

列グループの削除 DBMS_STATS.DROP_EXTENDED_STATS:仕様

PROD(JASON)> exec dbms_stats.drop_extended_stats('JASON','T_OBJ','(OWNER,OBJECT_TYPE)');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.34
PROD(JASON)>

拡張統計情報確認
DBA_STAT_EXTENSIONS

個別値の数と、列グループにヒストグラムが作成されているかどうかを確認する。

PROD(JASON)> select e.extension col_group, t.num_distinct, t.histogram
from user_stat_extensions e, user_tab_col_statistics t
where
        e.extension_name=t.column_name
and e.table_name=t.table_name
and t.table_name='T_OBJ';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------- ------------ ---------------------------------------------
("OWNER","OBJECT_TYPE")                                                                    89 FREQUENCY

経過: 00:00:00.01
PROD(JASON)>
2.3 式の統計

式function(col1)に関する式の統計を収集することで、オプティマイザではより正確な選択値を特定できる。
式の統計の作成

PROD(JASON)> exec dbms_stats.gather_table_stats('JASON','T_OBJ', method_opt =>'for all columns size skewonly for columns (lower(object_type)) size skewonly');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.63
PROD(JASON)>

式の統計の確認
PROD(JASON)> select extension_name, extension from user_stat_extensions where table_name='T_OBJ';

EXTENSION_NAME                           EXTENSION
---------------------------------------- ------------------------------------------------------------
SYS_STUVJ5LMUFWDLVURW#_ROXEK1U           (LOWER("OBJECT_TYPE"))

経過: 00:00:00.02

式の統計の削除
PROD(JASON)> exec dbms_stats.drop_extended_stats('JASON','T_OBJ','(lower(object_type))');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.26
PROD(JASON)>
3. カーソルを無効にしない統計情報の取得
  • DBMS_STATS.GATHER_[DATABASE/DICTIONARY/FIXED_OBJECTS/INDEX/SCHEMA/TABLE]_STATSプロシージャのno_invalidate引数を設定することによって制御できる。
  • no_invalidate引数のデフォルトはDBMS_STATS.AUTO_INVALIDAT。Oracleに決定される。
  • TRUEに設定されている場合は、依存カーソルを無効化しない。
  • FALSEに設定されている場合は、すぐに依存カーソルを無効化する。
PROD(JASON)> exec dbms_stats.gather_table_stats('JASON','T_OBJ',no_invalidate=> TRUE );

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.88
PROD(JASON)>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License