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/，如需轉載，請註明出處，否則將追究法律責任。