Honey, I shrunk the indexes - Part 2

Ver este articulo en Español

How to pick the perfect candidates

Warning: index compression may change execution plans and affect performance, try this on a test database and check if application SQL and PL/SQL code execution improves.

Not every index must be compressed, sometimes compression will give minimal space reductions, that don't compensate the overhead incurred. But how do we know that?

I may suggest two approaches:
1) Size oriented
2) Access frequency oriented

Size Oriented

Let's start with size oriented picking, saying: the bigger the elephant is, better results will get when on diet.

I've used a script like this to get my list of candidates for shrinking:

SELECT
substr(segment_name,1,20) as index_name,
bytes, blocks, extents
FROM  dba_segments
WHERE owner = '{write here the owner}'
AND   segment_type = 'INDEX'
AND   extents > 63            <---this you may change order by bytes desc; 

After running above script, you'll get a listing like this:

INDEX NAME                BYTES     BLOCKS    EXTENTS                 
-------------------- ---------- ---------- ----------                 
PROD_NAME_IX             524288         64          8                 
PRD_DESC_PK              327680         40          5                 
SYS_C009603              131072         16          2                 
SYS_C009607               65536          8          1                 
SYS_C009606               65536          8          1                 
ACTION_TABLE_MEMBERS      65536          8          1                 
LINEITEM_TABLE_MEMBE      65536          8          1                 
SYS_C009602               65536          8          1                 

Now you have to forecast the best compression ratio for your index, and there is a feature very accurate for doing so: ANALYZE the index.

Despite the fact that analyzing tables or objects have deprecated the statistics purpose (one of them), we may use this sentence to test structure. Following command and a quick query to INDEX_STATS will show us if the selected index is a best fit, which compression order to choose and expected size reduction:

SQL> ANALYZE INDEX owner.index_name VALIDATE STRUCTURE OFFLINE;

Index Analyzed

SQL> SELECT name, height, blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE
FROM index_stats
WHERE name = '{index_name}';

The resulting value OPT_CMPR_COUNT is the value you specify for COMPRESS {n} clause, and OPT_CMPR_PCTSAVE is the "expected" compression ratio for that value. All other values from INDEX_STATS are present figures.

Then your sentences may look like this:

SQL> ALTER INDEX owner.index_name REBUILD COMPRESS {value from OPT_CMPR_COUNT}

or

SQL> CREATE INDEX owner.index_name ON {table_index_clause}
2:   TABLESPACE {Tablespace Name}
3:   COMPRESS {value from OPT_CMPR_COUNT}
4:   {other storage clauses};

Second approach: Access Frequency

For this we're going to need the help of two important views: V$SEGSTAT(9i and up) and ALL_OBJECTS. We need V$SEGSTAT because that dynamic view will show us valuable statistics regarding logical reads/writes or physical reads/writes. Following script is proposed as an aid to find the top used indexes within a schema.

SELECT a.object_name, b.statistic_name, b.value
FROM all_objects a, v$segstat b
WHERE  a.object_id = b.obj#
AND  a.owner = '{your schema owner here}'
AND  a.object_type = 'INDEX'
AND  b.statistic_name = 'physical reads'  <-- You may change this for physical reads direct ORDER by b.value desc 

Above query will give you a list of candidates for compression, now you have to apply the ANALYZE and check if there are good space reductions that 'may' correspond to less IO.

Jump to Part III Honey, I shrunk the indexes - Part 3: Index Compression is good or evil?

View starting post: Honey, I shrunk the indexes - Part 1



Ver este articulo en Español

Subscribe to Oracle Database Disected by Email