某客户SYSAUX表空间异常增长问题分析

背景描述

    接到客户请求，说sysaux表空间异常增长非常快，需要排查一下具体原因。

分析

    根据dba_segments查询统计，异常增长的段是scheduler$_job_output表的output字段，此字段为lob字段，通过MOS搜索，发现Doc ID 2095104.1 与此情况比较匹配，客户按照MOS的方式对此表进行了truncate，但是并不能根本解决问题，隔几天又涨回来了，但是MOS并没有提供根本的解决方案，所以需要进一步分析。

    首先查看此lob字段的具体内容：

A1CDC0510;12QC05;Z655266E0201;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0201;19-6月 -2219-6月 -22
A1CDC0521;12QC05;Z655266E0201;19-6月 -2219-6月 -22
A1CDC0520;12QC05;Z655266E0201;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0201;19-6月 -2219-6月 -22
A1CDC0510;12QC05;Z655266E0202;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0202;19-6月 -2219-6月 -22
A1CDC0521;12QC05;Z655266E0202;19-6月 -2219-6月 -22
A1CDC0520;12QC05;Z655266E0202;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0202;19-6月 -2219-6月 -22
A1CDC0510;12QC05;Z655266E0203;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0203;19-6月 -2219-6月 -22
A1CDC0521;12QC05;Z655266E0203;19-6月 -2219-6月 -22
A1CDC0520;12QC05;Z655266E0203;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0203;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0204;19-6月 -2219-6月 -22
A1CDC0510;12QC05;Z655266E0204;19-6月 -2219-6月 -22
A1CDC0521;12QC05;Z655266E0204;19-6月 -2219-6月 -22
A1CDC0520;12QC05;Z655266E0204;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0204;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0205;19-6月 -2219-6月 -22
A1CDC0510;12QC05;Z655266E0205;19-6月 -2219-6月 -22
A1CDC0521;12QC05;Z655266E0205;19-6月 -2219-6月 -22
A1CDC0520;12QC05;Z655266E0205;19-6月 -2219-6月 -22
A1CDC0512;12QC05;Z655266E0205;19-6月 -2219-6月 -22
...
...
...
复制

    此lob字段内容达到了百万行，从内容分析，也不像数据库内部的，怀疑是某个plsql产生的，接下来就需要搞清楚这个内容到底从哪里来。
    通过oracle官方文档，MOS，google搜索，发现针对此表的描述几乎没有，接下来想到去$ORACLE_HOME/rdbms/admin里面搜，看看能不能找到相关描述：

[oracle@devin-enmo admin]$ grep -R "SCHEDULER\$\_JOB\_OUTPUT"
upobjxt.lst:SYS,SCHEDULER$_JOB_OUTPUT,,2,
upobjxt.lst:SYS,SCHEDULER$_JOB_OUTPUT_PK,,1,
复制

    rdbms/admin中也没有搜到相关描述，接下来直接看一下具体的建表语句，是否能找到蛛丝马迹：

-- Create table
create table SCHEDULER$_JOB_OUTPUT
(
  log_id NUMBER,
  errors BLOB,
  output BLOB
)
tablespace SYSAUX
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SCHEDULER$_JOB_OUTPUT
  add constraint SCHEDULER$_JOB_OUTPUT_PK unique (LOG_ID)
  using index 
  tablespace SYSAUX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SCHEDULER$_JOB_OUTPUT
  add constraint SCHEDULER$_JOB_OUTPUT_FK foreign key (LOG_ID)
  references SCHEDULER$_JOB_RUN_DETAILS (LOG_ID) on delete cascade;
复制

    可以看到此表与另外一张detail表有主外键关联关系，关联列为log_id，那接下来去detail表中查找：

select * from scheduler$_job_output
LOG_ID  ERRORS  OUTPUT
------- ------ -------
383850	<BLOB>	<BLOB>
383834	<BLOB>	<BLOB>

select log_id,session_id from scheduler$_job_run_details where log_id=383850
LOG_ID  SESSION_ID
------  ----------
383850	1024,3328
复制

    在detail此表中查询到了相应的执行记录，根据执行记录再去ash中查询详细信息：

SELECT SQL_ID,SQL_OPNAME,MODULE,ACTION FROM V$ACTIVE_SESSION_HISTORY WHERE SESSION_ID=1024 AND SESSION_SERIAL#=3328

0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
0qbzfjt00pbsx	PL/SQL EXECUTE	DBMS_SCHEDULER	LOAD_ARRAY_CHAMBER_PATH
复制

    继续查看相关action的具体内容，发现调用的plsql中有一段dbms_output的输出：

DBMS_OUTPUT.put_line(l_rec2.sub_equip_id ||';'||l_rec.step_id || ';' ||  l_rec.glass_id || ';' || l_rec.date_item1 || l_rec.glass_start_time );
复制

     output的内容格式与scheduler$_job_output的output字段记录的内容一致，所以到这里基本确认就是因为这个output内容被oracle记录下来了，通过研究代码也发现，这个段dbms_output是在循环体中，每执行一次会循环上百万次，从而导致output字段急剧膨胀。
     第一次接触这个视图，也第一次get这个知识点，做个简单记录，oracle是会记录plsql中的dbms_output内容的，所以在代码中，尤其循环结构中，千万别dbms_output，防止sysaux撑爆。