ORACLE常用性能監控SQL【二】

系列相關

oracle常用性能監控SQL【一】

ORACLE常用性能監控SQL【二】

Oracle-動態性能視圖解讀

查詢Oracle正在執行的sql語句及執行該語句的用戶

SELECT b.sid oracleID, b.username 登錄Oracle用戶名, b.serial#, spid 操作系統ID, paddr, sql_text 正在執行的SQL, b.machine 計算機名 FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value

1

2

3

4

5

6

7

8

9

10

或者

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT from v$session a, v$sqlarea b where a.sql_address = b.address

1

2

3

4

查詢Oracle執行過的sql語句及執行該語句的用戶

---執行過的 select a.USERNAME 登錄Oracle用戶名, a.MACHINE 計算機名, SQL_TEXT, b.FIRST_LOAD_TIME, b.SQL_FULLTEXT from v$sqlarea b, v$session a where a.sql_hash_value = b.hash_value and b.FIRST_LOAD_TIME between '2016-11-01/09:24:47' and '2016-11-31/09:24:47' order by b.FIRST_LOAD_TIME desc;

1

2

3

4

5

6

7

8

9

10

11

12

查看正在執行sql的發起者的發放程序

SELECT OSUSER 電腦登錄身份, PROGRAM 發起請求的程序, USERNAME 登錄系統的用戶名, SCHEMANAME, B.Cpu_Time 花費cpu的時間, STATUS, B.SQL_TEXT 執行的sql FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY b.cpu_time DESC

1

2

3

4

5

6

7

8

9

10

11

v$sql、v$sqlarea 、v$sqltext

1

這三哥視圖都可以用于查詢共享池中已經解析過的SQL語句及其相關信息。

V$SQL中列出了共享SQL區中所有語句的信息，它不包含GROUP BY字句，并且為每一條SQL語句中單獨存放一條記錄；

V$SQLAREA中一條記錄顯示了一條共享SQL區中的統計信息。它提供了有在內存中、解析過的和準備運行的SQL語句的統計信息；

V$SQLTEXT包含了庫緩存中所有共享游標對應的SQL語句。它將SQL語句分片顯示。

查出oracle當前的被鎖對象

SELECT l.session_id sid, s.serial#, l.locked_mode 鎖模式, l.oracle_username 登錄用戶, l.os_user_name 登錄機器用戶名, s.machine 機器名, s.terminal 終端用戶名, o.object_name 被鎖對象名, s.logon_time 登錄數據庫時間 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;

1

2

3

4

5

6

7

8

9

10

11

12

13

kill掉當前的鎖對象可以為

alter system kill session 'sid， s.serial#‘;

1

查看占io較大的正在運行的session

SELECT se.sid, se.serial#, pr.SPID, se.username, se.status, se.terminal, se.program, se.MODULE, se.sql_address, st.event, st. p1text, si.physical_reads, si.block_changes FROM v$session se, v$session_wait st, v$sess_io si, v$process pr WHERE st.sid = se.sid AND st. sid = si.sid AND se.PADDR = pr.ADDR AND se.sid > 6 AND st. wait_time = 0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

查詢碎片程度高

（實際使用率小于30%）的表，也就是可以收縮的表

條件為什么block>100，因為一些很小的表，只有幾行數據實際大小很小，但是block一次性分配就是5個（11g開始默認一次性分配1M的block大小了，見create table storged的NEXT參數），5個block相對于幾行小表數據來說就相差太大了

算法中/0.9是因為塊的pfree一般為10%，所以一個塊最多只用了90%，而且一行數據大于8KB時容易產生行鏈接，把一行分片存儲，一樣的一個塊連90%都用不滿

AVG_ROW_LEN還是比較準的，比如個人實驗情況一表6個字段，一個number，其他5個都是char(100)但是實際數據都是’1111111’7位，AVG_ROW_LEN顯示依然為513

SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理論大小M", (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"實際大小M", round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "實際使用率%" FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3 order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc

1

2

3

4

5

6

7

8

9

10

查詢索引碎片的比例

（索引刪除行數除以索引總行數的百分比>30%即認為索引碎片大），也就是需要重建的索引

select name, del_lf_rows, lf_rows, round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct from index_stats where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;

1

2

3

4

5

6

7

8

集群因子clustering_factor高的表

集群因子越接近塊數越好，接近行數則說明索引列的列值相等的行分布極度散列，可能不走索引掃描而走全表掃描

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行數" from user_tables tab, user_indexes ind where tab.table_name=ind.table_name and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

1

2

3

4

5

6

7

8

9

或

select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行數" from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS') and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

1

