DBA - PowerPoint PPT Presentation

About This Presentation
Title:

DBA

Description:

This presentation contains information proprietary to Oracle Corporation. DBA s New Best Friend: Oracle Database 10g and 11g SQL Performance Analyzer – PowerPoint PPT presentation

Number of Views:368
Avg rating:3.0/5.0
Slides: 45
Provided by: leviperei
Category:
Tags: dba | oracle

less

Transcript and Presenter's Notes

Title: DBA


1
DBAs New Best Friend Oracle Database 10g and
11g SQL Performance Analyzer
  • Kurt Engeleiter
  • Product Manager
  • Database Manageability

2
Outline
  • SQL Performance Analyzer (SPA) Introduction
  • Recommended Testing Methodology
  • Usage Scenarios
  • Evaluating Changes on Production System
  • 10.2 ? 11g DB Upgrade
  • SPA Enhancements in Oracle Database 11g Release 2
  • SPA Exadata Simulation
  • Conclusion

3
SPA Motivation
  • Businesses need to adapt to changes to stay
    competitive, compliant and evolve
  • DB upgrades, schema, optimizer statistics refresh
  • SQL performance regressions 1 cause of poor
    system perf.
  • Current testing landscape and limitations
  • Expensive capture, partial workload,
    non-production optimizer context, binds
  • Large workloads (100Ks SQL stmts are common)
  • Manual and time consuming testing and regression
    tuning
  • No end-to-end testing solution
  • 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

4
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
  • 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

SQL Workload STS
SQL plans stats
SQL plans stats
Pre-change Trial
Post-change Trial
Compare SQL Performance
Analysis Report
5
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
  • Execute SQL Locally or Remotely
  • Test execute statements in actual environment
  • For remote execution, database link needs to be
    specified
  • 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
  • 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

6
SPA Common Usage Scenarios
  • Database upgrades and patch-set releases
  • 9.2/10.1 ?10.2 or 11g releases
  • 10.2.0.x ?10.2.0.y or 11g releases
  • Optimizer statistics refresh
  • Database parameter changes
  • Database schema changes (e.g., add/drop indexes)
  • Implementation of tuning recommendations
  • I/O subsystem changes (e.g., ASM, Exadata)
  • SPA can be used for
  • any change that affects SQL execution plan
    performance
  • in production as well as test environments

Information for use cases on OTN/ML Note 560977.1
7
SPA Enterprise Manager Interface
  • Rich GUI through Enterprise Manager New
    workflows added!
  • DBMS_SQLPA package PL/SQL API

New Workflows !
8
Recommended Testing Methodology
9
Real Application Testing Recommended Methodology
1. USE SPA FOR SQL RESPONSE TIME / UNIT TESTING
No
DONE?
Yes
2. USE DB REPLAY FOR LOAD / CONCURRENCY TESTING
DONE?
No
Yes
DEPLOY CHANGE TUNING
10
Recommended Testing Methodology with SPA
1. CAPTURE REPRESENTATIVE SQL WORKLOAD TO STS
  • Use Production or identical Test System
  • Test one change at a time to understand causality

2. ESTABLISH BASELINE (SQL TRIAL-1) WITHOUT CHANGE
  • Reports show deviations from production

3. MAKE CHANGE, ESTABLISH SQL TRIAL-N
  • Compare Baseline to Trial-N in the same
    environment
  • For incremental tuning Compare Trial N-1 to
    Trial-N

4. COMPARE TRIALS REVIEW SPA REPORT, REMEDIATE
5. Deploy Change Tuning to PROD OR further
testing thru DB Replay
No
Yes
DONE?
11
Usage Scenario Evaluating Changes On Production
12
Using SPA For Changes in Production Example
Prod
Add indexes
And so on
Parameter change was bad in this case
1. Fix Regression thru SPM
Index Unusable
Parameter Change
New Stats Change
Validate Tuning
Partitioning
Fix Regression SQL Profile
SQL Profile
  • Bubble following the arrow indicates the delta
    change on Production
  • SPA is used for testing every change

13
How to Minimize Impact on Production?
  • Generate Plan Vs Test Execute
  • Use Generate Plan Trial Method to subset SQL with
    plan changes
  • Only test execute SQL with plan changes
  • Limit testing scope to private session or schema
    where possible
  • Use alter session set ltparametergt ltvaluegt
    (Vs system)
  • Example usage for SQL Profiles
  • alter session set sqltune_category TEST
  • Implement SQL Profiles and test Only sessions
    with TEST sqltune_category see these Profiles
    - private scope!!
  • alter session set sqltune_category DEFAULT
    -- Now SQL Profiles visible globally to all
    sessions
  • Similarly for Invisible Indexes, Pending Stats
  • Use SPA time limit to control resource usage
  • Test during maintenance window or non-peak
    activity when spare resources are available

