记录一次 Oracle 慢查询的排查过程 , 便于以后直接使用。
看了一些文档 , Oracle 中优化的方案和 Mysql 基本上是一致的 , 通常包括一下几个方向 :
以上几个方面 , 基本上就能将问题定位了 , 通过问题再考虑解决的方法
区别于 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 <= 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;
结果解释 :
拿到平均执行时间后就可以明显的发现查询时间较长的 SQL , 但是这一类 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 etime
from dba_hist_sqlstat
where ELAPSED_TIME_DELTA / 1000000 >= 1) b,
dba_hist_active_sess_history c,
dba_users u
where a.sql_id = b.sql_id
and u.username = 'SYNC_PLUS_1_20190109'
and c.user_id = u.user_id
and 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;
select *
from v$sql_plan v
where v.operation = 'TABLE ACCESS'
and v.OPTIONS = 'FULL'
and v.OBJECT_OWNER='SYNC_PLUS_1_20190109';
select s.SQL_TEXT
from v$sqlarea s
where 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';
explain plan for
select * from t_call_records where t_bjhm='123456'
# 查看执行结果
select * from table(dbms_xplan.display)
索引内容补充
从这里可以明显看到走了全表扫描 , 那么就需要根据情况加索引和校验
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$SESSION SE,/*会话信息。每一个连接到 ORACLE 数据库的会话都能在该视图中对应一条记录*/
gV$SQLAREA 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$SESSION SE,
gV$SQLAREA 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;
补充 : 相关的表结构可以生乳查询 Oracle 官方文档
Step 2 : 查询结果
这里可以通过 SID 再去查找对应的 SQL , 找到对应的锁对象
查询那些用户, 操纵了那些表造成了锁机
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$session s,
v$lock l,
all_objects o
WHERE
l.sid = s.sid
AND 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 语句
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';
// 避免 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 问题 , 性能优化也就碰到过几次 , 导致方法学到不少 , 实际就用过几个 , 但是我都记下来了! !! 😜😜😜
这里直接引用别人文章的结果 , 没有测试 , 仅供参考 !
// 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 的思路 , 可以看到 , 性能提升很大
此处是使用 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;
系统全局区域 (SGA) 是一组共享内存结构, 称为 SGA 组件, 包含一个 Oracle 数据库实例的数据和控制信息。SGA 由所有服务器和后台进程共享。SGA 中存储的数据示例包括缓存的数据块和共享的 SQL 区域。
组成部分 :
笔者只是基于通过业务要求的角度进行 Oracle 优化 , 并没有深入 Oracle 业务优化 , 感兴趣的可以看看 《Oracle 数据库性能优化方法论和最佳实践》, 对数据库进行系统的优化。
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>
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
select a.session_id ,b.* from v$locked_object a,all_objects b
where a.object_id=b.object_id
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)
查 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’;
查 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=‘刚才查到的过程名’;
查 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’;
(1). 先杀 ORACLE 进程:
ALTER SYSTEM KILL SESSION ‘查出的 SID, 查出的 SERIAL#’;
(2). 再杀操作系统进程:
KILL -9 刚才查出的 SPID
或
ORAKILL 刚才查出的 SID 刚才查出的 SPID
–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;