Ver este articulo en EspañolHow 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:
After running above script, you'll get a listing like this:
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;
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.
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.
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
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ñolSubscribe to Oracle Database Disected by Email
