1. Oracle 慢查詢排查步驟

1.1. 前言
記錄一次 Oracle 慢查詢的排查過程 , 便于以后直接使用。
看了一些文檔 , Oracle 中優化的方案和 Mysql 基本上是一致的 , 通常包括一下幾個方向 :

基準測試 (吞吐量): 包括 Oracle 本身吞吐量和磁盤 I/O 吞吐量
硬件分析 (資源情況): 包括查看服務器 CPU , 硬盤的使用情況
SQL 分析: 分析 SQL 中是否存在慢查詢 , 是否命中索引
配置優化: 分析是否可以通過環境配置提高性能
以上幾個方面 , 基本上就能將問題定位了 , 通過問題再考慮解決的方法

1.2. 排查步驟

1.2.1. 查詢慢查詢日志

?區別于 Mysql 直接寫到 log 中的日志 , Oracle 可以通過語句拉出慢查詢的 SQL

# 慢查詢耗時
select *from (select sa.SQL_TEXT "執行 SQL",sa.EXECUTIONS "執行次數",round(sa.ELAPSED_TIME / 1000000, 2) "總執行時間",round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均執行時間",sa.COMMAND_TYPE,sa.PARSING_USER_ID "用戶 ID",u.username "用戶名",sa.HASH_VALUEfrom v$sqlarea saleft join all_users uon sa.PARSING_USER_ID = u.user_idwhere sa.EXECUTIONS > 0order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)where rownum <= 50;

# 查詢次數最多的 SQL?
select *from (select s.SQL_TEXT,s.EXECUTIONS "執行次數",s.PARSING_USER_ID "用戶名",rank() over(order by EXECUTIONS desc) EXEC_RANKfrom v$sql sleft join all_users uon u.USER_ID = s.PARSING_USER_ID) twhere exec_rank <= 100;

結果解釋 :

拿到平均執行時間后就可以明顯的發現查詢時間較長的 SQL , 但是這一類 SQL 不一定是慢查詢 , 需要根據情況判斷 , 如果出現很離譜的時間 , 就需要分析索引

1.2.2. Oracle 查詢 SQL 語句執行的耗時

select a.sql_text SQL 語句,?b.etime 執行耗時,?c.user_id 用戶 ID,c.SAMPLE_TIME 執行時間,?c.INSTANCE_NUMBER 實例數,?u.username 用戶名, a.sql_id SQL 編號from dba_hist_sqltext a,(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etimefrom dba_hist_sqlstatwhere ELAPSED_TIME_DELTA / 1000000 >= 1) b,dba_hist_active_sess_history c,dba_users uwhere a.sql_id = b.sql_idand u.username = 'SYNC_PLUS_1_20190109'and c.user_id = u.user_idand b.sql_id = c.sql_id-- and a.sql_text like '%insert into GK_ZWVCH_HSC_NEW ? ? ?select ?%'order by ?SAMPLE_TIME desc,b.etime desc;

1.2.3. 定位系統里面哪些 SQL 腳本存在 TABLE ACCESS FULL (掃全表) 行為

select *from v$sql_plan vwhere v.operation = 'TABLE ACCESS'and v.OPTIONS = 'FULL'and v.OBJECT_OWNER='SYNC_PLUS_1_20190109';

select s.SQL_TEXTfrom v$sqlarea swhere s.SQL_ID = '4dpd97jh2gzsd'and s.HASH_VALUE = '1613233933'and s.PLAN_HASH_VALUE = '3592287464';
或者select s.SQL_TEXT from v$sqlarea s where s.ADDRESS = '00000000A65D2318';

1.2.4. 查看索引情況

explain plan for
select * from t_call_records where t_bjhm='123456'# 查看執行結果
select * from table(dbms_xplan.display)

索引內容補充

從這里可以明顯看到走了全表掃描 , 那么就需要根據情況加索引和校驗

index unique scan : 索引唯一掃描 (主鍵索引)
index range scan : 索引范圍掃描 (組合索引的情況)
index full scan : 全索引掃描
index fast full scan : 索引快速掃描, 掃描索引中的全部的數據塊, 與全索引掃描的方式基本上類似。
兩者之間的明顯的區別是, 索引快速掃描對查詢的數據不進行排序, 數據返回的時候不是排序的。

1.2.5. 查看鎖的競爭情況

Step 1 : 查看后臺鎖競爭

SELECTSQ.INST_ID,SQ.SQL_TEXT, /*SQL 文本*/SE.SID, /*會話的唯一標識, 通常要對某個會話進行分析前, 首先就需要獲得該會話的 SID。*/SE.BLOCKING_SESSION,SQ.OPTIMIZER_COST AS COST_,/* COST 值*/SE.LAST_CALL_ET CONTINUE_TIME,/*執行時間*/SE.EVENT,/*等待事件*/SE.LOCKWAIT,/*是否等待 LOCK(SE, P)*/SE.MACHINE,/*客戶端的機器名。(WORKGROUP\PC-201211082055)*/SQ.SQL_ID,/*SQL_ID*/SE.USERNAME,/*創建該會話的用戶名*/SE.LOGON_TIME,/*登陸時間*/'ALTER SYSTEM KILL SESSION ' || SE.SID || ',' || SE.SERIAL # ?--若存在鎖情況, 會用到 KILL 鎖釋放~
FROMgV$SESSION SE,/*會話信息。每一個連接到 ORACLE 數據庫的會話都能在該視圖中對應一條記錄*/gV$SQLAREA SQ /*跟蹤所有 SHARED POOL 中的共享 CURSOR 信息, 包括 執行次數, 邏輯讀, 物理讀等*/
WHERESE.SQL_HASH_VALUE = SQ.HASH_VALUE ? ?AND SE.STATUS = 'ACTIVE' ? ?AND SE.SQL_ID = SQ.SQL_ID ? ?AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME ? ? ? --過濾條件AND SE.USERNAME = 'FWSB' --用戶名AND se.BLOCKING_SESSION IS NOT NULL;// 實際運行腳本====================== ??
SELECTSQ.INST_ID,SQ.SQL_TEXT,SE.SID,SE.BLOCKING_SESSION,SQ.OPTIMIZER_COST AS COST_,SE.LAST_CALL_ET CONTINUE_TIME,SE.EVENT,SE.LOCKWAIT,SE.MACHINE,SQ.SQL_ID,SE.USERNAME,SE.LOGON_TIME,'ALTER SYSTEM KILL SESSION ' || SE.SID || ','?
FROMgV$SESSION SE,gV$SQLAREA SQ?
WHERESE.SQL_HASH_VALUE = SQ.HASH_VALUE?AND SE.STATUS = 'ACTIVE'?AND SE.SQL_ID = SQ.SQL_ID?AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME?AND SE.USERNAME = 'FWSB'?AND SE.BLOCKING_SESSION IS NOT NULL;

補充 : 相關的表結構可以生乳查詢 Oracle 官方文檔

Step 2 : 查詢結果

這里可以通過 SID 再去查找對應的 SQL , 找到對應的鎖對象

1.2.6. 其他鎖語句

查詢那些用戶, 操縱了那些表造成了鎖機

SELECTs.username,decode(l.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL ) LOCK_LEVEL,o.owner,o.object_name,o.object_type,s.sid,s.terminal,s.machine,s.program,s.osuser
FROMv$session s,v$lock l,all_objects o
WHEREl.sid = s.sidAND l.id1 = o.object_id(+)AND s.username is NOT Null

詳情參考 :—> V$Lock

查出被鎖的表, 和鎖住這個表的會話 ID

select a.session_id ,b.* from v$locked_object a,all_objects b where a.object_id=b.object_id

查出對應的 SQL 語句

SELECTvs.SQL_TEXT,vsess.sid,vsess.SERIAL #,vsess.MACHINE,vsess.OSUSER,vsess.TERMINAL,vsess.PROGRAM,vs.CPU_TIME,vs.DISK_READS?
FROMv$sql vs,v$session vsess?
WHEREvs.ADDRESS = vsess.SQL_ADDRESS?AND vsess.sid = 36

補充語句 :

// 查哪個過程被鎖 -> 查 V$DB_OBJECT_CACHE 視圖:?
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='過程的所屬用戶' AND LOCKS!='0';// 查是哪一個 SID, 通過 SID 可知道是哪個 SESSION. -> 查 V$ACCESS 視圖:?
SELECT * FROM V$ACCESS WHERE OWNER='過程的所屬用戶' AND NAME='剛才查到的過程名';// 查出 SID 和 SERIAL# -> 查 V$SESSION 視圖 + 查 V$PROCESS 視圖
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='剛才查到的 SID'
SELECT SPID FROM V$PROCESS WHERE ADDR='剛才查到的 PADDR';

1.3. 慢查詢優化

1.3.1. SQL 部分

// 避免 in 操作
Oracle 中 in 會被試圖轉換成多個表的連接 , 轉換不成功會先進行 in 中的子查詢 , 再進行外部查詢// 避免 not in
不管哪個數據庫 , 一般都是不推薦的 , 這種寫法會跳過索引 (同理還有 is null 和 not null)// 避免使用 <>
類似 , 不走索引// **采用函數處理的字段不能利用索引**// 關聯查詢
- 多用 Where 語句把單個表的結果集最小化, 多用聚合函數匯總結果集后再與其它表做關聯
- 多用 右連接// 過濾多用 where , 避免使用 having
- 這個和 mysql 是一致的 , having 是對 where 的數據進行過濾組處理 , 對于數據的過濾 , 優先用 where
- 總結 : 先過濾小的結果集, 然后通過這個小的結果集和其他表做關聯// like 操作符
like 操作可以通過 instr 代替// union 操作符?
- 通常不會產生重復結果 , 而 union 會額外觸發一次排序
- 采用 union ALL 操作符替代 union, 因為 union ALL 操作只是簡單的將兩個結果合并后就返回// SQL 執行保證統一性
涉及到 SGA 的概念// where 后面的條件順序影響
這里不是全表索引的問題 , 而是由于 where 多個條件時 , 比較帶來的 cpu 占用率問題// 詢表順序的影響
- 表的順序不對會產生十分耗服務器資源的數據交叉// 其他的方案還包括以下方式
@ https://www.jb51.net/article/97515.htm@ https://www.jb51.net/article/23071.htm@ https://www.jb51.net/article/40281.htm

1.4. 性能優化

1.4.1. 整體性能優化流程

這里直接引用別人文章的結果 , 沒有測試 , 僅供參考 !

// PS : 初始化時間 49.41// 增大 SGA Buffer Cache 和 SGA Shared Pool -> 48.57
- 增大 SGA 已經緩沖看來對于性能的提升并不顯著, 加載時間只提升了 1.73%// 增大 SGA Redo Cache 和 Redo Log Files -> 41.39
- 加載時間提升了 17.35%, TPS 也提升了 9.33%。因為加載和同時插入, 更新, 刪除需要比 8M 大的空間
- 但是看起來增加內存性能并沒有顯著提升// 增大 Database Block Size (2K-4K) -> 17.35
- 加載時間提升了 138%! 而對 TPS 值沒有很大的影響// 使用 Tablespaces Local -> 15.07
- TPS 輕微提升// Database Block Size 增大 (4K-8K) -> 11.42
- TPS 繼續提升 , 區別較大// 添加 io_slaves -> 10.48
dbwr_io_slaves 4\
lgwr_io_slaves (derived) 4// 優化 Linux 內核 -> 9.40
可以看到 , 內核版本優化后 , 性能是有一定提升的// 調整虛擬子內存 -> 5.58
- /ect/sysctl.cong-> vm.bdflush = 100 1200 128 512 15 5000 500 1884 2

這個流程不能作為標桿 , 但是可以作為優化 Oracle 的思路 , 可以看到 , 性能提升很大

1.4.2. 硬件優化

此處是使用 IO 校準 (I/O Calibration), 可以用于評測一下數據庫的 I/O 性能 , 通過 分析 IO 結果判斷采用不同的策略

// Step 1 : 確定并行度配置 (通常是核數的 2 倍)
show parameters parallel_thread// Step 2 : 確定并行策略 (auto : Oracle 將依據要執行的操作的特性和對象的大小來確定并行度)
- 查詢策略 : show parameters parallel_degree_policy
- 設置策略 : alter session set parallel_degree_policy = 'auto'// Step 3 : 查看并行度數據
- 打開系統默認設置的輸出功能 : set serveroutput on
- 查看詳情 :?
set serveroutput on
DECLARElat ?INTEGER;iops INTEGER;mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);DBMS_OUTPUT.PUT_LINE ('latency ?= ' || lat);dbms_output.put_line('max_mbps = ' || mbps);
end;
/// 問題補充 : ORA-56708: 找不到任何具有異步 I/O 功能的數據文件
- 確定 sync : show parameter filesystemio_options
- 設置 sync : filesystemio_options- ASYNCH: 使 Oracle 支持文件的異步 (Asynchronous)IO- DIRECTIO: 使 Oracle 支持文件的 Direct IO- SETALL: 使 Oracle 同時支持文件的 Asynchronous IO 和 Direct IO- NONE: 使 Oracle 關閉對 Asynchronous IO 和 Direct IO 的支持1> alter system set filesystemio_options=setall scope=spfile;2> shutdown immediate;3> startup// PS : 注意其中管理員權限問題alter system set filesystemio_options=none scope=spfile;

