Oracle 11g SQL Performance Analyzer(原创)

Overview The SQL Performance Analyzer
The SQL Performance Analyzer focuses on comparing the performance of a specific SQL workload before and after a major system change. The analyzer does this by building two versions of the SQL workload
performance, which includes both the SQL execution plans as well as their execution statistics. After analyzing SQL performance both before and after you make a major change, the SQL Performance Analyzer provides suggestions to prevent potential performance degradation of SQL statements. This is especially handy when you're planning an upgrade of your database to a newer release of the Oracle database. The
SQL Performance Analyzer, by enabling you to compare SQL performance on two systems running on different versions of the Oracle database, lets you know ahead of the upgrade which of the SQL statements may show a deterioration in performance. Thus, you can reengineer those statements prior to the actual upgrade.

Tips:The SQL Performance Analyzer executes SQL in a serial fashion and ignores concurrency.
You can use the SQL Performance Analyzer to predict performance changes resulting from the following system changes:

Database and application upgrades
Hardware upgrades
Operating system upgrades
Initialization parameter changes
SQL tuning actions such as the creation of SQL profiles
Statistics gathering
Schema changes

Using the DBMS_SQLPA package, you can build and compare two versions of workload performance ----one before the change and---- one after the change----and compare the differences between the two versions and easily trap the SQL statements that are adversely affected by the system change.
In addition to the new DBMS_SQLPA package, you can also use several procedures in the DBMS_SQLTUNE package to create the SQL Tuning Set that you need to capture the SQL workload and conduct a performance analysis.
You use a SQL Tuning Set (STS) to capture the SQL workload on the production system. The STS includes the SQL text, bind variables, as well as information relating to the execution environment, execution plans, and execution statistics of one or more SQL statements. You export the STS from the production system to the test system to provide the SQL workload input to the SQL Performance Analyzer.
Because the STS is a persistent database object, you can modify the STS as you want and even select data from the STS as you would from a table. You can use an STS's filtering capability to weed out any undesirable SQL.
You can use one of the following sources to load statements into an STS:

Automatic workload repository (AWR) snapshots
AWR baselines
A cursor cache
Another STS

You can either collect all the SQL statements at once or over a period of time. The SQL Performance Analyzer, which is primarily designed to predict the impact of major system changes on the SQL workload response time, does the following things when analyzing the SQL workload performance:

Builds different versions of SQL workload performance
Executes SQL serially without respecting concurrency characteristics
Analyzes performance differences including the response time of the before-and after-change SQL workloads
Uses the SQL Tuning Advisor to tune regressed SQL statements

Following is the workflow involved in using the SQL Performance Analyzer:
1. Capture the pre-change SQL workload performance. Use the SQL Performance Analyzer to capture the SQL workload in an STS that you create beforehand. You may also use the AWR instead to capture the top SQL statements in order to provide a complete SQL workload capture. You transport the SQL workload to a test system for analysis.
2. Analyze the pre-change SQL workload performance. The performance data includes execution plans and execution statistics for metrics such as elapsed time and disk reads.
3. Make the system changes. Once you capture the pre-change SQL workload from the production system, make the necessary changes on the test system. For example, if you want to test the impact of an upgrade to a new version of the database, install the new Oracle release software on the test system and upgrade a copy of the production database to the new release.
4. Capture the post-change SQL workload performance. Use the SQL Performance Analyzer to capture the workload, this time on the post-change test system.
5. Analyze the post-change SQL workload performance. Examine the execution plans and execution statistics for the same metrics you examined before you made the system change.
6. Compare and analyze the SQL performance. Use the SQL Performance Analyzer to compare the SQL performance in the production system and the post-change test system in order to identify changes in SQL execution plans. You can also compare statistics relating to user-specified metrics such as execution time, buffer gets, disk reads, and others. You can then calculate the impact of the change on both individual SQL statements and on the SQL workload as a whole. This enables you to foresee whether the change would lead to an improvement or regression in SQL performance or whether it would have no net impact on the SQL workload.

Tips:The SQL workload you must capture from the production system must be from a representative peak period.

Capturing the SQL Workload
Before running SQL Performance Analyzer, capture a set of SQL statements on the production system that represents the SQL workload which you intend to analyze.
The captured SQL statements should include the following information:

• SQL text
• Execution environment
• SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics
• Parsing schema under which a SQL statement can be compiled
• Compilation environment, including initialization parameters under which a SQL statement is executed
• Number of times a SQL statement was executed

Capturing a SQL workload has a negligible performance impact on your production system and should not affect throughput. A SQL workload that contains more SQL statements will better represent the state of the application or database. This will enable SQL Performance Analyzer to more accurately forecast the potential impact of system changes on the SQL workload. Therefore, you should capture as many SQL statements as possible. Ideally, you should capture all SQL statements that are either called by the application or are running on the database.
You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer. A SQL tuning set is a database object that includes one or more SQL statements, along with their execution statistics and execution context.  Capturing a SQL workload using a SQL tuning set enables you to:

• Store the SQL text and any necessary auxiliary information in a single, persistent database object
• Populate, update, delete, and select captured SQL statements in the SQL tuning set
• Load and merge content from various data sources, such as the Automatic Workload Repository (AWR) or the cursor cache
• Export the SQL tuning set from the system where the SQL workload is captured and import it into another system
• Reuse the SQL workload as an input source for other advisors, such as the SQL Tuning Advisor and the SQL Access Advisor

Setting Up the Test

The SQL performance analyzer requires SQL tuning sets, and SQL tuning sets are pointless unless they contain SQL, so the first task should be to issue some SQL statements. We are only trying to demonstrate the technology, so the example can be really simple. The following code creates a test user called SPA_TEST_USER.
CONN / AS SYSDBA
CREATE USER spa_test_user IDENTIFIED BY spa_test_user
  QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO spa_test_user;
Next, connect to the test user and create a test table called MY_OBJECTS using a query from the ALL_OBJECTS view.
CONN spa_test_user/spa_test_user
CREATE TABLE my_objects AS
      SELECT * FROM all_objects;
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);
This schema represents our "before" state. Still logged in as the test user, issue the following statements.
SELECT COUNT(*) FROM my_objects WHERE object_id <= 100;
SELECT object_name FROM my_objects WHERE object_id = 100;
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000;
SELECT object_name FROM my_objects WHERE object_id = 1000;
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000;
Notice, all statements make reference to the currently unindexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state.
The select statements are now in the shared pool, so we can start creating an SQL tuning set.
Creating SQL Tuning Sets using the DBMS_SQLTUNE Package
The DBMS_SQLTUNE package contains procedures and functions that allow us to create, manipulate and drop SQL tuning sets. The first step is to create an SQL tuning set called spa_test_sqlset using the CREATE_SQLSET procedure.
CONN / AS SYSDBA
EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');
Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "my_objects". The resulting cursor is loaded into the tuning set using the LOAD_SQLSET procedure.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',
                attribute_list => 'ALL')
            ) a;
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'spa_test_sqlset',
                           populate_cursor => l_cursor);
END;
/

The database uses an incremental capture method to populate the STS from the cursor cache over a period of time. During the populating of the STS, the database filters out any undesirable SQL.
If you need to export the captured SQL workload in the form of the STS to the test system so you can invoke the SQL Performance Analyzer there.You need to use following instruduction，if you don't need it,just ignore  this phase.
Transport the SQL Tuning Set 

Before you can transport the SQL tuning set, you must first create a staging table using the CREATE_STGTAB_SQLSET procedure, so you can use this table to export the STS that contains the production SQL workload to the test system. After you export the STS, you must import it into the test database.
SQL> exec dbms_sqltune.create_stgtab_sqlset (table_name => 'stagetab');

Tips:we can't create staging table in SYS schema.
The CREATE_SQLTAB_SQLSET procedure creates a staging table named STAGETAB. Export the production STS into the staging table STAGETAB that you just created, using the PACK_STGTAB_SQLSET procedure:
SQL> exec dbms_sqltune.pack_stgtab_sqlset (sqlset_name =>
     'test_sts',
     staging_table_name => 'stagetab');
