Storage optimisation of database tables

imageBymatthiasFebruar 24, 2016 0
Storage optimisation of database tables

In your organisation the storage growth of your database could be an important topic. Storage becomes cheaper and cheaper but there is no room for wastage. Sometimes I get the challenge to optimise existing databases in terms of storage consumption. This post will describe how I’m proceeding with tables and which kind of methods are possible.

First of all you’ve to identify the “biggest fishes”. So for this we are using the data dictionary and looking which tables are using the most space.

SELECT
  segment_type,
  owner,
  segment_name,
  round(bytes/1024/1024) AS mbytes
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP','XDB') --Exclude system tables
      AND bytes/1024/1024 > 100                    --Should be greater than 100MB
      AND segment_type = 'TABLE'                   --Only focusing on tables
ORDER BY bytes DESC;                               --Sort biggest first

Reorganisation

One option is to reorganise your tables. So you can MOVE a table between two tablespaces.

Syntax:

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace>;

ALTER INDEX <index_name> REBUILD;

In my example the table IMP_DATABASE_ORA stays at tablespace APP_DATA and will be moved to other tablespace APP_FILES and then back:

ALTER TABLE IMP_DATABASE_ORA MOVE TABLESPACE APP_FILES;
ALTER TABLE IMP_DATABASE_ORA MOVE TABLESPACE APP_DATA;

Be aware that if a table moves the index becomes invalid. Therefore you’ve to rebuild your index:

ALTER INDEX IX_DBNAME REBUILD;
  •  Pro
    • Easy to use
    • No extra licence required
  • Cons
    • No DML-actions are possible during reorganisation
    • Additional temp space is required
    • Index rebuild

Other method is to use the SHRINK-option which is available since Oracle 11g. It defragments database blocks online and sets the highwater mark lower. Before using row movement needs to enabled.

Syntax:

ALTER TABLE <table_name> ENABLE ROW MOVEMENT;

ALTER TABLE <table_name> SHRINK SPACE [COMPACT];

ALTER TABLE IMP_DATABASE_ORA ENABLE ROW MOVEMENT;
ALTER TABLE IMP_DATABASE_ORA SHRINK SPACE;
  • Pro
    • No temp space required
    • With COMPACT-clause can be done online
    • No extra licence required
  • Cons
    • Not usable with compressed tables
    • Not usable with tables using functional indexes

Compression

Comparing to reorganisation you can also compress data within your tables. Oracle provides the feature “Compression” and “Advanced Compression” where advanced compression requires a further database option. Additionally to the saved storage you also save IOs because with compression you’re able to store more rows in one database block like before. With a full table scan the user session will request less database blocks as without compression.

Syntax: CREATE TABLE <table_name> ( <COLUMN_DEFINITION> ) COMPRESS [FOR OLTP];

CREATE TABLE IMP_DATABASE_ORA_2 COMPRESS FOR OLTP
AS
SELECT *
FROM IMP_DATABASE_ORA;

Please be aware that the “FOR OLTP” expression activates the “Advanced Compression”-option.

  • Pro
    • Transparent for the application / users
    • Very efficient but depends of the data
    • Less I/Os
  • Cons
    • OLTP-Option >> Extra licence required
    • Higher CPU utilisation

Optimisation

Another option is to adjust the parameter PCTFREE of your tables. PCTFREE defines the percentage of a database block which is free for future updates. This makes sense because when one database block exceeds the blocksize, the row will move to another block. This is called row migration can cause performance problems and should be avoided.

Assuming you’ve a table where only INSERT-statements occur. You will never make UPDATEs on that table for example if you’ve a data warehouse and collecting data over a big timeframe. In this case it makes sense to set PCTFREE to zero and save space.

Syntax: CREATE TABLE <table_name> ( <COLUMN_DEFINITION> ) PCTFREE 0;

By default PCTFREE is set to 10, means 10 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6} of row is reservered.

CREATE TABLE IMP_DATABASE_ORA_2 PCTFREE 0
AS
SELECT *
FROM IMP_DATABASE_ORA;
  • Pro
    • 10 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6} space savings
    • Transparent for the application/users
  • Cons
    • You’ve to know which kind of transactions are made on your tables
    • Not advisable for tables with common updates

Comparison

[table]
Table Name,Used Technic,Storage (MB), Savings

IMP_DATABASE_ORA, default size, 1.550 MB, 0 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6}
IMP_DATABASE_ORA, Reorganisation between TBS, 1.310 MB, 15 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6}

IMP_DATABASE_ORA, Reorganisation + PCTFREE=0, 1.178 MB, 24 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6}

IMP_DATABASE_ORA, COMPRESS FOR OLTP, 413 MB, 73 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6}

IMP_DATABASE_ORA, COMPRESS FOR OLTP+PCTFREE=0, 364MB, 76 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6}

[/table]

In my case I’ve saved up to 76 {254b2f06d25c03c6e42b27fd3028f54f80a3c308106eaad6fe02ee24b8dfaaa6} of space, where the compression created the most benefit. The combination of multiple methods is the key to reach the best result.

 

Schreibe einen Kommentar