[20141223]result cache 与sql profile,sql patch.txt
--前面blog已经提到result cache的好处与缺点，对于第三方优化，sql profile可以改变稳定执行计划，是否可以通过改变提示来稳定
--执行计划，这样对频繁执行的语句较少逻辑读，提高服务器响应有积极意义。
--sql patch 也具有相似的作用，看看这种方式是否可行。
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> exec sys.DBMS_RESULT_CACHE.flush
PL/SQL procedure successfully completed.
1.采用sql profile方式：
--假设程序频繁执行如下语句，看是否可以加入result_cache提示：
select * from emp,dept where dept.deptno=emp.deptno;
--sql_id='ab5asdvqxfm27'
declare
v_sql CLOB;
begin
select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';
dbms_sqltune.import_sql_profile(
name => 'profile_result_cache',
description => 'SQL profile created manually',
-- category => 'TEST',
sql_text => v_sql,
profile => sqlprof_attr(
'RESULT_CACHE'
),
replace => TRUE,
force_match => TRUE
);
end;
/
select * from emp,dept where dept.deptno=emp.deptno;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ab5asdvqxfm27, child number 0
-------------------------------------
select * from emp,dept where dept.deptno=emp.deptno
Plan hash value: 615168685
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 8 (13)| 14 |00:00:00.01 | 15 | 1035K| 1035K| 764K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 6 | 4 (0)| 6 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- SQL profile profile_result_cache used for this statement
--可以发现sql profile已经生效，但是执行计划没有使用result_cache.
SCOTT@test> execute dbms_sqltune.drop_sql_profile(name => 'profile_result_cache')
PL/SQL procedure successfully completed.
2.采用sql patch模式：
declare
v_sql CLOB;
begin
select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => v_sql,
hint_text => 'result_cache',
name => 'result_cache_patch');
end;
/
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ab5asdvqxfm27, child number 0
-------------------------------------
select * from emp,dept where dept.deptno=emp.deptno
Plan hash value: 615168685
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 8 (13)| 14 |00:00:00.01 | 15 | 1035K| 1035K| 753K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 6 | 4 (0)| 6 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
- SQL patch "result_cache_patch" used for this statement
--可以发现sql patch已经生效，但是执行计划没有使用result_cache.
SCOTT@test> exec dbms_sqldiag.drop_sql_patch('result_cache_patch');
PL/SQL procedure successfully completed.
3.后记：
--google发现如下链接：
http://lcmarques.com/2014/05/10/sql-patch-and-result-cache-hint/
As seen,no RESULT CACHE was used (also easily seen by time taken to count the rows) even if SQLPatch inplace. This is
actually result of a bug: Bug 16974854 : RESULT CACHE HINT DOES NOT WORK WITH SQL PATCH . Oracle also promised a patch
soon (and included in some BP for 11.2.0.3/4). It will eventually be fixed also in Oracle 12.2.x according to bug
description.
--看来要实行这个功能，用户只能等了................