oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集统计信息呢_part2

最新推荐文章于 2023-03-25 15:20:54 发布

clg10051

最新推荐文章于 2023-03-25 15:20:54 发布

阅读量177

点赞数

文章标签：数据库

前言：

上篇，我们主要测试了dbms_stats过程create_stat_table,export_table_stats,import_table_stats的用法，本文我们继续学习dbms_stats包的过程delele_table_stats,restore_table_stats

测试目标：

理解与掌握dbms_stats包的过程delele_table_stats,restore_table_stats的用法及应用场景。

测试步骤：

1,创建测试表并插入数据

SQL> conn tbs_11204/system

Connected.

SQL> create table t_stat(a int,b int);

Table created.

SQL> insert into t_stat select mod(level,3),mod(level,5)+3 from dual connect by level<=100000;

100000 rows created.

SQL> commit;

Commit complete.

手工收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

查看表的统计信息

SQL> select table_name,object_type,num_rows,stale_stats,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA STATT

------------------------------ ------------ ---------- --- -----

T_STAT TABLE 98582 NO

SQL>

2,删除表的统计信息

官方注解

DELETE_TABLE_STATS Procedure

This procedure deletes table-related statistics.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE, 
   cascade_columns  BOOLEAN  DEFAULT TRUE,
   cascade_indexes  BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Parameters

Table 103-16 DELETE_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema
tabname	Name of the table to which this column belongs
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.
stattab	User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)
cascade_parts	If the table is partitioned and if partname is NULL, then setting this to TRUE causes the deletion of statistics for this table for all underlying partitions as well
cascade_columns	Indicates that DELETE_COLUMN_STATS should be called for all underlying columns (passing the cascade_parts parameter)
cascade_indexes	Indicates that DELETE_INDEX_STATS should be called for all underlying indexes (passing the cascade_parts parameter)
statown	Schema containing stattab (if different than ownname)
no_invalidate	Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When value of this argument is TRUE, deletes table statistics even if locked

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20005: Object statistics are locked.

SQL> exec dbms_stats.delete_table_stats(user,'t_stat');

PL/SQL procedure successfully completed.

这下表的统计信息已经被清除了

SQL> select table_name,object_type,num_rows,stale_stats,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA STATT

------------------------------ ------------ ---------- --- -----

T_STAT TABLE

表上列的统计信息也被清除，因为默认情况下清除表的统计信息时，列的统计信息也会同步被清除，请大家注意cascade_columns

选项的含义

SQL> select table_name,column_name,num_distinct,num_nulls,last_analyzed from user_tab_col_statistics where lower(table_name)='t_stat';

no rows selected

3,如果cascade_columns配置为false,清除表统计信息时，是否会保存列的统计信息呢

重新收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

查询表的统计信息

SQL> select table_name,object_type,num_rows,stale_stats,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA STATT

------------------------------ ------------ ---------- --- -----

T_STAT TABLE 100600 NO

列的统计信息

SQL> set linesize 300

SQL> select table_name,column_name,num_distinct,num_nulls,last_analyzed from user_tab_col_statistics where lower(table_name)='t_stat';

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANALY

------------------------------ ------------------------------ ------------ ---------- ----------

T_STAT A 3 0 07-8? -14

T_STAT B 5 0 07-8? -14

清除表的统计信息，但不清除列的统计信息

SQL> exec dbms_stats.delete_table_stats(user,'t_stat',cascade_columns=>false);

PL/SQL procedure successfully completed.

列的统计信息保存下来

SQL> select table_name,column_name,num_distinct,num_nulls,last_analyzed from user_tab_col_statistics where lower(table_name)='t_stat';

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANALY

------------------------------ ------------------------------ ------------ ---------- ----------

T_STAT A 3 0 07-8? -14

T_STAT B 5 0 07-8? -14

表的统计信息被清除

SQL> select table_name,object_type,num_rows,stale_stats,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA STATT

------------------------------ ------------ ---------- --- -----

T_STAT TABLE

小结：

1，cascade_columns可以控制是否清除列的统计信息

2，cascade_indexes,cascade_parts同理，不再测试

4，恢复表的统计信息

RESTORE_TABLE_STATS Procedure

This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.

Syntax

DBMS_STATS.RESTORE_TABLE_STATS (
   ownname                   VARCHAR2, 
   tabname                   VARCHAR2, 
   as_of_timestamp           TIMESTAMP WITH TIME ZONE,
   restore_cluster_index     BOOLEAN DEFAULT FALSE,
   force                     BOOLEAN DEFAULT FALSE,
   no_invalidate             BOOLEAN DEFAULT to_no_invalidate_type
                                                    (GET_PARAM('NO_INVALIDATE')));

Parameters

Table 103-64 RESTORE_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The schema of the table for which the statistics are to be restored
tabname	The table name
as_of_timestamp	The timestamp to which to restore statistics
restore_cluster_index	If the table is part of a cluster, restore statistics of the cluster index if set to TRUE
force	Restores statistics even if the table statistics are locked. If the table statistics were not locked at the specified timestamp, it unlocks the statistics.
no_invalidate	Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values.

ORA-20006: Unable to restore statistics, statistics history not available.

上面as_of_timestamp选项，指定把统计信息还原到哪个时间点，数据类型为timestamp with time zone

SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff';

Session altered.

