oracle sql profile(Manual)使用。

最新推荐文章于 2024-02-01 11:25:02 发布
最新推荐文章于 2024-02-01 11:25:02 发布
阅读量271 收藏
点赞数
文章标签： 数据库

以下模拟如何在不改变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博客 ” ，链接：http://blog.itpub.net/30049665/viewspace-1364846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30049665/viewspace-1364846/

cuizu4125
关注
Oracle的SQL监视工具SQLTracker
04-19
Oracle的SQL监视工具SQLTracker是一款强大的性能分析工具，专为数据库管理员和开发人员设计，用于诊断和优化SQL查询性能。这款工具在Oracle数据库环境中扮演着重要角色，它可以帮助用户实时监控SQL语句的执行情况，...
Oracle SQL Profile及SPM使用
dba笔记
06-25 1499
IT数据库行业小学生，记录日常工作中数据库知识及一些故障案例，如有不对请指正，欢迎关注小编，小编微信xh870545795,CSDN:dba_notes一、区别 我们知道oracle稳定执行计划有两种方式，一种是使用SQL profile（oracle 10g以前通常使用这种方式），还有一种是SPM(SQL Plan Management,oracle 11g推出的新功能)，两者的主要...
Oracle执行计划的稳定（使用MANUAL类型的SQL PROFILE）
Lissen_Gan的博客
01-13 309
使用MANUAL类型的SQL PROFILE来稳定执行计划 手动类型的SQL PROFILE总的来说就修改HINT，就是将COST较少的HINT部分复制到COST较多的里面去。就是执行计划中的Outline Data部分。 1、环境准备 ...
oracle固定执行计划1-sql profile
fanzhuozhuo的博客
03-05 1262
oracle固定执行计划SQL Profileautomatic类型的sql profile SQL Profile 1、SQL profile在Oracle 10g引入 2、通过为特定的SQL文本指定优化器的一些信息，从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的。 3、通常和SQL Tuning Advisor一起用 4、也可以手工设置 automatic类型的sql profile 1、构造测试环境： session 1： conn scott/tige
Oracle Manual
lishengc的专栏
06-28 476
oracle manualexpexp user/password owner=user file=/data/**.dumpexp user/password owner=user statistics=none file=/data/**.dumpimp imp user/password fromuser=user touser=user file=/data/**.dump imp use
oracle数据库查询慢sql profile解决
lmxx2020的博客
01-30 919
该问题，主要是查询的sql未按正常的执行计划查询，查询超级慢，导致应用一直卡在那边。对“union all”的子查询挨个查了一遍，发现第一个和第二个子查询不能出结果，通过整个试图查看发现“TJ_TIJIANDZHXM”表是全表扫描，该表有700多万数据。后台不能跟踪出执行计划，检查发现“MZ_SHOURU_RB_CB”是个同义词，最终是个由多个试图查询做“union all”连接，查询数据库内的几个试图，都能很快出来。应用某个功能模块访问数据库卡住没反应，这个模块以前也慢，但都能正常访问，现在查询出不来。
Oracle固定执行计划之SQL PROFILE概要文件详解
09-09
Oracle的固定执行计划是数据库管理系统中一种确保SQL语句始终使用特定执行计划的技术，它能够稳定查询性能，避免因为统计信息的变化导致的执行计划选择不当。SQL PROFILE是Oracle 10g引入的一种优化工具，它是固定...
ORACLESQL性能优化.pptx
04-24
SQL 共享原理是 Oracle的一个功能，它可以大大地提高 SQL 的执行性能并节省了内存的使用。通过对 SQL 共享原理的理解，可以更好地优化 SQL 语句。 因此，在数据库管理中，需要对 Oracle SQL 性能优化给予足够的...
OracleSQLDeveloper
11-14
Oracle SQL Developer 是一款由Oracle公司推出的免费数据库管理工具，它为数据库管理员（DBA）和开发人员提供了一个全面的集成环境，以便于管理和操作Oracle数据库。这个工具的强大之处在于其直观的用户界面和广泛的...
Oracle SQL 官方文档
03-08
Oracle SQL 是一种强大的数据库查询和编程语言，广泛用于管理和操作Oracle数据库系统。11g第二版（11G2）是Oracle的一个重要版本，提供了许多增强功能和优化。本官方文档集包括四份重要的参考资料，涵盖了Oracle SQL...
Oracle manual
08-20
oracle 11i mannual, 电子书。有点过时，仅作参考
SQL reference manual -- vertica
04-05
SQL reference manual -- vertica
oracle固定执行计划2-sql profile
fanzhuozhuo的博客
03-08 1225
oracle固定执行计划2-sql profilemauanl类型的sql profile绑定已经存在的执行计划绑定不存在的执行计划--绑定加hint执行计划 上篇介绍了如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。本篇将介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)。 那么，这里最关键的一点是，如何来手工创建SQL Profiles? 使用DBMS_SQLTUNE.IMPORT_SQL_P
【ORACLE】Oracle 11g RAC使用Manual和Policy Managed方法配置
Stayhungry,Stayfoolish.
09-12 814
软件环境： 操作系统：Red Hat Enterprise Linux 5.4(Tikanga) Oracle：11gR2 11.2.0.4.0 RAC 数据库名称: racdb RAC集群两节点名称：racdb01，racdb02 Policy-Managed方式介绍 基于策略的管理方式，是以服务器池（Server Pools）为基础的，简单地说，就是先定义一些服务器池，池中包含一定量
oracle manual
weixin_34034261的博客
07-30 107
http://www.stanford.edu/dept/itss/docs/oracle/ 转载于:https://blog.51cto.com/xu20cn/90352
oracle manual and setup
Shen_Hai的专栏
08-18 150
expexp pdm/pdm owner=pdm file=/tmp/e3k5dev1.exp imp imp pdm2/pdm2 fromuser=pdm touser=pdm2 file=e3k5dev1.exp start oracle817启动oracle数据库$ svrmgrl SVRMGR&gt; connect internal Connected. SVRMGR&gt; sta...
Oracle的SQL BASELINE和SQL PROFILE
最新发布
DBAIOps的博客
02-01 822
实际上用户是把SQL PROFILE当成绑定执行计划了，其实从原理上讲，SQL PROFILE并不是强行绑定执行计划，而是通过SPM分析发现统计信息与实际运行情况不符，因此通过SQL PROFILE设置了一些TABLE_STATS hint，从而让优化器可以使用更为精准的生成执行计划。上面这张图也来自于Oracle的官方文档，这张图十分清晰，从上面我们可以看出，SQL PROFILE是用于纠正过去错误的执行计划的，但是并不限定今后不会再次使用这个错误的执行计划。不过它的缺陷是缺乏灵活性。
Oracle SQL*PLUS使用指南
"Oracle SQL*PLUS工具的使用" Oracle SQL*PLUS是Oracle数据库系统中一个强大的命令行工具，它提供了一个交互式的环境，用于执行SQL和PL/SQL语句，进行数据库管理和操作。通过SQL*PLUS，用户可以方便地输入、编辑、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值