oracle dbms_stat与analyze 获取有效的统计信息(5)

#比较对象统计信息
1.dbms_stats备份统计信息
2.dbms_stats 收集时候10G 会自动备份和保留统计信息
3.11g待定统计信息
以上情况 对象上有多套统计信息，这时候可以比较统计信息 看使用哪套好

SQL> EXECUTE dbms_stats.create_stat_table('SYS','MYSTAT');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats(OWNNAME=>'SYS',TABNAME=>'T1',statown=>'SYS',STATTAB=>'MYSTAT',STATID=>'STAT_1');

PL/SQL procedure successfully completed.

 

#使用备份表比较（stattab1(id1,1own）指定备份表1，stattab2...指定备份表2，若只指定了一个备份表则 默认和当前统计信息比较
SELECT *
FROM table(dbms_stats.diff_table_stats_in_stattab(
             ownname      => 'sys',
             tabname      => 'T1',
             stattab1     => 'MYSTAT',
             statid1      => 'STAT_1',
             stattab1own  => 'SYS',
             pctthreshold => 10));

 

SQL>
SQL> SET TERMOUT ON
SQL> SET FEEDBACK OFF
SQL> SET VERIFY OFF
SQL> SET SCAN ON
SQL> SET LONG 1000000
SQL> SELECT *
  2  FROM table(dbms_stats.diff_table_stats_in_stattab(
  3               ownname      => 'SYS',
  4               tabname      => 'T1',
  5               stattab1     => 'MYSTAT',
  6               statid1      => 'STAT_1',
  7               stattab1own  => 'SYS',
  8               pctthreshold => 10));

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T1
OWNER         : SYS
SOURCE A      : User statistics table MYSTAT
              : Statid     : STAT_1

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
              : Owner      : SYS
SOURCE B      : Current Statistics in dictionary
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
...............................................................................

T1                          T   A   2          1          3          2
                                B   3          1          3          3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~***********可以看到没有不同的列统计信息
REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                  INDEX: T1_ID

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
                                  ............

T1_ID           I   A   NO_STATS
                    B   3       1       2       1     1     1       0   3
###############################################################################

 

SQL>

#使用备份统计信息比较，10g oracle会自动存储历史统计信息,time1,time2是要比较统计信息的时间，time2 null表示和当前统计信息比较
SELECT *
FROM table(dbms_stats.diff_table_stats_in_history(
             ownname      => 'SYS',
             tabname      => 'T1',
             time1        => systimestamp - to_dsinterval('0 00:15:15'),
             time2        => NULL,
             pctthreshold => 10));

 

#11g待定统计信息比较，当11g使用待定统计信息时比较用,time_stamp指定存储的历史统计信息，若为null则表示使用当前统计信息，默认为null
SELECT *
FROM table(dbms_stats.diff_table_stats_in_pending(
             wnname => 'SYS',
             tabname => 'T1',
             time_stamp => NULL,
             pctthreshold => 10));

pctthreshold
 The function reports difference in statistics only if it exceeds this limit. The default value is 10.
 

#综上来看11g收集待定统计信息，然后比较是比较好的

#删除统计信息
delete_database_stats,delete_dictionary_stats,delete_fixed_objects_stats,delete_schema_stats,delete_table_stats,delete_column_stats(用来删列上统计信息&histogram),delete_index_stats
#删除时候可以指定的一些参数
cascade_parts:是否级联删所以分区统计信息默认true都删除
cascade_columns是否级联删除列上统计信息，默认true
cascade_indexes是否级联删除index统计信息，默认true
col_stat_type:指明删除哪一个统计信息,all=列统计信息+histogram,histogram(仅删histogram的),默认all(11g才可用)

#10g统计信息历史
当使用dbms_stats收集系统or object统计信息时，会在写新的统计信息之前备份当天的统计信息到数据字典里（并保留一段时间）
1#默认保留时间是31天
SQL> select dbms_stats.get_stats_history_retention  from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31
SQL>
#改变保留时间(0禁止保留历史统计信息，null恢复默认值得，-1禁止清楚历史统计信息)
execute dbms_stats.alter_stats_history_retention(retention=>&retention_days)

SQL> execute dbms_stats.alter_stats_history_retention(retention=>&retention_days)
Enter value for retention_days: 14
SQL> select dbms_stats.get_stats_history_retention  from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         14
SQL> execute dbms_stats.alter_stats_history_retention(retention=>&retention_days)
Enter value for retention_days: null
SQL> select dbms_stats.get_stats_history_retention  from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

#statistics_level=typical or all时候 超过保留期间的将自动删除
#手动删除历史统计信息（需要analyze any dictionary）
execute dbms_stats.purge_stats(before_timestamp=>systimestamp-14)

