|
首先要明确系统的自动收集机制 如果insert update delete truncate发生的数据量变化大于总记录的10%系统就会自动重新统计信息.如果统计信息为null时系统蚕食OPTOMIZER_DYNAMIC_SAMPLING 能够控制如何进行收集. Hint是 /*+ dynamic_sampling (table integer) */ 详见TOP第五章1.3节 以下为收集后用来存储统计信息的视图: - user_tab_statistics --统计之后用来显示表的统计信息 3.1.2
- user_tables --查看schames下所有表的统计信息
exec dbms_stats.gather_schema_stats(ownname =>
user, estimate_percent =>
0.5);
SELECT table_name, sample_size, num_rows, round(sample_size/num_rows*100,1) AS "%"
FROM user_tables WHERE num_rows >
0
ORDER
BY table_name;
TABLE_NAME SAMPLE_SIZE NUM_ROWS %
------------------------------ ----------- -------- ----------
BIG_TABLE 12435 ###### 5
COLOCATED 5087
98484
5.2
T 1001
1001
100
- user_tab_col_statistics --统计之后用来显示列的统计信息 3.1.3
- user_tab_histograms --统计之后用来显示直方图的统计信息 3.1.4
- user_ind_statistics --统计之后用来显示索引的统计信息 3.1.5
- user_indexes
Oracle的CBO的执行计划选择是基于柱状图(Histogram)的, 柱状图用于记录表中的列的分布情况,有了柱状图CBO就可以选择最优的执行计划,否则就需要根据 索引的选择性(selectivity)来判断是否使用该索引. 以下是建立测试环境 CREATE
TABLE t
AS
SELECT rownum AS id,
round(dbms_random.normal*1000) AS val1,
100+round(ln(rownum/3.25+2)) AS val2,
100+round(ln(rownum/3.25+2)) AS val3,
dbms_random.string('p',250) AS pad
FROM dual
CONNECT BY
level
<=
1000
ORDER
BY dbms_random.value;
UPDATE t SET val1 =
NULL
WHERE val1 <
0;
ALTER
TABLE t ADD
CONSTRAINT t_pk PRIMARY
KEY (id);
CREATE
INDEX t_val1_i ON t (val1);
CREATE
INDEX t_val2_i ON t (val2);
--收集方法 dbms_stats.gather_table_stats vs Analyze众所周知，Table是分区的时候，analyze根据所有partition上的已有的统计信息“计算”出整个表级别上的统计信息；而 dbms_stats是实际去计算整个表范围的统计信息，因此表级别的统计信息比analyze更精确，反映表上真实的情况. - alter system flush shared_pool;
analyze table t compute
statistics;
analyze table t delete
statistics;
analyze table ljb_test compute
statistics
for
table
for
all indexes for
all indexed columns;
exec dbms_stats.gather_table_stats(user,'t');
exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for columns size 1 status');
--直方图 (n<6是等高度直方图 n>=6 是频率直方图(精准)) 我的一个讨论帖http://www.itpub.net/viewthread.php?tid=1247685&page=2#pid14860199method_opt=>'FOR ALL COLUMNS SIZE n' -- n<6是等高度直方图 n>=6 是频率直方图(精准) 但是列的唯一值大于254还是要使用等高直方图。其中n定义了bucket的取值范围1~254,oralce 根据这个列的distinct值来计算bucket的个数(size 1例外它表示不创建直方图)
如果distinct > n 那么就使用等高直方图,就是说当有一列的唯一值大于254(最大允许的桶的数量)就不能够使用频率直方图了如果distinct <=n 那么就是用频率直方图，bucket数目是distinct~除了这个选项还有几个可选： - size repeat 刷新可用直方图
- size skewonly 只收集非均匀分布的直方图，系统自动决定桶数
- size auto 类似skewonly加上where短语引用的列根据一个列使用历史P115 col_usage.sql统计表决定是否收集
- cascade=>true DBMS_STATS will collect for all columns and the indexes
select id , count(*) from t group
by id order
by id;
....................
19978
1
19979
1
19982
1
19996
1
19997
1
12500 rows selected.
exec dbms_stats.gather_table_stats(user,'T',method_opt=>'FOR ALL COLUMNS SIZE 6');
-- n>=6 选择使用频率直方图
Select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
From user_tab_col_statistics where table_name =
'T'
and column_name =
'ID';
--这里的distinct是12500远远大于254所以如果distinct > n 那么就使用等高直方图
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ----------- ------------ ----------- -----------------
T ID 12500
6 HEIGHT BALANCED
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME =
'T'
AND COLUMN_NAME =
'ID';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------------------------------------------------
T ID 0
1
T ID 1
1563
T ID 2
3126
T ID 3
4689
T ID 4
6252
T ID 5
7814
T ID 6
9376
T ID 7
13809
T ID 8
19997
0~1 第一个桶
1~2 第二个桶
....
3~4 第四个桶
等高直方图中endpoint_number就是每个通的端点号,而不是桶号说白了就是桶号从0开始
频率直方图中endpoint_number就是桶中的累计个数每一个endpoint_number代表一个桶说白了就是桶号从1开始
--频率直方图能够精准的返回基数cardinality，比等高直方图精准
DELETE plan_table;
EXPLAIN PLAN
SET STATEMENT_ID '101'
FOR
SELECT
*
FROM t WHERE val2 =
101;
EXPLAIN PLAN
SET STATEMENT_ID '102'
FOR
SELECT
*
FROM t WHERE val2 =
102;
EXPLAIN PLAN
SET STATEMENT_ID '103'
FOR
SELECT
*
FROM t WHERE val2 =
103;
EXPLAIN PLAN
SET STATEMENT_ID '104'
FOR
SELECT
*
FROM t WHERE val2 =
104;
EXPLAIN PLAN
SET STATEMENT_ID '105'
FOR
SELECT
*
FROM t WHERE val2 =
105;
EXPLAIN PLAN
SET STATEMENT_ID '106'
FOR
SELECT
*
FROM t WHERE val2 =
106;
COLUMN statement_id FORMAT A12
SELECT statement_id, cardinality FROM plan_table WHERE id =
0
ORDER
BY statement_id;
--频率直方图能够精准的返回基数cardinality使用method_opt => 'for all columns size skewonly'进行收集
STATEMENT_ID CARDINALITY
------------ -----------
101
8
102
25
103
68
104
185
105
502
106
212
STATEMENT_ID CARDINALITY --等高直方图不准确的基数cardinality 使用method_opt =>5进行收集
------------ -----------
101
50
102
50
103
50
104
50
105
400
106
300
| 
免费注册
发表于 2010-11-19 16:27
