Identifying tablespace fragmentation (subjective)
|
4546
2006-NOV-25 13:15:44
|
|
Moderator
|
|
|
Registered On: Mar 2006
Total Posts: 233
|
|
Identifying tablespace fragmentation (subjective)
We will perform tablespace analysis looking for fragments of
free space that are potentially wasted. The goal here
is to find free space fragments that are smaller than
the next extent of "active" segments.
**You** can only tell what the active segments are, in this
case (for the sake of presentation) the list is built using
collection from DBA_SEGMENTS using the following predicate:
owner NOT IN ( 'SYS','SYSTEM' ) AND
(extents > 50 OR bytes/1024/1024 > 200)
To fix tablespace fragmentation you can do the following:
a) Carefully alter the <NEXT_EXTENT> of any of the
active segments to fill up the space (see note below).
NOTE:
This is useful when you are dealing with legacy tablespaces
that were converted from dictionary managed to local managed
but have no uniform size defined (typical issue with
ORACLE APPS instance upgraded from pre-11i to a later version).
b) Leave it alone if you can live with it.
c) Reorganize tablespace when fragmentation is so severe that
you are experiencing performance degradation or when you
desperately need to reclaim wasted space fragments (rarely the case
since the storage media is so cheap these days).
SYSTEM@XUPR:amanda> set echo on
SYSTEM@XUPR:amanda> set trims on
SYSTEM@XUPR:amanda> set time off
SYSTEM@XUPR:amanda> set feed on
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> drop table ext_per_ts
2 /
Table dropped.
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> drop table wasted_free_frags
2 /
Table dropped.
SYSTEM@XUPR:amanda>
NOTE:
Tune below sql to your taste -- only you can tell what your
active segments are.
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> create table ext_per_ts
2 tablespace tools
3 as
4 select OWNER,
5 DECODE(INSTR(segment_type,'PARTITION'),
6 0,segment_name,
7 segment_name||' '||partition_name) segment_name,
8 segment_type,
9 EXTENTS, PCT_INCREASE, tablespace_name,
10 next_extent
11 from dba_segments
12 WHERE owner NOT IN ( 'SYS','SYSTEM' )
13 AND (extents > 50 OR bytes/1024/1024 > 200)
14 AND segment_name NOT LIKE 'EVNT_SGS%TEMP%'
15 /
Table created.
SYSTEM@XUPR:amanda> create table wasted_free_frags
2 tablespace tools
3 as
4 select f.cnt, f.bytes,
5 f.tablespace_name
6 from (select count(*) cnt, bytes, tablespace_name
7 from dba_free_space
8 group by tablespace_name, bytes
9 having count(*) > 10) f
10 where NOT EXISTS (select 'x'
11 from ext_per_ts m
12 where (f.bytes = m.next_extent OR m.next_extent < f.bytes)
13 and f.tablespace_name = m.tablespace_name)
14 /
Table created.
SYSTEM@XUPR:amanda> col SEGMENT_NAME format a65 trunc heading "Segment"
SYSTEM@XUPR:amanda> col SEGMENT_type format a15 trunc heading "Seg Type"
SYSTEM@XUPR:amanda> col TABLESPACE_NAME format a12 trunc heading "TS Name"
SYSTEM@XUPR:amanda> col pct format 999
SYSTEM@XUPR:amanda> col mnkbytes heading "N Kbytes"
SYSTEM@XUPR:amanda> col fkbytes heading "F Kbytes"
SYSTEM@XUPR:amanda> col tfkbytes heading "TOT F Kbytes"
SYSTEM@XUPR:amanda> col cnt heading "Fragments"
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> set pages 55
SYSTEM@XUPR:amanda> set lines 60
SYSTEM@XUPR:amanda> ttit "Potentially wasted fragments of free space | [grouped by tablespace fragment]"
SYSTEM@XUPR:amanda> break on report
SYSTEM@XUPR:amanda> compute sum of tfkbytes on report
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> select cnt, bytes/1024 fkbytes,
2 cnt*bytes/1024 tfkbytes,
3 tablespace_name
4 from wasted_free_frags
5 order by fkbytes desc
6 /
Sat Nov 25 page 1
Potentially wasted fragments of free space
[grouped by tablespace fragment]
Fragments F Kbytes TOT F Kbytes TS Name
---------- ---------- ------------ ------------
13 256 3328 APINDX
26 128 3328 MRP
82 80 6560 MSC
14 64 896 APPS_UNDOTS1
33 48 1584 OEINDX
13 48 624 OSMINDX
11 40 440 INV
25 40 1000 POINDX
129 40 5160 MSC
13 32 416 INV
35 32 1120 QAINDX
12 32 384 XYLSHARE
17 32 544 MRPINDX
11 32 352 ONTD
15 24 360 INV
18 24 432 XYLSHARE
12 24 288 QAINDX
14 24 336 SHR
23 16 368 INV
23 16 368 APINDX
17 16 272 ENGINDX
39 16 624 MRPINDX
25 16 400 XYLSHARE
98 16 1568 POINDX
24 16 384 SHR
13 8 104 INV
14 8 112 XYLSHARE
44 8 352 INVINDX
12 8 96 ARINDX
------------
sum 31800
29 rows selected.
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> set lines 128
SYSTEM@XUPR:amanda> ttit "Segments that will not fit into space fragments | [they were used to create above projection]"
SYSTEM@XUPR:amanda> clear breaks
SYSTEM@XUPR:amanda> clear computes
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda>
SYSTEM@XUPR:amanda> select m.owner||'.'||m.SEGMENT_NAME SEGMENT_NAME,
2 m.segment_type,
3 m.EXTENTS , m.PCT_INCREASE pct,
4 m.next_extent/1024 nkbytes,
5 m.tablespace_name
6 from ext_per_ts m
7 where EXISTS (select 'x'
8 from wasted_free_frags f
9 where f.tablespace_name = m.tablespace_name
10 and m.next_extent > f.bytes)
11 order by m.tablespace_name, m.owner, m.SEGMENT_NAME
12 /
Sat Nov 25 page 1
Segments that will not fit into space fragments
[they were used to create above projection]
Segment Seg Type EXTENTS PCT NKBYTES TS Name
----------------------------------------------------------------- --------------- ---------- ---- ---------- ------------
AR.AR_CASH_RECEIPTS_N8 INDEX 54 0 24 ARINDX
AR.AR_JOURNAL_INTERIM_N1 INDEX 93 0 768 ARINDX
AR.AR_JOURNAL_INTERIM_N3 INDEX 77 0 768 ARINDX
AR.HZ_CONTACT_POINTS_N6 INDEX 70 0 24 ARINDX
AR.HZ_DQM_SYNC_INTERFACE_N1 INDEX 125 0 24 ARINDX
AR.HZ_LOCATIONS_N3 INDEX 58 0 24 ARINDX
AR.HZ_LOCATION_PROFILES_N2 INDEX 58 0 24 ARINDX
AR.HZ_ORIG_SYS_REFERENCES_N1 INDEX 304 0 24 ARINDX
...
...
...
QA.QA_RESULTS_N2 INDEX 12 0 51200 QAINDX
QA.QA_RESULTS_N3 INDEX 13 0 51200 QAINDX
QA.QA_RESULTS_N4 INDEX 12 0 51200 QAINDX
185 rows selected.
SYSTEM@XUPR:amanda>