ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 10g SYSAUX表空间快速增长之WRH$_SQL_PLAN篇

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

原创 Linux操作系统 作者:dcswinner 时间:2011-12-30 20:17:19 0 删除 编辑

通过分析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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-03

  • 博文量
    61
  • 访问量
    486821