快捷導(dǎo)航

Oracle數(shù)據(jù)庫執(zhí)行慢問題排查詳細過程

更新時間：2023年07月25日 15:51:38 作者：kida_yuan

這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫執(zhí)行慢問題排查的詳細過程,在企業(yè)級應(yīng)用中,數(shù)據(jù)庫的穩(wěn)定性和性能是至關(guān)重要的,文中給出了詳細的代碼實例,需要的朋友可以參考下

1. 若出現(xiàn)插入速度慢或者無法插入數(shù)據(jù)的情況下，先檢查表空間
2. 表空間正常但發(fā)現(xiàn)只能查詢不能修改或者插入時，可以初步判定存在鎖表的可能性
3. 服務(wù)器端出現(xiàn)多個Oracle進程并CPU占用率高
4. 進一步深挖究竟執(zhí)行什么導(dǎo)致CPU使用率高
5. 還有另一種解法獲取等待時間長的用戶和執(zhí)行語句
后日談

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

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

“All Roads Lead to Rome”

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

1. 若出現(xiàn)插入速度慢或者無法插入數(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)將列名都用中文標識清楚了，若用戶表空間使用率達到峰值，則基本只能查詢，其他的操作都不能做了。除此之外，本腳本還可以看到系統(tǒng)的表空間情況，其中值得注意的是UNDOTBS1這個表空間，如下圖：

請?zhí)砑訄D片描述

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

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

通過以下語句查詢是否存在表級鎖

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;

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

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掉當前死鎖執(zhí)行腳本如下：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

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

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

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

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

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

請?zhí)砑訄D片描述

4. 進一步深挖究竟執(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í)行之后如下圖所示，查詢出來后可以根據(jù)USERNAME來進行篩選，再來拿到語句的ADDRESS（SGA內(nèi)存地址）和HASH_VALUE（Oracle Hash值）進行后續(xù)查詢。

請?zhí)砑訄D片描述

下面腳本將根據(jù)ADDRESS和HASH_VALUE來找到對應(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（所有子游標運行這條語句導(dǎo)致的讀內(nèi)存次數(shù)），DISK_READS（所有子游標運行這條語句導(dǎo)致的讀磁盤次數(shù)），EXECUTIONS（所有子游標的執(zhí)行這條語句次數(shù)），PARSE_CALLS （語句的解析調(diào)用(軟、硬)次數(shù)），CPU_TIME （語句被解析和執(zhí)行的CPU時間），如下圖：

請?zhí)砑訄D片描述

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

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

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;

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

后日談

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

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

您可能感興趣的文章: