Data Lifecycle Management; How to find your unused or read only Oracle segments?

This topic was discussed today on our forum group OracleTURK. First things first, what may be the reasons you may need to find unused or read only Oracle segments in your database.

– of course if we now they are not needed we can easily get rid of them right :)
– if you can find your read only data you can put them into specific separate read only tablespaces, compress those segments as a result gain in backup duration and space.

If your database applications pro-actively handles this data lifecycle management need or you can easily gather this information from them then life will be much more easy for you. Otherwise your database management software, for us Oracle of course :), is your friend again as usual, all you have to do is to choose some combination of features already supplied within your Oracle release depending to your need and their advantages and disadvantages.

After 10g if you have the extra cost option Automatic Workload Repository(AWR-DBA_HIST% views) helps a lot, but what if you are still on 9i or you do not have that cost options? Auditing or Statspack data(PERFSTAT schema’s tables) will help similarly if you configure them. Before getting into the details of some options I tried be warned about the retention of the data you will be analyzing. For example if last 15 days information is relevant for you to decide if a segment is not used or can be read only and your AWR retention is 7 days then you will conclude wrong. So as an initial step controlling AWR or Statspack reports retentions will be important.

SELECT retention FROM dba_hist_wr_ control;

Or if you will be depending on V$SQL kind of dynamic performance views data you must ensure that your last instance startup time is enough behind for your analysis need.

Below mentioned options may have their own alternative performance costs, no pain no gain in the real world, so who can not pay for AWR, Statpack kind of a repository activity can prefer to use some kind of a network sniffer tool capturing any SQL requested from the instance.

1- Database auditing based options

a) Standart auditing;



FROM sys.aud$
WHERE .. ;

b) Fine grained access control;

dbms_fga.add_policy (object_schema=>’..’, object_name=>’..’, policy_name=>’..’);

FROM dba_fga_audit_trail a, dba_objects b
WHERE a.object_name = b.object_name
AND a.object_schema = b.owner
AND .. ;

2- 10g AWR based options

a) Historical SQL execution plan statistics;

FROM dba_hist_sql_plan p, dba_hist_sqlstat s
WHERE p.sql_id = s.sql_id
AND .. ;

b) Historical segment statistics;

FROM dba_hist_seg_stat ss, dba_hist_seg_stat_obj o
WHERE ss.obj# = o.obj#
AND ss.dataobj# = o.dataobj#
AND ss.dbid = o.dbid
AND .. ;

3- Statspack based options

a) Historical SQL statistics;

COUNT(DECODE(s.command_type, 2, 1, 6, 1, 7, 1, 189, 1)) sql_change,
COUNT(DECODE(s.command_type, 3, 1)) sql_read
FROM perfstat.stats$sqltext s
WHERE s.sql_text LIKE ‘% ‘||t.table_name||’ %’
AND .. ;

b) Historical segment statistics;

FROM perfstat.stats$seg_stat s, perfstat.stats$seg_stat_obj o
WHERE s.obj# = o.obj#
AND s.dataobj# = o.dataobj#
AND s.dbid = o.dbid
AND .. ;

4- Others require mentioning

DBA_TAB_MODIFICATIONS information is reset after statistics gathering, so you may easily capture the modification amounts for your segments from the last stats gathering time.

FROM dba_tab_modifications
WHERE .. ;

V$ACCESS can show the accessed objects for the moment that you are querying;

FROM v$access
WHERE .. ;

And lets meet with Oracle’s new answer to this kidn of a need; Information Lifecycle Management(ILM)