如何利用dbms_stats准确的收集表的统计信息

最新推荐文章于 2024-05-30 07:32:10 发布

congjiong8459

最新推荐文章于 2024-05-30 07:32:10 发布

阅读量522

点赞数

文章标签：数据库

SQL优化的核心思想是减少物理I/O扫描的次数，可以通过直方图和统计信息促使数据库准确执行SQL。统计信息包括行数、一致性物理读次数、物理读次数、物理写次数以及花费的时间。
数据库在业务运行期间频繁的DML操作会产生大量业务数据造成统计信息过期，而过期的统计信息是不准确的。
如何正确的收集统计信息是否过期及其原因，如何通过DBMS_STATS正确收集统计信息呢，下面我们通过示例阐述一下。

一、首先介绍下DBMS_STATS，这个包可以统计和优化数据库对象。在这里，我们只要使用了gather_table_stats，当然还有很多其他很重要的方法。
1.1 gather_table_stats的can

点击(此处)折叠或打开

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2,                #要分析表的拥有者及schema
   tabname          VARCHAR2,                #分析表的名称
   partname          VARCHAR2 DEFAULT NULL,           #分区表的名称 只对分区表和分区索引有用
   estimate_percent      NUMBER   DEFAULT to_estimate_percent_type       #行采样分析的百分比，取值[0.000001,100]，null表示全部分析  
   block_sample        BOOLEAN  DEFAULT FALSE,           #是否采用块采样替代行采样
   method_opt         VARCHAR2 DEFAULT get_param('METHOD_OPT'), #histograms的信息统计方式，有fol all [indexd/hidden]columns size <n>/repeat/auto/skewonly
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), #并行度  值最大为cpu number/2
   granularity        VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),            #
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), #是否决定收集索引信息
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate        BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')), #与共享池中相关cursor是否失效有关，建议设置为false 表示统计信息立即使用
   force            BOOLEAN DEFAULT FALSE);        #即使表锁定 也强制收集
)

在这写参数中，比较特殊的就是mothd_opt，着重解释一下。一个稳定的数据库推荐是用repeat参数只统计有histograms的列。auto表示让数据库根据where条件的谓词自己去判断是否收集histograms

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

对于动态采样的比率一般值在30-50之间，就全部数据的30%-50%。

点击( 此处 )折叠或打开

#普通表建议使用的参数
begin
dbms_stats.gather_table_stats(ownname => 'scott',
tabname => 'emp',
estimate_percent => 30,
method_opt =>'for all columns size repeat',
degree => 6,
granularity => 'ALL',
cascade => true);
end;
/
#分区表收集
begin
dbms_stats.gather_table_stats(ownname => 'scott',
tabname => 'emp',
partname => 'partname1' #指定分区名称
estimate_percent => 30,
method_opt =>'for all columns size repeat',
degree => 6,
granularity => 'partition', #选的分区
granularity => 'ALL',
cascade => true);
end;
/

二、测试统计信息过期并查找原因

点击(此处)折叠或打开

SQL> CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> select owner, table_name name, object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where table_name = 'test'
4 and owner = 'scott'
5 and (stale_stats = 'YES' or last_analyzed is null);
no rows selected
SQL> select count(*) from test;
COUNT(*)
----------
86953
SQL> select owner, table_name name, object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where table_name = upper('test')
4 and owner = 'SCOTT'
5 and (stale_stats = 'YES' or last_analyzed is null);
no rows selected
#删除20%以上的数据，促使统计信息过期。统计信息过期的理论值为10%
SQL> delete from test where rownum<20000;
19999 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> col owner for a20
SQL> col name for a20
SQL> col object_type for a20
SQL> col stale_stats for a20
SQL> select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where table_name = upper('test')
and owner = 'SCOTT'
and (stale_stats = 'YES' or last_analyzed is null);
#stale_stats 为yes表示过期
OWNER NAME OBJECT_TYPE STALE_STATS LAST_ANALYZED
-------------------- -------------------- -------------------- -------------------- -------------------
SCOTT TEST TABLE YES 2016-08-01 23:40:28
#思考：是什么操作造成统计信息过期呢，在这里我们可以才到时delete的操作。那么在未知环境中，如何确定？
#如何确定表的动态采样比率呢
SQL> select owner,table_name, num_rows,sample_size,trunc(sample_size/num_rows*100) estimate_percet
2 from dba_tab_statistics
3 where owner='SCOTT' and table_name='TEST';

OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCET
-------------------- ------------------------------------------------------------ ---------- ----------- ---------------
SCOTT TEST 86953 86953 100

# 我们再次在dbms_stats 这是estimate_percent = 30 ,测试脚本是否准确
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select owner,table_name, num_rows,sample_size,trunc(sample_size/num_rows*100) estimate_percet
2 from dba_tab_statistics
3 where owner='SCOTT' and table_name='TEST';

OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCET
-------------------- ------------------------------------------------------------ ---------- ----------- ---------------
SCOTT TEST 66977 20093 29
# 查看TEST表是否有直方图信息，结果如下图没有信息
SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 66977 23 .03 NONE 1
OBJECT_NAME 66977 35966 53.7 NONE 1
SUBOBJECT_NAME 66977 72 .11 NONE 1
OBJECT_ID 66977 66977 100 NONE 1
DATA_OBJECT_ID 66977 6107 9.12 NONE 1
OBJECT_TYPE 66977 28 .04 NONE 1
CREATED 66977 558 .83 NONE 1
LAST_DDL_TIME 66977 577 .86 NONE 1
TIMESTAMP 66977 602 .9 NONE 1
STATUS 66977 1 0 NONE 1
TEMPORARY 66977 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 66977 2 0 NONE 1
SECONDARY 66977 2 0 NONE 1
NAMESPACE 66977 13 .02 NONE 1
EDITION_NAME 66977 0 0 NONE 0

15 rows selected.
#我们执行一个带where谓词的SQL，并在此通过methd_opt为aotu的方式收集统计信息，可以看到owner列是具有FREQUENCY直方图的
SQL> select count(*) from test where owner='HR';

COUNT(*)
----------
34

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 66717 23 .03 FREQUENCY 23
OBJECT_NAME 66717 36548 54.78 NONE 1
SUBOBJECT_NAME 66717 92 .14 NONE 1
OBJECT_ID 66717 66717 100 NONE 1
DATA_OBJECT_ID 66717 6047 9.06 NONE 1
OBJECT_TYPE 66717 31 .05 NONE 1
CREATED 66717 553 .83 NONE 1
LAST_DDL_TIME 66717 574 .86 NONE 1
TIMESTAMP 66717 590 .88 NONE 1
STATUS 66717 1 0 NONE 1
TEMPORARY 66717 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 66717 2 0 NONE 1
SECONDARY 66717 2 0 NONE 1
NAMESPACE 66717 16 .02 NONE 1
EDITION_NAME 66717 0 0 NONE 0

15 rows selected.
#如何单独的对某列执行收集直方图，使用skewonly 表示只要数据倾斜就收集直方图信息，指定for columns owner size 1 ，及表示清除列直方图信息，指定'for columns owner size skewonly表示对列单独收集
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for columns owner size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 66480 24 .04 NONE 1
OBJECT_NAME 66480 36548 54.98 NONE 1
SUBOBJECT_NAME 66480 92 .14 NONE 1
OBJECT_ID 66480 66717 100.36 NONE 1
DATA_OBJECT_ID 66480 6047 9.1 NONE 1
OBJECT_TYPE 66480 31 .05 NONE 1
CREATED 66480 553 .83 NONE 1
LAST_DDL_TIME 66480 574 .86 NONE 1
TIMESTAMP 66480 590 .89 NONE 1
STATUS 66480 1 0 NONE 1
TEMPORARY 66480 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 66480 2 0 NONE 1
SECONDARY 66480 2 0 NONE 1
NAMESPACE 66480 16 .02 NONE 1
EDITION_NAME 66480 0 0 NONE 0

