Oracle 慢查询排查步骤
目录
- 1. Oracle 慢查询排查步骤
- 1.1. 前言
- 1.2. 排查步骤
- 1.2.1. 查询慢查询日志
- 1.2.2. Oracle 查询 SQL 语句执行的耗时
- 1.2.3. 定位系统里面哪些 SQL 脚本存在 TABLE ACCESS FULL (扫全表) 行为
- 1.2.4. 查看索引情况
- 1.2.5. 查看锁的竞争情况
- 1.2.6. 其他锁语句
- 1.3. 慢查询优化
- 1.3.1. SQL 部分
- 1.4. 性能优化
- 1.4.1. 整体性能优化流程
- 1.4.2. 硬件优化
- 1.5. 概念补充
- 1.5.1. SGA
- 1.6. 总结
- 2. 解析 oracle 对 select 加锁的方法以及锁的查询
- 2.1. oracle 对 select 加锁方法
- 2.2. 查询那些用户, 操纵了那些表造成了锁机
- 2.3. 查出被锁的表, 和锁住这个表的会话 ID
- 2.4. 查出对应的 SQL 语句
- 2.5. 集合
- 2.5.1. 查哪个过程被锁
- 2.5.2. 查是哪一个 SID, 通过 SID 可知道是哪个 SESSION.
- 2.5.3. 查出 SID 和 SERIAL#
- 2.5.4. 杀进程
- 2.6. 查找最耗费系统资源的 SQL
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_VALUE from v$sqlarea sa left join all_users u on sa.PARSING_USER_ID = u.user_id where sa.EXECUTIONS > 0 order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum V$Lock查出被锁的表, 和锁住这个表的会话 ID
select a.session_id ,b.* from v$locked_object a,all_objects b where a.object_id=b.object_id
查出对应的 SQL 语句
SELECT vs.SQL_TEXT, vsess.sid, vsess.SERIAL #, vsess.MACHINE, vsess.OSUSER, vsess.TERMINAL, vsess.PROGRAM, vs.CPU_TIME, vs.DISK_READS FROM v$sql vs, v$session vsess WHERE vs.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. 性能优化
挺不好意思的! !!
都是抄的书上的 !!!
而且大多数还没实践过 !!!
Oracle 毕竟接触有限 , 就算碰到了多数是 SQL 问题 , 性能优化也就碰到过几次 , 导致方法学到不少 , 实际就用过几个 , 但是我都记下来了! !! 😜😜😜
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 DECLARE lat 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 4 8 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 v s e s s i o n s , v session s,v sessions,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 v s q l v s , v sql vs,v sqlvs,vsession vsess
where vs.ADDRESS=vsess.SQL_ADDRESS
and vsess.sid=(上面查出来的会话 ID)
2.5. 集合
2.5.1. 查哪个过程被锁
查 V D B O B J E C T C A C H E 视图 : S E L E C T ∗ F R O M V DB_OBJECT_CACHE 视图: SELECT * FROM V DBOBJECTCACHE视图:SELECT∗FROMVDB_OBJECT_CACHE WHERE OWNER=‘过程的所属用户’ AND LOCKS!=‘0’;
2.5.2. 查是哪一个 SID, 通过 SID 可知道是哪个 SESSION.
查 V A C C E S S 视图 : S E L E C T ∗ F R O M V ACCESS 视图: SELECT * FROM V ACCESS视图: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’
查 V P R O C E S S 视图 : S E L E C T S P I D F R O M V PROCESS 视图: SELECT SPID FROM V PROCESS视图: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 V s q l a r e a a , v sqlarea a, v sqlareaa,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 v s q l a r e a a , v sqlarea a, v sqlareaa,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 v s e s s t a t s , v sesstat s,v sesstats,vstatname n
where s.statistic#=n.statistic# and n.name=‘CPU used by this session’
and s.value>0
order by 2 desc;
- Database buffer cache : 数据缓存
