advertisements
_____________________________________________________________________________________________________________________
Introduction: The data volume keep on increasing in the databases day by day which result the need of more storage for the databases. Oracle 11g provides advance compression feature to reduce the storage on an average of 3X actual data volume. It is kind of go green feature which can be applied on structured data and unstructured data like documents, images and multimedia. Using this feature database can store more data, faster queries executions, etc.
Benefits:
· Up to 2-4X storage reduction on database and backups
· Improved query performance due to less disk scans, more memory efficiency and less I/O
· Reduces the network traffic and better network bandwidth utilization
· No application changes required
· Compressed data ensures improved memory efficiency.
Compression Methods
1. Table:
ü Table compression introduced in Oracle 9i rel2.
ü Applicable during the bulk load operations like CTAS, direct load
ü Compression is not applicable on conventional DMLs like insert, update
ü Data compression at database block level
ü Improved buffer cache efficiency
ü Can be enabled either table or partition level
Syntax: CREATE TABLE order (
ord_id NUMBER,
ord_type VARCHAR2(128) ,
ord_name VARCHAR2(128)
) COMPRESS [BASIC];
create table emp2 compress for all operations as select * from emp;
create table emp (col1 number) NOCOMPRESS;
create table emp (col1 number) COMPRESS FOR DIRECT_LOAD OPERATIONS;
create table emp (col1 number) COMPRESS FOR ALL OPERATIONS;
Alter an existing table to enable compression
alter table emp compress for all operations;
2. OLTP Table:
ü OLTP table compression introduced in Oracle 11g
ü Applicable for conventional DMLs like insert and update
ü Significantly reduces the write overhead and no read impact
ü Read operation may give performance improvement due to less I/O rate and enhanced memory efficiency
Syntax: CREATE TABLE order (
ord_id NUMBER,
ord_type VARCHAR2(128) ,
ord_name VARCHAR2(128)
) COMPRESS FOR OLTP;
Data Dictionary View to monitor the Compression
View: DBA_TABLES
Colums: COMPRESSION[ENABLED/DISABLED]
COMPRESS_FOR[FOR ALL OPERATIONS/DIRECT READ ONLY]
Pic Courtesy: Oracle Corporation
3. SecureFiles:
ü SecureFiles includes Large objects (LOB) like documentations (doc), PDF, XML, Video, images
ü Techniques used are deduplication and compression
Deduplication: intelligent technology that eliminates duplicate copies of secure files
Compression: Compresses the file data stored within the database with 3 levels of compression[LOW/[MEDIUM]/HIGH]
ü Automatically detects the if SecureFile data is compressible
ü Allows for random reads and writes to Compressed SecureFile data
ü Can be specified at a partition level
ü 2-3x compression for typical SecureFiles
ü Provides significant storage savings for unstructured data
Syntax for Compress
CREATE TABLE Doc (pht CLOB)
LOB(pht) STORE AS SECUREFILE (
COMPRESS
CACHE );
Syntax for Deduplication
CREATE TABLE doc (pht CLOB)
LOB(pht) STORE AS SECUREFILE (
DEDUPLICATE
CACHE );
4. Backup: Data Pump & RMAN
ü Metadata compression introduced in Oracle 10g
ü Feature extends to table data during exports in Oracle 11g
ü No need to decompress before import
ü Option used in expdp is COMPRESSION={ALL|DATA_ONLY|[METADATA_ONLY]|NONE}
ü Single step compression for both data and metadata
ü Reduces the dump file size up to 75%
Syntax
Data Pump: expdp mobile DIRECTORY=export_dir DUMPFILE=exp_mobile.dmp COMPRESSION=ALL
RMAN:
RMAN>configure compression algorithm ‘MEDIUM’;
RMAN>backup as COMPRESSED BACKUPSET database plus archivelog;
5. Network and Data Guard Redo transport
ü Advanced Compression offers the capability to compress Oracle Data Guard/standby redo data as it is transmitted over the network
ü Reduces the network traffic between primary and standby database
ü 15-35% less time required to transmit 1GB of data
ü Bandwidth consumption can be reduced up to 35% and may reduce the transmission time of redo data in an Oracle data Guard configuration
Syntax: LOG_ARCHIVE_DEST_3=’SERVICE=standby SYNC COMPRESSION=ENABLE|[DISABLE]’
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment