Oracle Index Komprimierung II

Dazu gibt es schon einen Post  - doch der  Hinweis 1109743 - Komprimierung von Indexschlüsseln für Oracle-Datenbanken enthält nun ein SQL Script mit Erfahrungswerten vieler Kunden welche Indizes auf einem ERP System komprimiert werden sollten!

Weiters gibt es im SDN noch ein SQL Skript mit dem man die Indizes ermitteln kann
welche relevant für eine Komprimierung sind!

Link: SND

Index Compression Overview


Being able to compress indexes has many advantages:

Less space consumption on disk

Less space consumption in buffer pool

Less I/O activities

Better performance due to less disk reads and buffer gets

Less CPU consumption (because the small compression related CPU overhead is smaller than the CPU savings due to less disk reads and buffer gets)

The main problem of index compression is that an optimal amount of leading index columns (prefix) has to be found that can be compressed

An exact analysis (e.g. via VALIDATE STRUCTURE or provided PL/SQL scripts) needs to scan a lot of data and runs a long time

The following SQL command can be used to determine an acceptable index compression prefix and the expected minimum space gain quickly based on CBO statistics.

AnalysisSQL Command: Space_IndexCompression.txt


Purpose:

Conservative index compression analysis based on CBO statistics

Features:

Threshold value for minimum amount of saved data space

Analysis can be restricted to particularly large or I/O intensive segments (focus on space or performance)

Generation of Oracle or BRSPACE index rebuild command for compression activation possible

Restrictions:

Result can„t be 100 % precise because correlation of leading index columns is not visible in CBO statistics, script assumes worst case scenario of uncorrelated columns, in about 20 % of all indexes this can lead to a suggested prefix length that is smaller than the optimal prefix length

Prerequisites:

Regular BRCONNECT statistic run must be scheduled (recommendation: daily)