系统运行过程中可能遇见各种各样的性能问题,比如latch、enq、buffer并发竞争、gc的cluster节点竞争、db file sequential read、db file scattered等IO等待,或者direct path read temp、direct path write temp等临时表空间磁盘上的等待竞争。
如果是当前系统的性能问题是可以通过查询Oracle的数据字典视图、hanganalyze、systemdump、event等方法来分析性能问题,而如果是历史的则可以通过awr、ash、addm等诊断工具,但是有些瞬间出现的性能问题则可能在awr、addm的报告中并不明显或者由于采样粒度无法获取短暂的时间内的性能问题,此时则可以通过历史的ash相关的数据来获取,下面以历史temp表空间不足和热块故障的两个案例来展示如何对历史和当前的性能问题来分析。
案例之temp表空间不足
一、 故障现象和描述
客户一套核心的系统,在运行某个核心业务时总是报出temp表空间不足问题,相关业务也会因为该异常导致无法运行完毕,经和业务沟通了解到该业务是包含存储过程、匿名块还有某些单独运行的SQL,而业务也无法知道是哪个部分运行导致temp表空间消耗殆尽而抛出错误。
二、 故障分析—警告日志
对于该问题我们的常规思路就是登陆系统查看temp表空间使用率,可是登陆系统后查询temp表空间使用率非常低,消耗temp的相关会话和SQL_ID如下:
SELECT se.username,
sid,
serial#,
se.sql_id,/*需要注意的是这里查询sql_id要用v$session视图的sql_id,而不要用v$sort_usage视图的sql_id,v$sort_usage视图里面的sql_id是不准确的*/
machine,
program,
tablespace,
segtype,
su.BLOCKS,
round(((su.blocks*P.VALUE)/1024/1024),2) MB
FROM v$session se,v$sort_usage su,v$parameter p
WHERE se.saddr = su.session_addr
and upper(p.name)='DB_BLOCK_SIZE'
order by su.BLOCKS desc
USERNAME SID SERIAL# SQL_ID MACHINE PROGRAM TABLESPACE SEGTYPE BLOCKS MB
-------------------- ------ ---------- --------------- ------------------------------ ---------------------------------------- ---------- ---------- ---------- ----------
DBSNMP 974 36807 norddb12 JDBC Thin Client TEMP LOB_DATA 256 2
IOM 8993 13460 frhmy3872u19f ordcenter08 JDBC Thin Client TEMP LOB_DATA 256 2
IOM 1711 28464 frhmy3872u19f ordcenter08 JDBC Thin Client TEMP LOB_DATA 256 2
复制
总共消耗temp才有6MB,而这个数据库的temp容量有32GB,难不成业务反馈的是有问题的,系统没有出现temp不足的问题,既然出现了temp问题,那么数据库的alert日志肯定是会记录的。
Wed Mar 22 14:00:20 2017
Global Enqueue Services Deadlock detected (DID = 12_1_19). More information in file
/oracle/app/oracle/diag/rdbms/norddb1/norddb12/trace/norddb12_lmd0_71645.trc.
Wed Mar 22 14:09:03 2017
Thread 2 advanced to log sequence 8444 (LGWR switch)
Current log# 12 seq# 8444 mem# 0: +DATADG/NORDDB1/ONLINELOG/group_12.275.899285913
Wed Mar 22 14:09:06 2017
Archived Log entry 17249 added for thread 2 sequence 8443 ID 0xbde74e80 dest 1:
Wed Mar 22 14:20:05 2017
ORA-1652: unable to extend temp segment by 256 in tablespace TEMP
Wed Mar 22 14:20:05 2017
ORA-1652: unable to extend temp segment by 256 in tablespace TEMP
Wed Mar 22 14:30:09 2017
Thread 2 advanced to log sequence 8445 (LGWR switch)
Current log# 13 seq# 8445 mem# 0: +DATADG/NORDDB1/ONLINELOG/group_13.276.899285915
复制
业务反馈属实,系统确实曾经出现过ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的错误,接下来就是如何去抓取历史的TEMP问题了。
三、ASH抓取瞬间出现的TEMP问题
这个故障能否重现了,推测只要这个业务运行了就会出现,但是如果要业务程序再次失败对于业务是有影响的,并且这个ORA-1652: unable to extend temp segment by 256 in tablespace TEMP持续时间也不会特别长,因为一旦抛出这个错误,相关SQL就会运行失败,已经被使用的Temp就会释放掉,也不一定能够能够抓取相关的问题SQL。
此时回到了在上面介绍的如何对历史问题进行诊断,这里要推出oracle的历史性能问题诊断利器ASH,注意这个AWR采样是无发获取到相关信息的,并且AWR其实针对的是数据库整理性能问题,对于这种个别会话出现ORA-1652错误并不一定能够抓取到。
v$active_session_history、dba_hist_active_sess_history视图,第一个视图是每秒钟对活动的会话采样一次,第二个视图是每十秒对活动会话采样一次,此时由于告警日志中记录的ORA-1652错误时间段的v$acitve_session_history视图数据还存在,这里就直接截取了其中故障时段临近几分钟的数据插入到ash0322 临时构造的表中
create table ash0322 as
select * from dba_hist_active_sess_history a
where a.SAMPLE_TIME between
to_date('2017-03-22 14:18:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2017-03-22 14:21:00', 'yyyy-mm-dd hh24:mi:ss')
SQL> select a.SAMPLE_TIME,
2 sum(temp_space_allocated) / 1024 / 1024 / 1024,
3 sql_id
4 from ash0322 a
5 where a.TEMP_SPACE_ALLOCATED is not null
6 group by a.SAMPLE_TIME, sql_id
7 order by 2 desc
8 ;
复制
这里我们使用了v$active_session_history视图中的temp_space_allocated字段来获取了系统每个sample_time下相关SQL_ID的temp表空间资源消耗。
通过上面的查询已经得知就是SQL_ID 9q1kqvzvnzbjc单次消耗就需要16GB的temp表空间,而数据库的Temp表空间容量只有30G,这个SQL只要有并发或者节点之间一起使用则马上就会出现 ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的报错。
四、 剖析SQL 性能、优化改善
SQL_ID 9q1kqvzvnzbjc
--------------------
SELECT *
FROM (SELECT t1.*, rownum sn1
FROM (select o.order_id,
o.order_type,
o.type_code,
o.status,
ot.acc_nbr,
o.order_amount,
o .shipping_type
from es_order o, es_order_items ot
where o.order_id = ot.order_id
and o.source_from = ot.source_from
and o.source_from = :1
order by o.create_time desc) t1
where rownum <= 10) t2
WHERE t2.sn1 >= 1
复制
temp消耗在这个SQL执行计划中有两部分,一部分是sort order by stopkey另一个部分则是hash join,Sort order by stopkey是排序时的分页操作,消耗的temp空间是有限的,hash join才是消耗temp的根本原因。
从执行计划ID 6得知表es_order_items ot作为驱动表返回的结果集是87M,也就是8千多万的数据行,而我们知道在oracle的hash join运算时如果驱动表返回的数据行较多,由于PGA空间有限则构造hash table可能会在temp表空间也就是磁盘上运行;对于87M的数据量在构造hash table时必须是必须大量使用temp表空间,正是这个hash join导致了数据库的temp表空间消耗殆尽。
这里也简单讲述下sort order by stopkey,这个虽然是排序,也需要将所有的数据取出来后来排序,但是消耗的内存或者temp空间是有限的,因为oracle不需要将所有数据取出来全部一起排序,而是可以分批进行,每次保证在排序区域只需要有固定行数的数据。
这个我们可以认为这个排序区域只有10行数据,然后每次新的数据进入到排序后就进行比较,发现这条数据是否最小,如果不是最小就将排序区原来10条数据中的create_time最小那条数据踢出去,这个排序算法是我个人认为理解,oracle的算法应该更加科学点,不过可以先这么理解,总之排序分页环境下temp消耗是有限的,大家可以手动创建一个非常小的temp tablespace关闭自动扩展,构造一个亿数量级的大表来排序,但是只取其中的前10行数据,看是否temp是报出空间不足,也可以用gather_plan_statistics来查看SQL实际的运行的temp资源消耗等方法都可以验证。
如何优化改善:
- 由于该SQL有rownum限制,其实相当于一个分页取数据的方式,如果能够去掉order by o.create_time desc的排序,则该SQL能够走nested loop的方式,并且在nested loop阶段就走counter stopkey,既不用排序也不用扫描所有表段或者索引段后来做表关联!
SELECT *
FROM (SELECT t1.*, rownum sn1
FROM (select o.order_id,
o.order_type,
o.type_code,
o.status,
ot.acc_nbr,
o.order_amount,
o .shipping_type
from WSSMALL.es_order o, WSSMALL.es_order_items ot
where o.order_id = ot.order_id
and o.source_from = ot.source_from
and o.source_from = :1) t1
where rownum <= 10) t2
WHERE t2.sn1 >= 1
复制
- 如果业务上必须排序,也可以在表es_order上创建create_time索引,不过需要注意的是必须创建global级别索引才能保证该SQL返回的数据是有序的(因为这里没有走分区裁剪,是扫描了多个分区,如果local索引在跨分区索引范围扫描时并不能保证取出的数据是有序的,oracle不会走nested loop的执行计划),这样该SQL在表关联阶段就会走nested loop的方式。
- 添加更高效的过滤条件,控制驱动表返回的数据量。
综述:
当遇见历史问题时,需要结合警告日志、进程跟踪文件、业务日志等综合分析,在数据库层面上ASH提供丰富的Session历史数据。
ASH中的两个视图v$active_session_history、dba_hist_active_sess_history,通过对ASH数据挖掘,找到相关的等待事件、SQL_ID等信息深入分析。