性能调优：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=(‘SCOTT’)
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=(‘SCOTT’)
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=(‘SCOTT’)
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=’SCOTT3,测试是否成功
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------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
