DBA’s New Best Friend: Oracle Database 10g and 11g SQL ...
[Pages:47]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) ? Introduction ? Recommended Testing Methodology ? Usage Scenarios
? Evaluating Changes on Production System ? 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 ? D61/62
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
SPA Overview
? Helps users predict the impact of system changes on SQL workload response time
SQL Workload STS
? 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
SQL plans + stats Pre-change Trial
Compare SQL Performance
SQL plans + stats Post-change Trial
? Offers fine-grained performance analysis on individual SQL
Analysis Report
? Integrated with STS, SQL Plan Baselines, & SQL Tuning Advisor to form an end-to-end solution
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
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
SPA: Enterprise Manager Interface
? Rich GUI through Enterprise Manager ? New workflows added! ? DBMS_SQLPA package PL/SQL API
New Workflows !
Recommended Testing Methodology
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- these instructions apply to learners who have installed an
- oracle database sql language quick reference
- security concepts in oracle multitenant
- oracle database express edition
- mini user s guide for sql plus t j teorey table of
- dba s new best friend oracle database 10g and 11g sql
- unplugging plugging and upgrading a pdb to a new cdb
- oracle multitenant 19c all about pluggable databases
Related searches
- oracle database examples download
- oracle database 11g download free
- oracle database 11g sql pdf
- sample oracle database download
- testing oracle database examples
- oracle database xe
- oracle database 10g express edition download
- oracle database online practice
- oracle database 11g xe express download
- download oracle database 11
- oracle database 10g download free
- best friend quotes and sayings