1）Table statistics

Number of rows --行數(shù)量

Number of blocks --block數(shù)量

Average row length --平均行的長(zhǎng)度.

2）Column statistics

Number of distinct values (NDV) in column --列中distinct的值

Number of nulls in column --列中null的值

Data distribution (histogram) --數(shù)據(jù)分布

3）Index statistics

Number of leaf blocks --子節(jié)點(diǎn)的塊數(shù)量

Levels --子節(jié)點(diǎn)數(shù)量

Clustering factor --集群因子

4）System statistics

I/O performance and utilization --IO性能和利用率

CPU performance and utilization --CPU的性能和利用率

dbms_stats.gather_table_stats 收集表、列和索引的統(tǒng)計(jì)信息；

dbms_stats.gather_schema_stats收集SCHEMA下所有對(duì)象的統(tǒng)計(jì)信息；

dbms_stats.gather_index_stats收集索引的統(tǒng)計(jì)信息；

dbms_stats.gather_system_stats收集系統(tǒng)統(tǒng)計(jì)信息

dbms_stats.GATHER_DICTIONARY_STATS：所有字典對(duì)象的統(tǒng)計(jì)；

DBMS_STATS.GATHER_DICTIONARY_STATS其收集所有系統(tǒng)模式的統(tǒng)計(jì)

dbms_stats.delete_table_stats刪除表的統(tǒng)計(jì)信息

dbms_stats.delete_index_stats 刪除索引的統(tǒng)計(jì)信息

dbms_stats.export_table_stats輸出表的統(tǒng)計(jì)信息

dbms_stats.create_state_table

dbms_stats.set_table_stats 設(shè)置表的統(tǒng)計(jì)

dbms_stats.auto_sample_size

analyze table tablename compute statistics;

analyze table tablename compute statistics for all indexes;

analyze table tablename delete statistics;

select job_name, program_name, enabled, stop_on_window_close

from dba_scheduler_jobs

where job_name = 'gather_stats_job';

FREQ關(guān)鍵字用來(lái)指定間隔的時(shí)間周期，可選參數(shù)有：YEARLY（年）, MONTHLY（月）, WEEKLY（周）, DAILY（日）, HOURLY（時(shí)）, MINUTELY（分）, SECONDLY（秒）等單位。

INTERVAL關(guān)鍵字用來(lái)指定間隔的頻繁，可指定的值的范圍從1-999。

BYHOUR指定一天中的小時(shí)?？芍付ǖ闹档姆秶鷱?-24。16，17，18就表示每天下午的4、5、6點(diǎn)。

BYDAY關(guān)鍵字用來(lái)指定每周的哪天運(yùn)行。

BYMONTHDAY關(guān)鍵字用來(lái)指定每月中的哪一天。-1表示每月最后一天。

BYMONTH關(guān)鍵字用來(lái)指定每年的月份。

BYDATE指定日期。0310就表示3月10日。

https://blog.csdn.net/zq9017197/article/details/6985109

--WINDOW_NAME：任務(wù)名

--REPEAT_INTERVAL：任務(wù)重復(fù)間隔時(shí)間

--DURATION：持續(xù)時(shí)間

--enabled為true表明當(dāng)前的這些作業(yè)處于激活狀態(tài)

-維護(hù)窗口組

select * from dba_scheduler_window_groups;

--維護(hù)窗口組對(duì)應(yīng)窗口

select * from dba_scheduler_wingroup_members

--維護(hù)窗口歷史信息

select* from dba_scheduler_windows

--查詢自動(dòng)收集任務(wù)正在執(zhí)行的job

select * from DBA_AUTOTASK_CLIENT_JOB;

--查詢自動(dòng)收集任務(wù)歷史執(zhí)行狀態(tài)

select * from DBA_AUTOTASK_JOB_HISTORY;

select * from DBA_AUTOTASK_CLIENT_HISTORY;

1.關(guān)閉單個(gè)調(diào)度時(shí)間窗口,這里我們關(guān)閉MONDAY_WINDOW

BEGIN

DBMS_AUTO_TASK_ADMIN.disable (

 client_name   => 'auto optimizer stats collection',

 operation     => NULL,

 window_name   => 'MONDAY_WINDOW');

 END;

 /

--驗(yàn)證關(guān)閉情況，如下，optimizer_stats列為DISABLED

SELECT window_name,window_next_time,window_active,optimizer_stats

FROM dba_autotask_window_clients

WHERE window_name = 'MONDAY_WINDOW'

ORDER BY window_next_time;

2.開(kāi)啟單個(gè)調(diào)度時(shí)間窗口及所有時(shí)間調(diào)度窗口，只需要使用enable過(guò)程

 --注：?jiǎn)蝹€(gè)應(yīng)指定窗口名字，如window_name   => 'MONDAY_WINDOW'

 BEGIN

 DBMS_AUTO_TASK_ADMIN.enable (

 client_name   => 'auto optimizer stats collection',

 operation     => NULL,

 window_name   => 'MONDAY_WINDOW');

 END;

 /

--1.停止任務(wù)

BEGIN