15 rows selected.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
#SELECTIVITY 值大的可选择性高，适合创建索引
SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 66800 23 .03 FREQUENCY 23
OBJECT_NAME 66800 36548 54.71 NONE 1
SUBOBJECT_NAME 66800 92 .14 NONE 1
OBJECT_ID 66800 66717 99.88 NONE 1
DATA_OBJECT_ID 66800 6047 9.05 NONE 1
OBJECT_TYPE 66800 31 .05 NONE 1
CREATED 66800 553 .83 NONE 1
LAST_DDL_TIME 66800 574 .86 NONE 1
TIMESTAMP 66800 590 .88 NONE 1
STATUS 66800 1 0 NONE 1
TEMPORARY 66800 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 66800 2 0 NONE 1
SECONDARY 66800 2 0 NONE 1
NAMESPACE 66800 16 .02 NONE 1
EDITION_NAME 66800 0 0 NONE 0

15 rows selected.

SQL>
#我们再次通过 for columns owner size 1 删除owner列的统计信息，对比分别执行size skewonly 和size repeat的区别
SQL> select owner,count(*) from test group by owner;

OWNER COUNT(*)
-------------------- ----------
OWBSYS_AUDIT 12
MDSYS 2011
PUBLIC 27549

。。。。。

25 rows selected.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for all columns size repeat',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 67223 25 .04 NONE 1
OBJECT_NAME 67223 35861 53.35 NONE 1
SUBOBJECT_NAME 67223 83 .12 NONE 1
OBJECT_ID 67223 67223 100 NONE 1
DATA_OBJECT_ID 67223 6343 9.44 NONE 1
OBJECT_TYPE 67223 28 .04 NONE 1
CREATED 67223 559 .83 NONE 1
LAST_DDL_TIME 67223 580 .86 NONE 1
TIMESTAMP 67223 599 .89 NONE 1
STATUS 67223 1 0 NONE 1
TEMPORARY 67223 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 67223 2 0 NONE 1
SECONDARY 67223 2 0 NONE 1
NAMESPACE 67223 15 .02 NONE 1
EDITION_NAME 67223 0 0 NONE 0

15 rows selected.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 30,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select a.column_name, b.num_rows,a.num_distinct Cardinality,
2 round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets
3 from dba_tab_col_statistics a, dba_tables b
4 where a.owner = b.owner
5 and a.table_name = b.table_name
6 and a.owner = 'SCOTT'
7 and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
OWNER 66590 22 .03 FREQUENCY 22
OBJECT_NAME 66590 35861 53.85 NONE 1
SUBOBJECT_NAME 66590 83 .12 NONE 1
OBJECT_ID 66590 67223 100.95 NONE 1
DATA_OBJECT_ID 66590 6343 9.53 NONE 1
OBJECT_TYPE 66590 28 .04 NONE 1
CREATED 66590 559 .84 NONE 1
LAST_DDL_TIME 66590 580 .87 NONE 1
TIMESTAMP 66590 599 .9 NONE 1
STATUS 66590 1 0 NONE 1
TEMPORARY 66590 2 0 NONE 1

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------ ---------- ----------- ----------- ------------------------------ -----------
GENERATED 66590 2 0 NONE 1
SECONDARY 66590 2 0 NONE 1
NAMESPACE 66590 15 .02 NONE 1
EDITION_NAME 66590 0 0 NONE 0

15 rows selected.

三、关闭数据库在带的JOB任务，原因1：该JOB是晚上运行，与备份等任务竞争资源 2:这个JOB的mothd_opt采用的AUTO 容易造成直方图误收集，查收绑定变量窥探操作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2122810/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27039319/viewspace-2122810/

congjiong8459

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
如何利用dbms_stats准确的收集表的统计信息

SQL优化的核心思想是减少物理I/O扫描的次数，可以通过直方图和统计信息促使数据库准确执行SQL。统计信息包括行数、一致性物理读次数、物理读次数、物理写次数以及花费的时间。数据库在业务运行期间频繁的DML操作会产生大量业务...
复制链接

扫一扫