The link to download script: compression_analyzer.sql
Update: Script was updated on 14.11.2014 12:00
This script, actually one PL/SQL block, is very useful for those who want to implement or analyze existing table compression in Oracle database.
It automates most steps of analyzes and can save huge amount of time.
What it can do:
- Identify and list partitioned and single segment tables larger than specified number of megabytes
- Generates html report
- Prints details about compression related properties of the tables
- Prints related segment statistics per table or per partition
- Makes checks to identify if compression is possible according to some Oracle restrictions
- Estimates compression ratio and calculates how much space can be saved after compression
- Makes a probe to the table or to partitions of the partitioned table, by randomly selected rowids, to identify real compression status of the table
It can be executed before compression implementation to identify which tables to compress. It can be executed periodically on already compression implemented databases to get information about real compression status of the tables.
HTML report will consist from 3 parts:
- List of the tables larger than specified megabytes
- Information about tables in detail with summary for single segment tables at the end
- Information by partition for partitioned tables
Script requires source for segment statistics to fill report. It selects from SEGSTATS table, this table by default not exists. If you want to get current cumulative data from V$SEGMENT_STATISTICS (cumulated from instance startup), then create synonym on it:
create synonym SEGSTATS for V$SEGMENT_STATISTICS;
But this data can be unusable, for example you want to get information about which partitions of the partitioned table are modified, then if you have monthly partitions and your database is up since year, then each partition will show up as modified and you can not identify when it was. To avoid it, it is advicable to select from delta values. To do it create snapshots of V$SEGMENT_STATISTICS and then join them to delta table, then you can use this table for report.
Steps to do:
1. Create first snapshot:
create table SEGSTAT_01052014 as select * from V$SEGMENT_STATISTICS;
2. Wait one month, or any other time period which matches your requirements
3. Create second snapshot:
create table SEGSTAT_01062014 as select * from V$SEGMENT_STATISTICS;
4. Create table which will contain delta of this two snapshots, you can use next DDL:
CREATE TABLE STATDELTA AS
SELECT A.OWNER, A.OBJECT_NAME, A.SUBOBJECT_NAME, A.TABLESPACE_NAME,
A.TS#, A.OBJ#, A.DATAOBJ#, A.OBJECT_TYPE, A.STATISTIC_NAME,
A.STATISTIC#, NVL(A.VALUE-B.VALUE,A.VALUE) value
FROM SEGSTAT_01062014 A, SEGSTAT_01052014 B -- Alias B for older snapshot!
WHERE A.OBJ# = B.OBJ#(+)
AND A.DATAOBJ# = B.DATAOBJ#(+)
AND A.STATISTIC# = B.STATISTIC#(+);
5. Now you can create synonym SEGSTATS for this delta table to let script to use it.
create synonym SEGSTATS for STATDELTA;
6. Now you can execute the script
Script gives possibility to adjust many parameters according to your needs and environment. These parameters is constant variables in the script itself, you must adjust them before execution.
||Number of Megabytes. Script will collect information about tables larger than this number of megabytes.
||The name of the Oracle directory where to save resulting report. It must be created with “create directory” command.
||Can be Y or N. If Y then DBMS_COMPRESSION.GET_COMPRESSION_RATIO procedure will be used to calculate compression ratio for affected tables. Note that when it is enabled execution can take long time to finish.
||Specify for which compression type to estimate. The type of this constant is integer. Use constants from DBMS_COMPRESSION package specification. For example DBMS_COMPRESSION.COMP_FOR_OLTP to estimate for OLTP compression.
||Tablespace name to be used by GET_COMPRESSION_RATIO procedure.
||Setting this variable to Y will enable table or partition probing. It will take EXTENTS_TO_PROBE count randomly identified extents of the segment and from each of them will take first row from the middle block. Then will check for which type of compression particular row compressed.
||Number of extents to pick randomly. With this parameter you can adjust the number of rows to be probed, because one row per extent will be selected. From which extents to select identified in random fashion.
||For partitioned tables you can define per how many partitions to probe, if it is set to 3 then each third partition will be probed. If you have tables with huge number of partitions then it is better to specify this value to bigger numbers to reduce execution time
Screenshots of example report:
This section lists the tables which matches the specified conditions.
This section details each table from Section 1. Here you can see that the compression ratio for table OPERTRAN is 1.5 and after compression it can be 79827 Megabytes. At the moment compression for it DISABLED and it is located in STRDB_DATA tablespace. From the statistics we can see that table used mostly for reads there was no write operations direct or conventional. Then we can see the probe from this table which gives us actual status of the compression. First filed is the extent_id of the table, then rowid and compression status. It is very important information, because table can be with DISABLED compression but data inside can be actually in compressed state. Or, table can be set for BASIC compression but because of inserts was done conventional or often update operations was done on this table, rows are actually not compressed.
It also reports that for table TRANSFORDSR no BASIC or OLTP compression can be specified because it have more than 255 columns!
This section will generate information by partitions or subpartitions. In this example each third partition probed to identify actual compression status.
The link to download script: compression_analyzer.sql
(c) Aychin Gasimov, 11/2014, Munich, Germany