1.5. 概念補充

1.5.1. SGA

系統全局區域 (SGA) 是一組共享內存結構, 稱為 SGA 組件, 包含一個 Oracle 數據庫實例的數據和控制信息。SGA 由所有服務器和后臺進程共享。SGA 中存儲的數據示例包括緩存的數據塊和共享的 SQL 區域。

組成部分 :

Database buffer cache : 數據緩存
在查詢或修改數據庫中存儲的數據之前, 必須從磁盤讀取數據并將其存儲在緩沖區緩存中。
所有連接到數據庫的用戶進程都共享對緩沖區緩存的訪問。
為了獲得最佳性能, 緩沖區緩存應該足夠大, 以避免頻繁的磁盤 I/O 操作。
Shared pool : 共享池緩存用戶共享的信息 , 包括如下內容
可重用的 SQL 語句
來自數據字典的信息, 例如用戶帳戶數據、表和索引描述以及特權
存儲過程, 它是存儲在數據庫中的可執行代碼
Redo log buffer : 這個緩沖區通過緩存重做信息來提高性能, 直到可以將它寫入存儲在磁盤上的物理在線重做日志文件
Large pool : 這個可選區域用于為各種服務器進程緩沖大型 I/O 請求
Java pool : Java 池是用于 Java 虛擬機 (JVM) 中所有特定于會話的 Java 代碼和數據的內存區域
Streams pool : Streams 池是 Oracle Streams 特性使用的內存區域
Result cache : 結果緩存緩沖區查詢結果。如果運行的查詢將結果存儲在結果緩存中, 那么數據庫將從結果緩存返回查詢結果, 而不是重新運行查詢。

