DBA’s New Best Friend: Oracle Database 10g and 11g SQL ...

DBA¡¯s New Best Friend: Oracle Database 10g and 11g SQL

Performance Analyzer

Prabhaker Gongloor (GP)

Khaled Yagoub

Pete Belknap

Database Manageability Group

Outline

? SQL Performance Analyzer (SPA) ¨C Introduction

? Recommended Testing Methodology

? Usage Scenarios

¨C Evaluating Changes on Production System

¨C 10.2
11g DB Upgrade

? SPA Exadata Simulation

? SPA Enhancements in Oracle Database 11g Release 2

? Conclusion

* Please visit us at the OOW Demogrounds Moscone

West ¨C D61/62

SPA Motivation

? Businesses need to adapt to changes to stay competitive, compliant

and evolve

¨C DB upgrades, schema, optimizer statistics refresh

¨C SQL performance regressions: #1 cause of poor system perf.

? Current testing landscape and limitations

¨C Expensive capture, partial workload, non-production optimizer context,

binds

¨C Large workloads (100Ks SQL stmts are common)

¨C Manual and time consuming testing and regression tuning

¨C No end-to-end testing solution

¨C Test In Production is not too uncommon

SQL Performance Analyzer (SPA)

 Proactively detects ALL SQL regressions, BEFORE actual change is deployed

 Integrated comprehensive solution for end-to-end SQL workload testing

SPA Overview

?

?

Helps users predict the impact of

system changes on SQL workload

response time

Low overhead capture of SQL

workload to SQL Tuning Set (STS)

on production system

SQL Workload

STS

SQL plans + stats

Pre-change Trial

?

Build different SQL trials

(experiments) of SQL statements

performance by test execution

?

Analyzes performance differences

?

Offers fine-grained performance

analysis on individual SQL

?

Integrated with STS, SQL Plan

Baselines, & SQL Tuning Advisor to

form an end-to-end solution

Compare

SQL Performance

Analysis Report

SQL plans + stats

Post-change Trial

SQL Trials

? SQL Trials capture execution performance (plans and statistics) of the

STS under a given environment

? SPA Trials handle the SELECTS and query part of DML, DDL is skipped

? There are 3 methods to build SQL Trials:

¨C Execute SQL Locally or Remotely

? Test execute statements in actual environment

? For remote execution, database link needs to be specified

¨C Generate Plans Locally or Remotely

? Generated execution plans have bind visibility, so better than vanilla

¡°explain¡±

? Quick way to check if wide-spread changes to SQL plans

¨C Build from STS

? Convert STS to SQL Trial

? Use for SQL centric analysis with DB Replay or other testing tools

? Use for 9i/10.1 upgrade to higher releases

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download