Heat Map and Automatic Data Optimization with Oracle Database 12c

60 %
40 %
Information about Heat Map and Automatic Data Optimization with Oracle Database 12c
Technology

Published on February 5, 2014

Author: digicompFR

Source: slideshare.net

Description

Présentation des nouveautés Oracle Database Data Optimization 12c.

http://www.digicomp.ch/fr/oracle

1 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Heat Map and Automatic Data Optimization with Oracle Database 12c Jean-Philippe Breysse

Program Agenda  Heat Map  Automatic Data Optimization  Benefits of Heat Map and ADO 3 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Data Compression Reduce storage footprint, read compressed data faster Hot Data Warm Data Archive Data 111010101010101 10101010111010100110101 001101010101011 11000010100010110111010 010001011011000 10100101001001000010001 110100101000001 01010110100101101001110 001110001010101 00010100100101000010010 101001011010010 00010001010101110011010 110001010010011 111001001000010 001010101101000 101010101110101 001101011100001 010001011011101 010100101001001 000010001010101 101001011010011 100001010010010 100001001000010 001010101101001 101010101110101 001101011100001 010001011011101 010100101001001 000010001010101 101001011010011 100001010010010 100001001000010 001010101101001 3X 10X 15X Advanced Row Compression 4 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 10101010111010100110101110000101000101 10111010101001010010010000100010101011 01001011010011100001010010010100001001 00001000101010111001101110011000111010 10101010111010100110101110000101000101101110101 01001010010010000100010101011010010110100111000 01010010010100001001000010001010101110011011100 Columnar Query Compression Columnar Archive Compression

Oracle Advanced Compression Transparent, Smaller, Faster  100% Application Transparent  End-to-end Cost/Performance Benefits across CPU, DRAM, Flash, Disk & Network  Runs Faster: OLTP Apps (Transactional & Analytics) & DW  Reduces Database Footprint – CapEx & OpEx savings – Increases Cloud ROI through Database Footprint reduction in DRAM Memory 5 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Details: Oracle Advanced Compression + New Features, New Feature Names Oracle Advanced Compression Oracle Database 11g Oracle Database 12c OLTP Compression Advanced Row Compression Secure Files Compression Advanced LOB Compression Secure Files De-duplication Advanced LOB Deduplication NEW Heat Map (Object and Row Level) + Automatic Data Optimization NEW 6 NEW Temporal Optimization Hybrid Columnar Compression Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Hybrid Columnar Compression

Understanding Data Usage Patterns Database ‘heat map’ 0 0101110101001101 1 1 0 0 0 1 0 1 0 11 1 0 1 0 1 0 1 0 011 1010101010100 0 1 10 01000101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 010 1110000011101 1 10 01000101010 101 1 1 10 01000101010 101 1 1 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 10 01000101010 101 1 1 10 01100101010 101 1 1 8 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 1 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1010101010100 0 1 0 01000101010101 11 1 10 01000101010 101 1 1 011 1110101011101 0 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 010 1110000011101 1 10 01000101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 1 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110101011101 0 1 10 01100101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1010101010100 0 1 0 01000101010101 11 1 0 1 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 10 01000101010 101 1 1 10 01000101010 101 1 1 011 1110101011101 0 1 1 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110101011101 0 1 10 01100101010 101 1 1

Understanding Data Usage Patterns Database ‘heat map’ 0 0101110101001101 1 1 0 0 0 1 0 1 0 11 1 0 1 0 1 0 1 0 011 1010101010100 0 1 10 01000101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 010 1110000011101 1 10 01000101010 101 1 1 10 01000101010 101 1 1 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 10 01000101010 101 1 1 10 01100101010 101 1 1 9 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 1 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1010101010100 0 1 0 01000101010101 11 1 10 01000101010 101 1 1 011 1110101011101 0 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 010 1110000011101 1 10 01000101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 1 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110101011101 0 1 10 01100101010 101 1 1 1 0 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1010101010100 0 1 0 01000101010101 11 1 0 1 0 0 1 0 11 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110100011100 0 1 10 01000101010 101 1 1 1 0 0 1 1 0 11 1 0 0 0 1 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 10 01000101010 101 1 1 10 01000101010 101 1 1 011 1110101011101 0 1 1 1 0 1 0 1 0 1 0 1 0 10 1 0 1 1 1 011 1110101011101 0 1 10 01100101010 101 1 1

Heat Map What it tracks  “Heat Map” tracking HOT Active – Access and modification times tracked for tables and partitions – Modification times tracked for database blocks  Comprehensive Frequent Access WARM – Segment level shows both reads and writes – Distinguishes index lookups from full table scans – Automatically excludes stats gathering, DDLs, table Occasional Access redefinitions, etc  High Performance Dormant 10 COLD Copyright © 2013, Oracle and/or its affiliates. All rights reserved. – Object level at no cost – Block level < 5% cost

Heat Map Enterprise Manager 11 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Automatic Data Optimization Usage Based Data Compression 01110101010010 10000100010101 01011100001010 Warm Data 101010101110101001101011100001010001011011 0101101110101010010100100100001000 101010100101001001000010001010101101001011 1010101101001011010011100001010010 10101010111010100110101 11000010100010110111010 10100101001001000010001 011100001010001011011 01010110100101101001110 101010100101001001000 00010100100101000010010 010001010101101001011 00010001010101110011010 010101001010010010001 10100101001001000010001 Archive Data 1010101011101010011010111000010100 Hot Data 010011100001010010010100001001000010001010 0101101110101010010100100100001000 0101000010010000100010101011010010 101010101110101001101011100001010001011011 1010101101001011010011100001010010 1000010100100101001010110111000010 101010101110101001101011100001011101011001 1110010100100101001010110111011010 10X 3X Advanced Row Compression 12 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 2012, 15X Columnar Query Compression Columnar Archive Compression Confidential – Oracle Restricted

