oracler統計信息如何查看與收集

127次閱讀

沒有評論

共計 7146 個字符，預計需要花費 18 分鐘才能閱讀完成。

這篇文章主要為大家展示了“oracler 統計信息如何查看與收集”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領大家一起研究并學習一下“oracler 統計信息如何查看與收集”這篇文章吧。

查看某個表的統計信息

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

查看某個表上索引的統計信息

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 會在一個固定的時間將數據庫里的表和索引的相關統計信息進行收集，默認選擇周一到周五晚上 10 點，持續收集 4 小時，和周六周日早上 6 點，持續收集 20 小時。

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

修改統計信息自動收集時間

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.

關閉自動統計信息收集

BEGIN

DBMS_SCHEDULER.DISABLE(

name = SYS . SATURDAY_WINDOW ,

force = TRUE);

END;

/

修改自動統計信息持續時間

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name = SYS . SATURDAY_WINDOW ,

attribute = DURATION ,

value = numtodsinterval(240, minute

END;

/

修改自動統計信息開始時間

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name = SYS . SATURDAY_WINDOW ,

attribute = REPEAT_INTERVAL ,

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

END;

/

開啟自動統計信息收集

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.

手動收集統計信息

收集表統計信息

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

收集分區表的某個分區統計信息

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

收集索引統計信息

exec dbms_stats.gather_index_stats(ownname = USER ,indname = IDX_OBJECT_ID ,estimate_percent = 10 ,degree = 4

收集表和索引統計信息

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

收集某個用戶的統計信息

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

收集整個數據庫的統計信息

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

ownname：USER_NAME

tabname：TABLE_NAME

partname: 分區表的某個分區名

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

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

method_opt：

cascade: 是否收集此表索引的統計信息

degree: 并行處理的 cpu 數量

granularity：統計數據的收集，ALL – 收集所有（子分區，分區和全局）統計信息

動態采集統計信息

對于新創建的表，當訪問此表時，oracle 會動態的收集這個表的相關信息，等到晚上 10 點，再將其收集到數據字典中。

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.

新建的表，查不到統計信息

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

NUM_ROWS BLOCKS LAST_ANAL

———- ———- ———

查看執行計劃：

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) 表示動態采樣，但是不記錄數據字典，除非手動收集表的統計信息。

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

NUM_ROWS BLOCKS LAST_ANAL

———- ———- ———

SQL

以上是“oracler 統計信息如何查看與收集”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！

正文完

發表至：數據庫

2023-07-24

轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。

Python如何實現MySQL客戶端操作庫

如何進行MySQL update數據時InnoDB內部的操作

mysql出現死鎖的必要條件是什么

mysql常用功能有哪些

如何通過shell腳本查看數據庫表空間使用情況

久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

oracler統計信息如何查看與收集