Oracle Database In-Memory Advisor (English)

Oracle Database In-Memory Advisor (English)

• 1.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Ileana Someşan Senior PreSales Consultant Core Technology Oracle Database In-Memory Advisor English version Ileana Someşan
• 2.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Oracle Database In-Memory Option Brief introduction Ileana Someşan 2
• 3.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Add-on to the Oracle Database – Available in release 12.1.0.2+ – On all hardware platforms certified for Oracle DB • New way of organizing and processing data in memory – Goal: real-time analytics Overview Ileana Someşan 3 In-Memory Option
• 4.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Extreme acceleration of data warehousing, analytics, reporting – Factor 100 • Faster OLTP – In mixed workload environments (operational + reporting) – Column Store replaces analytical indexes removing maintenance overhead • Easy to implement • Full control over the objects that are populated into memory – Only performance-critical data, not entire database • No application changes • Fully integrated with pre-existing Oracle DB technologies Ileana Someşan 4 In-Memory Option Benefits Advisor
• 5.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Dual-format data representation in memory – Row and column format – Transactionally consistent • OLTP uses traditional row format – Best at frequent inserts/updates/deletes • Analytic queries use new column format – Best at data scans/filtering/aggregation Ileana Someşan 5 Oracle Database with In-Memory Option Memory (SGA) Row Format Traditional Buffer Cache Disk Row Format SALES New In-Memory Column Store SALES Column Format
• 6.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Installed automatically with the DB, but not enabled • Set the size of the In-Memory Column Store • • Specify objects to be populated into the In-Memory Column Store • Oracle Optimizer automatically uses the In-Memory Column Store Ileana Someşan 6 How to enable & use alter system set inmemory_size = XXX G scope=spfile; shutdown immediate; startup; alter table XXX … inmemory; Advisor Advisor
• 7.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Oracle Database In-Memory Advisor Ileana Someşan 7
• 8.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Goal: – Estimate the benefit of In-Memory Option for a specific DB – Advise on optimal configuration of In-Memory Option • Easy to install and run • Can be run on Oracle DB 11.2.0.3+ – Recommendations can be implemented in Oracle DB 12.1.0.2+ Overview Ileana Someşan 8 In-Memory Advisor
• 9.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Identifies the analytic workload in your DB – From Automatic Workload Repository (AWR) and Active Session History (ASH) • Produces results based on... – Current database statistics – Assumptions and heuristic parameter settings, e.g. performance improvement by eliminating waits, compression/decompression costs • Advisor results are estimates – Does not use In-Memory Option Ileana Someşan 9 How it works
• 10.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • HTML Report – Recommendations: size of In-Memory Column Store, objects that should be placed In-Memory for maximum benefit, compression type – Expected performance improvement and time saving • SQL script – Quickly implement Advisor recommendations Ileana Someşan 10 Output
• 11.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Ileana Someşan 11
• 12.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Ileana Someşan 12
• 13.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • License for Diagnostics Pack and Tuning Pack • Oracle DB release 11.2.0.3+ • Traditional (Non CDB) architecture – 12c Multitenant environenment not yet supported • Analytic workload • Up to date database statistics Ileana Someşan 13 Prerequisits
• 14.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | 1. Download ZIP archive from My Oracle Support – Support Note 1965343.1 2. Unpack ZIP archive 3. Run provided installation script – Guides you through installation – Creates: DB-User IMADVISOR, Package DBMS_INMEMORY_ADVISOR, Oracle directory IMADVISOR_DIRECTORY Ileana Someşan 14 Installation $ sqlplus sys/<pw> as sysdba SQL> @instimadv
• 15.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Run provided SQL-Skript – Or write your own – Task Name – Begin time und duration of analyzed workload Ileana Someşan 15 Execution $ sqlplus sys/<pw> as sysdba SQL> @imadvisor_analyze_and_report
• 16.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | • Run Advisor on relevant DB workload – E.g. end of month with intense reporting activity • Advisor does not run in realtime – Investigates previous DB activity • Time frame (duration) of the analysis – 1 h oder longer, depending on your DB workload • Overhead for running the Advisor – Little, similar to other DB-Advisors – Can be eliminated by loading DB-Statistics into another database • SQL Performance Analyzer to validate recommendations Ileana Someşan 16 Hints for In-Memory Advisor
• 17.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Thanks! Questions? Ileana Someşan 17 ileana.somesan@oracle.com http://de.slideshare.net/somesan
• 18.
  Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Ileana Someşan 18