14
Using SPA on Production Evaluating Optimizer
Statistics Refresh and other changes
  • Scenario
  • Can I use SPA to check if any SQL statements
    regressed due to optimizer statistics refresh on
    my 10.2/11g production databases? If so, how?
  • Goal
  • Assess impact of optimizer statistics gathering
    on SQL workload performance on production system
    using SPA make sure are no negative effects of
    the change

15
Evaluating Optimizer Statistics Refresh
  • Assumptions
  • Statistics refreshed periodically through custom
    jobs
  • No test system available, so evaluation is done
    on production
  • Should impact end-users minimally
  • Test using optimizer pending statistics feature
  • Use SPA remote trials capability (generate plan
    and test execute) to evaluate statistics refresh
    on 10.2/11g production database
  • Analyze SPA report and take appropriate action
  • Overall improvement but few SQL regressions
  • Solution Use SQL Profiles or Plan Management
  • No improvement and many regressions
  • Solution Revert to old statistics Use optimizer
    statistics retention/history feature.
    Alternatively, configure optimizer statistics
    appropriately
  • For Oracle Database 11g, publish pending
    statistics after evaluation of statistics

OBE Tutorial on OTN Gathering and Publishing
Stats Independently
16
Using SPA on Production Evaluating Optimizer
Statistics Refresh
Production Database (10.2/11g)
No Test Database Used!!
11g SPA System
Prod Before Stats
  • No app schema/data necessary
  • Not Mandatory for 11g db
  • Repository for many tests!

Remotely Build SQL Trials
Evaluate Opt Statistics Refresh
Prod Stats Refreshed
  1. Capture SQL workload to STS

2. Import STS
3. Gather Pending Stats
  1. Use SPA to detect performance changes

5. Publish Pending Stats and Remediate Regressions
17
Evaluating Optimizer Statistics Refresh
  • Tips!
  • On production, first use SPA to generate
    execution plans only, then test-execute SQLs with
    changed plans
  • If youre creating remote trials, you will need a
    logon trigger to set optimizer_use_pending_statist
    ics TRUE private to the second trial (SPA
    establishes the connection itself, over db link,
    so you cannot run alter session yourself)
  • To test-execute only those SQLs with changed
    plans, subset your STS after doing trial 2, then
    do a new SPA experiment on that STS
  • Script to subset STS is on Notes slide

18
Usage Scenario End-to-End Case Study for 10.2 ?
11g Upgrade
19
10.2 ?11g DB Upgrade Using Enterprise Manager
Grid Control (EMGC) 10.2.0.5
  • Scenario
  • I want to upgrade from 10.2 to 11g database
    release to benefit from 11g functionality. How
    can I best accomplish the upgrade?
  • Goal
  • Assess impact of upgrade on SQL workload
    performance using SPA so that there are no
    surprises after upgrade. Once migrated to 11g new
    features can be enabled one at a time. Use EMGC
    10.2.0.5 for this purpose

20
10.2 ? 11g DB Upgrade
Production Database
Test Database
Test DB (11g)
Upgrade
Upgrade
Test DB (10.2)
  1. Capture SQL workload to STS

11g SPA System
5. Deploy Tuning and Change to Prod
21
10.2 ? 11g DB Upgrade (1) Capture Workload to STS
  • Steps 1 Capture workload into STS through
    Incremental Capture Workload into STS Preferred
    method
  • Other sources for STS also possible Top SQL in
    AWR / AWR Baseline

22
10.2 ? 11g DB Upgrade (1) Capture Workload to STS
23
10.2 ? 11g Upgrade
  1. Capture SQL workload to STS

2. Transport STS
3. Establish 10.2 and 11g Trials
4. Compare performance and Generate SPA report
5. Deploy Tuning and Change to Prod
24
10.2 ? 11g DB Upgrade (2) Transport STS
  • Step 2
  • Copy STS to SPA system
  • Setup Test DB (Copy of Prod) 10.2
  • Upgrade Test DB from 10.2 to 11g

25
10.2 ? 11g DB Upgrade
  1. Capture SQL workload to STS

2. Transport STS
3. Establish 10.2 and 11g Trials
4. Compare performance and Generate SPA report
5. Deploy Tuning and Change to Prod
26
10.2 ? 11g DB Upgrade (3) Establish 10.2 and
11g Trials
27
10.2 ? 11g Upgrade
  1. Capture SQL workload to STS

2. Transport STS
3. Establish 10.2 and 11g Trials
4. Compare Performance, Generate SPA Report
5. Tune Regressions, Deploy Tuning and Change to
Prod
28
10.2 ? 11g DB Upgrade (4) Compare Performance
and Generate Report
3
Note tuning/regression fix is done on remote test
system that is being upgraded
2
5
1
4
29
SPA Report Regressed SQL Statements
30
10.2 ?11g Upgrade
  1. Capture SQL workload to STS