Automatic Data Optimization Simple Declarative SQL extension ALTER TABLE sales ILM add Active Frequent Access Occasional Access Dormant 13  Advanced Row Compression (2-4x)  Affects ONLY candidate rows  Cached in DRAM & FLASH row store compress advanced row after 2 days of no update  Warehouse Compression(10x)  High Performance Storage column store compress for query low after 1 week of no update  Warehouse Compression(10x)  Low Cost Storage tier to SATA Tablespace  Archive Compression(15-50X)  Archival Storage column store compress for archive high after 6 months of no access Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Automatic Data Optimization OLTP Reporting Compliance & Reporting 10x compressed 15x compressed This Quarter Row Store for fast OLTP 14 This Year Compressed Column Store for fast analytics Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Prior Years Archive Compressed Column Store for max compression As data cools down, Automatic Data Optimization automatically converts data to columnar compressed Online

Up to 15x Smaller Footprint & Faster Queries Automatic Data Optimization for OLTP  Both Columnar & Archive Compression now complement Advanced Row Compression  Best Practice: – Step 1: Use Advanced Row Compression for entire DB and then – Step 2: ADO automatically converts into columnar compressed once the updates cool down, and is used mainly for reporting => Query speed of Columnar & 10x smaller footprint – Step 3: ADO automatically converts into archive compressed once data cools down further and is no longer frequently queried => 15-50x smaller footprint 16 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Optimizes Data Based on Heat Map Automatic Data Optimization for DW  Data generally comes in via Bulk Loading  Workload dominated by queries, even during loading Step 1: Bulk Load directly into Columnar Compressed – 10x smaller footprint, Query speed of Columnar Step 2: ADO automatically converts to Archive Compressed and moves to Lower Cost Storage once its queried infrequently – Data remains online, with 15-50x smaller footprint, & lower storage cost 17 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Fast, Flexible Loads & Queries on Columnar Automatic Data Optimization – Mixed Use  Fastest Load with uncompressed & Fastest Queries with columnar – Mixed workloads often use Java app or 3rd party tools to insert and update data that does not use Bulk Loads, so cannot use Columnar  Step 1: Load into uncompressed, conventional inserts & updates – Fast loading, & flexibility of using a regular OLTP app for loading  Step 2: ADO moves to Row Compressed or Columnar Compressed or Low Cost Storage once updates cool down – Faster Queries, 3-10x smaller footprint 18 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Backup & Recovery Automatic Data Optimization OLTP Reporting Compliance & Reporting 10x compressed 15x compressed Read / Write Tablespace 19 Read-mostly data is moved to a READONLY Tablespace Copyright © 2013, Oracle and/or its affiliates. All rights reserved. READONLY TBS As data cools down, Automatic Data Optimization automatically moves it to a READONLY TBS, it’s backed up only once after that

Summary Heat Map & Automatic Data Optimization  Heat Map – Automatically tracks access – Database-aware: maintenance jobs, backups, etc. don’t affect Heat Map  Automatic Data Optimization – Declarative easy-to-use syntax to define data compression & movement policies – Extensible with business-specific logic – Automates and simplifies ILM for Oracle Database data 20 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

21 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Oracle Advanced Compression with Oracle Database 12c

ORACLE ADVANCED COMPRESSION WITH ORACLE DATABASE 12C ... Automatic Data Optimization ... The Heat Map and Automatic Data Optimization features of ...
Read more

Heat Map, Automatic Data Optimization and ILM with Oracle ...

Heat Map, Automatic Data Optimization and ILM ... In Oracle Database 12c, Automatic Data ... Heat Map and Automatic Data Optimization provide total ...
Read more

Oracle 12c New Feature – Heat Map and Automatic Data ...

Oracle 12c Pluggable Database ... Heat Map and Automatic Data Optimization . ... Oracle 12c as a new feature called Heat Map which tracks and marks ...
Read more

Oracle 12c Heat Map and Automatic Data Optimization ...

Oracle 12c Optimizer enhancements; Oracle 12c RAC enhancements; Oracle 12c Maximum Availability Architecture enhancements; Oracle 12c Temporal Database
Read more

Oracle 12c Database Heat Maps and Automatic Data Optimization

Take advantage of Oracle 12c Database Heat Maps and Automatic Data Optimization. Automate the manual movement of less critical data onto lower storage tiers
Read more

Heat Map and Automatic Data Optimization — orabase.org

Heat Map and Automatic Data Optimization Heat Map and Automatic Data Optimization: Heat Map and ... Exploring Oracle 12c’s Automatic Database ...
Read more

Oracle 12c - Oracle FAQ

Oracle 12c is a version of the Oracle ... Automatic Data Optimization (ADO) with heat maps to ... Adaptive Query Optimization Database Migration ...
Read more

Oracle Database 12c: New Features for Administrators

Online Datafile Move and Automatic Data Optimization Data classification in 12c : tablespace, group, object, row levels Configure heat map Automatic ...
Read more