oracle sysaux扩大,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，通过修补补丁

可以解决。