Improving Performance for an Oracle Database

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space.

As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But as row deletes and updates shrink the amount of data in the object, the high water mark is not altered to reflect the segment's new characteristics. Full table scans all the way to the table's high water mark.

Prior to Oracle 10g, truncating a table was the only way to reduce the high water mark value. Oracle10g introduces the ability to reclaim space from a segment by shrinking the segment. This makes unused space available to other segments in the tablespace, does not affect your ability to access data, and may improve the performance of queries and DML operations. To use this feature, you must have the Automatic Segment Space Management (ASSM) and row movement features enabled.

The following steps can help to get available statistics and reduce files sizes, if needed:

1. Check Oracle Instance Configuration. Parameter statistics_level should be TYPICAL or ALL. This means that the Automatic Database Diagnostic Monitor (ADDM) is turned ON and gathers different types of statistics performed by the Memory Monitor (MMON) background process. SELECT value   FROM v$parameter WHERE name = 'statistics_level'
2. Find the list of tables and dependencies as the candidates for shrinking space.
   SELECT a.task_id,
       c.execution_end,
       b.TYPE,
       a.command ,
       b.message "Action Message",
       a.attr1 "Command to Correct"
  FROM dba_advisor_actions a,
       dba_advisor_findings b,
       dba_advisor_tasks c
 WHERE a.task_id = b.task_id
   AND b.task_id = c.task_id
   AND a.command = 'SHRINK SPACE'
   AND c.status='COMPLETED'
 ORDER BY c.execution_end desc
   Possible tables - candidates for shrink space process:
   LYRUNSUMMARIZEDRECIPS
INMAIL_
LYRDELIVERYATTEMPTLOG
MEMBERS_
LYRMETRICEVENTS
LYRACTIVERECIPS
LYRCOMPLETEDRECIPS
   
3. The shrinking process is only available for objects in locally managed tablespaces with automatic segment-space management enabled.
   Check tablespace configuration:
   Type: PERMANENT
   Extent Management: LOCAL
   Segment Space Management: Automatic
   
4. Run the following commands for each table you want to perform space shrinking on.
   For tables:
   Enable row movement: ALTER TABLE schema_name.table_name ENABLE ROW MOVEMENT;

   Divide shrink process into two parts:

   • SHRINK SPACE COMPACT: The SHRINK SPACE COMPACT phase defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the reallocation of the space until a future time.
   • SHRINK SPACE: The SHRINK SPACE phase can be performed later during off-peak hours.

   First phase: Recovers space but does not amend the high water mark (HWM). ALTER TABLE schema_name.table_name SHRINK SPACE COMPACT;

   Second phase: Recovers space and amends the high water mark (HWM). ALTER TABLE schema_name.table_name SHRINK SPACE;

   For indexes: ALTER INDEX schema_name.index_name SHRINK SPACE;