oracle数据库新特性之spa.pptVIP

SQL Performance Analyzer Objectives After completing this lesson, you should be able to: Identify the benefits of using SQL Performance Analyzer Describe the SQL Performance Analyzer workflow phases Use SQL Performance Analyzer to ascertain performance gains following a database change SQL Performance Analyzer: Overview Targeted users: DBAs, QAs, application developers Helps predict the impact of system changes on SQL workload response time Builds different versions of SQL workload performance (that is, SQL execution plans and execution statistics) Executes SQL serially (concurrency not honored) Analyzes performance differences Offers fine-grained performance analysis on individual SQL Is integrated with SQL Tuning Advisor to tune regressions SQL Performance Analyzer: Use Cases SQL Performance Analyzer is beneficial in the following use cases: Database upgrades Implementation of tuning recommendations Schema changes Statistics gathering Database parameter changes OS and hardware changes Using SQL Performance Analyzer Capture SQL workload on production. Transport the SQL workload to a test system. Build “before-change” performance data. Make changes. Build “after-change” performance data. Compare results from steps 3 and 5. Tune regressed SQL. Step 1: Capture SQL Workload SQL Tuning Set (STS) is used to store SQL workload. Includes: SQL Text Bind variables Execution plans Execution statistics Incremental capture is used to populate STS from cursor cache over a period of time. STS’s filtering and ranking capabilities filter out undesirable SQL. Step 2: Transport to a Test System Copy SQL Tuning Set to staging table (“pack”). Transport staging table to test system (data pump, DB link, etc). Copy SQL Tuning Set from staging table (“unpack”). Step 3: Build Before Change Performance Data Before change, SQL performance version is the SQL workload performance baseline. SQL performance = execution plans + execution statistics Test/execute SQL in STS: Produce execution pl