maaz anjum - ioug collaborate 2013 - an insight into space realization on oda and exadata

About MaazMaaz Anjum

• Marietta, Georgia • Solutions Architect:

• OEM12c• Golden Gate• Engineered Systems

• Member of IOUG• Using Oracle products SINCE 2001

Blog: http://blogspot.maazanjum.comEmail: [email protected]

http://blogspot.maazanjum.com/

mailto:[email protected]

• Founded in 2000• Distinguished Oracle Leader

– Technology Momentum– Portal Blazer Award– Titan Award – Red Stack + HW Momentum– Excellence in Innovation

• Management Team is Ex-Oracle• Location(s): Headquartered in Atlanta; Regional office in Washington D.C.; Offshore – Hyderabad and Chennai, India• 200+ with 10+ yrs of Oracle experience avg (75 openings

today…)• Inc.500 fastest growing private company in the U.S. for the

3rd Time• Voted Best Place to work in Atlanta for 2nd year

• Oracle Platinum Certified Systems Integrator & Oracle GSA Software Reseller • Consulting expertise and past performance across the entire Oracle Stack• One stop shop for all things Oracle including Hardware, Software, Consulting, Managed Services, and Staff Augmentation• 350 customers across Federal Civilian Agencies, Department of Defense, State and Local Government, and Fortune 500• 1500 successful implementations since 2000• Certified across the entire Oracle Stack (1 of 8 partners out of 10,000)• Top 5 Oracle reseller in the United States• Business Intelligence Pillar Partner for Public Sector and Commercial

Oracle’s Advanced CompressionAn Insight into

Space Realization on ODA and

Exadata#523

• Overview• A Brief History • Pre-11g• 11g: New Features• Case Study - Story

AGENDA

7

• Combined with Oracle Database 11g helps businesses manage more data in a cost effective manner while providing for storing and auditing of historical data.

• Delivers compression rates of 2-4x across all types of data and applications improving query performance.

• Includes compression for structured data (numbers, characters, etc.), unstructured data (documents, images, etc.), backups (RMAN and Data Pump) and network transport (redo log transport during Data Guard gap resolution).

OVERVIEW

8

• Reduces database storage requirements and associated costs

• Compresses transaction processing and data warehousing application tables

• Compresses structured, unstructured, backup and Data Guard Redo Log network transport data

• Includes Total Recall for storing and auditing historical data

• Cascades storage savings throughout the data center

OVERVIEW

• Overview• A Brief History • Pre-11g• 11g: New Features• Case study - Story

AGENDA

• Given many names• Data Compression

• Source Coding

• Bit-Rate Compression

A Brief History


AGENDA

12

• First introduced in Oracle 9.2.0.1• WITH COMPRESS

• A trade-off between CPU and Disk I/O• The use of spare CPU cycles to decrease the bytes written and

read• Transparent to applications, SQL, and PL/SQL• May improve performance by requiring the transfer of fewer bytes

from disk through the network, into the CPU, to be stored in the buffer cache

• Increase the amount of data stored on existing disk

Pre-11G


AGENDA

14

The Advanced Compression Option includes:• Data Guard Network Compression• Data Pump Compression• Fast RMAN Compression• OLTP Table Compression• SecureFile Compression and Deduplication

• Leveraged in 11gR2 DBFS (DataBase File System)

11G New Features

15

• Compressed Tablespaces• Segment Compression

• COMPRESS• COMPRESS FOR BASIC• COMPRESS FOR OLTP

• user_tablespaces.compress_for column• Hybrid Columnar Compression

• Warehouse Compression (Query)• Archival Compression (Archive)

11G New Features

16

Fully supported with…• B-Tree, Bitmap Indexes, Text indexes• Materialized Views• Exadata Server and Cells• Partitioning • Parallel Query, PDML, PDDL• Schema Evolution support, online, metadata-only • add/drop columns• Data Guard Physical Standby

11G New Features

17

Compress for OLTPCREATE TABLE ct1COMPRESS FOR OLTPAS

SELECT * FROM dba_objects;

Compress for QueryCREATE TABLE ct2COMPRESS FOR QUERY HIGHASSELECT * FROM dba_objects;

