【統計資訊】Oracle常用的收集統計資訊方式

1. 檢視某個表的統計資訊

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in (' TAB 1',' TAB 2');

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

TAB1                                86180       1231 2020-11-27 06:16:04

TAB2

上面結果表示： TAB1 表收集時間為“ 2020-11-27 06:16:04 ”， TAB2 沒有收集統計資訊

2. 檢視某個表索引的統計資訊

SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name ='TAB1';

ABLE_NAME   INDEX_NAME     BLEVEL        NUM_ROWS      LEAF_BLOCKS LAST_ANALYZED

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

TAB1          INDX_TAB1    1              86261        191           2020-11-27 06:20:57

上面結果顯示，表TAB1 的索引名字為 INDX_TAB1 ，收集時間為“ 2020-11-27 06:20:57 ”

3. 自動收集統計資訊

oracle 會在一個固定的時間將資料庫裡的表和索引的相關統計資訊進行收集，預設 時間為：

>> 週一到週五晚上10 點，持續收集 4 小時 ;

>> 週六週日早上6 點，持續收集 20 小時。

1 ）檢視自動收集統計資訊時間

set linesize 200

col REPEAT_INTERVAL for a60

col DURATION for a30

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2   where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION

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

MONDAY_WINDOW      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00

WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00

THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00

FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00

SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00

SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:0 0

上述結果顯示，從週一到週五，自動收集統計資訊時間為22 點開始，持續 4 個小時；週六和週日自動收集統計資訊時間為 6 點開始，持續 20 個小時。

2 ）修改自動收集統計資訊時間

-- 修改週六自動收集統計資訊時間為 8 點開始

SQL> BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"SYS"."SATURDAY_WINDOW"',

attribute => 'REPEAT_INTERVAL',

value   =>   'freq=daily;byday=SAT;byhour= 8 ;byminute=0; bysecond=0 ');

END;

/

4.  手動收集統計資訊

1 ）常用手動收集統計資訊

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => ' TAB_NAME ',estimate_percent => 10,method_opt=> 'for all indexed columns');

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TAB_NAME',CASCADE=>TURE);

注：引數說明：

ownname ： USER_NAME

tabname ： TABLE_NAME

partname: 分割槽表的某個分割槽名

estimate_percent: 取樣百分比， 最大值為100

block_sample ：使用隨機塊取樣代替隨機行取樣

method_opt ：

cascade: 是否收集此表索引的統計資訊 , ' true ' 為收集索引

degree: 並行 ，使用CPU 的數量

granularity ： 統計資料的收集， 'ALL' - 收集所有（子分割槽，分割槽和全域性）統計資訊

2 ） 收集分割槽表的統計資訊

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'RANGE_PART_TAB',partname => 'p_201312',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE

3 ）收集表和索引統計資訊

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE

4 ）收集某個使用者的統計資訊

SQL> exec dbms_stats.gather_schema_stats(ownname=>' TEST_USER ',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

5 ）收集全庫統計資訊

SQL> exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL'

 

---- end ----