這篇文章主要講解了“oracle統計信息查看與收集的方法是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“oracle統計信息查看與收集的方法是什么”吧！
一、查看表統計信息
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='WOO';
備注：通過腳本查看統計信息,參考MOS：SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)
二、查看表上的索引信息
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name='DUMP_TABLE'; SQL> col table_name format a11 SQL> col index_name format a16 SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed 2 from user_indexes t 3 where table_name='DUMP_TABLE'; TABLE_NAME INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS LAST_ANAL ----------- ---------------- ---------- ---------- ----------- --------- DUMP_TABLE WORNUM_IND 0 10 1 18-MAR-20
三、檢查當前統計信息收集策略
set linesize 140
col WINDOW_NAME format a17
col REPEAT_INTERVAL for a55
col DURATION for a15
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');
SQL> set linesize 140
SQL> col WINDOW_NAME format a17
SQL> col REPEAT_INTERVAL for a55
SQL> col DURATION for a15
SQL> select t1.window_name,t1.repeat_interval,t1.duration from
2 dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
3 where t1.window_name=t2.window_name
4 and t2.window_group_name
5 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:00
7 rows selected.
3.1. 關閉自動統計信息收集
BEGIN DBMS_SCHEDULER.DISABLE( name => '"SYS"."SATURDAY_WINDOW"', force => TRUE); END; /
3.2 修改自動統計信息持續時間
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name => '"SYS"."SATURDAY_WINDOW"', attribute => 'DURATION', value => numtodsinterval(180,'minute')); END; /
3.3 修改自動統計信息開始時間，每周六22點開始
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name => '"SYS"."SATURDAY_WINDOW"', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0 '); END; /
3.4 開啟自動統計信息收集
BEGIN DBMS_SCHEDULER.ENABLE( name => '"SYS"."SATURDAY_WINDOW"'); END; /
3.5 再次檢查策略是否正確
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=22;byminute=0;bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.四、手工收集統計信息
4.1 收集索引統計信息
exec dbms_stats.gather_index_stats(ownname => 'WOO',indname => 'WORNUM_IND',estimate_percent => '10',degree => '4');
4.2 收集表和索引統計信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
4.3 收集表的統計信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',estimate_percent => 10,method_opt=> 'for all indexed columns');
4.4 收集分區表統計信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',partname => 'p_20190318',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
4.5 收集某個用戶的統計信息
exec dbms_stats.gather_schema_stats(ownname=>'WOO',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
4.6 收集整個數據庫的統計信息
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
五、動態采樣：
對于新創建的表，當訪問此表時，oracle會動態的收集這個表的相關信息，等到晚上10點，再將其收集到數據字典中。
SQL> set linesize 200 SQL> set autotrace traceonly SQL> select * from DUMP_TABLE; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1795212136 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 20390 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUMP_TABLE | 10 | 20390 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1305 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
- dynamic sampling used for this statement (level=2) 表示動態采樣，但是不記錄數據字典，除非手動收集表的統計信息。
咱們通過user_tables查看結果也是一樣的
SQL> set autotrace off; SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE'; NUM_ROWS BLOCKS LAST_ANAL ---------- ---------- --------- SQL>
六、統計信息收集完之后：
SQL> set linesize 200 SQL> set autotrace traceonly SQL> select * from DUMP_TABLE; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1795212136 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 470 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUMP_TABLE | 10 | 470 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 33 recursive calls 0 db block gets 56 consistent gets 0 physical reads 0 redo size 1305 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 10 rows processed SQL> set autotrace off; SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE'; NUM_ROWS BLOCKS LAST_ANALYZED ---------- ---------- ------------------- 10 4 2020-03-18 17:06:16 SQL>
感謝各位的閱讀，以上就是“oracle統計信息查看與收集的方法是什么”的內容了，經過本文的學習后，相信大家對oracle統計信息查看與收集的方法是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
