文中使用的Oracle版本為10g。

這是之前在工作中遇到的慢查詢排查記錄，為了防杠先做個(gè)聲明。

“All Roads Lead to Rome”

以下方法是本人處理思路以及在排除掉其他外部因素后，只針對(duì)數(shù)據(jù)庫(kù)層面的排查內(nèi)容。當(dāng)然了肯定有更好的排查方式，這里只是提供一個(gè)方案而已。

1. 若出現(xiàn)插入速度慢或者無(wú)法插入數(shù)據(jù)的情況下，先檢查表空間

SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比",
F.TOTAL_BYTES "空閑空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
        ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
        ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 　　
        FROM SYS.DBA_FREE_SPACE 　　
        GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
           ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 　　
           FROM SYS.DBA_DATA_FILES DD 　　
           GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
--and F.TABLESPACE_NAME = '<tablespace>'
ORDER BY 1;

上面的腳本已經(jīng)將列名都用中文標(biāo)識(shí)清楚了，若用戶表空間使用率達(dá)到峰值，則基本只能查詢，其他的操作都不能做了。除此之外，本腳本還可以看到系統(tǒng)的表空間情況，其中值得注意的是UNDOTBS1這個(gè)表空間，如下圖：

這個(gè)是回滾表空間，越大越能夠儲(chǔ)存回滾段。在做提交操作或者復(fù)雜運(yùn)算的時(shí)候這里的使用率會(huì)飛漲。這個(gè)表空間是系統(tǒng)自動(dòng)回收的，當(dāng)系統(tǒng)判斷資源不需要被使用之后表空間將會(huì)被回收。但若這個(gè)表空間長(zhǎng)時(shí)間不回收就需要留意是否存在大批量的提交操作甚至鎖表情況出現(xiàn)。

2. 表空間正常但發(fā)現(xiàn)只能查詢不能修改或者插入時(shí)，可以初步判定存在鎖表的可能性

通過(guò)以下語(yǔ)句查詢是否存在表級(jí)鎖

SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
 WHERE AO.OBJECT_ID = LO.OBJECT_ID
   AND LO.SESSION_ID = SESS.SID;

一般情況下會(huì)在做DML操作時(shí)系統(tǒng)會(huì)自動(dòng)分配一個(gè)行級(jí)排它鎖。同時(shí)這個(gè)事務(wù)就會(huì)獲得一個(gè)表鎖以防止其他的DDL操作影響DML操作。以上說(shuō)的都是系統(tǒng)自動(dòng)操作的，但是需要用到上面語(yǔ)句來(lái)進(jìn)行查詢的時(shí)候應(yīng)該大多數(shù)情況都是產(chǎn)生了死鎖，即多個(gè)DML操作產(chǎn)生了沖突引起的鎖。這個(gè)時(shí)候就只能查詢出來(lái)鎖的SID和SERIAL#來(lái)將其KILL掉。除此之外還有其他找鎖表的語(yǔ)句，譬如：

SELECT (SELECT USERNAME FROM V$SESSION WHERE SID = A.SID) BLOCKER,
       A.SID,
       '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;

找到SID和SERIAL#字段信息并KILL掉當(dāng)前死鎖執(zhí)行腳本如下：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

還有很多情況也會(huì)產(chǎn)生出鎖表的現(xiàn)象，不過(guò)本人經(jīng)歷過(guò)最多的鎖表原因都在于：

• 應(yīng)用程序的編寫(xiě)不當(dāng)引起，包括JDBC連接沒(méi)有正常關(guān)閉，通過(guò)循環(huán)提交負(fù)責(zé)級(jí)聯(lián)更新操作等
• 數(shù)據(jù)庫(kù) 存儲(chǔ)過(guò)程/觸發(fā)器 編寫(xiě)邏輯混亂引起的同表資源搶占（一個(gè)操作沒(méi)有完成就又要提交另一個(gè)操作）
• 數(shù)據(jù)庫(kù)利用定時(shí)器模仿多線程進(jìn)行同表數(shù)據(jù)DML操作

3. 服務(wù)器端出現(xiàn)多個(gè)Oracle進(jìn)程并CPU占用率高

要想知道是那個(gè)數(shù)據(jù)庫(kù)用戶在占用CPU可以使用以下語(yǔ)句找到：

SELECT A.SID,
       SPID,
       STATUS,
       SUBSTR(A.PROGRAM, 1, 40) PROG,
       A.TERMINAL,
       OSUSER,
       VALUE / 60 / 100 VALUE
  FROM V$SESSION A, V$PROCESS B, V$SESSTAT C
 WHERE C.STATISTIC# = 12
   AND C.SID = A.SID
   AND A.PADDR = B.ADDR 
   -- AND STATUS = 'ACTIVE'  
 ORDER BY VALUE DESC;

執(zhí)行后如下圖所示，可以通過(guò)圖中的字段知道哪個(gè)用戶是通過(guò)哪種方式連接到數(shù)據(jù)庫(kù)的，是否在線狀態(tài)，數(shù)據(jù)庫(kù)中執(zhí)行id是什么，操作系統(tǒng)中是那個(gè)用戶，CPU耗時(shí)多長(zhǎng)時(shí)間，以此來(lái)定為那個(gè)用戶。

