Solving event I/O and Cluster wait and freeing wasted space in LOBs

Issue: I/O and Cluster wait for LOBs

Sometimes in ADDM Report you can finding in "Top SQL Statements" following action:

Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID 
      "2adh0x2quv6uh". Additionally, investigate this statement for possible 
      performance improvements. You can supplement the information given here 
      with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 2adh0x2quv6uh.
         INSERT INTO USERINFO (SESSIONID, USERID, DICTIONARY, EXPIRES) 
         VALUES(:1 , :2 , :3 , :4)
   Rationale
      I/O and Cluster wait for LOB "SCOTT.SYS_LOB0000155472C00008$$" with 
      object ID 154473 consumed 60% of the database time spent on this SQL 
      statement.

Segment Advisor recommend shrink table SESSIONINFORMATION for reclaim space, but shrinking does not work for LOBs::

ALTER TABLE "SCOTT"."USERINFO" ENABLE ROW MOVEMENT
ALTER TABLE "SCOTT"."USERINFO" SHRINK SPACE

Solution

Reclaiming Wasted Space:

1. Use CASCADE option for shrink a table and all of its dependent segments (including LOB segments).
   
   When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows
   but postpones the resetting of the high water mark and the deallocation of the space
   until you reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE COMPACT;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;

2. Check free unused bytes, returns information about the position of the high water mark and the amount of unused space in a segment.

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   VARIABLE total_blocks NUMBER
   VARIABLE total_bytes NUMBER
   VARIABLE unused_blocks NUMBER
   VARIABLE unused_bytes NUMBER
   VARIABLE lastextf NUMBER
   VARIABLE last_extb NUMBER
   VARIABLE lastusedblock NUMBER
   exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'USERINFO', 'TABLE', :total_blocks, -
       :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
       :last_extb, :lastusedblock)
   print

3. Reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete.
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;

4. Check unused bytes again, script from step 2.
5. Issue deallocate unused space. Prior to deallocation, you can run the UNUSED_SPACE procedure, script from step 2.
   

   ALTER TABLE SCOTT.USERINFO  DEALLOCATE UNUSED;

6. Check unused bytes again, script from step 2

The LOBSEGMENT SYS_LOB0000155472C00008$$ has been reclaimed wasted space.

Resources

Oracle 10g New Features: Oracle Reclaiming Unused Space
Space, Object, and Transaction Management in Oracle Database 10g: Online Segment Shrink