21 Metric Extensions are Cool! First-class metrics of your own design Easy-to-use user interface makes it a snap Lifecycle support and export/import important value-adds Monitor targets for application-specific issues OOB metrics are for the general case RunawaySQL ME has found many bugs Using EM to improve EM 24

25 Identify High Risk SQL with Growing Data Volumes 1 Run SPA to establish baseline in test of a given workload* 2 3 Modify statistics to simulate data scale-up factor increase Run SPA to identify high risk SQL Repeat incrementally on each scale-up 4 Periodically run high risk SQL in production to monitor performance * Workload captured into SQL Tuning Sets (STS) 28

26 1 SPA Baseline Trial in Test Setup test environment either full or subset of data Ensure table and index statistics are same as that of production Run SPA trials in explain plan mode for the workload 29

27 2 Simulate Data Growth using Statistics Backup existing table and index statistics Create table to hold original table and index statistics dbms_stats.create_stat_table() Export current table and index statistics to this table dbms_stats.export_table_stats() dbms_stats.export_index_stats() Simulate scale up of data volume Assuming column data is uniformly distributed: For all tables increase number of rows and blocks by a factor of 1.1 (10%) using dbms_stats.set_table_stats() For all indexes increase number of rows, number of leaf blocks, number of distinct keys and clustering factor by a factor of 1.1 (10%), using dbms_stats.set_index_stats() 30 Refer to slide notes section for example commands

28 3 Run SPA Trial Analysis Run SPA Trial with the scaled-up optimizer statistics to assess the impact of data volume growth Identify High Risk SQLs (SQL with plan changes) due to data volume growth 31

29 4 Periodically Test High Risk SQL in Production Subset high risk SQL (SQL with volatile execution plans) into a separate SQL Tuning Set Create a new empty SQL Tuning Set: dbms_sqltune.create_sqlset() Load high risk SQL into the new SQL Tuning set from the SPA task dbms_sqltune.load_sqlset() Using this STS, run SPA trials periodically with per execution time limits in production to monitor and tune volatile SQL Plans 32

30 4 Periodically Test High Risk SQL in Production Baseline STS on production once with SPA On a routine basis run SPA Analysis and compare to baseline to identify SQL regressions Trials Production Database Preempt performance degradation and fix proactively in rapidly growing data volume environments Volatile SQL 33

34 SPA with Growing Data Volume Summary SPA can be used to identify high risk SQL with minimal effort in environments with Large workloads and increasing data volumes Prevent unexpected performance degradation proactively Test high risk SQL in production and tune 37

36 Safe Harbor Statements The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle s products remains at the sole discretion of Oracle. 39

DBA Best Practices: A Primer on Managing Oracle Databases Leng Leng Tan Vice President, Systems and Applications Management The following is intended to outline our general product direction. It is intended

Real Application Testing Fred Louis Oracle Enterprise Architect The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated

Oracle University Contact Us: 1.800.529.0165 Oracle Database 12c: Performance Management and Tuning NEW Duration: 5 Days What you will learn In the Oracle Database 12c: Performance Management and Tuning

Management Packs for Database Diagnostics Pack for Database Oracle Diagnostics Pack for Database offers a complete, cost-effective, and easy to use solution for managing the performance of Oracle Database

Oracle University Contact Us: 1.800.529.0165 Oracle Database 12c: Performance Management and Tuning NEW Duration: 5 Days What you will learn In the Oracle Database 12c: Performance Management and Tuning

Real Application Testing Dave Foster Master Principal Sales Consultant The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated

Programa de Actualización Profesional ACTI Oracle Database 11g: SQL Tuning Workshop What you will learn This Oracle Database 11g SQL Tuning Workshop training is a DBA-centric course that teaches you how

Strategies for Monitoring Large Data Centers with Oracle Enterprise Manager Ana McCollum Consulting Product Manager The following is intended to outline our general product direction. It is intended for

Oracle Database 10g: New Features for Administrators Course ON10G 5 Day(s) 30:00 Hours Introduction This course introduces students to the new features in Oracle Database 10g Release 2 - the database for

DATA SHEET CA Database Performance CA Database Performance helps you proactively manage and alert on database performance across the IT infrastructure, regardless of whether the database is located within

Thank you for choosing to download a Toad for Oracle trial. This guide will enable you to evaluate Toad s key technical features and business value. It can be used to evaluate the database administration

ORACLE DATABASE 10G ENTERPRISE EDITION OVERVIEW Oracle Database 10g Enterprise Edition is ideal for enterprises that ENTERPRISE EDITION For enterprises of any size For databases up to 8 Exabytes in size.

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material,

Course 6231A: Maintaining a Microsoft SQL Server 2008 Database About this Course Elements of this syllabus are subject to change. This five-day instructor-led course provides students with the knowledge

ORACLE ENTERPRISE MANAGER CLOUD CONTROL TRANSFORM ENTERPRISE IT THROUGH TOTAL CLOUD CONTROL Create self-service IT, simplify and automate IT operations, and deliver enterprise clouds that provide maximum

WhatsUp Gold v11 Features Overview This guide provides an overview of the core functionality of WhatsUp Gold v11, and introduces interesting features and processes that help users maximize productivity

Response Time Analysis A Pragmatic Approach for Tuning and Optimizing Oracle Database Performance By Dean Richards Confio Software, a member of the SolarWinds family 4772 Walnut Street, Suite 100 Boulder,

ITIL A guide to event management Event management process information Why have event management? An event can be defined as any detectable or discernable occurrence that has significance for the management

Oracle Database 11g: New Features for Administrators DBA Release 2 Duration: 5 Days What you will learn This Oracle Database 11g: New Features for Administrators DBA Release 2 training explores new change

SQL Server Solutions GETTING STARTED WITH SQL Diagnostic Manager Purpose of this document Due to its depth and potential for customization, there are often features of SQL Diagnostic Manager that are overlooked

An Oracle White Paper February, 2015 Oracle Database In-Memory Advisor Best Practices Disclaimer The following is intended to outline our general product direction. It is intended for information purposes

Oracle 11g New Features - OCP Upgrade Exam This course gives you the opportunity to learn about and practice with the new change management features and other key enhancements in Oracle Database 11g Release

Proactive Performance Management for Enterprise Databases Abstract DBAs today need to do more than react to performance issues; they must be proactive in their database management activities. Proactive

White Paper The Ten Features Your Web Application Monitoring Software Must Have Executive Summary It s hard to find an important business application that doesn t have a web-based version available and

Monitoring Call Center Applications with Application Response Use ehealth Application Response to monitor and manage the performance of call center applications, such as desktop applications used by call

Oracle University Contact Us: 67 52 67 24 Oracle Database 12c: New Features for Administrators Duration: 5 Days What you will learn In the Oracle Database 12c: New Features for Administrators course, you