Compress for ArchiveCREATE TABLE ct3COMPRESS FOR ARCHIVE LOWASSELECT * FROM dba_objects;

11.2 Table segment compression

18

11.2 Table segment compression

19

Types of Compression

20

Compression Characteristics

21

• Tablespaces• Tables• Partitions• Indexes• SecureFiles• RMAN Backups• Data Pump Backups

What Can Be Compressed?

22

Tablespaces

CREATE TABLESPACE test_ts DATAFILE '/u01/app/oracle/oradata/DB11G/test_ts01.dbf' SIZE 1M DEFAULT COMPRESS FOR ALL OPERATIONS;

SELECT def_tab_compression, compress_forFROM dba_tablespacesWHERE tablespace_name = 'TEST_TS';

DEF_TAB_ COMPRESS_FOR -------- ------------------ ENABLED FOR ALL OPERATIONS


23

Partitions

CREATE TABLE test_tab_2 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL, created_date DATE NOT NULL)PARTITION BY RANGE (created_date) ( PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS, PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR OLTP, PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) COMPRESS FOR OLTP, PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS);


Key-Compressed Indexes•Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.•Key compression breaks an index key into a prefix and a suffix entry. •Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. •This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;


SecureFiles•SecureFile compression does not entail table or index compression and vice-versa.•A server-wide default SecureFile compression algorithm is used.•MEDIUM and HIGH options provide varying degrees of compression. The higher the degree of compression, the higher the latency incurred. HIGH setting incurs more work, but will compress the data better. The default is MEDIUM.


SecureFiles•Compression can be specified at a partition level. The lob_storage_clause enables specification for partitioned tables on a per-partition basis.•SecureFile compression is performed on the server-side and enables random reads and writes to LOB data. Client side compression utilities like utl_compress cannot provide random access.•DBMS_LOB.SETOPTIONS can be used to enable and disable compression on individual LOBs.•LOB compression is applicable only to SECUREFILE LOBs.


RMAN BackupsCONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DEVICE TYPE TAPE BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE COMPRESSION ALGORITHM 'BASIC';CONFIGURE COMPRESSION ALGORITHM 'NONE';CONFIGURE COMPRESSION ALGORITHM 'LOW';CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';CONFIGURE COMPRESSION ALGORITHM 'HIGH';


RMAN Backups


RMAN BackupsSample “Medium” Algorithm compression results


Data Pump Backups•The ability to compress the metadata associated with a Data Pump job was first provided in Oracle Database 10g Release 2. •In Oracle database 11g, this compression capability has been extended so that table data can be compressed on export.


Data Pump BackupsFull Data Pump functionality is available using a compressed file. Any command that is used on a regular file will also work on a compressed file. Users have the following options to determine which parts of a dump file set should be compressed:•ALL enables compression for the entire export operation.•DATA-ONLY results in all data being written to the dump file in compressed format. •METADATA-ONLY results in all metadata being written to the dump file in compressed format. This is the default.•NONE disables compression for the entire export operation.


• When compression is specified at multiple levels, the most specific setting is always used

• As such, partition settings always override table settings, which always override tablespace settings

CONSIDERATIONS

Two Types• Warehouse Compression• Archive Compression

Works with Exadata and now ZFS StorageZFS Storage can be attached with an ODA as NAS Storage

Hybrid Columnar Compression

Hybrid Columnar Compression

• Data is decompressed while being read• Oracle Database does not need to decompress table

blocks when reading data. Oracle can keep blocks compressed in memory and read them directly. Hence, more data can be packed in memory which results in improved cache hit ratio and reduced I/O.

• Data needs to be recompressed once update• Not true with 11gR2 – COMPRESS WITH OLTP

algorithm compresses newer data without uncompressing updated rows.

MYTHS

• When should I compress?• What should I compress?

CONSIDERATIONS

OLTP Applications• Table Partitioning• Heavily accessed data

• Partitions using OLTP Table Compression• Cold or historical data

• Partitions using Online Archival Compression• Data Warehouses

Compression & Partitioning

DBMS_COMPRESSION built-in package• GET_COMPRESSION_RATIO

• Returns the possible compression ratio for an uncompressed table or materialized view and estimates achievable compression

• GET_COMPRESSION_TYPE• Inspects data and reports what compression type is in use