4. 進(jìn)一步深挖究竟執(zhí)行什么導(dǎo)致CPU使用率高

SELECT OSUSER, USERNAME, SQL_TEXT, ADDRESS, HASH_VALUE
FROM V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
--AND USERNAME = 'I_INFO' 
ORDER BY ADDRESS, PIECE;

執(zhí)行之后如下圖所示，查詢出來(lái)后可以根據(jù)USERNAME來(lái)進(jìn)行篩選，再來(lái)拿到語(yǔ)句的ADDRESS（SGA內(nèi)存地址）和HASH_VALUE（Oracle Hash值）進(jìn)行后續(xù)查詢。

下面腳本將根據(jù)ADDRESS和HASH_VALUE來(lái)找到對(duì)應(yīng)SQL執(zhí)行性能消耗情況：

SELECT HASH_VALUE,
       BUFFER_GETS,
       DISK_READS,
       EXECUTIONS,
       PARSE_CALLS,
       CPU_TIME
  FROM V$SQLAREA
 WHERE HASH_VALUE = 1977390476
   AND ADDRESS = HEXTORAW('000000018B83E578');

執(zhí)行之后可以看到BUFFER_GETS（所有子游標(biāo)運(yùn)行這條語(yǔ)句導(dǎo)致的讀內(nèi)存次數(shù)），DISK_READS（所有子游標(biāo)運(yùn)行這條語(yǔ)句導(dǎo)致的讀磁盤(pán)次數(shù)），EXECUTIONS（所有子游標(biāo)的執(zhí)行這條語(yǔ)句次數(shù)），PARSE_CALLS （語(yǔ)句的解析調(diào)用(軟、硬)次數(shù)），CPU_TIME （語(yǔ)句被解析和執(zhí)行的CPU時(shí)間），如下圖：

一般來(lái)說(shuō)EXECUTIONS，BUFFER_GETS越高表示讀內(nèi)存多，磁盤(pán)少是比較理想的狀態(tài)，因此越高越好。之后若發(fā)現(xiàn)語(yǔ)句資源消耗異?？梢詮腟QL_TEXT找到對(duì)應(yīng)的語(yǔ)句，放到執(zhí)行計(jì)劃里面進(jìn)行分析看看具體是那個(gè)地方造成性能問(wèn)題。

5. 還有另一種解法獲取等待時(shí)間長(zhǎng)的用戶和執(zhí)行語(yǔ)句

SELECT S.SID, S.USERNAME, SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
  FROM V$ACTIVE_SESSION_HISTORY A, V$SESSION S
 WHERE A.SAMPLE_TIME BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
 GROUP BY S.SID, S.USERNAME
 ORDER BY TOTAL_WAIT_TIME DESC;

SELECT A.PROGRAM,
       A.SESSION_ID,
       A.USER_ID,
       D.USERNAME,
       S.SQL_TEXT,
       SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
  FROM V$ACTIVE_SESSION_HISTORY A, V$SQLAREA S, DBA_USERS D
 WHERE A.SAMPLE_TIME BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
   AND A.SQL_ID = S.SQL_ID
   AND A.USER_ID = D.USER_ID
 GROUP BY A.PROGRAM, A.SESSION_ID, A.USER_ID, S.SQL_TEXT, D.USERNAME;

兩個(gè)腳本里面都有TOTAL_WAIT_TIME字段，這個(gè)字段就是這些用戶和SQL從產(chǎn)生開(kāi)始到目前為止等待的最長(zhǎng)時(shí)間，可以根據(jù)這個(gè)定位用戶和SQL。若問(wèn)題仍然存在，就需要注意內(nèi)存使用情況和Oracle的SGA和PGA分配情況（這個(gè)網(wǎng)上有太多方法了就不再敘述）。

后日談

在另一次慢查詢分析時(shí)在執(zhí)行到了第四步“進(jìn)一步深挖究竟執(zhí)行什么導(dǎo)致CPU使用率高”后找到了執(zhí)行慢的語(yǔ)句，進(jìn)一步執(zhí)行分析計(jì)劃后看到語(yǔ)句本應(yīng)該是主鍵的ID字段沒(méi)有走到索引，本以為Oracle會(huì)自動(dòng)基于成本規(guī)則選擇了別的執(zhí)行方式。但并沒(méi)有…后來(lái)將這個(gè)結(jié)果與所在項(xiàng)目組溝通才知道他們?cè)谧鰯?shù)據(jù)遷移時(shí)執(zhí)行腳本用CTAS方式建表忘記額外生成主鍵了。其實(shí)我想說(shuō)的是，只要不是過(guò)于復(fù)雜的業(yè)務(wù)過(guò)程或者大規(guī)模運(yùn)算的情況下哪有那么多性能問(wèn)題，往往性能問(wèn)題都是因?yàn)橐恍?ldquo;粗心大意”下引起的，聽(tīng)我說(shuō)多用checklist準(zhǔn)沒(méi)錯(cuò)的。

到此這篇關(guān)于Oracle數(shù)據(jù)庫(kù)執(zhí)行慢問(wèn)題排查詳細(xì)過(guò)程的文章就介紹到這了,更多相關(guān)Oracle執(zhí)行慢問(wèn)題排查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論