SQL> select to_timestamp_tz('2014-08-07 00:00:00.00','yyyy-mm-dd hh24:mi:ss.ff') from dual;

TO_TIMESTAMP_TZ('2014-08-0700:00:00.00','YYYY-MM-DDHH24:MI:SS.FF')

---------------------------------------------------------------------------

2014-08-07 00:00:00.000000000

SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALY STATT

------------------------------ ------------ ---------- --- ---------- -----

T_STAT TABLE

SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 00:00:00.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

再次手工收集表统计信息，为了测试restore_table_stats的功能

SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

由下可以表的统计信息是 2014-08-07 09:02:26产生的

SQL> col table_name for a30

SQL> set linesize 300

SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 101073 NO 2014-08-07 09:02:26

SQL>

还原表的统计信息到上述

2014-08-07 09:02:26这个时间之前

SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 09:00:00.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

表的统计信息已经不存在了，因为这个时间点表没有产生统计信息呢

SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE

SQL> select table_name,num_rows,last_analyzed from user_tables where lower(table_name)='t_stat';

TABLE_NAME NUM_ROWS LAST_ANALYZED

------------------------------ ---------- -------------------

T_STAT

继续测试在不同时间点产生表的数据，然后恢复到到不同的时间点，查看表统计信息的差异

SQL> set time on

09:09:25 SQL> truncate table t_stat;

Table truncated.

09:09:41 SQL> insert into t_stat values(1,1);

1 row created.

09:09:52 SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

2014-08-07 09:11:19.482998

第一次收集统计信息

09:13:45 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:13:46 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 1 NO 2014-08-07 09:13:46

第2次收集统计信息

09:16:17 SQL> insert into t_stat values(2,2);

1 row created.

09:16:26 SQL> commit;

Commit complete.

09:16:27 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:16:30 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:16:30

第3次收集统计信息

09:16:57 SQL> insert into t_stat values(3,3);

1 row created.

09:17:05 SQL> commit;

Commit complete.

09:17:06 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:17:09 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 3 NO 2014-08-07 09:17:09

09:17:12 SQL>

恢复表的统计信息到第3次收集统计信息之前

09:21:58 SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 09:16:31.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

表t_stat统计信息正好恢复到第2次收集表的统计信息

09:23:21 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:16:30

如果上次恢复到指定的时间点后，再次恢复到另一个时间点，就不会成功了

09:24:18 SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 09:17:09.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

09:27:29 SQL>

09:27:30 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:16:30

小结：

1，restore_table_stats可以灵活把表的统计信息恢复到不同的时间点

2, restsore_table_stats只可以使用1次，再次恢复虽不会报错，但还是保持第一次恢复后的时间点

5，测试如果表发生了ddl,上述的restore_table_stats会采取如何的表现呢？

马上最新收集表的统计信息

09:37:45 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:39:04 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 3 NO 2014-08-07 09:39:03

表添加c列

09:42:39 SQL> alter table t_stat add c int;

Table altered.

09:43:31 SQL> insert into t_stat values(4,4,4);

1 row created.

09:43:35 SQL> commit;

Commit complete.

09:45:25 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:45:30 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 4 NO 2014-08-07 09:45:30

46:42 SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 09:39:03.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

发现恢复没有报错，但是恢复到最新收集统计信息之前的哪个时间点了

09:47:32 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:16:30

为了确认ddl对于restore_table_stats的影响是否如上述所言，再次进行测试

09:54:28 SQL> drop table t_stat purge;

Table dropped.

09:54:41 SQL> create table t_stat(a int,b int);

Table created.

09:54:50 SQL> insert into t_stat values(1,1);

1 row created.

09:54:58 SQL> commit;

Commit complete.

第1次收集表统计信息

09:54:59 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:55:03 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 1 NO 2014-08-07 09:55:03

09:55:11 SQL> insert into t_stat values(2,2);

1 row created.

09:56:09 SQL> commit;

Commit complete.

第2次收集表统计信息

09:56:13 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:56:16 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:56:16

发生ddl操作

09:56:55 SQL> alter table t_stat add c int;

Table altered.

09:57:02 SQL> insert into t_stat values(3,3,3);

1 row created.

09:57:15 SQL> commit;

Commit complete.

第3次收集表统计信息

09:57:17 SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

09:57:19 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 3 NO 2014-08-07 09:57:19

运用restore_table_stats恢复到ddl之前的时间点

09:57:21 SQL> exec dbms_stats.restore_table_stats(user,'t_stat',to_timestamp_tz('2014-08-07 09:56:17.00','yyyy-mm-dd hh24:mi:ss.ff'),false,true,true);

PL/SQL procedure successfully completed.

可以恢复到ddl之前的时间点

09:59:10 SQL> select table_name,object_type,num_rows,stale_stats,last_analyzed,stattype_locked from user_tab_statistics where lower(table_name)='t_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS STA LAST_ANALYZED STATT

------------------------------ ------------ ---------- --- ------------------- -----

T_STAT TABLE 2 NO 2014-08-07 09:56:16

小结：ddl操作仍可以正常使用restore_table_stats把表的统计信息恢复到你想恢复的哪个时间点

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

转载于:http://blog.itpub.net/9240380/viewspace-1248303/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集统计信息呢_part2

前言：上篇，我们主要测试了dbms_stats过程create_stat_table,export_table_stats,import_table_stats的用法，本文我们继续学习dbm...
复制链接

扫一扫