Monthly Archives: April 2009

Oracle Advanced Compression

Databases grow in sizes over time, there are many reasons for this. Some of them are
1) Detailed web usage (click tracking) capture, to be used for analytics.
2) User created content in web 2.0 applications
3) Rarely used (But needed) historical data that does not get purged (maybe for compliance).
4) Application consolidation.

This means that customers needs to procure and maintain large amounts of disk storage (Read Storage Array Networks, or Network attached storage). I am not a storage expert, but i am sure that the cost of procuring and maintaining highly performant, high end storage (cost per gb per year) is very high.

With the Oracle advanced compression option, customers can reduce the space used by oracle database tables. In oracle 9i oracle introduced the ability to compress data that is bulk loaded into the database (direct path loads and create table as select statements). In oracle 11g this was expanded to compressing all data that is loaded into oracle tables.

The benefits of compression are manyfold.
1) Reduction of disk space used for storage
2) Reduction in I/O bandwidth requirements
3) Faster full table scans
4) Lower server memory usage.

You can enable compression on a table by specifying the “compress for” clause of a create table statement.

eg: create table emp (emp_id number, first_name varchar2(128), last_name varchar2(128) ) compress for all operations.

Oracle enables compression by storing a symbol table of the repeating column values in a block and then, references are stored in the rows to point to the entry in the symbol table.

Oracle compresses a block in batch mode, rather than compressing data every single time a write operation takes place. All inserts into a newly initialized block are uncompressed (So there is no difference here, while insert’s are taking place into this block). When a transaction causes the data in the block to reach the pctfree limit (The algorithm that decides when to re-compress the block, considers various factors, where pctfree is only one of the factors), the contents of the block gets compressed (Note that this is the only transaction that incurs the overhead for compression). Therefore a majority of OLTP transactions on compressed blocks will have exactly the same performance as they would with uncompressed blocks.

The compression ratio that you are going to get varies with the contents of the table. So you should use the compression advisor to determine how much storage you are going to save by using advanced compression on the table.

You can download the compression advisor from You can install it by logging in as SYS to the database and running dbmscomp.sql and prvtcomp.plb.

The compression advisor can be invoked by using a command similar to the one below.

SQL>  exec DBMS_COMPRESSION.getratio(‘SH’,’SALES’,10);

Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression Option: 2.96

PL/SQL procedure successfully completed.


MOS Master Note for OLTP compression.

Grid control New features – Part III

Real-time Sql monitoring is a really exciting new feature that was introduced in oracle database 11gR1. This feature allows you to monitor the performance of sql statements as they are executing. Sql monitoring is automatically started if a sql statement is executing in parallel, or if the sql statement has spend more than 5 seconds of CPU or I/O time during a single execution. Details for this feature can be found in the 11g Performance Tuning guide.

You can run the report from sqlplus (And spool the html output to an o/s file) as follows

sqlplus / as sysdba

set long 1000000000
set pages 0

spool sqlmon.html

type=>’HTML’) as report
from dual;

spool off

A sample output can be seen here.

Grid control,exposes this functionality from the management console (Until now you could access this from 11g database control or using the API DBMS_SQLTUNE.REPORT_SQL_MONITOR). You can access this by navigating to Targets -> Databases -> Choose your 11g database -> Performance. You can then click on “SQL monitoring” to monitor your sql statements that are currently executing.

This screen gets refreshed every 10 seconds, and it shows the SQL Id, The total execution time up till now, Degree of parallelism in use, The database time, Total I/O count, Start Time (If the sql has completed, the End Time) and the actual sql statement.

You can click on Status to further drill down into the sql statement.

The overview section shows the Duration, Database Time, IO count and buffer gets done by the sql (And the screen refreshes every 10 seconds, showing you the progress.)

The details section has 3 different views . Plan statistics that shows you the execution plan , and time spend, in different steps in the execution plan.

The parallel view, gives you further drill down into each parallel server and shows the Db time, Wait Activity%, IO count and Buffer gets by each parallel server.

The activity view , gives you the color coded activity breakdown within the session CPU, and other wait events.

You can also get to the real-time sql monitoring screen by identifying a sqlid and drilling down into the sql id. Then there is a tab named “sql monitoring” , which gives you this same information.

Really neat, dont you think ?

Grid control new features – Part II

Two more new features from enterprise manager, grid control.

Fine-grained, target privileges

When you create an administrator in Grid control, to whom you are granting operator privileges, you can now further control what activities they are able to perform.

You can choose from granting them

Blackout target

Manage target metrics

Configure target

Manage target alerts

Clone database from existing backup

Prior to grid control, your only two options, to clone a database using the provisioning pack, were to 1) do a hotclone from an existing database 2) use an existing backup from a previous clone.

Now in grid control, to clone a database, you can  choose to use an existing database backup. You can choose to clone from a existing database, and in the Source Type screen, you can choose “An Existing database backup”.