当前位置: 首页 > 调优 > 正文

SPM中加载提示执行计划

测试平台:11.2.0.3

 

1,查看参数

这个测试一定得确定参数optimizer_use_sql_plan_baselines得为true才行,不过默认就是true

 

欢迎大家加入ORACLE超级群:17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

 

SQL> @parameter

SQL> set echo off

 

+————————————————————————+

| display one parameter value                                            |

+————————————————————————+

 

Enter Search Parameter Value (i.e. session) : optimizer_use_sql_plan_baselines

 

Inst                                                          Display              Is         Session    System     Instance

  Id NAME                                     TYPE            Value                Default    Modify     Modify     Modify     Description

—- —————————————- ————— ——————– ———- ———- ———- ———- ————————————————–

   1 optimizer_use_sql_plan_baselines         Boolean         FALSE                TRUE       TRUE       IMMEDIATE  TRUE       use of SQL plan baselines for captured sql stateme

SQL> alter system set optimizer_use_sql_plan_baselines=true;

 

System altered.

 

2,执行测试SQL语句

执行原始测试SQL

SQL> select count(*) from scott.test t where owner=(‘SCOTT’);

 

  COUNT(*)

———-

         7

 

利用原SQL语句,创建plan_baseline

 

SQL> @sql_spm_create_plan.sql

SQL> variable rst number

SQL> exec :rst := dbms_spm.load_plans_from_cursor_cache(sql_id => ‘&sqlid’, plan_hash_value => ‘&plan_hash_value’)

Enter value for sqlid: 3babcaq4kbqhf

Enter value for plan_hash_value: 395258997

 

PL/SQL procedure successfully completed.

 

SQL> undefine sqlid;

SQL> undefine plan_hash_value;

执行修改后的SQL语句

SQL> select /*+ full(t)*/count(*) from scott.test t where owner=(‘SCOTT’);

 

  COUNT(*)

———-

         7

 

SQL> @sql_spm.sql

SQL> set echo off

 

                                                                             ENABLE:ACCTPE

                                                                             FIXED                         PARSING         CREATED

SQL_HANDLE                     PLAN_NAME                      ORIGIN         AUTOPURGE                COST SCHEMA          MODIFIED          SQL_TEXT

—————————— —————————— ————– ——————– ——– ————— —————– ————————————————–

SQL_03c25f2b4e20a1bc           SQL_PLAN_07hkz5d7218dwf835f4ea MANUAL-LOAD    YES.YES.NO.YES              8 SYS             11-23 07.11-23 07 select count(*) from scott.test t where owner=(‘SC

                                                                                                                                             OTT’)

 

SQL_2f030a70eeeec40f           OUTLINE_TEST                   STORED-OUTLINE YES.YES.NO.NO               8 SYS             11-23 07.11-23 07 select count(*) from scott.test where owner=’SCOTT

                                                                                                                                            

 

利用修改后的sql语句执行plan baseline

 

SQL> @sql_spm_create_handle.sql

SQL> set echo off

Enter value for sqlid: 8v5r0xmh28spj

Enter value for plan_hash_value: 1950795681

Enter value for sql_handle: SQL_03c25f2b4e20a1bc(这里千万要记住是原始语句的SQL_HANDLE)

 

PL/SQL procedure successfully completed.

 

SQL> @sql_spm.sql

SQL> set echo off

 

                                                                             ENABLE:ACCTPE

                                                                             FIXED                         PARSING         CREATED

SQL_HANDLE                     PLAN_NAME                      ORIGIN         AUTOPURGE                COST SCHEMA          MODIFIED          SQL_TEXT

—————————— —————————— ————– ——————– ——– ————— —————– ————————————————–

SQL_03c25f2b4e20a1bc           SQL_PLAN_07hkz5d7218dw6b581ab9 MANUAL-LOAD    YES.YES.YES.YES           297 SYS             11-23 07.11-23 07 select count(*) from scott.test t where owner=(‘SC

                                                                                                                                             OTT’)

 

SQL_03c25f2b4e20a1bc           SQL_PLAN_07hkz5d7218dwf835f4ea MANUAL-LOAD    YES.YES.NO.YES              8 SYS             11-23 07.11-23 07 select count(*) from scott.test t where owner=(‘SC

                                                                                                                                             OTT’)

 