DBMS_SCHEDULER.DISABLE(

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

force=>TRUE);

END;

/

使用如下腳本驗(yàn)證是否停止

SELECT window_name,window_next_time,window_active,optimizer_stats

FROM dba_autotask_window_clients

WHERE window_name = 'FRIDAY_WINDOW '

ORDER BY window_next_time;

--2.修改任務(wù)的持續(xù)時(shí)間，單位是分鐘

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

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

attribute=>'DURATION',

value=>numtodsinterval(180, 'minute'));

END;

/

--3.開(kāi)始執(zhí)行時(shí)間，BYHOUR=2,表示2點(diǎn)開(kāi)始執(zhí)行

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

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

attribute=>'REPEAT_INTERVAL',

value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

END;

/

--4.開(kāi)啟任務(wù)

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>'"SYS"."MONDAY_WINDOW"');

END;

/

修改時(shí)間窗口到特定的時(shí)間

--如下示例，將周五時(shí)間窗口時(shí)間到晚間23點(diǎn)30分

BEGIN

DBMS_SCHEDULER.DISABLE (name => '"SYS"."FRIDAY_WINDOW"', force => TRUE);

END;

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

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

attribute => 'REPEAT_INTERVAL',

VALUE => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0');

END;

BEGIN

DBMS_SCHEDULER.ENABLE (name => '"SYS"."FRIDAY_WINDOW"');

END;

--驗(yàn)證修改

SELECT w.window_name,

w.repeat_interval,

w.duration,

w.enabled

FROM dba_autotask_window_clients c, dba_scheduler_windows w

WHERE c.window_name = w.window_name

AND c.optimizer_stats = 'ENABLED'

AND c.window_name = 'FRIDAY_WINDOW';

WINDOW_NAME REPEAT_INTERVAL DURATION ENABL

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

FRIDAY_WINDOW FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0 +000 04:00:0 TRUE

7、新增維護(hù)時(shí)間窗口

-- 假定我們要處理的是修改周一的時(shí)間窗口

-- 首先關(guān)閉周一的時(shí)間窗口

BEGIN

DBMS_AUTO_TASK_ADMIN.disable (

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => 'MONDAY_WINDOW');

END;

/

--接下來(lái)創(chuàng)建一個(gè)窗口并設(shè)定時(shí)間調(diào)度間隔

--如下，每周一5點(diǎn)執(zhí)行，持續(xù)時(shí)間為1小時(shí)

BEGIN

DBMS_SCHEDULER.create_window (

window_name => 'STATS_WINDOW',

resource_plan => 'DEFAULT_MAINTENANCE_PLAN',

repeat_interval => 'freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0',

duration => INTERVAL '1' HOUR,

comments => 'Test window for stats task');

EXCEPTION

WHEN OTHERS

THEN

IF SQLCODE = -27477

THEN

NULL;

ELSE

RAISE;

END IF;

END;

BEGIN

DBMS_SCHEDULER.set_attribute ('STATS_WINDOW', 'SYSTEM', TRUE);

DBMS_SCHEDULER.set_attribute ('STATS_WINDOW',

'FOLLOW_DEFAULT_TIMEZONE',

TRUE);

EXCEPTION

WHEN OTHERS

THEN

IF SQLCODE = -27477

THEN

NULL;

ELSE

RAISE;

END IF;

END;

/

SQL> SELECT window_name, repeat_interval, enabled

2 FROM dba_scheduler_windows

3 WHERE window_name = 'STATS_WINDOW';

WINDOW_NAME REPEAT_INTERVAL ENABL

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

STATS_WINDOW freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0 TRUE

--接下來(lái)將窗口STATS_WINDOW添加到維護(hù)窗口組

BEGIN

DBMS_SCHEDULER.add_window_group_member ('MAINTENANCE_WINDOW_GROUP',

'STATS_WINDOW');

END;

/

SQL> SELECT WINDOW_NAME,

2 WINDOW_NEXT_TIME,

3 WINDOW_ACTIVE,

4 OPTIMIZER_STATS

5 FROM DBA_AUTOTASK_WINDOW_CLIENTS

6 WHERE WINDOW_NAME in ('STATS_WINDOW','MONDAY_WINDOW')

7 ORDER BY WINDOW_NEXT_TIME;

WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE

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

STATS_WINDOW 27-MAR-17 05.00.00.000000 AM PRC FALSE ENABLED --允許

MONDAY_WINDOW 27-MAR-17 10.00.00.000000 PM PRC FALSE DISABLED --當(dāng)前被disabled

select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len from user_tables;

NUM_ROWS,--表中的記錄數(shù)

BLOCKS,--表中數(shù)據(jù)所占的數(shù)據(jù)塊數(shù)

EMPTY_BLOCKS,--表中的空塊數(shù)

AVG_SPACE,--數(shù)據(jù)塊中平均的使用空間

CHAIN_CNT,--表中行連接和行遷移的數(shù)量

AVG_ROW_LEN--每條記錄的平均長(zhǎng)度

select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,

avg_data_blocks_per_key, clustering_factor from user_indexes;

BLEVEL, --索引的層數(shù)

