oracler統(tǒng)計信息如何查看與收集

這篇文章主要為大家展示了“oracler統(tǒng)計信息如何查看與收集”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“oracler統(tǒng)計信息如何查看與收集”這篇文章吧。

查看某個表的統(tǒng)計信息

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

Session altered.

SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2');

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T1                                   2000         30 2017-07-16 14:02:23

T2                                   2000         30 2017-07-16 14:02:23

查看某個表上索引的統(tǒng)計信息 

SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name in ('T1','T2');

TABLE_NAME     INDEX_NAME                 BLEVEL   NUM_ROWS LEAF_BLOCKS LAST_ANALYZED

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

T1             IDX_T1_OBJ_ID                   1       2000           5 2017-07-16 12:06:33

T2             IDX_T2_OBJ_ID                   1       2000           5 2017-07-16 14:02:23

T2             IDX_T2_OBJ_TYPE                 1       2000           5 2017-07-16 14:02:23

T2             IDX_T2_OBJ_NAME                 1       2000           8 2017-07-16 14:02:23

T2             IDX_T2_DATA_OBJ_ID              1       1198           3 2017-07-16 14:02:23

T2             IDX_T2_STATUS                   1       2000           5 2017-07-16 14:02:23

T2             IDX_T2_CREATED                  1       2000           6 2017-07-16 14:02:23

T2             IDX_T2_LAST_DDL_TIME            1       2000           6 2017-07-16 14:02:23

8 rows selected.

oracle會在一個固定的時間將數(shù)據(jù)庫里的表和索引的相關(guān)統(tǒng)計信息進(jìn)行收集，默認(rèn)選擇周一到周五晚上10點(diǎn)，持續(xù)收集4小時，和周六周日早上6點(diǎn)，持續(xù)收集20小時。 

oracle可以專門對表的記錄變化量進(jìn)行管理，當(dāng)某表一天記錄變化量沒有超過指定的閥值時，oracle就不會對該表進(jìn)行統(tǒng)計信息收集。

修改統(tǒng)計信息自動收集時間

SQL> set linesize 200

SQL> col REPEAT_INTERVAL for a60

SQL> col DURATION for a30

SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

  2  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:00

7 rows selected.

關(guān)閉自動統(tǒng)計信息收集

BEGIN

  DBMS_SCHEDULER.DISABLE(

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

  force => TRUE);

END;

/

修改自動統(tǒng)計信息持續(xù)時間

BEGIN

  DBMS_SCHEDULER.SET_ATTRIBUTE(

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

  attribute => 'DURATION',

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

END;  

/

修改自動統(tǒng)計信息開始時間

BEGIN

  DBMS_SCHEDULER.SET_ATTRIBUTE(

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

  attribute => 'REPEAT_INTERVAL',

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

END;

/

開啟自動統(tǒng)計信息收集

BEGIN

  DBMS_SCHEDULER.ENABLE(

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

END;

/

SQL> set linesize 200

SQL> col REPEAT_INTERVAL for a60

SQL> col DURATION for a30

SQL> 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 04:00:00

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

7 rows selected.

手動收集統(tǒng)計信息

收集表統(tǒng)計信息

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

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

收集分區(qū)表的某個分區(qū)統(tǒng)計信息

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);

收集索引統(tǒng)計信息

exec dbms_stats.gather_index_stats(ownname => 'USER',indname => 'IDX_OBJECT_ID',estimate_percent => '10',degree => '4');

收集表和索引統(tǒng)計信息 

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

收集某個用戶的統(tǒng)計信息

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

收集整個數(shù)據(jù)庫的統(tǒng)計信息

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

ownname： USER_NAME

tabname： TABLE_NAME

partname: 分區(qū)表的某個分區(qū)名

estimate_percent: 采樣百分比，有效范圍為[0.000001,100]

block_sample：使用隨機(jī)塊采樣代替隨機(jī)行采樣

method_opt：

cascade:是否收集此表索引的統(tǒng)計信息

degree:并行處理的cpu數(shù)量

granularity： 統(tǒng)計數(shù)據(jù)的收集，'ALL' - 收集所有（子分區(qū)，分區(qū)和全局）統(tǒng)計信息

動態(tài)采集統(tǒng)計信息

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

SQL> set autotrace off

SQL> set linesize 1000

SQL> drop table t_sample purge;

drop table t_sample purge

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create table t_sample as select * from dba_objects;

Table created.

SQL> create index idx_t_sample_objid on t_sample(object_id);

Index created.

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

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

  NUM_ROWS     BLOCKS LAST_ANAL

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

查看執(zhí)行計劃：

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> select  * from t_sample where object_id=20;

Execution Plan

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

Plan hash value: 1453182238

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

| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                    |     1 |   207 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |     1 |   207 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=20)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

         24  recursive calls

          0  db block gets

         93  consistent gets

          1  physical reads

          0  redo size

       1608  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)

          1  rows processed

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

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

  NUM_ROWS     BLOCKS LAST_ANAL

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

SQL> 

以上是“oracler統(tǒng)計信息如何查看與收集”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！