Oracle11g performance tuning-use SQLPlanManagement (2)

This document describes how to use SQL Plan Management to manage SQL Execution plans. SQL Plan management provides components for capturing, selecting, and evolving SQL Plan information to prevent performance degradation caused by sudden changes in the execution Plan of SQL statements.

See http://blog.csdn.net/davidwang9527/article/details/18620953 FOR THE FIRST PART

This article includes the following topics:

3. Use SQL Plan Baseline with SQL Tuning Advisor
4. Use Fixed SQL Plan Baseline

5. Displaying SQL Plan Baseline
6. SQL Management Base
7. Export and Import SQL Plan Baseline

3. Use SQL Plan Baseline with SQL Tuning Advisor

When SQL Tuning Advisor is used to optimize SQL statements, if the advisor finds a Tuning Plan and verifies that its performance is better than the Plan in the corresponding SQL Plan baseline, we recommend that you use an SQL Profile. When the SQL Profile is accepted, the database adds the Plan to the corresponding SQL Plan baseline. However, SQL Tuning Advisor does not verify the unaccepted plan in plan history.

In Oracle11g, an automatic configuration task runs SQL Tuning Advisor in the maintenance window. This task identifies high-load SQL statements by collecting execution performance data in the AWR snapshot. This automatic configuration task will implement the SQL profile recommendation of SQL Tuning Advisor. Therefore, the database automatically adds the optimization Plan of High-load SQL statements to SQL Plan baselines.

4. Use Fixed SQL Plan Baseline

If an SQL Plan baseline contains at least one attribute Fixed set to true and is enabled, the SQL plan baseline is fixed. You can use fixed SQL to Plan baseline as an SQL statement to fix a set of possible plans (usually a Plan ).

If a fixed SQL Plan baseline also contains non-fixed plans, the optimizer selects the fixed plan first instead of the non-fixed plan. Therefore, the optimizer uses the lowest cost plan in the fixed plan, even if a non-fixed plan has a lower cost.

The optimizer does not add a new plan to a fixed SQL plan baseline. Because the optimizer does not automatically add new plans, When you execute DBMS_SPM.EVOLVE_ SQL _PLAN_BASELINE, the database will not evolve a fixed SQL Plan baseline. however, you can manually load a new plan from the shared SQL area or SQL tuning set.

When you use SQL Tuning Advisor to adjust an SQL statement that uses fixed SQL plan baseline, this SQL profile is recommended to have special significance. When the SQL profile is accepted, the database adds the optimization plan to the fixed SQL plan baseline with the non-fixed plan. However, as mentioned above, the optimizer does not use the tuned plan when the fixed plan can be repeated at the time. Therefore, the benefits of SQL tuning cannot be realized. To use this tuned plan, you need to manually change the plan to a fixed plan (change the fixed attribute to YES ).

5.Displaying SQL Plan Baselines

To view the plan of the given statement stored in SQL plan baseline, use the DISPLAY_ SQL _PLAN_BASELINE FUNCTION OF THE DBMS_XPLAN package. The following example shows one active execution plan for a specified SQL statement:

SELECT * FROM TABLE(     DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(         sql_handle=>'SYS_SQL_209d10fabbedc741',         format=>'basic'));

In this example, you can use SQL _handle or plan name to display a single plan.

This function uses the scheduler information stored in SQL Management Base to explain and display the scheduler. In this example, the DISPLAY_ SQL _PLAN_BASELINE function displays the execution plan of the SQL statement specified by the handle SYS_ SQL _209d10fabbedc741:

SQL handle: SYS_SQL_209d10fabbedc741SQL text: select cust_last_name, amount_sold from customers c,          sales s where c.cust_id=s.cust_id and cust_year_of_birth=:yob--------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SYS_SQL_PLAN_bbedc741a57b5fc2Enabled: YES      Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE----------------------------------------------------------------------------------Plan hash value: 2776326082----------------------------------------------------------------------------------| Id  | Operation                                | Name                          |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT                         |                               ||   1 | HASH JOIN                                |                               ||   2 |   TABLE ACCESS BY INDEX ROWID            | CUSTOMERS                     ||   3 |     BITMAP CONVERSION TO ROWIDS          |                               ||   4 |     BITMAP INDEX SINGLE VALUE            | CUSTOMERS_YOB_BIX             ||   5 |    PARTITION RANGE ALL                   |                               ||   6 |    TABLE ACCESS FULL                     | SALES                         |--------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SYS_SQL_PLAN_bbedc741f554c408Enabled: YES     Fixed: NO      Accepted: YES       Origin: MANUAL-LOAD----------------------------------------------------------------------------------Plan hash value: 4115973128----------------------------------------------------------------------------------| Id  | Operation                                | Name                          |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT                         |                               ||   1 |   NESTED LOOPS                           |                               ||   2 |     NESTED LOOPS                         |                               ||   3 |       TABLE ACCESS BY INDEX ROWID        | CUSTOMERS                     ||   4 |         BITMAP CONVERSION TO ROWIDS      |                               ||   5 |           BITMAP INDEX SINGLE VALUE      | CUSTOMERS_YOB_BIX             ||   6 |       PARTITION RANGE                    |                               ||   7 |        BITMAP CONVERSION TO ROWIDS       |                               ||   8 |          BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX                ||   9 |     TABLE ACCESS BY LOCAL INDEX ROWID    | SALES                         |----------------------------------------------------------------------------------

You can use the select statement to query the DBA_ SQL _PLAN_BASELINE View:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM   DBA_SQL_PLAN_BASELINES; SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX------------------------------------------------------------------------SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741a57b5fc2  YES  NO     NOSYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741f554c408  YES  YES    NO

6. SQL Management Base

SQL Management Base (SMB) is part of the data dictionary that resident the SYSAUX tablespace. It stores statement log, plan history, SQL plan baselines, and SQL profile. SMB uses automatic space management (ASSM) to clean unused plans and logs every week ).

You can also manually add a group of SQL statement plans to SMB. This feature is especially useful when upgrading from a database earlier than Oracle Database 11 to oracle11g because it helps reduce performance degradation caused by the adoption of a new optimizer version.

Because SMB is completely in SYSAUX, if the tablespace is unavailable, the database does not use SQL Plan Management and SQL tuning functions.

6.1 disk space usage

The database regularly checks the disk space used for SMB. By default, SMB cannot exceed 10% of the SYSAUX size. The allowed range is between 1% and 50%.

Every week, background processes measure the total space occupied by SMB. When the limit is exceeded, you will want to write a warning to the alert log. This warning will be written until the following method is used:

Add SMB limit

Increase the size of the SYSAUX tablespace

The disk space used by SMB is reduced by clearing the SQL Management object (SQL plan baseline or SQL profile ).

To change the limit ratio, use the CONFIGURE of the DBMS_SPM package. The following example changes the space limit to 30%:

BEGIN  DBMS_SPM.CONFIGURE('space_budget_percent',30);END;/

• 6.2 Purging policy

  The disk space used by the weekly scheduled purge Task management SQL plan management will be purged 53 weeks ago. Week 53 ensures that the plan information is valid for one year. This rentiton period can be between 5 weeks and 523 weeks (more than 10 years.

  Use the CONFIGURE of the DBMS_SPM package to change rentiong period to week 105:

  BEGIN  DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);END;/

  6.3 SQL Management Base Configuration Paramters

  You can view the two parameters through DBA_ SQL _MANAGEMENT_CONFIG:

  SELECT PARAMETER_NAME, PARAMETER_VALUE FROM   DBA_SQL_MANAGEMENT_CONFIG; PARAMETER_NAME                 PARAMETER_VALUE------------------------------ ---------------SPACE_BUDGET_PERCENT                        30PLAN_RETENTION_WEEKS                       105

  7. Import and export SQL Plan baseline

  SQL Plan baseline is imported and exported using oracle data pump.

  1. In the original database, use dbms_spm.create_stgtab_baseline to create a stage table:

  BEGIN  DBMS_SPM.CREATE_STGTAB_BASELINE(    table_name => 'stage1');END;/

  2. Use PACK_STGTAB_BASELINE

  DECLARE
  My_plans number;
  BEGIN
  My_plans: = DBMS_SPM.PACK_STGTAB_BASELINE (
  Table_name => 'stage1 ',
  Enabled => 'yes ',
  Creator => 'dba1 ');
  END;
  /

  3. Use data pump to export the staging table to a flat file, transfer to the target system, and then use data pump import to import the table to the target database.

  4. Use the UNPACK_STGTAB_BASELINE function to unpackage data from the staging table to the SQL Plan baseline:

  DECLARE  my_plans number;BEGIN  my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(    table_name => 'stage1',    fixed => 'yes');END;/