2. Transport STS
3. Establish 10.2 and 11g Trials
4. Compare performance and Generate SPA report
5. Tune Regressions, Deploy Tuning and Change to
Prod
31
10.2 ? 11g DB Upgrade (5) Regression Remediation
  • If SPA system has application data/schema, tuning
    can be done locally on the same DB
  • If a separate SPA system is used, manually
    subset SQL into a separate STS and then use EM to
    transport it to remote DB
  • Create SQL Plan Baselines Helps revert to
    previously known, stable plans
  • SQL Tuning Advisor Helps explore better
    execution plans
  • All regressed SQL are targeted automatically for
    remediation

Code snippet in Real Application Testing Users
Guide (11.2), Chapter 11 Page 23
32
10.2 ? 11g DB Upgrade (5) Regression Remediation
33
10.2 ? 11g DB Upgrade(5) Deploy Tuning and
Change in Production
34
10.2 ? 11g Upgrade
  1. Capture SQL workload to STS

2. Transport STS
3. Establish 10.2 and 11g Trials
4. Compare performance and Generate SPA report
5. Tune Regressions, Deploy Tuning and Change to
Prod
35
Demo
SPA
36
SPA Enhancements in Oracle Database 11g Release 2
37
SPA Enhancements Oracle Database 11g Release 2
  • Multiple test executes for better trial accuracy
    first execution ignored, averaged stats
  • Alternate Plans New recommendation added for
    regression remediation
  • If a better execution plan was noticed in AWR or
    baseline SQL Trial then a SQL Plan Baseline can
    be created from it
  • Active Reports
  • New interactive, offline reports based on Adobe
    Flash Player UI
  • Customers can share reports internally or with
    Oracle Support
  • Save, email, View Reports
  • Support can diagnose problems more efficiently
  • Get EM UI without installing EM useful for
    packaged apps and internal development teams
  • SPA Active Report Example

38
SPA Enhancements Oracle Database 11g Release 2
Using SPA With Other Testing Tools Build
Trials from STS
  • Allows comparing two different STS and generate
    SPA report
  • Eliminates re-executing SQL for trial data
  • Performance statistics picked up from the STS
  • Multiple plans even those with different
    binds/env picked up for comparison
  • Use cases Perform SQL-centric analysis
  • Database Replay
  • SQL workload capture into STS-1 during Database
    Replay - Workload Capture
  • STS-2 during Database Replay Workload Replay
  • Use SPA to build trials from STS-1 and STS-2 and
    generate report
  • Similarly with Oracle Application Testing Suite
    or home-grown scripts
  • Reporting SPA Report Additional information
  • Common SQL, Missing/New SQL, Plan Changes
  • Overall Workload and Individual SQL Statistics
  • Available in 11.2 database release, API only

39
SPA Exadata Simulation Estimating Exadata Server
Performance Gains
40
Predicting performance gains with SPA
  • Existing SPA and Database Replay functionality
    can be used to compare non-Exadata to Exadata
    configurations
  • SPA Unit test to find SQL response time gains
  • DB Replay Stress test with concurrency to find
    throughput gains
  • Measure improvements for your workload
  • This approach requires you to provision Exadata
    HW
  • Oracle Database Release 11.1.0.7 Adds SPA
    functionality to simulate Exadata on existing
    hardware to predict gains
  • Oracle Database Release 11.2 Adds SPA GUI
    support
  • Simulation measures reduction in I/O interconnect
    usage that will in turn, reduce IO and CPU
    consumed on the DB server

41
SPA Exadata SimulationWorkflow
  • SPA Exadata Simulation builds two SQL Trials
  • Trial 1 Current configuration with No Exadata
  • Trial 2 Current configuration with Exadata
    simulation
  • Comparison metric I/O Interconnect Bytes
  • Trial 1 value I/O Interconnect (disk) bytes
  • Trial 2 value is reduced depending on SQL
    operation eligibility (for full table scans,
    index full scans, etc.)
  • Plans will not change between trials

42
SPA Exadata Simulation Workflow
43
SPA Exadata Simulation Report
44
SPA Report SQL Details
45
Conclusion
  • SPA provides comprehensive and easy-to-use
    solution for SQL workload testing
  • SPA can be used for many changes both test and
    production environments
  • Helps adopt technology faster by significantly
    cutting down testing costs and production
    deployment risk
  • With SPA and Real Application Testing businesses
    can be stay competitive and improve profitability

46
Important Resources
  • Oracle Database Manageability
  • http//www.oracle.com/technology/products/manageab
    ility/database/index.html
  • Oracle Database 2-Day DBA Guide
  • http//download.oracle.com/docs/cd/E11882_01/serve
    r.112/e10897/toc.htm
  • Oracle Real Application Testing Users Guide
  • http//download.oracle.com/docs/cd/E11882_01/serve
    r.112/e12254/toc.htm
  • SPA Active Reports
  • http//www.oracle.com/technology/products/manageab
    ility/database/sqlpa.html

Write a Comment
User Comments (0)
About PowerShow.com