ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle sql profile(Manual)使用。

oracle sql profile(Manual)使用。

原创 Oracle 作者：dbtianti 时间：2014-12-11 18:02:52 0 删除 编辑

以下模拟如何在不改变sql文本的情况下使用sql profile来改变其执行计划，以期达到优化的目的。
sql profile分Manual ,AUTO两类，先介绍Manual方式的创建。

使用Manual的sql profile时，需要借助mos【215187.1】的脚本coe_xfr_sql_profile.sql 。


1，创建测试表。

点击(此处)折叠或打开

  1. SQL> select * from v$version;
  2.  
  3. BANNER
  4. ----------------------------------------------------------------
  5. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
  6. PL/SQL Release 10.2.0.5.0 - Production
  7. CORE    10.2.0.5.0    Production
  8. TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
  9. NLSRTL Version 10.2.0.5.0 - Production


  12. SQL> create table tb_m as
  13.   2 select level c1,level+1 c2 from dual connect by level <101;
  14.  
  15. Table created

在表上建立索引，并收集统计信息。

点击(此处)折叠或打开

  1. SQL> create index ind_tb_m on tb_m(c1);
  2.  
  3. Index created


  6. SQL> exec dbms_stats.gather_table_stats('YY','TB_M');
  7.  
  8. PL/SQL procedure successfully completed

执行以下sql(sql_1)，执行计划是索引扫描。

