手记 : Oracle 慢查询排查步骤

news/2025/1/2 11:06:59/

这篇文章主要记录了 Oracle 慢查询的排查步骤、优化方法及相关概念。排查步骤包括查询慢查询日志、查看索引和锁的竞争情况等；优化涵盖 SQL 部分和整体性能、硬件方面；还介绍了 SGA 等概念。文中提到多种优化策略，并指出性能优化流程仅供参考，后续可能会深入研究 Oracle 细节。

一 . 前言
记录一次 Oracle 慢查询的排查过程 , 便于以后直接使用.
看了一些文档 , Oracle 中优化的方案和 Mysql 基本上是一致的 , 通常包括一下几个方向 :

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

以上几个方面 , 基本上就能将问题定位了 , 通过问题再考虑解决的方法
. 排查步骤
2.1 查询慢查询日志

区别于 Mysql 直接写到 log 中的日志 , Oracle 可以通过语句拉出慢查询的 Excle log @ oracle 慢查询 - 我是属车的 - 博客园 (cnblogs.com)

慢查询耗时

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 <= 50;

查询次数最多的 SQL

select *
from (select s.SQL_TEXT,
s.EXECUTIONS “执行次数”,
s.PARSING_USER_ID “用户名”,
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;

结果解释 :

image.png

拿到平均执行时间后就可以明显的发现查询时间较长的 SQL , 但是这一类 SQL 不一定是慢查询 , 需要根据情况判断 , 如果出现很离谱的时间 , 就需要分析索引
2.2 查看索引情况

explain plan for
select * from t_call_records where t_bjhm=‘123456’

查看执行结果

select * from table(dbms_xplan.display)

索引内容补充

image.png

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

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

2.3 查看锁的竞争情况

Step 1 : 查看后台锁竞争

SELECT
SQ.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锁释放~
FROM
gV S E S S I O N S E , / ∗ 会话信息。每一个连接到 O R A C L E 数据库的会话都能在该视图中对应一条记录 ∗ / g V SESSION SE,/*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录*/ gV SESSIONSE,/∗会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录∗/gVSQLAREA SQ /跟踪所有SHARED POOL中的共享CURSOR信息，包括 执行次数，逻辑读，物理读等/
WHERE
SE.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;

// 实际运行脚本======================
SELECT
SQ.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 || ‘,’
FROM
gV S E S S I O N S E , g V SESSION SE, gV SESSIONSE,gVSQLAREA SQ
WHERE
SE.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;

image.png

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

Step 2 : 查询结果

image.png

这里可以通过 SID 再去查找对应的 SQL , 找到对应的锁对象
2.4 其他锁语句

以下内容参考自 : blog.csdn.net/u011019491/… , 各位可以看看原文

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

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.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

详情参考 :—> V$Lock

image.png

查出被锁的表，和锁住这个表的会话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 s q l v s , v sql vs, v sqlvs,vsession vsess
WHERE
vs.ADDRESS = vsess.SQL_ADDRESS
AND vsess.sid = 36

image.png

补充语句 :

// 查哪个过程被锁 -> 查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 DBO​BJECTC​ACHE视图:SELECT∗FROMVDB_OBJECT_CACHE WHERE OWNER=‘过程的所属用户’ AND LOCKS!=‘0’;

// 查是哪一个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=‘刚才查到的过程名’;

// 查出SID和SERIAL# -> 查V S E S S I O N 视图 + 查 V SESSION视图 + 查V SESSION视图+查VPROCESS视图
SELECT SID,SERIAL#,PADDR FROM V S E S S I O N W H E R E S I D = ′ 刚才查到的 S I D ′ S E L E C T S P I D F R O M V SESSION WHERE SID='刚才查到的SID' SELECT SPID FROM V SESSIONWHERESID=′刚才查到的SID′SELECTSPIDFROMVPROCESS WHERE ADDR=‘刚才查到的PADDR’;

三 . 慢查询优化
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

四 . 性能优化

挺不好意思的!!!
都是抄的书上的 !!!
而且大多数还没实践过 !!!

Oracle 毕竟接触有限 , 就算碰到了多数是SQL 问题 , 性能优化也就碰到过几次 , 导致方法学到不少 , 实际就用过几个 , 但是我都记下来了!!! 😜😜😜
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 的思路 , 可以看到 , 性能提升很大
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;

五 . 概念补充
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 : 结果缓存缓冲区查询结果。如果运行的查询将结果存储在结果缓存中，那么数据库将从结果缓存返回查询结果，而不是重新运行查询。

http://www.ppmy.cn/news/1559558.html

相关文章

艾体宝方案丨全面提升API安全：AccuKnox 接口漏洞预防与修复

艾体宝方案丨全面提升API安全：AccuKnox 接口漏洞预防与修复

一、API 安全&#xff1a;现代企业的必修课 在现代技术生态中&#xff0c;应用程序编程接口&#xff08;API&#xff09;扮演着不可或缺的角色。从数据共享到跨平台集成&#xff0c;API 成为连接企业系统与外部服务的桥梁。然而&#xff0c;伴随云计算的普及与微服务架构的流行…
阅读更多...
前端网络之【浏览器跨域问题分析与解决方案】

前端网络之【浏览器跨域问题分析与解决方案】

文章目录 同源策略同源与异源跨域的限制场景解决方案 CORS 请求分类 简单请求预检请求 注意点一 【关于Cookie】注意点二 【关于跨域获取响应头】 JSONP 基本流程 代理服务器 如何选择对应方案 同源策略 **同源策略是一套浏览器的安全策略机制&#xff0c;当一个源的文档和脚…
阅读更多...
plantuml的picoweb无法渲染分页图表的问题

plantuml的picoweb无法渲染分页图表的问题

1. 背景 使用plantuml自带的picoweb在渲染分页图表时&#xff0c;vscode无法换页&#xff0c;但是使用plantuml官网渲染则没问题。查看plantuml官网的picoweb的说明&#xff0c;picoweb只是plantuml服务的最小实现。因此考虑可能是plantuml服务不全导致的上述问题。 2. docke…
阅读更多...
【2025优质学术推荐】征稿控制科学、仪器、智能系统、通信、计算机、电子信息、人工智能、大数据、机器学习、软件工程、网络安全方向

【2025优质学术推荐】征稿控制科学、仪器、智能系统、通信、计算机、电子信息、人工智能、大数据、机器学习、软件工程、网络安全方向

【2025优质学术推荐】征稿控制科学、仪器、智能系统、通信、计算机、电子信息、人工智能、大数据、机器学习、软件工程、网络安全方向 【2025优质学术推荐】征稿控制科学、仪器、智能系统、通信、计算机、电子信息、人工智能、大数据、机器学习、软件工程、网络安全方向 文章目…
阅读更多...
【Springboot知识】Springboot基础-过滤器与拦截器开发

【Springboot知识】Springboot基础-过滤器与拦截器开发

过滤器与拦截器知识 过滤器一、过滤器概述二、过滤器的生命周期三、过滤器的创建与注册四、过滤器的顺序五、过滤器的使用示例六、注意事项 拦截器一、拦截器的作用二、拦截器的实现步骤三、拦截器的具体实现四、拦截器的执行流程五、注意事项 过滤器与拦截器的区别一、出身与实…
阅读更多...
医疗数仓数据仓库设计

医疗数仓数据仓库设计

医疗数仓数据仓库设计 数据仓库构建流程数据调研明确数据域构建业务总线矩阵明确统计指标交易主题医生主题用户主题评价主题 维度模型设计汇总模型设计 数据仓库构建流程 数据仓库分层规划 优秀可靠的数仓体系&#xff0c;需要良好的数据分层结构。合理的分层&#xff0c;能够…
阅读更多...
Windows11 的开发

Windows11 的开发

Windows 11的开发涉及多个方面&#xff0c;包括应用开发、系统集成、安全性增强等。以下是对Windows 11开发的具体内容、开发方法以及常用开发语言的详细解答&#xff1a; 一、Windows 11开发的具体内容 应用开发&#xff1a; 桌面应用&#xff1a;使用UWP&#xff08;通用Win…
阅读更多...
【AI大模型】深入GPT-2模型细节：揭秘其卓越性能的秘密

【AI大模型】深入GPT-2模型细节：揭秘其卓越性能的秘密

目录 &#x1f354; GPT2的架构 &#x1f354; GPT2模型的细节 2.1 模型过程 2.2 GPT2工作细节探究 &#x1f354; 小结 学习目标 掌握GPT2的架构掌握GPT2的训练任务和模型细节 &#x1f354; GPT2的架构 从模型架构上看, GPT2并没有特别新颖的架构, 它和只带有解码器模块…
阅读更多...
最新文章

Copyright @ 2022~2023