2

3

4

5

6

7

8

9

10

11

根據sid查spid或根據spid查sid

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal from v$session s, v$process p where s.paddr = p.addr and s.sid = XX or p.spid = YY

1

2

3

4

5

根據sid查看具體的sql語句

select username, sql_text, machine, osuser from v$session a, v$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value and a.sid = &sid order by piece;

1

2

3

4

5

6

7

根據spid查詢具體的sql語句

select ss.SID, ss.SERIAL#, ss.LOGON_TIME, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.machine, ss.TERMINAL, ss.PROGRAM, ss.USERNAME, ss.STATUS, ss.OSUSER, ss.last_call_et from v$process pr, v$session ss, v$sqlarea sa where ss.status = 'ACTIVE' and ss.username is not null and pr.ADDR = ss.PADDR and ss.SQL_ADDRESS = sa.ADDRESS and ss.SQL_HASH_VALUE = sa.HASH_VALUE and pr.spid = XX

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

查看歷史session_id的SQL來自哪個IP

(當然這是個誤解，都是歷史的了，怎么可能還查到spid，其實查看trace文件名就可以知道spid,trace文件里面有sid和具體sql，如果trace存在incident，那trace就看不到具體sql，但是可以在incident文件中看到具體的sql，如DW_ora_17751.trc中17751就是spid，里面有這樣的內容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具體的sql語句)

DB_ora_29349.trc中出現如下

* SESSION ID:(5057.12807) 2016-10-26 14:45:52.726

通過表V$ACTIVE_SESSION_HISTORY來查，如下

select a.sql_id, a.machine, a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id = 5057 and a.SESSION_SERIAL# = 12807

1

2

3

4

5

查詢上面的machine的IP是多少

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal from v$session s, v$process p where s.paddr = p.addr and s.machine = 'localhost'

1

2

3

4

5

通過上面的spid在oracle服務器上執行netstat -anp |grep spid即可

[oracle@dwdb trace]$ netstat -anp |grep 17630 tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

1

2

3

4

5

出現兩個，說明來自220，連接了228數據庫服務器，但是又通過228服務器的dblink去連接了16服務器

查詢DML死鎖會話sid

（對象鎖被釋放的等待者），及引起死鎖的堵塞者會話blocking_session（對象加鎖者）

select sid, blocking_session, LOGON_TIME, sql_id, status, event, seconds_in_wait, state, BLOCKING_SESSION_STATUS from v$session where event like 'enq%' and state = 'WAITING' and BLOCKING_SESSION_STATUS = 'VALID'

1

2

3

4

5

6

7

8

9

10

11

12

13

14

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

可以在v$session.LOGON_TIME上看到引起死鎖的堵塞者會話比等待者要早

如果遇到RAC環境，一定要用gv$來查，并且執行alter system kill session ‘sid,serial#’要到RAC對應的實例上去執行

或如下也可以

select (select username from v$session where sid=a.sid) blocker, a.sid, a.id1, a.id2, ' is blocking ' "IS BLOCKING", (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

查詢DDL鎖的sql

查詢x開頭的動態性能視圖，只能用sys用戶

SELECT sid, event, p1raw, seconds_in_wait, wait_time FROM sys.v_$session_wait WHERE event like 'library cache %'

1

2

3

4

5

6

p1raw結果為’0000000453992440’

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='0000000453992440';

1

2

3

4

5

6

7

結果為671 0 3 2011-11-1 12:00:00

525 2 0 2011-11-4 12:00:00

1

查詢鎖住的DDL對象

select d.session_id, s.SERIAL#, d.name from dba_ddl_locks d, v$session s where d.owner = 'CC' and d.SESSION_ID = s.sid

1

2

3

4

5

查詢當前正在執行的sql

SELECT s.sid, s.serial#, s.username, spid, v$sql.sql_id, machine, s.terminal, s.program, sql_text FROM v$process, v$session s, v$sql WHERE addr = paddr and s.sql_id = v$sql.sql_id AND sql_hash_value = hash_value

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

查詢正在執行的SCHEDULER_JOB

select owner, job_name, sid, b.SERIAL#, b.username, spid from ALL_SCHEDULER_RUNNING_JOBS, v$session b, v$process where session_id = sid and paddr = addr

1

2

3

4

5

6

查詢正在執行的dbms_job

select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr

1

查詢一個會話session、process平均消耗多少內存，查看下面avg_used_M值

select round(sum(pga_used_mem) / 1024 / 1024, 0) total_used_M, round(sum(pga_used_mem) / count(1) / 1024 / 1024, 0) avg_used_M, round(sum(pga_alloc_mem) / 1024 / 1024, 0) total_alloc_M, round(sum(pga_alloc_mem) / count(1) / 1024 / 1024, 0) avg_alloc_M from v$process;

1

2

3

4

5

TOP 10 執行次數排序

select * from (select executions,username,PARSING_USER_ID,sql_id,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc) where rownum <=5;

1

2

3

4

5

6

7

TOP 10 物理讀排序（消耗IO排序，即最差性能SQL、低效SQL排序）

select * from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc) where rownum <=5;

