Oracle故障处理秘籍（三）

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

SYSAUX表空间不可用时，数据库核心功能还是可以继续运行的，只不过存放在SYSAUX表空间里的功能使用会受限。
col segment_name format a30
col segment_type format a30
set linesize 300
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB 
from dba_segments
where TABLESPACE_NAME='SYSAUX'
order by 4;

1. 查看单节点临时表空间使用情况
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
 
 2. 查询会话使用临时段的情况
 SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       s.sql_id
       SUM(T.blocks) * TBS.block_size / 1024  mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;
 
 3. 如果确认是由于某些会话过度使用临时段引发，与业务确认后kill会话
 alter system kill session '<sid>,<serial#>' immediate;
 
 4. 如果确认这些会话使用的临时段空间属正常，则加大临时表空间
 alter tablespace TEMP add tempfile '/xx/xx/tempxx.dbf' size 5GB;

1. 分析trace文件，定位到运行的大事务，针对该大事务进行分解，减少对UNDO表空间资源的需求量；
2. 扩容undo表空间的大小

1. 通过sql_id确定统计信息是否一致（如下语句会将AWR中所有信息查找出来）
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id,
       ss.instance_number node,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       nvl(executions_delta, 0) execs,
       (elapsed_time_delta 
       decode(nvl(executions_delta, 0), 0, 1, executions_delta))  1000000 avg_etime,
       (buffer_gets_delta 
       decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)) avg_lio,
       (disk_reads_delta 
       decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)) avg_pio
  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
 where sql_id = nvl('&sql_id', '5xqs2g5tmnk82')
   and ss.snap_id = S.snap_id
   and ss.instance_number = S.instance_number
   and s.instance_number like nvl('&instance_number', s.instance_number)
   and executions_delta > 0
 order by 1, 2, 3
/


2. 如果统计信息不一致，需要检查统计信息是否是最新，如果统计信息更新时间比较久了，则手动收集统计信息。
3. 如果统计信息正常，检查相关业务表数据量是否大幅增加。
4. 如果数据量大幅增加，则需要考虑清理数据或做SQL优化。

Note
-----
   - dynamic sampling used for this statement

1. 禁用动态采样
select /*+dynamic_sampling(t 0) */
2. 如果命中bug 9272549，则及时更新补丁。