oracle 10g SYSAUX表空间快速增长之WRH$_SQL_PLAN篇

通过分析oracle sysaux表空间下的各模块信息：

SELECT t.OCCUPANT_NAME,SUM(t.SPACE_USAGE_KBYTES)/1024/1024
  FROM gV$SYSAUX_OCCUPANTS t
  GROUP BY t.OCCUPANT_NAME
  ORDER BY 2 DESC;

OCCUPANT_NAME	SUM(t.SPACE_USAGE_KBYTES)/1024/1024
SM/AWR	16.33325195
SM/OPTSTAT	1.88671875
SM/ADVISOR	0.661987305
EM	0.365966797
XDB	0.094116211
SDO	0.080078125
SM/OTHER	0.05871582
XSOQHIST	0.05090332
AO	0.05090332
LOGMNR	0.034179688
STREAMS	0.031005859
XSAMD	0.030395508
JOB_SCHEDULER	0.015991211
WM	0.013916016
TEXT	0.009033203
EXPRESSION_FILTER	0.007080078
EM_MONITORING_USER	0.003051758
LOGSTDBY	0.001708984
ORDIM	0.000976563
TSM	0.000488281
ODM	0.000488281
ORDIM/PLUGINS	0
STATSPACK	0
ULTRASEARCH_DEMO_USER	0
ORDIM/SQLMM	0
ULTRASEARCH	0

上面就是oracle中占据sysaux表空间的各模块情况。

通过以下sql分析sysaux表空间的使用情况：

WITH
  ts_total_space AS (SELECT
                       TableSpace_name,
                       SUM(bytes) as bytes,
                       SUM(blocks) as blocks,
                       SUM(maxbytes) as maxbytes
                     FROM dba_data_files
                     GROUP BY TableSpace_name),
  ts_free_space AS (SELECT
                      ddf.TableSpace_name,
                      NVL(SUM(dfs.bytes),0) as bytes,
                      NVL(SUM(dfs.blocks),0) as blocks
                    FROM
                      dba_data_files ddf,
                      dba_free_space dfs
                    WHERE ddf.file_id = dfs.file_id(+)
                    GROUP BY ddf.TableSpace_name),
  ts_total_segments AS (SELECT
                          TableSpace_name,
                          SUM(bytes) as bytes,
                          SUM(blocks) as blocks
                        FROM dba_segments
                        GROUP BY TableSpace_name),
  ts_total_extents AS (SELECT
                         TableSpace_name,
                         SUM(bytes) as bytes,
                         SUM(blocks) as blocks
                       FROM dba_extents
                       GROUP BY TableSpace_name)
SELECT
  dt.TableSpace_name as "TSname",
  dt.status as "TSstatus",
  ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",
  ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",
  ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",
  ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",
  ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",
  ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",
  ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",
  CASE
    WHEN ttsp.maxbytes = 0 THEN 'No' ELSE 'Yes'
  END as "AutoExtFile",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))
  END as "TSMaxSizeMb",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))
  END as "TSMaxUsedPrct",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))
  END as "TSMaxFreePrct"
FROM
  dba_TableSpaces dt,
  ts_total_space ttsp,
  ts_free_space tfs,
  ts_total_segments ttse,
  ts_total_extents tte
WHERE dt.TableSpace_name = ttsp.TableSpace_name(+)
AND dt.TableSpace_name = tfs.TableSpace_name(+)
AND dt.TableSpace_name = ttse.TableSpace_name(+)
AND dt.TableSpace_name = tte.TableSpace_name(+)
AND dt.TableSpace_name = 'SYSAUX';

或者通过sql分析sysaux表空间下的各segments情况：

SELECT
  ROUND(SUM(ds.bytes)/1024/1024,0) as "SgmntSize",
  ds.TableSpace_name as "TSname",
  ds.owner as "SgmntOwner",
  ds.segment_name as "SgmntName",
  ds.segment_type as "SgmntType"
FROM dba_segments ds
WHERE ds.segment_type IN ('TABLE','INDEX','LOBSEGMENT')
AND TableSpace_name = 'SYSAUX'
GROUP BY
  ds.TableSpace_name,
  ds.owner,
  ds.segment_name,
  ds.segment_type
ORDER BY "SgmntSize" DESC;

发现有这张表：WRH$_SQL_PLAN也占据了此表空间很大的一部分，

分析metalink文章：Wrh$_sql_plan table growth causes Sysaux Tablespace size increase continuously [ID 1243058.1]

发现此乃oracle的一个bug，通过修补补丁patch 6394861

可以解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12129601/viewspace-714271/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12129601/viewspace-714271/