by row

Enterprise Manager Segment Advisor• Estimates OLTP Table Compression automatically• Advises tables that will benefit from OLTP Compression

New Compression Advisors

GET_COMPRESSION_RATIO

CREATE TABLE comp_test1 ASSELECT * FROM dba_objects;

set serveroutput onDECLARE

blkcnt_comp PLS_INTEGER;blkcnt_uncm PLS_INTEGER;row_comp PLS_INTEGER;row_uncm PLS_INTEGER;comp_ratio PLS_INTEGER;comp_type VARCHAR2(30);

BEGINdbms_compression.get_compression_ratio('UWDATA', 'UWCLASS', 'COMP_TEST1', NULL, dbms_compression.comp_for_oltp, blkcnt_cmp, blkcnt_uncmp, row_comp, row_uncm,dbms_compression.comp_for_oltp, blkcnt_cmp, blkcnt_uncmp, row_comp, row_uncm,comp_ratio, comp_type);dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(blkcnt_comp));dbms_output.put_line('Block Count UnCompressed: ' || TO_CHAR(blkcnt_uncm));dbms_output.put_line('Row Count Compressed: ' || TO_CHAR(row_comp));dbms_output.put_line('Row Count UnCompressed: ' || TO_CHAR(row_uncm));dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(comp_ratio));dbms_output.put_line('Compression Type: ' || comp_type;

END;/

Compression Ratio Estimate

• Overview• A Brief History • Pre-11g• 11g: New Features• Case study of BIAS' implementation for a

customer.• Background • requirements• Storage savings achieved; tablespaces and

tables

AGENDA

Challenge– 8TB Database Uncompressed and Unpartitioned– ODA had only 2.3TB of usable space.

Goals– Compress customer data and achieve similar (if not

better) performance– Use Database Replay to simulate workload– Perform Detailed Analysis of Performance Statistics

CASE STUDY

CASE STUDYHardware

– Platform: ODA 2.1.0.3.0 – 2 Nodes running Oracle Enterprise Linux 5.7 64bit

– CPU: 24 cores per node– RAM: 96GB per node

Database Version– 11.2.0.2 64bit

Instance parameters– SGA: 48GB– PGA: 10GB– Block Size: 8K

Data CharacteristicsBillions of Rows

Across Several Tables

VARCHAR2 and NUMBER data types

Repeating Patterns within each table

CASE STUDY

Steps– Create a compressed export dump of a

schema.

– Create compressed tablespaces – in our case for OLTP.

– Import Meta data only; users, grants, objects (excluding indexes and constraints).

– Alter tables for compression.ALTER TABLE CARBON COMPRESS FOR OLTP;

CASE STUDY

Steps– Import only table data – with appropriate parallel

degree.

– Import index creation scripts from export dump.

– Alter relevant indexes to add compression factor of 1.

– Create indexes with appropriate parallel option.

– Import only constraints.

– For good measure, generate statistics on the schema.

CASE STUDY

• What should I compress first?• Multiple iterations with import• Tweaked level of compression• Time is the biggest enemy

CHALLENGES

What’s that?

You want to see proof of compression??

8 TB Database, compressed to less than 1.5TB on an Oracle Database Appliance

RESULTS

RESULTS

RESULTSDatabase Replay Results

Capture: 3 ½ HoursReplay: Nearly 10


•Database was CPU bound•Presumably because indexes were compressed


Two INSERT statements are the top consumers (over 75%) of the total sql statements


Average Active Sessions show database was CPU bound

What Did We Learn?• Compression Ratio’s vary but are mainly dependent on

Block redundancy• Choose appropriate compression type• Database Replay (conditions need to be perfect)

• Ensure workload capture is done with a consistent backup

• Ensure same number of clients can be spawned• Spend adequate time analyzing the results• Patience is golden virtue!

QUESTIONS

Blog: http://blogspot.maazanjum.comEmail: [email protected]: @maaz_anjumSession: 523

collaborate13.ioug.org/eval

http://blogspot.maazanjum.com/



maaz anjum - ioug collaborate 2013 - an insight into space realization on oda and exadata

Technology

oltp compression

types of data

data center8

rate compression

compression rates

compression characteristics20

ddmmyyyy compress

basic compress