oracle数据库新特性之spa.ppt

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 phasesUse SQL Performance Analyzer to ascertain performance gains following a database change,SQL Performance Analyzer:Overview,Targeted users:DBAs,QAs,application developersHelps predict the impact of system changes on SQL workload response timeBuilds different versions of SQL workload performance(that is,SQL execution plans and execution statistics)Executes SQL serially(concurrency not honored)Analyzes performance differencesOffers fine-grained performance analysis on individual SQLIs integrated with SQL Tuning Advisor to tune regressions,SQL Performance Analyzer:Use Cases,SQL Performance Analyzer is beneficial in the following use cases:Database upgradesImplementation of tuning recommendationsSchema changesStatistics gatheringDatabase parameter changesOS 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,Database Instance,Productiondatabase,Cursor cache,SQL Tuning Set(STS)is used to store SQL workload.Includes:SQL TextBind variablesExecution plansExecution statisticsIncremental capture is used to populate STS from cursor cache over a period of time.STSs filtering and ranking capabilities filter out undesirable SQL.,Incremental capture,Productiondatabase,Testdatabase,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”).,Database instance,Cursor cache,Database instance,Step 3:Build Before Change Performance Data,Before change,SQL performance version is the SQL workload performance baseline.SQL performance=execution plans+execution statisticsTest/execute SQL in STS:Produce execution plans and statistics.Execute SQL serially(no concurrency).Every SQL is executed at least twice.Skip DDL/DML effects.Explain plan SQL in STS generates only SQL plans.,Testdatabase,Database instance,Beforechanges,Test/execute,11.2 update,Step 4:Implement Planned Change andStep 5:Build After-Change Performance Data,Manually implement the planned change:Database upgradeImplementation of tuning recommendationsSchema changesStatistics gathering Database parameter changesOS and hardware changesReexecute SQL after change:Test/execute SQL in STS to generate SQL execution plans and statistics.Explain plan SQL in STS to generate SQL plans.,Testdatabase,Database instance,After changesimplemented,Afterchanges,Step 6:Compare and Analyze Performance andStep 7:Tune Regressed SQL,Rely on user-specified metric to compare SQL performance:ELAPSED_TIME,BUFFER_GETS,DISK_READS,.Calculate impact of change on individual SQLs and SQL workload:Overall impact on workload Net SQL impact on workloadUse SQL execution frequency to define a weight of importance.Detect improvements,regressions,and unchanged performance.Detect changes in execution plans.Recommend running SQL Tuning Advisor to tune regressed SQLs.Analysis results can be used to seed SQL Plan Management baselines.,Testdatabase,Database instance,Compareanalysis,Regression,Improvement,SQL Tuning Advisor,Quiz,Which of the following does SQL Performance Analyzer perform?Tunes regressionsProvides before-and-after execution statisticsExecutes SQL statements seriallyBuilds different versions of SQL workload performance,Accessing SQL Performance Analyzer,Use Enterprise ManagerUse the DBMS_SQLPA package,Using Enterprise Manager to Access SQL Performance Analyzer,Access SQL Performance Analyzer on the Software and Support tab.Select one of the five types of workflows:Upgrade from 9i or 10.1Upgrade from 10.2 or 11gParameter ChangeExadata SimulationGuided WorkflowTune regressing statements by invoking SQL Tuning Advisor.Prevent regressions by using SQL plan baselines.,SQL Performance Analyzer:PL/SQL Example,exec:tname:=dbms_sqlpa.create_analysis_task(-sqlset_name=MYSTS,task_name=MYSPA);,exec dbms_sqlpa.execute_analysis_task(task_name=:tname,-execution_type=TEST EXECUTE,execution_name=before);,SELECT dbms_sqlpa.report_analysis_task(task_name=:tname,type=text,section=summary)FROM dual;,Create the tuning task:Execute the task to build the before-change performance data:Produce the before-change report:,After making your changes:Create the after-change performance data:Generate the after-change report:Compare the task executions:Generate the analysis report:,SQL Performance Analyzer:PL/SQL Example,EXEC dbms_sqlpa.execute_analysis_task(task_name=:tname,-execution_type=TEST EXECUTE,execution_name=after);,SELECT dbms_sqlpa.report_analysis_task(task_name=:tname,type=text,section=summary)FROM dual;,EXEC dbms_sqlpa.execute_analysis_task(task_name=:tname,execution_type=COMPARE PERFORMANCE);,SELECT dbms_sqlpa.report_analysis_task(task_name=:tname,type=text,section=summary)FROM dual;,Tuning Regressed SQL Statements,BEGINDBMS_SQLTUNE.CREATE_TUNING_TASK(spa_task_name=MYSPA,spa_compare_exec=MYCOMPEXEC);END;/,To tune regressed SQL statements reported by SQL Performance Analyzer,create a SQL tuning task for the SQL Performance Analyzer execution by using the DBMS_SQLTUNE.CREATE_TUNING_TASK function:,11.2,Testing Database Upgrades:Oracle9i Database and Oracle Database 10g Release 1,SQL Performance Analyzer supports testing database upgrades of Oracle9i and Oracle Database 10g Release 1,to Oracle Database 10g Release 2 and later releases.Execute the SQL tuning set on the upgraded database remotely over a database link.The production system which you are upgrading from should be running Oracle9i or Oracle Database 10g Release 1.The test system which you are upgrading to should be running Oracle Database 10g Release 2(10.2.0.2)or a newer release.Set up a separate system for SQL Performance Analyzer:Oracle Database 11g Release 1(11.1.0.7)or a later release.,Testing Database Upgrades:Oracle9i Database and Oracle Database 10g Release 1,To use SQL Performance Analyzer in a database upgrade from Oracle9i or Oracle Database 10g Release 1 to a newer release,perform the following steps:1.Enable the SQL Trace facility on the production system.2.On the production system,create a mapping table.3.Move the SQL trace files and the mapping table from the production system to the SQL Performance Analyzer system.4.On the SQL Performance Analyzer system,construct a SQL tuning set by using the SQL trace files.,Testing Database Upgrades:Oracle9i Database and Oracle Database 10g Release 1,5.On the SQL Performance Analyzer system:Use SQL Performance Analyzer to create a SQL Performance Analyzer task and convert the contents in the SQL tuning set into a preupgrade SQL trial that will be used as a baseline for comparison.Remotely test execute the SQL statements on the test system over a database link to build a postupgrade SQL trial.6.Compare SQL performance and fix regressed SQL statements.,Testing Database Upgrades:Oracle Database 10g Release 2 and Later Releases,SQL Performance Analyzer supports testing database upgrades of Oracle Database 10g Release 2 or a later release to any later release.Capture a SQL tuning set on the production system,then execute it twice remotely over a database link on a test system.The production system which you are upgrading from should be running Oracle Database 10g Release 2 or a later release.The test system which you are upgrading to initially should be running the same release as the production system.Set up a separate system for running SQL Performance Analyzer:Oracle Database 11g Release 1(11.1.0.7)or a later release.,Testing Database Upgrades:Oracle Database 10g Release 2 and Later Releases,Perform the following steps to use SQL Performance Analyzer in a database upgrade from Oracle Database 10g Release 2 and later releases to a newer release:1.On the production system,capture the SQL workload that you intend to analyze and store it in a SQL tuning set.2.Set up the test system so that it matches the production environment as closely as possible.3.Transport the SQL tuning set to the SQL Performance Analyzer system.4.On the SQL Performance Analyzer system,create a SQL Performance Analyzer task using the SQL tuning set as its input source.,Testing Database Upgrades:Oracle Database 10g Release 2 and Later Releases,5.Upgrade the test system.6.Remotely test execute the SQL statements a second time on the upgraded test system over a database link to build a postupgrade SQL trial.7.Compare SQL performance and fix regressed SQL statements.,SQL Performance Analyzer:Data Dictionary Views,Modified views in Oracle Database 11g:DBAUSER_ADVISOR_TASKS:Displays details about the analysis taskDBAUSER_ADVISOR_FINDINGS:Displays analysis findingsNew views in Oracle Database 11g:DBAUSER_ADVISOR_EXECUTIONS:Lists metadata information for task executionDBAUSER_ADVISOR_SQLPLANS:Displays the list of SQL execution plansDBAUSER_ADVISOR_SQLSTATS:Displays the list of SQL compilation and execution statistics,Summary,In this lesson,you should have learned how to:Identify the benefits of using SQL Performance Analyzer Describe the SQL Performance Analyzer workflow phasesUse SQL Performance Analyzer to determine performance gains following a database change,Practice 9:Overview,This practice covers the following topics:Capturing SQL Tuning SetsMigrating SQL Tuning Sets from Oracle Database 10g to Oracle Database 11gUsing SQL Performance Analyzer in an upgrade scenario,