前言：

上篇，我們主要測試了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 .
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 .

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把表的統計資訊恢復到你想恢復的哪個時間點

oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集統計資訊呢_part2

DELETE_TABLE_STATS Procedure

RESTORE_TABLE_STATS Procedure

相關文章