LEAF_BLOCKS,--葉子結(jié)點(diǎn)的個(gè)數(shù)

DISTINCT_KEYS, --唯一值的個(gè)數(shù)

AVG_LEAF_BLOCKS_PER_KEY, --每個(gè)KEY的平均葉塊個(gè)數(shù)

AVG_DATA_BLOCKS_PER_KEY, --每個(gè)KEY的平均數(shù)據(jù)塊個(gè)數(shù)

CLUSTERING_FACTOR --群集因子

select num_distinct,low_value, high_value,

density, num_nulls, num_buckets, histogram from user_tab_columns;

NUM_DISTINCT, --唯一值的個(gè)數(shù)

LOW_VALUE, --列上的最小值

HIGH_VALUE, --列上的最大值

DENSITY, --選擇率因子（密度）

NUM_NULLS, --空值的個(gè)數(shù)

NUM_BUCKETS, --直方圖的BUCKET個(gè)數(shù)

HISTOGRAM--直方圖的類型

DBA_TABLES

DBA_OBJECT_TABLES

DBA_TAB_STATISTICS

DBA_TAB_COL_STATISTICS

DBA_TAB_HISTOGRAMS

DBA_INDEXES

DBA_IND_STATISTICS

DBA_CLUSTERS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_COL_STATISTICS

DBA_PART_HISTOGRAMS

DBA_SUBPART_COL_STATISTICS

DBA_SUBPART_HISTOGRAMS

Level 0: Do not use dynamic sampling.

Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: The number of blocks sampled is 2 times the default number of dynamic sampling blocks.

Level 3: The number of blocks sampled is 4 times the default number of dynamic sampling blocks.

Level 4: The number of blocks sampled is 8 times the default number of dynamic sampling blocks.

Level 5: The number of blocks sampled is 16 times the default number of dynamic sampling blocks.

Level 6: The number of blocks sampled is 32 times the default number of dynamic sampling blocks.

Level 7: The number of blocks sampled is 64 times the default number of dynamic sampling blocks.

Level 8: The number of blocks sampled is 128 times the default number of dynamic sampling blocks.

Level 9: The number of blocks sampled is 256 times the default number of dynamic sampling blocks.

Level 10: Read all blocks in the table

對(duì)于新創(chuàng)建的表，當(dāng)訪問(wèn)此表時(shí)，oracle會(huì)動(dòng)態(tài)的收集這個(gè)表的相關(guān)信息，等到晚上10點(diǎn)，再將其收集到數(shù)據(jù)字典中。

[html] view plain copy

1. SQL> set autotrace off  
2. SQL> set linesize 1000  
3. SQL> drop table t_sample purge;  
4. drop table t_sample purge  
5.            *  
6. ERROR at line 1:  
7. ORA-00942: table or view does not exist  
8.     
9.     
10. SQL> create table t_sample as select * from dba_objects;  
11.     
12. Table created.  
13.     
14. SQL> create index idx_t_sample_objid on t_sample(object_id);  
15.     
16. Index created.  

新建的表，查不到統(tǒng)計(jì)信息

[html] view plain copy

1. SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';  
2.     
3.   NUM_ROWS     BLOCKS LAST_ANAL  
4. ---------- ---------- ---------  

[html] view plain copy

1. SQL> set autotrace traceonly  
2. SQL> set linesize 1000  
3. SQL> select  * from t_sample where object_id=20;  
4.     
5.     
6. Execution Plan  
7. ----------------------------------------------------------  
8. Plan hash value: 1453182238  
9.     
10. --------------------------------------------------------------------------------------------------  
11. | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  
12. --------------------------------------------------------------------------------------------------  
13. |   0 | SELECT STATEMENT            |                    |     1 |   207 |     2   (0)| 00:00:01 |  
14. |   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |     1 |   207 |     2   (0)| 00:00:01 |  
15. |*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |     1 |       |     1   (0)| 00:00:01 |  
16. --------------------------------------------------------------------------------------------------  
17.     
18. Predicate Information (identified by operation id):  
19. ---------------------------------------------------  
20.     
21.    2 - access("OBJECT_ID"=20)  
22.     
23. Note  
24. -----  
25.    - dynamic sampling used for this statement (level=2)  
26.     
27.     
28. Statistics  
29. ----------------------------------------------------------  
30.          24  recursive calls  
31.           0  db block gets  
32.          93  consistent gets  
33.           1  physical reads  
34.           0  redo size  
35.        1608  bytes sent via SQL*Net to client  
36.         523  bytes received via SQL*Net from client  
37.           2  SQL*Net roundtrips to/from client  
38.           0  sorts (memory)  
39.           0  sorts (disk)  
40.           1  rows processed  

- dynamic sampling used for this statement (level=2)表示動(dòng)態(tài)采樣，但是不記錄數(shù)據(jù)字典，除非手動(dòng)收集表的統(tǒng)計(jì)信息。

[html] view plain copy

1. SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';  
2.     
3.   NUM_ROWS     BLOCKS LAST_ANAL  
4. ---------- ---------- ---------  
5.     
6.     
7. SQL>