点击(此处)折叠或打开

  1. SQL> explain plan for select * from tb_m a where c1=1;
  2.  
  3. Explained
  4.  
  5. SQL> select * from table(dbms_xplan.display());
  6.  
  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. Plan hash value: 3028611639
  10. --------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
  12. --------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00
  14. | 1 | TABLE ACCESS BY INDEX ROWID| TB_M | 1 | 6 | 2 (0)| 00
  15. |* 2 | INDEX RANGE SCAN | IND_TB_M | 1 | | 1 (0)| 00
  16. --------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  19.    2 - access(\"C1\"=1)
  20.  
  21. 14 rows selected

如果现在不想让sql_1走索引，如何用sql profile改写它。
首先将sql_1加hint提示改写成sql_2。
sql_2:
select/*+no_index(a) */ * from tb_m a where c1=1;

点击(此处)折叠或打开

  1. SQL> explain plan for select/*+no_index(a) */ * from tb_m a where c1=1;
  2.  
  3. Explained
  4.  
  5. SQL> select * from table(dbms_xplan.display());
  6.  
  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. Plan hash value: 4109591867
  10. --------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. --------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
  14. |* 1 | TABLE ACCESS FULL| TB_M | 1 | 6 | 3 (0)| 00:00:01 |
  15. --------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  18.    1 - filter(\"C1\"=1)
  19.  
  20. 13 rows selected
分别找到其sql_id和对应的plan_hash_value。
sql_1 : fzruqwqvu81dd
sql_2 : f12uvb7b6j270

点击(此处)折叠或打开

  1. SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%where c1=1%';
  2.  
  3. SQL_TEXT SQL_ID VERSION_COUNT
  4. -------------------------------------------------------------------------------- ------------------------------ -------------
  5. select * from tb_m a where c1=1 fzruqwqvu81dd 1
  6.  select/*+no_index(a) */ * from tb_m a where c1=1 f12uvb7b6j270 1


  9. SQL> select sql_id,plan_hash_value from v$sql where sql_id in ('fzruqwqvu81dd','f12uvb7b6j270');
  10.  
  11. SQL_ID PLAN_HASH_VALUE
  12. ------------------------------ ------------------------------
  13. fzruqwqvu81dd 3028611639
  14. f12uvb7b6j270 4109591867

使用脚本coe_xfr_sql_profile.sql生成sql_1的manual sqlprofile ;

点击(此处)折叠或打开

  1. SQL>@'d:\coe_xfr_sql_profile.sql' fzruqwqvu81dd 3028611639

  3. Parameter 1:
  4. SQL_ID (required)






  11. PLAN_HASH_VALUE AVG_ET_SECS
  12. --------------- -----------
  13.      3028611639 .001


  16. Parameter 2:
  17. PLAN_HASH_VALUE (required)




  22. Values passed to coe_xfr_sql_profile:
  23. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  24. SQL_ID : \"fzruqwqvu81dd\"
  25. PLAN_HASH_VALUE: \"3028611639\"


  28. SQL>BEGIN
  29.   2 IF :sql_text IS NULL THEN
  30.   3 RAISE_APPLICATION_ERROR(-20100, \'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).\');
  31.   4 END IF;
  32.   5 END;
  33.   6 /
  34. SQL>SET TERM OFF;
  35. SQL>BEGIN
  36.   2 IF :other_xml IS NULL THEN
  37.   3 RAISE_APPLICATION_ERROR(-20101, \'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).\');
  38.   4 END IF;
  39.   5 END;
  40.   6 /
  41. SQL>SET TERM OFF;


  44. Execute coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql
  45. on TARGET system in order to create a custom SQL Profile
  46. with plan 3028611639 linked to adjusted sql_text.




  51. COE_XFR_SQL_PROFILE completed.

使用脚本coe_xfr_sql_profile.sql生成sql_2的manual sqlprofile ;

点击(此处)折叠或打开

  1. SQL>@'d:\coe_xfr_sql_profile.sql' f12uvb7b6j270 4109591867


  4. Parameter 1:
  5. SQL_ID (required)






  12. PLAN_HASH_VALUE AVG_ET_SECS
  13. --------------- -----------
  14.      4109591867 .002


  17. Parameter 2:
  18. PLAN_HASH_VALUE (required)




  23. Values passed to coe_xfr_sql_profile:
  24. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  25. SQL_ID : \"f12uvb7b6j270\"
  26. PLAN_HASH_VALUE: \"4109591867\"


  29. SQL>BEGIN
  30.   2 IF :sql_text IS NULL THEN
  31.   3 RAISE_APPLICATION_ERROR(-20100, \'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).\');
  32.   4 END IF;
  33.   5 END;
  34.   6 /
  35. SQL>SET TERM OFF;
  36. SQL>BEGIN
  37.   2 IF :other_xml IS NULL THEN
  38.   3 RAISE_APPLICATION_ERROR(-20101, \'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).\');
  39.   4 END IF;
  40.   5 END;
  41.   6 /
  42. SQL>SET TERM OFF;


  45. Execute coe_xfr_sql_profile_f12uvb7b6j270_4109591867.sql
  46. on TARGET system in order to create a custom SQL Profile
  47. with plan 4109591867 linked to adjusted sql_text.




  52. COE_XFR_SQL_PROFILE completed.
会生成2个.sql文件，sql_1对应的是coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql；
                           sql_2对应的是coe_xfr_sql_profile_f12uvb7b6j270_4109591867.sql；

打开.sql文件会发现如下内容：

点击(此处)折叠或打开

  1. h := SYS.SQLPROF_ATTR(
  2. q\'[BEGIN_OUTLINE_DATA]\',
  3. q\'[IGNORE_OPTIM_EMBEDDED_HINTS]\',
  4. q\'[OPTIMIZER_FEATURES_ENABLE(\'10.2.0.5\')]\',
  5. q\'[ALL_ROWS]\',
  6. q\'[OUTLINE_LEAF(@\"SEL$1\")]\',
  7. q\'[FULL(@\"SEL$1\" \"A\"@\"SEL$1\")]\',
  8. q\'[END_OUTLINE_DATA]\');

将sql_2(coe_xfr_sql_profile_f12uvb7b6j270_4109591867.sql)中的以上内容替换到sql_1(coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql)中。
同时执行coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql；
SQL> @'d:\coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql';

点击(此处)折叠或打开

  1. SQL> @'d:\coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql';
  2. SQL>REM
  3. SQL>REM $Header: 215187.1 coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql 11.4.3.5 2014/12/11 carlos.sierra $
  4. SQL>REM
  5. SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
  6. SQL>REM
  7. SQL>REM AUTHOR
  8. SQL>REM carlos.sierra@oracle.com
  9. SQL>REM
  10. SQL>REM SCRIPT
  11. SQL>REM coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql
  12. SQL>REM
  13. SQL>REM DESCRIPTION
  14. SQL>REM This script is generated by coe_xfr_sql_profile.sql
  15. SQL>REM It contains the SQL*Plus commands to create a custom
  16. SQL>REM SQL Profile for SQL_ID fzruqwqvu81dd based on plan hash
  17. SQL>REM value 3028611639.
  18. SQL>REM The custom SQL Profile to be created by this script
  19. SQL>REM will affect plans for SQL commands with signature
  20. SQL>REM matching the one for SQL Text below.
  21. SQL>REM Review SQL Text and adjust accordingly.
  22. SQL>REM
  23. SQL>REM PARAMETERS
  24. SQL>REM None.
  25. SQL>REM
  26. SQL>REM EXAMPLE
  27. SQL>REM SQL> START coe_xfr_sql_profile_fzruqwqvu81dd_3028611639.sql;
  28. SQL>REM
  29. SQL>REM NOTES
  30. SQL>REM 1. Should be run as SYSTEM or SYSDBA.
  31. SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
  32. SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
  33. SQL>REM 4. To drop this custom SQL Profile after it has been created:
  34. SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(\'coe_fzruqwqvu81dd_3028611639\');
  35. SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
  36. SQL>REM for the Oracle Tuning Pack.
  37. SQL>REM
  38. SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
  39. SQL>REM
  40. SQL>VAR signature NUMBER;
  41. SQL>REM
  42. SQL>DECLARE
  43.   2 sql_txt CLOB;
  44.   3 h SYS.SQLPROF_ATTR;
  45.   4 BEGIN
  46.   5 sql_txt := q\'[
  47.   6 select * from tb_m a where c1=1
  48.   7 ]\';
  49.   8 h := SYS.SQLPROF_ATTR(
  50.   9 q\'[BEGIN_OUTLINE_DATA]\',
  51.  10 q\'[IGNORE_OPTIM_EMBEDDED_HINTS]\',
  52.  11 q\'[OPTIMIZER_FEATURES_ENABLE(\'10.2.0.5\')]\',
  53.  12 q\'[ALL_ROWS]\',
  54.  13 q\'[OUTLINE_LEAF(@\"SEL$1\")]\',
  55.  14 q\'[FULL(@\"SEL$1\" \"A\"@\"SEL$1\")]\',
  56.  15 q\'[END_OUTLINE_DATA]\');
  57.  16 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
  58.  17 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
  59.  18 sql_text => sql_txt,
  60.  19 profile => h,
  61.  20 name => \'coe_fzruqwqvu81dd_3028611639\',
  62.  21 description => \'coe fzruqwqvu81dd 3028611639 \'||:signature||\'\',
  63.  22 category => \'DEFAULT\',
  64.  23 validate => TRUE,
  65.  24 replace => TRUE,
  66.  25 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
  67.  26 END;
  68.  27 /


  71. PL/SQL 过程已成功完成。


  74. SQL>WHENEVER SQLERROR CONTINUE
  75. SQL>SET ECHO OFF;


  78.             SIGNATURE
  79. ---------------------
  80.  16498969119777112484




  85. ... manual custom SQL Profile has been created




  90. COE_XFR_SQL_PROFILE_fzruqwqvu81dd_3028611639 completed

接下来再执行一下sql_1,发现其已经由索引扫描变成了全表扫描。

点击(此处)折叠或打开

  1. SQL> explain plan for select * from tb_m a where c1=1;
  2.  
  3. Explained
  4.  
  5. SQL> select * from table(dbms_xplan.display());
  6.  
  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. Plan hash value: 4109591867
  10. --------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. --------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
  14. |* 1 | TABLE ACCESS FULL| TB_M | 1 | 6 | 3 (0)| 00:00:01 |
  15. --------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  18.    1 - filter(\"C1\"=1)
  19. Note
  20. -----
  21.    - SQL profile \"coe_fzruqwqvu81dd_3028611639\" used for this statement
  22.  
  23. 17 rows selected

如果将变量值修改一下，执行计划会如何呢？
发现其又变成了索引扫描。

点击(此处)折叠或打开

  1. SQL> explain plan for select * from tb_m a where c1=2;
  2.  
  3. Explained
  4.  
  5. SQL> select * from table(dbms_xplan.display());
  6.  
  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. Plan hash value: 3028611639
  10. --------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
  12. --------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00
  14. | 1 | TABLE ACCESS BY INDEX ROWID| TB_M | 1 | 6 | 2 (0)| 00
  15. |* 2 | INDEX RANGE SCAN | IND_TB_M | 1 | | 1 (0)| 00
  16. --------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  19.    2 - access(\"C1\"=2)
  20.  
  21. 14 rows selected


针对此情况，修改文件coe_fzruqwqvu81dd_3028611639.sql的force_match参数即可。
将force_match 由FALSE 修改为 TRUE；










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

0
0
上一篇： 没有了~
下一篇： coe_xfr_sql_profile.sql
请登录后发表评论 登录
全部评论
dbtianti

注册时间：2014-12-03

  • 博文量
    2
  • 访问量
    7395

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593