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

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

創(chuàng)新互聯(lián)建站為您提適合企業(yè)的網(wǎng)站設(shè)計(jì)?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強(qiáng)的網(wǎng)絡(luò)競(jìng)爭(zhēng)力！結(jié)合企業(yè)自身，進(jìn)行網(wǎng)站設(shè)計(jì)及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)，我們的網(wǎng)頁(yè)設(shè)計(jì)師為您提供的解決方案。

查看某個(gè)表的統(tǒng)計(jì)信息

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

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

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

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

修改統(tǒng)計(jì)信息自動(dòng)收集時(shí)間

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)閉自動(dòng)統(tǒng)計(jì)信息收集

BEGIN

DBMS_SCHEDULER.DISABLE(

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

force => TRUE);

END;

修改自動(dòng)統(tǒng)計(jì)信息持續(xù)時(shí)間

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

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

attribute => 'DURATION',

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

END;

修改自動(dòng)統(tǒng)計(jì)信息開(kāi)始時(shí)間

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

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

attribute => 'REPEAT_INTERVAL',

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

END;

開(kāi)啟自動(dòng)統(tǒng)計(jì)信息收集

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.

手動(dòng)收集統(tǒng)計(jì)信息

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

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ū)表的某個(gè)分區(qū)統(tǒng)計(jì)信息

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)計(jì)信息

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

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

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

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

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

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

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

ownname： USER_NAME

tabname： TABLE_NAME

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

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

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

method_opt：

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

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

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

動(dòng)態(tài)采集統(tǒng)計(jì)信息

對(duì)于新創(chuàng)建的表，當(dāng)訪問(wèn)此表時(shí)，oracle會(huì)動(dòng)態(tài)的收集這個(gè)表的相關(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)計(jì)信息

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

NUM_ROWS BLOCKS LAST_ANAL

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

查看執(zhí)行計(jì)劃：

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> select * from t_sample where object_id=20;

Execution Plan

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

Plan hash value: 1453182238

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

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

| 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) 表示動(dòng)態(tài)采樣，但是不記錄數(shù)據(jù)字典，除非手動(dòng)收集表的統(tǒng)計(jì)信息。

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

NUM_ROWS BLOCKS LAST_ANAL

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

SQL>

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

本文題目：oracler統(tǒng)計(jì)信息如何查看與收集
網(wǎng)頁(yè)URL：http://bm7419.com/article0/jdedio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開(kāi)發(fā)、Google、品牌網(wǎng)站建設(shè)、定制網(wǎng)站、網(wǎng)站建設(shè)、響應(yīng)式網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容