I had the sysman tablespace on a database grow to 22Gb and could not figure out why. Logged an SR for it and followed up with suprising results.
Checking the components in the sysman collection process identified the largest space occupant in there.
SQL> select OCCUPANT_NAME, SPACE_USAGE_KBYTES from v$sysaux_occupants order by SPACE_USAGE_KBYTES desc;
OCCUPANT_NAME SPACE_USAGE_KBYTES—————————————————————- ——————
SM/AWR 21738880
SM/ADVISOR 204800
SM/OPTSTAT 199232
EM 94336
XDB 49216
SM/OTHER 29632
XSOQHIST 26688
AO 26688
SDO 22400
XSAMD 15936
JOB_SCHEDULER 12480
WM 7296
LOGMNR 6080
TEXT 4736
EXPRESSION_FILTER 3712
EM_MONITORING_USER 2048
LOGSTDBY 896
STREAMS 512
ORDIM 512
ODM 256
TSM 256
ULTRASEARCH_DEMO_USER 0
ORDIM/PLUGINS 0
STATSPACK 0
ULTRASEARCH 0
ORDIM/SQLMM 0 SM/AWR held over 21Gb of data. Checking the items in there identified a table which contained 10Gb of data. SQL> select round(sum(bytes)/(1024*1024),2) Mb from dba_segments where segment_name = ‘WRH$_LATCH_CHILDREN’; MB
———-
10641.38
Checking through metalink, it recommended shrinking the segment, but I found there were no blocks which were candidates for this. (Metalink note 729149.1) This refers to Bug 5987262 TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS.
set serveroutput onexec dbms_output.enable(1000000); declare
unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;
own dba_tables.owner%type;
tab dba_tables.table_name%type;
yesno varchar2(3);
type parts is table of dba_tab_partitions%rowtype;
partlist parts;
type cursor_ref is ref cursor;
c_cur cursor_ref;
begin
own:=upper(‘&owner’);
tab:=upper(‘&table_name’);
dbms_output.put_line(‘——————————————————————————–’); open c_cur for select partitioned from dba_tables
where owner=own and table_name=tab;
fetch c_cur into yesno;
close c_cur; dbms_output.put_line(‘Owner: ‘||own);
dbms_output.put_line(‘Table: ‘||tab); dbms_output.put_line(‘————————————————’); if yesno=’NO’
then
dbms_space.space_usage(own,tab,’TABLE’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full); else
open c_cur for select * from dba_tab_partitions
where table_owner=own and table_name=tab;
fetch c_cur bulk collect into partlist;
close c_cur; for i in partlist.first .. partlist.last
loop
dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,’TABLE PARTITION’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line(‘Partition: ‘||partlist(i).partition_name);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);
end loop; end if;
dbms_output.put_line(‘——————————————————————————–’); end;
/ Resulting output. Enter value for owner: SYS
old 22: own:=upper(‘&owner’);
new 22: own:=upper(‘SYS’);
Enter value for table_name: WRH$_LATCH_CHILDREN
old 23: tab:=upper(‘&table_name’);
new 23: tab:=upper(‘WRH$_LATCH_CHILDREN’);
——————————————————————————–
Owner: SYS
Table: WRH$_LATCH_CHILDREN
————————————————
Partition: WRH$_LATCH__3638518939_0
unf: 0 fs1: 2 fs2: 0 fs3: 0 fs4: 0 full: 306873
Partition: WRH$_LATCH__3638518939_42962
unf: 0 fs1: 1 fs2: 0 fs3: 0 fs4: 0 full: 328795
Partition: WRH$_LATCH__3638518939_43244
unf: 0 fs1: 1 fs2: 0 fs3: 1 fs4: 0 full: 397051
Partition: WRH$_LATCH__3638518939_43580
unf: 0 fs1: 0 fs2: 0 fs3: 1 fs4: 0 full: 320877
Partition: WRH$_LATCH__3638518939_43861
unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 0
Partition: WRH$_LATCH_CHILD_MXDB_MXSN
unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 0
——————————————————————————– PL/SQL procedure successfully completed.
The fs1,2,3 and 4 represent blocks using space of 75% respectively according to metalink. I think full is >80% mind.
Because of this, the recommended alter table shrink space cascade had no effect on the space. It turned out to be to do with the statistics_level.
At some point, the statistics level was set to ALL, then changed back to TYPICAL. This resulted on hundreds of thousands of records being placed in WRH$_LATCH_CHILDREN for every snapshot taken.
SQL> select min(snap_id), max(snap_id), snap_level from dba_hist_snapshot group by snap_level;
MIN(SNAP_ID) MAX(SNAP_ID) SNAP_LEVEL———— ———— ———-
42701 43850 2
42576 43941 1 SQL> execute dbms_workload_repository.drop_snapshot_range(42576, 42699); PL/SQL procedure successfully completed.
Dropping the snapshot range freed up some space, but not much, you’ll notice it’s only up to 42699 here. This was none of the level 2 snapshots.
If this command appears to fail, check for a baseline. drop_snapshot_range will not drop snapshots in a preserved baseline, so ensure any of these are removed and try again.
Here’s a breakdown of the items in the tablespace.
**********************************(3b) Space usage within AWR Components (> 500K)
********************************** COMPONENT MB SEGMENT_NAME – % SPACE_USED SEGMENT_TYPE
——— ——— ——————————————————————— —————
FIXED 3,108.9 WRH$_LATCH_CHILDREN.WRH$_LATCH__3638518939_43244 – 100% TABLE PARTITION
FIXED 2,575.1 WRH$_LATCH_CHILDREN.WRH$_LATCH__3638518939_42962 – 100% TABLE PARTITION
FIXED 2,513.3 WRH$_LATCH_CHILDREN.WRH$_LATCH__3638518939_43580 – 100% TABLE PARTITION
FIXED 2,403.7 WRH$_LATCH_CHILDREN.WRH$_LATCH__3638518939_0 – 100% TABLE PARTITION
FIXED 1,631.1 WRH$_LATCH_CHILDREN_PK.WRH$_LATCH__3638518939_43244 – 100% INDEX PARTITION
FIXED 1,353.8 WRH$_LATCH_CHILDREN_PK.WRH$_LATCH__3638518939_42962 – 100% INDEX PARTITION
FIXED 1,316.2 WRH$_LATCH_CHILDREN_PK.WRH$_LATCH__3638518939_43580 – 100% INDEX PARTITION
FIXED 1,266.8 WRH$_LATCH_CHILDREN_PK.WRH$_LATCH__3638518939_0 – 100% INDEX PARTITION
FIXED 96.0 WRH$_SYSMETRIC_HISTORY – 50% TABLE
FIXED 96.0 WRH$_SYSMETRIC_HISTORY_INDEX – 42% INDEX
FIXED 32.0 WRH$_SYSMETRIC_SUMMARY – 47% TABLE
FIXED 14.0 WRH$_SYSMETRIC_SUMMARY_INDEX – 89% INDEX
FIXED 13.0 WRH$_WAITCLASSMETRIC_HISTORY – 24% TABLE
FIXED 13.0 WRH$_WAITCLASSMETRIC_HIST_IND – 23% INDEX
Removing this consisted of dropping the range which had snap_level of 2, which is where the statistics_level was set to 2.
Once dropped, the alter table shrink space cascade command shrink the table and partitions from an incredible 10Gb to 65K extents or virtually zero in size. The sysman tablespace was then reduced to 1Gb occupied (from 22Gb) and I was able to reclaim all by 3Gb in space on the OS from the datafile.
Here’s what is collected depending on which statistics_level you have set.
SQL> col statistics_name for a40SQL> SELECT STATISTICS_NAME, SESSION_STATUS, SYSTEM_STATUS, ACTIVATION_LEVEL
2 FROM v$statistics_level
3 ORDER BY 3 ; STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT
—————————————- ——– ——– ——-
Timed OS Statistics DISABLED DISABLED ALL
Plan Execution Statistics DISABLED DISABLED ALL
Timed Statistics ENABLED ENABLED TYPICAL
Segment Level Statistics ENABLED ENABLED TYPICAL
PGA Advice ENABLED ENABLED TYPICAL
Shared Pool Advice ENABLED ENABLED TYPICAL
Modification Monitoring ENABLED ENABLED TYPICAL
Longops Statistics ENABLED ENABLED TYPICAL
Bind Data Capture ENABLED ENABLED TYPICAL
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL
Threshold-based Alerts ENABLED ENABLED TYPICAL
Global Cache Statistics ENABLED ENABLED TYPICAL
Active Session History ENABLED ENABLED TYPICAL
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL
Streams Pool Advice ENABLED ENABLED TYPICAL
Buffer Cache Advice ENABLED ENABLED TYPICAL
MTTR Advice ENABLED ENABLED TYPICAL 17 rows selected.
So, setting it to ALL will add Plan Execution Statistics in ther, and that is what appears to be the additional data which blew my sysman tablespace out.
