Thursday, April 24, 2008

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

Add to Technorati Favorites

Ver este articulo en Español

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

No comments:

Custom Search