1

2

3

4

5

6

7

（不要使用DISK_READS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）

TOP 10 邏輯讀排序（消耗內存排序）

select * from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc) where rownum <=5;

1

2

3

4

5

6

7

（不要使用BUFFER_GETS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）

TOP 10 CPU排序(單位秒=cpu_time/1000000)

select * from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc) where rownum <=5;

1

2

3

4

5

6

7

（不要使用CPU_TIME/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）

查詢等待事件

select event, sum(decode(wait_time, 0, 0, 1)) "之前等待次數", sum(decode(wait_time, 0, 1, 0)) "正在等待次數", count(*) from v$session_wait group by event order by 4 desc

1

2

3

4

5

6

7

查詢當前正在消耗temp空間的sql語句

Select distinct se.username, se.sid, su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G, su.tablespace, sql_text from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size' and su.session_addr=se.saddr and su.sqlhash=s.hash_value and su.sqladdr=s.address

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

查詢需要使用綁定變量的sql,10G以后推薦第二種

（任何一條執行過的語句不管執行了幾次在V$SQL中都只有一條記錄，V$SQL中會記錄執行了幾次。兩條一模一樣的語句但是在不同的schema下執行的兩種結果，如select * from t1.test在sye、system下執行則V$SQL只有一條記錄(誰先執行則PARSING_SCHEMA_NAME顯示誰)。如在sys和system都執行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILD_NUMBER和PARSING_SCHEMA_NAME不一樣。同一個用戶下執行一樣的語句如果大小寫不一樣或加了hint的話則會出現多個V$SQL記錄，說明V$SQL對應的sql語句必須一模一樣，如果alter system flush shared_pool（主站慎用）后再執行一樣的語句，發現語句在V$SQL中的SQL_ID和HASH_VALUE與之前的一樣，說明SQL_ID和HASH_VALUE應該是oracle自己的一套算法來的，只是根據sql語句內容來進行轉換，sql語句不變則SQL_ID和HASH_VALUE也不變。）

第一種

