当前位置：首页 > article >正文

Oracle 慢sql排查

article 2025/9/26 22:33:47

Oracle慢sql排查步骤

1.1. 前言

Oracle 慢查询的排查方向包括以下几个方向 :

基准测试 (吞吐量): 包括 Oracle 本身吞吐量和磁盘 I/O 吞吐量
硬件分析 (资源情况): 包括查看服务器 CPU , 硬盘的使用情况
SQL分析：分析 SQL 中是否存在慢查询 , 是否命中索引
配置优化: 分析是否可以通过环境配置提高性能

以上几个方面 , 基本上就能将问题定位了 , 通过问题再考虑解决的方法。

Oracle提供了多种工具来帮助追踪和优化慢查询，例如：

SQL Tuning Advisor：自动生成SQL语句的优化建议。
Automatic Workload Repository (AWR)：收集数据库性能数据，帮助分析性能瓶颈。
Oracle Enterprise Manager：提供图形化界面，帮助监控和管理数据库。

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;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_idorder 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='ORCL';

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_records where t_bjhm='123456';

select * from table(dbms_xplan.display);

EXPLAIN PLAN 的输出通常包含以下字段：

Operation：表示 Oracle 执行查询时使用的操作类型（如表扫描、索引扫描、连接操作）。
Options：显示特定操作的选项（如表扫描是全表扫描还是索引扫描）。
Object Name：表示查询操作所涉及的对象（如表或索引）。
Cost：表示 Oracle 估算的该操作的相对成本，值越大表示该操作的成本越高。
Cardinality：估算的行数，表示该操作处理的行数。
Bytes：估算的字节数，表示该操作处理的字节数。
Time：Oracle 估计的完成该操作所需的时间。
Predicate Information：显示查询条件和过滤谓词，帮助理解优化器如何应用 WHERE 子句。

索引内容补充

从这里可以明显看到走了全表扫描 , 那么就需要根据情况加索引和校验

index unique scan : 索引唯一扫描 (主键索引)
index range scan : 索引范围扫描 (组合索引的情况)
index full scan : 全索引扫描
index fast full scan : 索引快速扫描, 扫描索引中的全部的数据块, 与全索引扫描的方式基本上类似。
- 两者之间的明显的区别是, 索引快速扫描对查询的数据不进行排序, 数据返回的时候不是排序的。

1.2.5. 查看锁的竞争情况

Step 1 : 查看后台锁竞争

然后通过 SID 再去查找对应的 SQL , 找到对应的锁对象

补充 : 相关的表结构可以深入查询 Oracle 官方文档

1.2.6. 其他锁语句

查询那些用户, 操纵了那些表造成了锁机

查出被锁的表, 和锁住这个表的会话 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

补充语句 :

SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';SELECT SID,SERIAL
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的 PADDR';

1.3. 慢查询优化

1.3.1. SQL 部分

跳过索引：in、is null、not null
原因：
Oracle 中 in 会被试图转换成多个表的连接 , 转换不成功会先进行 in 中的子查询 , 再进行外部查询
不管哪个数据库 , 一般都是不推荐的 , 这种写法会跳过索引 建议：
- 多用 Where 语句把单个表的结果集最小化, 多用聚合函数汇总结果集后再与其它表做关联
- 多用 右连接
- 对于数据的过滤优先用 where, having 是对 where 的数据进行过滤组处理
- 总结 : 先过滤小的结果集, 然后通过这个小的结果集和其他表做关联
- 避免使用order by 和group by
- 在where字句中频繁使用的列上创建索引且 确保索引使用了与查询条件一致的数据类型- like 操作可以通过 instr 代替
- 用union ALL 操作符替代union, 因为union ALL操作只是简单的将两个结果合并后就返回，而union会额外触发一次排序

1.4. 性能优化

1.4.1. 整体性能优化流程

增大SGA缓冲

1.4.2. 硬件优化

此处是使用 IO 校准 (I/O Calibration), 可以用于评测一下数据库的 I/O 性能 , 通过分析 IO 结果判断采用不同的策略

show parameters parallel_thread- 查询策略 : show parameters parallel_degree_policy
- 设置策略 : alter session set parallel_degree_policy = 'auto'- 打开系统默认设置的输出功能 : set serveroutput on
- 查看详情 : 
set serveroutput on
DECLARElat  INTEGER;iops INTEGER;mbps INTEGER;
BEGINDBMS_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;
/- 确定 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> startupalter 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 : 结果缓存缓冲区查询结果。如果运行的查询将结果存储在结果缓存中, 那么数据库将从结果缓存返回查询结果, 而不是重新运行查询。

PS：可以看看《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;SQL> select * from test where a =1 for update skip locked;A          B1          2SQL> select * from test where a = 1 for update skip locked;
未选定行SQL> select * from test for update skip locked;A          B3          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 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 vsqlvs,vsession vsess
where vs.ADDRESS=vsess.SQL_ADDRESS and vsess.sid=(上面查出来的会话 ID)

2.5. 集合

2.5.1. 查哪个过程被锁

查 VDBOBJECTCACHE视图:

SELECT∗FROM VDB_OBJECT_CACHE WHERE OWNER=‘过程的所属用户’ AND LOCKS!=‘0’;

2.5.2. 查是哪一个 SID, 通过 SID 可知道是哪个 SESSION

查 VACCESS视图:

SELECT∗FROMVACCESS WHERE OWNER=‘过程的所属用户’ AND NAME=‘刚才查到的过程名’;

2.5.3. 查出 SID 和 SERIAL#

查 VKaTeX parse error: Expected 'EOF', got '#' at position 31: …LECT SID,SERIAL#̲,PADDR FROM VSESSION WHERE SID=‘刚才查到的 SID’
查 VPROCESS视图:SELECTSPIDFROMVPROCESS WHERE ADDR=‘刚才查到的 PADDR’;

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 Vsqlareaa,vsqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address 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 vsqlareaa,vsqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address order by a.disk_reads desc , b.piece

select s.sid,s.value “CPU Used”
from vsesstats,vstatname n
where s.statistic#=n.statistic# and n.name=‘CPU used by this session’
and s.value>0 order by 2 desc;