ITPub博客

首页 > 数据库 > Oracle > 1223 result cache,sql profile,sql patch

1223 result cache,sql profile,sql patch

原创 Oracle 作者:lfree 时间:2014-12-23 17:05:49 0 删除 编辑

[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.

--看来要实行这个功能,用户只能等了................

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1377511/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6467228