测试平台：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 |
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