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

oracle dbms_stat與analyze 獲取有效的統計資訊(5)

相關文章