select * from ( select count(*),sql_id, substr(sql_text,1,40) from v$sql group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10

1

2

3

4

5

6

7

第二種

count(1)>10表示類語句運行了10次以上

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text from v$SQL where FORCE_MATCHING_SIGNATURE in (select /*+ unnest */ FORCE_MATCHING_SIGNATURE from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 10)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

查看數據文件可用百分比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件總容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量", ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量", ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE order by b.tablespace_name;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

查看數據文件可用百分比（文件自增長的情況下）

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用總容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量", ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id and b.file_id>4 group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES order by b.tablespace_name;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

查看表空間可用百分比

select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b WHERE a.tablespace_name = b.tablespace_name order by "% Free";

1

2

3

4

5

6

7

8

9

查看臨時表空間使用率（臨時文件是AUTOEXTENSIBLE的情況下可能空閑率是0）

SELECT temp_used.tablespace_name,total,used, total - used as "Free", round(nvl(total-used, 0) * 100/total,3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

查詢undo表空間使用情況

select tablespace_name, status, sum(bytes) / 1024 / 1024 M from dba_undo_extents group by tablespace_name, status

1

2

3

4

查看ASM磁盤組使用率

select name, round(total_mb / 1024) "總容量", round(free_mb / 1024) "空閑空間", round((free_mb / total_mb) * 100) "可用空間比例" from gv$asm_diskgroup

1

2

3

4

5

統計每個用戶使用表空間率

SELECT c.owner "用戶", a.tablespace_name "表空間名", total/1024/1024 "表空間大小M", free/1024/1024 "表空間剩余大小M", ( total - free )/1024/1024 "表空間使用大小M", Round(( total - free ) / total, 4) * 100 "表空間總計使用率 %", c.schemas_use/1024/1024 "用戶使用表空間大小M", round((schemas_use)/total,4)*100 "用戶使用表空間率 %" FROM (SELECT tablespace_name, Sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a, (SELECT tablespace_name, Sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b, (Select owner ,Tablespace_Name, Sum(bytes) schemas_use From Dba_Segments Group By owner,Tablespace_Name) c WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name =c.Tablespace_Name order by "用戶","表空間名"

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

查看閃回區\快速恢復區空間使用率

select sum(percent_space_used) || '%' "已使用空間比例" from V$RECOVERY_AREA_USAGE

1

2

或

select round(100 * (a.space_used / space_limit), 2) || '%' "已使用空間比例", a.* from v$recovery_file_dest a;

1

2

3

4

查看僵死進程，分兩種（一種是會話不在的，另一種是會話標記為killed的但是會話還在的）

alter system kill session一執行則session即標記為KILLED，但是如果會話產生的數據量大則這個kill可能會比較久，在這個過程中session標記為KILLED但是這個會話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process進程還在；當kill過程執行完畢，則這個會話即不在V$session中

會話不在的

select * from v$process where addr not in (select paddr from v$session) and pid not in (1, 17, 18)

1

2

3

4

5

會話還在的，但是會話標記為killed

select * from v$process where addr in (select paddr from v$session where status = 'KILLED')

1

2

3

再根據上述結果中的SPID通過如下命令可以查看到process的啟動時間

ps auxw|head -1;ps auxw|grep SPID

1

查看行遷移或行鏈接的表

select * From dba_tables where nvl(chain_cnt, 0) <> 0

1

chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.

數據緩沖區命中率（百分比小于90就要加大db_cache_size）

查詢V$SYSSTAT視圖可以查看從內存中讀取數據的頻率。它提供了數據庫中設置的數據塊緩存區的命中率。這個信息可以幫助您判斷系統何時需要更多的數據緩存(DB_CACHE_SIZE)，或者系統的狀態何時調整得不佳(二者均將導致較低的命中率)。

通常情況下，您應當確保讀數據的命中率保持在95%以上。將系統的命中率從98%提高到99%，可能意味著性能提高了100%(取決于引起磁盤讀操作的語句)。

SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round(100 * (1 - c.value / (a.value + b.value)), 2) || '%' hit_ratio FROM v$sysstat a, v$sysstat b, v$sysstat c WHERE a.NAME = 'db block gets' AND b.NAME = 'consistent gets' AND c.NAME = 'physical reads';

1

2

3

4

5

6

7

8

9

10

11

12

或

SELECT DB_BLOCK_GETS + CONSISTENT_GETS Logical_reads, PHYSICAL_READS phys_reads, round(100 * (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))), 2) || '%' "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME = 'DEFAULT';

1

2

3

4

5

6

7

或

SELECT 1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) / (SUM(DECODE(NAME, 'db block gets', VALUE, 0)) + (SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) "Read Hit Ratio" FROM v$sysstat;

1

2

3

4

或者

在Oracle 10g中，也可以直接獲得V$SYSMETRIC中的 AWR 信息:

select metric_name, value from v$sysmetric where metric_name = 'Buffer Cache Hit Ratio';

1

2

3

4

測定數據字典的命中率(V$ROWCACHE)

可以使用V$ROWCACHE視圖來發現對數據字典的調用是否有效地利用了通過init.ora參數SHARED_POOL_SIZE分配的內存緩存.

如果字典的命中率不高，系統的綜合性能將大受影響。推薦的命中率是95%或者更高。如果命中率低于這個百分比，說明可能需要增加init.ora參數SHARED_POOL_SIZE。

但要記住，在V$SGASTAT視圖中看到的共享池包括多個部分，而這里僅僅就是其中之一。注意：在大幅度使用公共同名的環境中，字典命中率可能難以超過75%，即使共享池的尺寸很大。這是因為Oracle必須經常檢查不存在的對象是否依舊存在。

SQL> select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)+ sum(getmisses)))) * 100 HitRate from v$rowcache; SUM(GETS) SUM(GETMISSES) HITRATE ---------- -------------- ---------- 35555492 186408 99.4784608

1

2

3

4

5

在Oracle 10g中，也可以直接獲得V$SYSMETRIC中的AWR信息:

SQL> select metric_name, value from v$sysmetric where metric_name ='Library Cache Hit Ratio'; METRIC_NAME VALUE ---------------------------------------------------------------- ---------- Library Cache Hit Ratio 98.7987987 Library Cache Hit Ratio 100

1

2

3

4

5

6

7

測定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)

訪問V$LIBRARYCACHE視圖可以顯示實際使用的語句(SQL和PL/SQL)訪問內存的情況。如果init.ora的參數SHARED_POOL_SIZE設置得太小，內存中就沒有足夠的空間來存儲所有的語句。固定命中率通常應該是95%或更高，而重載的次數不應該超過1%。查詢V$SQL_BIND_CAPTURE視圖，看看每個SQL綁定是否太高，是否需要CURSOR_SHARING。

SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache; SUM(PINHITS)/SUM(PINS)*100 -------------------------- 98.2787413813059

1

2

3

4

5

SQL> select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache; SUM(PINHITS-RELOADS)/SUM(PINS) ------------------------------ 98.0597157838185

1

2

3

4

5

最好的方式：

select sum(pins) "Executions", sum(pinhits) "Hits", ((sum(pinhits) / sum(pins)) * 100) "PinHitRatio", sum(reloads) "Misses", ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio" from v$librarycache;

1

2

3

4

5

6

7

查詢 v$sql_bind_capture，看看 average binds 是否大于15 (issue):

select sql_id, count(*) bind_count from v$sql_bind_capture where child_number = 0 group by sql_id having count(*) > 20 order by count(*);

1

2

3

4

5

確定需要固定的PL/SQL對象

的可用空間均成為許多零散的片段，而沒有足夠大的連續空間，這是共享池中的普遍現象。消除共享池錯誤的關鍵是理解哪些對象會引起問題。一旦知道了會引起潛在問題的PL/SQL對象，就可以在數據庫啟動時固定這個代碼(這時共享池是完全連續的)。

select name, sharable_mem from v$db_object_cache where sharable_mem > 100000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') and kept = 'NO';

1

2

3

4

5

6

通過V$SQLAREA查找有問題的查詢

V$SQLAREA視圖提供了一種識別有潛在問題或者需要優化的SQL語句的方法，從而可通過減少磁盤的訪問來優化數據庫的綜合性能。

select b.username username, a.disk_reads reads, a.executions exec, a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio, a.command_type, a.sql_text Statement from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;

1

2

3

4

5

6

7

8

9

10

11

檢查用戶的當前操作及其使用的資源

將V$SESSION和V$SQLTEXT連接就可以顯示目前每一個會話正在執行的SQL語句。這在有些時候是極為有用的，例如DBA希望查看某一個給定的時間點上系統究竟執行了哪些操作。

select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece;

1

2

3

4

5

select a.username, b.block_gets, b.consistent_gets, b.physical_reads, b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username;

1

2

3

4

5

6

7

8

9

查找磁盤I/O問題

視圖V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了數據庫中所有數據文件和磁盤的文件I/O活動信息。理想情況下，物理的讀和寫應當平均分布。如果沒有合理的配置系統，其綜合性能就會受到影響。

select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;

1

2

3

查詢歸檔日志切換頻率

select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 3 order by first_time,minutes;

1

2

3

4

5

或

select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from v$loghist where first_time>sysdate-3 order by 1;

1

2

3

或

SELECT TO_CHAR(first_time, 'MM/DD') DAY, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23, COUNT(*) TOTAL FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18 and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME) GROUP BY TO_CHAR(first_time, 'MM/DD') ORDER BY MIN(RN);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

查詢lgwr進程寫日志時每執行一次lgwr需要多少秒，在state是waiting的情況下，某個等待編號seq#下，seconds_in_wait達多少秒，就是lgwr進程寫一次IO需要多少秒

select event, state, seq#, seconds_in_wait, program from v$session where program like '%LGWR%' and state = 'WAITING'

1

2

3

4

查詢沒有索引的表

Select table_name from user_tables where table_name not in (select table_name from user_indexes)

1

2

3

Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)

1

2

3

查詢7天的db time（db time=db cpu+io time+wait time不包含空閑等待）

WITH sysstat AS (select sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.stat_name stat_name, ss.value e_value, lag(ss.value, 1) over(order by ss.snap_id) b_value from dba_hist_sysstat ss, dba_hist_snapshot sn where trunc(sn.begin_interval_time) >= sysdate - 7 and ss.snap_id = sn.snap_id and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and ss.dbid = (select dbid from v$database) and ss.instance_number = (select instance_number from v$instance) and ss.stat_name = 'DB time') select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char(END_INTERVAL_TIME, ' hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))), 0) per_sec from sysstat where (e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

查詢產生熱塊較多的對象

x$bh .tch(Touch)表示訪問次數越高，熱點快競爭問題就存在

SELECT e.owner, e.segment_name, e.segment_type FROM dba_extents e, (SELECT * FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM < 11) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

導出AWR報告的SQL語句

select * from dba_hist_snapshot

1

select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))

1

select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));

1

查詢某個SQL的執行計劃

select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'

1

select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));

1

含順序的

select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

1

不過要先創建xplan包，再執行

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN; SQL> grant execute on sys.xplan to public;

1

2

3

Oracle SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。