1223 result cache,sql profile,sql patch

[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發現如下連結：

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.

--看來要實行這個功能，使用者只能等了................