|
SQL> create table histest as select rownum a from test;
Table created
SQL> desc histest;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
A NUMBER Y
SQL> drop table histest;
Table dropped
SQL> create table histest as select rownum a from test;
Table created
SQL> select count(*) from histest;
COUNT(*)
----------
6235
SQL> delete from histest where a between 1001 and 6234;
5234 rows deleted
SQL> select count(*) from histest;
COUNT(*)
----------
1001
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
76 A
--HISTEST.A是由1至1000连续数字和一个数字6235组成,当使用for all columns size skewonly收集histograms时,oracle认为a列是倾斜的用了75个桶收集了柱状图.
--但是用for all columns size auto确是另一个结果;
SQL> EXEC DBMS_STATS.DELETE_table_stats(ownname => 'MYDB',tabname => 'HISTEST');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
2 A
--oracle不认为histest.a倾斜,没有收集柱状图....
--oracle online文档上写着:
AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns.
可以看到两个方式的区别是auto把workload考虑进去了.
对两种收集方式作10046 trace
有意思的是我发现两个trac文件中都有sql_3
SELECT /*+ rule */ C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETFORM COL_CSF,
C.DEFAULT$ COL_DEF, C.NULL$ COL_NULL, C.PROPERTY COL_PROP, C.COL# COL_UNUM,
C.INTCOL# COL_INUM, C.OBJ# COL_OBJ, C.SCALE COL_SCALE, H.BUCKET_CNT H_BCNT,
H.DISTCNT H_PNDV, C.LENGTH COL_LEN, CU.TIMESTAMP CU_TIME, CU.EQUALITY_PREDS
CU_EP, CU.EQUIJOIN_PREDS CU_EJP, CU.RANGE_PREDS CU_RP, CU.LIKE_PREDS CU_LP,
CU.NONEQUIJOIN_PREDS CU_NEJP, CU.NULL_PREDS NP
FROM
SYS.USER$ U, SYS.OBJ$ O, SYS.COL$ C, SYS.COL_USAGE$ CU, SYS.HIST_HEAD$ H
WHERE U.NAME = :B2 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O.NAME = :B1
AND O.OBJ# = C.OBJ# AND C.OBJ# = CU.OBJ#(+) AND C.INTCOL# = CU.INTCOL#(+)
AND C.OBJ# = H.OBJ#(+) AND C.INTCOL# = H.INTCOL#(+)
里面有一个表SYS.COL_USAGE$
在网上找到了这个解释
col_usage$
This table allows to monitor the usage of predicates on columns in select statements. It is updated (if _column_tracking_level is set to 1) at intervalls by smon, so it might be a little out of date. Also, dbms_stats will make use of that info when deciding if it needs to create a histogram on a column.
看样子这个应该就是workload of columns的来源~
select
o.name,
c.name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds
from
sys.col_usage$ u
join sys.obj$ o on u.obj# = o.obj#
join sys.col$ c on u.obj# = c.obj# and u.intcol# = c.col#;
SQL> select
2 x.ksppinm name,
3 y.ksppstvl value,
4 y.ksppstdf isdefault,
5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
7 from
8 sys.x$ksppi x,
9 sys.x$ksppcv y
10 where
11 x.inst_id = userenv('Instance') and
12 y.inst_id = userenv('Instance') and
13 x.indx = y.indx and
14 x.ksppinm like '_column_tracking_level'
15 /
NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ---------- --------- ---------- -----
_column_tracking_level 1 TRUE FALSE FALSE
这个参数默认就是1,也就是说oracle应该是默认就收集column的workload~
下面建立一个肯定包含倾斜列a的表histest2来测试
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as MYDB
SQL> create table histest2 as select rownum a from dba_objects;
Table created
SQL> update histest2 set a=3;
6242 rows updated
SQL> update histest2 set a=2 where rownum<2000;
1999 rows updated
SQL> update histest2 set a=1 where rownum<20;
19 rows updated
SQL> commit;
Commit complete
SQL> select count(*),a from histest2 group by a;
COUNT(*) A
---------- ----------
19 1
1980 2
4243 3
SQL>
SQL> SELECT /*+ rule */
2 C.NAME COL_NAME,
3 C.TYPE# COL_TYPE,
4 C.CHARSETFORM COL_CSF,
5 C.DEFAULT$ COL_DEF,
6 C.NULL$ COL_NULL,
7 C.PROPERTY COL_PROP,
8 C.COL# COL_UNUM,
9 C.INTCOL# COL_INUM,
10 C.OBJ# COL_OBJ,
11 C.SCALE COL_SCALE,
12 H.BUCKET_CNT H_BCNT,
13 H.DISTCNT H_PNDV,
14 C.LENGTH COL_LEN,
15 CU.TIMESTAMP CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS CU_RP,
19 CU.LIKE_PREDS CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS NP
22 FROM SYS.USER$ U,
23 SYS.OBJ$ O,
24 SYS.COL$ C,
25 SYS.COL_USAGE$ CU,
26 SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28 AND O.OWNER# = U.USER#
29 AND O.TYPE# = 2
30 AND O.NAME = 'HISTEST2'
31 AND O.OBJ# = C.OBJ#
32 AND C.OBJ# = CU.OBJ#(+)
33 AND C.INTCOL# = CU.INTCOL#(+)
34 AND C.OBJ# = H.OBJ#(+)
35 AND C.INTCOL# = H.INTCOL#(+)
36 AND C.NAME='A'
37 /
COL_NAME COL_TYPE COL_CSF COL_DEF COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ COL_SCALE H_BCNT H_PNDV COL_LEN CU_TIME CU_EP CU_EJP CU_RP CU_LP CU_NEJP NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A 2 0 0 0 1 1 6563 22
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST2',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST2' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
2 A
--没有收集直方图
SQL> SELECT COUNT(*) FROM HISTEST2 WHERE A=1;
COUNT(*)
----------
19
--做一个等值查询
--然后到sys用户下用oradebug wakeup唤醒一下smon,不过唤醒了也等了1分钟后才有下面的结果
SQL> SELECT /*+ rule */
2 C.NAME COL_NAME,
3 C.TYPE# COL_TYPE,
4 C.CHARSETFORM COL_CSF,
5 C.DEFAULT$ COL_DEF,
6 C.NULL$ COL_NULL,
7 C.PROPERTY COL_PROP,
8 C.COL# COL_UNUM,
9 C.INTCOL# COL_INUM,
10 C.OBJ# COL_OBJ,
11 C.SCALE COL_SCALE,
12 H.BUCKET_CNT H_BCNT,
13 H.DISTCNT H_PNDV,
14 C.LENGTH COL_LEN,
15 CU.TIMESTAMP CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS CU_RP,
19 CU.LIKE_PREDS CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS NP
22 FROM SYS.USER$ U,
23 SYS.OBJ$ O,
24 SYS.COL$ C,
25 SYS.COL_USAGE$ CU,
26 SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28 AND O.OWNER# = U.USER#
29 AND O.TYPE# = 2
30 AND O.NAME = 'HISTEST2'
31 AND O.OBJ# = C.OBJ#
32 AND C.OBJ# = CU.OBJ#(+)
33 AND C.INTCOL# = CU.INTCOL#(+)
34 AND C.OBJ# = H.OBJ#(+)
35 AND C.INTCOL# = H.INTCOL#(+)
36 AND C.NAME='A'
37 /
COL_NAME COL_TYPE COL_CSF COL_DEF COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ COL_SCALE H_BCNT H_PNDV COL_LEN CU_TIME CU_EP CU_EJP CU_RP CU_LP CU_NEJP NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A 2 0 0 0 1 1 6563 1 3 22 2008-11-22 1 0 0 0 0 0
--CU_EP增加了1
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST2',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST2' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
3 A
--重新用auto分析,发现生成了直方图。
////////////////////////////////////////////////////
看样子auto的确是靠col_usage$来判断列的workload的.
/////////////////////////////////////////////////////////////
histest上的直方图应该是一个频率是方图,这个直方图应该应用于<or>连接的查询,所以col_usage$中必须range_preds有值才算有workload~
SQL> drop table histest;
Table dropped
SQL> create table histest as select rownum a from test;
Table created
SQL> delete from histest where a between 1001 and 6234;
5234 rows deleted
SQL> select count(*) from histest;
COUNT(*)
----------
1001
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
2 A
SQL>
SQL> SELECT /*+ rule */
2 C.NAME COL_NAME,
3 C.TYPE# COL_TYPE,
4 C.CHARSETFORM COL_CSF,
5 C.DEFAULT$ COL_DEF,
6 C.NULL$ COL_NULL,
7 C.PROPERTY COL_PROP,
8 C.COL# COL_UNUM,
9 C.INTCOL# COL_INUM,
10 C.OBJ# COL_OBJ,
11 C.SCALE COL_SCALE,
12 H.BUCKET_CNT H_BCNT,
13 H.DISTCNT H_PNDV,
14 C.LENGTH COL_LEN,
15 CU.TIMESTAMP CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS CU_RP,
19 CU.LIKE_PREDS CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS NP
22 FROM SYS.USER$ U,
23 SYS.OBJ$ O,
24 SYS.COL$ C,
25 SYS.COL_USAGE$ CU,
26 SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28 AND O.OWNER# = U.USER#
29 AND O.TYPE# = 2
30 AND O.NAME = 'HISTEST'
31 AND O.OBJ# = C.OBJ#
32 AND C.OBJ# = CU.OBJ#(+)
33 AND C.INTCOL# = CU.INTCOL#(+)
34 AND C.OBJ# = H.OBJ#(+)
35 AND C.INTCOL# = H.INTCOL#(+)
36 AND C.NAME='A'
37 /
COL_NAME COL_TYPE COL_CSF COL_DEF COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ COL_SCALE H_BCNT H_PNDV COL_LEN CU_TIME CU_EP CU_EJP CU_RP CU_LP CU_NEJP NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A 2 0 0 0 1 1 6568 1 1001 22
SQL> select count(*) from histest where a<900;
COUNT(*)
----------
899
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed
SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
76 A
--有直方图了~,看样子auto还是非常智能的~
ps:col_usage$的值同样使用EXEC DBMS_STATS.flush_database_monitoring_info;来刷新就可以~
这个是我在oraclefans中的一个帖子,遗留了2个问题。
1。如何清空$col_usage来重新收集workload(有可能是修改隐含参数_column_tracking_level =false)
2。为何skewonly的10046中还需要查询$col_usage?按理说他不需要workload来判断。
如果要看完整的帖子请看
http://www.oraclefans.cn/forum/showtopic.jsp?rootid=9652&CPages=1 |
|