sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库SPM中加载提示执行计划管理案例

时间:2016-10-24 20:53   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库在多个执行计划中使用SPM选择正确的执行计划的案例。
测试平台:11.2.0.3

1,查看参数

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

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 baselOracleoracleplus.netine "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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库SPM中加载提示执行计划管理案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1056.html

Oracle研究中心

关键词:

使用SPM和STA进行固定执行计划

Oralce SPM中加载提示执行计划详解

使用spm固定sql执行计划