Compressing infrequently accessed datafiles

Many customer databases do not use the Oracle partitioning feature. If partitioning is not used, then you can use Oracle catalog queries to identify datafiles that are not very active. Periodically, you can query the catalog tables and identify the least active datafiles and compress those files, as illustrated in the following example procedure.

To identify the least active datafiles and compress those files

  1. Query v$filestat and identify the least active datafiles:
    SQL> select  name, phyrds + phywrts 'TOT_IO' from v$datafile d
    and v$filestat f where d.file# = f.file# order by TOT_IO;
    
  2. Select files that have the least I/O activity from the report and compress those files:
    $ /opt/VRTS/bin/vxcompress file1 file2 file3 ...
  3. Periodically run the query again to ensure that the compressed files do not have increased I/O activity. If I/O activity increases, uncompress the files:
    $ /opt/VRTS/bin/vxcompress -u file1 file2 file3 ...