列に関するワークロードと拡張統計

先日、拡張統計を取りたいと思ったものの、そらでは書けず調べてもなかなか見つけられなかったので書いておきます。

マニュアルは、こちらを参照。12.1 のマニュアルですが 11.2.0.4 とかでも使えます。

以下コマンドの結果は、11.2.0.4 の環境のものになります。

0. 準備

connect sh/oracle
drop table customers_test;
create table customers_test as select * from customers;
exec dbms_stats.gather_table_stats(user, 'customers_test');

サンプルスキーマを使っています。

1. SEED_COL_USAGE で列の使用情報を収集

exec  dbms_stats.seed_col_usage(null,null,60);

2. SQL実行

1. で指定した時間内（今回は60秒以内）に

explain plan for
  select * from   customers_test
  where cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id = 52790;
 
select plan_table_output 
from   table(dbms_xplan.display('plan_table', null,'all'));

実行計画から情報を収集するので EXPLAIN PLAN FOR でも大丈夫です。

3. レポート

set long 100000 longc 100000 lines 120 pages 0
select dbms_stats.report_col_usage(user, 'customers_test') from dual;

結果は、こんな感じ

###############################################################################

COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
.........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
###############################################################################

まあ、この SQL は簡単なので見なくても分かりますが。

そもそも、Predicate information でいいんじゃないか？

4. 拡張統計作成

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test','(CUST_CITY, CUST_STATE_PROVINCE,COUNTRY_ID)') FROM DUAL;

CREATE_EXTENDED_STATS の戻り値は extension_name です。

12.1 だと列グループの指定をしなくても、DBMS_STATS.SEED_COL_USAGE で検出された列グループを自動で作成してくれるみたいです。

詳しくはこちら。

5. 確認

exec dbms_stats.gather_table_stats(user, 'customers_test');
select column_name,num_distinct from user_tab_col_statistics
where table_name='CUSTOMERS_TEST' and COLUMN_NAME like 'SYS%';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N          620

Gblog

おもにTips

列に関するワークロードと拡張統計