oracle database in-memory advisor (english)
TRANSCRIPT
![Page 1: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/1.jpg)
Copyright © 2015 Oracle 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
![Page 2: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/2.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Oracle Database In-Memory Option Brief introduction
Ileana Someşan 2
![Page 3: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/3.jpg)
Copyright © 2015 Oracle 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
![Page 4: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/4.jpg)
Copyright © 2015 Oracle 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
![Page 5: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/5.jpg)
Copyright © 2015 Oracle 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
![Page 6: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/6.jpg)
Copyright © 2015 Oracle 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
![Page 7: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/7.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Oracle Database In-Memory Advisor
Ileana Someşan 7
![Page 8: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/8.jpg)
Copyright © 2015 Oracle 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
![Page 9: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/9.jpg)
Copyright © 2015 Oracle 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
![Page 10: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/10.jpg)
Copyright © 2015 Oracle 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
![Page 11: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/11.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Ileana Someşan 11
![Page 12: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/12.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Ileana Someşan 12
![Page 13: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/13.jpg)
Copyright © 2015 Oracle 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
![Page 14: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/14.jpg)
Copyright © 2015 Oracle 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
![Page 15: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/15.jpg)
Copyright © 2015 Oracle 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
![Page 16: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/16.jpg)
Copyright © 2015 Oracle 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
![Page 17: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/17.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Thanks! Questions?
Ileana Someşan 17
http://de.slideshare.net/somesan
![Page 18: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/18.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Ileana Someşan 18
![Page 19: Oracle Database In-Memory Advisor (English)](https://reader035.vdocuments.us/reader035/viewer/2022062514/55a5e8fd1a28abb0128b471b/html5/thumbnails/19.jpg)