1.6. 總結

筆者只是基于通過業務要求的角度進行 Oracle 優化 , 并沒有深入 Oracle 業務優化 , 感興趣的可以看看 《Oracle 數據庫性能優化方法論和最佳實踐》, 對數據庫進行系統的優化。

2. 解析 oracle 對 select 加鎖的方法以及鎖的查詢

2.1. oracle 對 select 加鎖方法

create table test(a number,b number);
insert into test values(1,2);
insert into test values(3,4);
insert into test values(8,9);
commit;
---session 1 模擬選中一個號碼
SQL> select * from test where a =1 for update skip locked;A ? ? ? ? ?B
---------- ----------1 ? ? ? ? ?2
---session 2 對 a=1 再進行 select
SQL> select * from test where a = 1 for update skip locked;
未選定行
-- session 3 全表 select
SQL> select * from test for update skip locked;A ? ? ? ? ?B
---------- ----------3 ? ? ? ? ?48 ? ? ? ? ?9
SQL>

2.2. 查詢那些用戶, 操縱了那些表造成了鎖機

SELECT s.username,
decode(l.type,‘TM’,‘TABLE LOCK’,
‘TX’,‘ROW LOCK’,
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM vs e s s i o n s , v session s,vsessions,vlock l,all_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null

2.3. 查出被鎖的表, 和鎖住這個表的會話 ID

select a.session_id ,b.* from v$locked_object a,all_objects b
where a.object_id=b.object_id

2.4. 查出對應的 SQL 語句

select vs.SQL_TEXT,vsess.sid,vsess.SERIAL#,vsess.MACHINE,vsess.OSUSER
,vsess.TERMINAL,vsess.PROGRAM,vs.CPU_TIME,vs.DISK_READS
from vs q l v s , v sql vs,vsqlvs,vsession vsess
where vs.ADDRESS=vsess.SQL_ADDRESS
and vsess.sid=(上面查出來的會話 ID)

2.5. 集合

2.5.1. 查哪個過程被鎖

查v$db_object_cache視圖:SELECT * FROM v$db_object_cache a where a.locks='1'

2.5.2. 查是哪一個 SID, 通過 SID 可知道是哪個 SESSION.

SELECT * FROM V$ACCESS A WHERE A.owner='BIGSCREEN';

2.5.3. 查出 SID 和 SERIAL#

SELECT * FROM V$SESSION A WHERE A.Sid='45';
SELECT * FROM V$PROCESS A WHERE A.addr='0000000113B6ED30'

2.5.4. 殺進程

(1). 先殺 ORACLE 進程:
ALTER SYSTEM KILL SESSION ‘查出的 SID, 查出的 SERIAL#’;
(2). 再殺操作系統進程:
KILL -9 剛才查出的 SPID
或
ORAKILL 剛才查出的 SID 剛才查出的 SPID

2.6. 查找最耗費系統資源的 SQL

–-CPU
select b.sql_text,
a.buffer_gets,
a.executions,
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$SQLAREA A,V$SQLTEXT_WITH_NEWLINES b,dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address and c.username='BIGSCREEN'
order by a.buffer_gets desc , b.piece-–IO
select b.sql_text,
a.disk_reads,
a.executions,
a.disk_reads/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$SQLAREA A,V$SQLTEXT_WITH_NEWLINES b,dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address and c.username='BIGSCREEN'
order by a.disk_reads desc , b.piece;select n.*,s.sid,s.value  CPU_Used  
from v$sesstat s ,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;