Now you must expdp STAGETAB from the production system to the test system. Once you import the staging table by impdp, run the UNPACK_STGTAB_SQLSET procedure to import the STS into the test database where you'll replay the SQL workload.
SQL> exec dbms_sqltune.unpack_stgtab_sqlset (sqlset_name = '%',replace => true, staging_table_name => ('stagetab');
You're now ready to create your SQL Performance Analyzer task.

The DBA_SQLSET_STATEMENTS view allows us to see which statements have been associated with the tuning set.
SELECT sql_text
FROM   dba_sqlset_statements
WHERE  sqlset_name = 'spa_test_sqlset';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT object_name FROM my_objects WHERE object_id = 100
SELECT COUNT(*) FROM my_objects WHERE object_id <= 100
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000
SELECT object_name FROM my_objects WHERE object_id = 1000
5 rows selected.
Now we have an SQL tuning set, we can start using the SQL performance analyzer.The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance ananlyzer. The first step is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tuning set name and making a note of the resulting task name.
CONN / AS SYSDBA
VARIABLE v_task VARCHAR2(64);
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');
PL/SQL procedure successfully completed.
SQL> PRINT :v_task
V_TASK
--------------------------------------------------------------------------------
TASK_122
Next, use the EXECUTE_ANALYSIS_TASK procedure to execute the contents of the SQL tuning set against the current state of the database to gather information about the performance before any modifications are made. This analysis run is named before_change.
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

The execution_type parameter of the EXECUTE_ANALYSIS_TASK procedure can take one of the following three values:
TEST_EXECUTE：Executes all SQL statements in the captured SQL workload. The database only executes the query portion of the DML statements, in order to avoid adversely impacting user data or the database itself. The database generates both execution plans and execution statistics (for example, disk reads and buffer gets).
COMPARE_PERFORMANCE：Compares performance between two executions of the workload performance analysis.
EXPLAIN PLAN：Lets you generate SQL plans only, without actually executing them.

The EXECUTE_ANALYSIS_TASK procedure executes all DML statements but ignores any DDL statements to avoid unduly affecting the test data.
Now we have the "before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column. In a new SQL*Plus session create the index using the following statements.
CONN spa_test_user/spa_test_user
CREATE INDEX my_objects_index_01 ON my_objects(object_id);
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);
Now, we can return to our original session and test the performance after the database change. Once again use the EXECUTE_ANALYSIS_TASK procedure, naming the analysis task "after_change".
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/
Once the before and after analysis tasks are complete, we must run a comparison analysis task. The following code explicitly names the analysis tasks to compare using name-value pairs in the EXECUTION_PARAMS parameter. If this is ommited, the latest two analysis runs are compared.
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => :v_task,
    execution_type   => 'compare performance',
    execution_params => dbms_advisor.arglist(
                          'execution_name1',
                          'before_change',
                          'execution_name2',
                          'after_change')
    );
END;
/
With this final analysis run complete, we can check out the comparison report using the REPORT_ANALYSIS_TASK function. The function returns a CLOB containing the report in 'TEXT', 'XML' or 'HTML' format. Its usage is shown below.
Note. Oracle 11gR2 also includes an 'ACTIVE' format that looks more like the Enterprise Manager output.
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON
SPOOL /tmp/execute_comparison_report.htm
SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'HTML', 'ALL')
FROM   dual;
SPOOL OFF
An example of this file for each available type is shown below.
TEXT
HTML
XML
ACTIVE - Active HTML available in 11gR2 requires a download of Javascript libraries from an Oracle website, so must be used on a PC connected to the internet.
You can do the following during the compare and analysis phase:

Calculate the impact of the change on specific SQL statements.
Calculate the impact of the change on the SQL workload as a whole.
Assign weights to important SQL in the workload.
Detect performance regression and improvements.
Detect changes in the execution plans of the SQL statements.

Recommend the running of the SQL Tuning Advisor to tune regressed SQL statements. After using the advisor, you can create a new after-change version of the SQL workload performance to ensure that you have acceptable performance.

You can use the following views when working with the SQL Performance Analyzer:
DBA_ADVISOR_TASKS shows details about the analysis task.
DBA_ADVISOR_FINDINGS shows analysis findings, which are classified as performance regression, symptoms, informative messages, and errors.
DBA_ADVISOR_EXECUTIONS shows metadata information for task executions.
DBA_ADVISOR_SQLPLANS shows a list of SQL execution plans.
DBA_ADVISOR_SQLSTATS shows a list of SQL compilation and execution statistics.

 

参考至：《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

                     http://www.oracle-base.com/articles/11g/sql-performance-analyzer-11gr1.php
                     http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#CHEFGHHI
                     http://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_intro.htm#RATUG168

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]