#查询历史统计信息*_tab_stats_history
 SELECT stats_update_time
 FROM dba_tab_stats_history
 WHERE lower(owner)= '&owner' and lower(table_name) = '&tabname';

 

 

#恢复统计信息

SQL> select num_rows from user_tables where table_name='T1';

  NUM_ROWS
----------
         3

SQL> delete t1;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='T1';

  NUM_ROWS
----------
         0

 

SQL>  SELECT to_char(stats_update_time,'yyyy-mm-dd hh24:mi:ss')
  2   FROM dba_tab_stats_history
  3   WHERE lower(owner)= '&owner' and lower(table_name) = '&tabname' order by 1 desc;
Enter value for owner: sys
Enter value for tabname: t1
old   3:  WHERE lower(owner)= '&owner' and lower(table_name) = '&tabname' order by 1 desc
new   3:  WHERE lower(owner)= 'sys' and lower(table_name) = 't1' order by 1 desc

TO_CHAR(STATS_UPDAT
-------------------
2010-05-01 06:00:04
2010-05-01 02:59:01
2010-05-01 02:54:51
2010-05-01 02:46:14
2010-05-01 02:45:14
2010-05-01 02:45:08
2010-05-01 02:44:35
2010-05-01 02:41:31
2010-05-01 02:40:47

9 rows selected.

 

#恢复统计信息
restore_database_stats(恢复数据库统计信息)
restore_dictionary_stats(恢复数据字典对象统计信息)
restore_fixed_objects_stats(恢复固定表统计信息)
restore_system_stats(恢复系统统计信息)
restore_schema_stats(恢复schema统计信息)
restore_table_stats(恢复表统计信息)

#恢复时候参数

 

SQL> execute dbms_stats.restore_table_stats('SYS','T1',as_of_timestamp=>systimestamp - to_dsinterval('0 00:15:15') );

PL/SQL procedure successfully completed.

#其中还有几个选项目,比如force是否覆盖lock的统计信息(统计信息的lock也是历史统计信息的一部分,无论统计信息是否被lock,都会被恢复,default fasle)
no_invalidate相关sql是否失效

SQL> select num_rows from user_tables where table_name='T1';

  NUM_ROWS
----------
         3

 

关于备份表
execute dbms_stats.create_stat_table(ownname=>'&username',stattab=>'&stattab_name',tblspace=>'&tbls_name');
execute dbms_stats.drop_stat_table(ownname=>'&username',stattab=>'&stattab_name');

 

日志
10g可以获取统计信息日志(记录db,schema,数据字典级别的,tab的不记录),从这里我们也可以看到 自动运行的收集统计信息的 作业每次执行了多长时间
set linesize 1000
set pagesize 1000
SELECT operation, start_time,
 (end_time-start_time) DAY(1) TO SECOND(0) AS duration
 FROM dba_optstat_operations
ORDER BY start_time DESC;

关于 导入/导出 统计信息(可以看到分的很细)
export_column/index/table/schema/dictionary/FIXED_OBJECTS/database_stats
import_column/index/table/schema/dictionary/FIXED_OBJECTS/database_stats

 

SQL> conn xh/a123
Connected.
SQL> show user
USER is "XH"
SQL> create table tt (a int);

Table created.

SQL> insert into tt values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('XH','TT');

PL/SQL procedure successfully completed.

 

SQL> select num_rows from user_tables where table_name='TT';                

  NUM_ROWS
----------
         1

SQL> execute dbms_stats.create_stat_table(ownname=>'&username',stattab=>'&stattab_name',tblspace=>'&tbls_name');
Enter value for username: xh
Enter value for stattab_name: test_st
Enter value for tbls_name: users
PL/SQL procedure successfully completed.
 
SQL> execute dbms_stats.export_table_stats(OWNNAME=>'XH',tabname=>'TT',stattab=>'TEST_ST',statid=>'XH_1',statown=>'XH');
PL/SQL procedure successfully completed.

#cascade 默认=true表示会export index 统计信息

SQL> execute dbms_stats.gather_table_stats('XH','TT');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='TT';

  NUM_ROWS
----------
         2

SQL> execute dbms_stats.import_table_stats(OWNNAME=>'XH',tabname=>'TT',stattab=>'TEST_ST',statid=>'XH_1',statown=>'XH');

PL/SQL procedure successfully completed.

#cascade 默认=true表示会import index 统计信息

SQL> select num_rows from user_tables where table_name='TT';

  NUM_ROWS
----------
         1

 

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

转载于:http://blog.itpub.net/12020513/viewspace-664242/