SQL_2f030a70eeeec40f           OUTLINE_TEST                   STORED-OUTLINE YES.YES.NO.NO               8 SYS             11-23 07.11-23 07 select count(*) from scott.test where owner=’SCOTT

                                                                                                                                            

 

3,测试是否成功

 

SQL> set autotrace on;

SQL> select count(*) from scott.test t where owner=(‘SCOTT’);

 

  COUNT(*)

———-

         7

 

 

Execution Plan

———————————————————-

Plan hash value: 1950795681

 

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |     6 |   297   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |  3240 | 19440 |   297   (1)| 00:00:04 |

—————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   2 – filter("OWNER"=’SCOTT’)

 

Note

—–

   – SQL plan baseline "SQL_PLAN_07hkz5d7218dw6b581ab9" used for this statement

 

 

Statistics

———————————————————-

         43  recursive calls

         17  db block gets

       1091  consistent gets

          2  physical reads

       3072  redo size

        526  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

本文固定链接: http://www.htz.pw/2014/06/26/spm%e4%b8%ad%e5%8a%a0%e8%bd%bd%e6%8f%90%e7%a4%ba%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92.html | 认真就输

该日志由 huangtingzhong 于2014年06月26日发表在 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: SPM中加载提示执行计划 | 认真就输
关键字: ,
  1. Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1) To BottomTo Bottom Modified:19-Jun-2013Type:HOWTO Rate this document Email link to this document Open document in new window Printable PageIn this DocumentGoalSolutionReferencesAPPLIES TO:Oracle Database – Enterprise Edition – Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]Information in this document applies to any platform.Oracle Server Enterprise Edition – Version: 11.1.0.6 to 11.1.0.7GOALThis note will show how to create SQL Plan Baseline for- SQL coming from an application where the SQL can’t be modified- SQL need hints to run a good execution planPlease note that PLAN_HASH_VALUE is different than HASH_VALUE for the SQLIn the following section , PLAN_HASH_VALUE is only used and not HASH_VALUESOLUTION1- Capture sql plan baseline for the original SQL .var res number ;exec :res := dbms_spm.load_plans_from_cursor_cache(sql_id => ‘&original_sql_id’, plan_hash_value => ‘&original_plan_hash_value’ );2- Execute the hinted SQL.3- Find the SQL_ID and plan_hash_value from V$SQL or directly running this command after the SQL is successfully completed ( keep note of the SQL_ID and plan_hash_value for the hinted SQL , these will be used at step5)select * from table(dbms_xplan.display_cursor);4- Verify original SQL baseline exist . ( keep note of the sql_handle for the original SQL, will be used in step5 )select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;5- Associate the hinted execution plan to the original sql_handle.var res numberexec :res := dbms_spm.load_plans_from_cursor_cache( -sql_id => ‘&hinted_SQL_ID’, -plan_hash_value => &hinted_plan_hash_value, -sql_handle => ‘&sql_handle_for_original’);6- Verify the new baseline was added.select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;7- If the original plan captured initially is not needed, it can be dropped, or disabled.exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (‘&original_sql_handle’,’&original_plan_name’);8- Execute the SQL from application and verify that SQL is now using the the SQL Plan baseline, run the SQL against V$SQLselect SQL_PLAN_BASELINE from V$SQL where SQL_ID=’&original_SQL_ID’The hinted Plan that is loaded into the SPM repository is marked as acceptable and enabledand becomes part of sql plan baseline as it is manual load, so make sure to loadwell tuned and plans that has been well verified for performance.A test case is uploaded to this note which is implementing the steps above.Document 215187.1 SQLTXPLAIN offers a script ( ./utl/coe_load_sql_baseline.sql ) to automate the steps, review the script before running itReference:Oracle Database PL/SQL Packages and Types Reference11g Release 1 (11.1)Part Number B28419-03Using DBMS_SPMREFERENCESNOTE:215187.1 – SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong resultsNOTE:456518.1 – How to Use SQL Plan Management (SPM) – Example UsageNOTE:92202.1 – How to Specify Hidden Hints (Outlines) on SQL Statements in Oracle 8i