SQL Profiles-MANUAL

最新推荐文章于 2020-01-13 15:52:08 发布
最新推荐文章于 2020-01-13 15:52:08 发布
阅读量563 收藏
点赞数
分类专栏： ORACLE--性能优化 文章标签： sql profile STA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013820054/article/details/45532717
版权
Manual管理SQL Profile步骤
1、针对目标sql使用脚本coe_xfr_sql_profile.sql产生能生成其Manual类型的SQL Profile 的脚本A
2、改写目标sql的文本，在其中使用合适的hint，直到加入hint后的sql能走出我们想要的执行计划。然后对加入合适hint后的sql使用脚本coe_xfr_sql_profile.sql，产生能生成其Manual类型的SQL Profile的脚本B。
3、用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。

4、执行脚本A生成针对原目标的SQL的Manual类型的SQL Profile。

模拟SQL走错执行计划
SYS@fyl>select /*+ no_index(t ind_n) */ * from t where n=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |    30   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |    26 |    30   (4)| 00:00:01 |
--------------------------------------------------------------------------

使用hint改写sql
SYS@fyl>select /*+ index(t ind_n) */ * from t where n=3; 
         N
----------
         3
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  37bvrs54d4pu5, child number 0
-------------------------------------
select /*+ index(t ind_n) */ * from t where n=3
Plan hash value: 3063387565
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IND_N |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=3)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "N"[NUMBER,22]
Note
-----
   - dynamic sampling used for this statement (level=2)
46 rows selected.

SYS@fyl>select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%index%n=3%'; 

SQL_TEXT                                                     SQL_ID        VERSION_COUNT
------------------------------------------------------------ ------------- -------------
select /*+ index(t ind_n) */ * from t where n=3              37bvrs54d4pu5             1
select /*+ no_index(t ind_n) */ * from t where n=3           9wypc3n066q48             1

查询加入hint后的sql的plan_hash_value
SYS@fyl>select plan_hash_value from v$sql where sql_id='37bvrs54d4pu5';
PLAN_HASH_VALUE
---------------
     3063387565
查询错误的sql的plan_hash_value
SYS@fyl>select plan_hash_value from v$sql where sql_id='9wypc3n066q48';
PLAN_HASH_VALUE
---------------
     1601196873
1、针对原目标sql使用脚本coe_xfr_sql_profile.sql产生能生成其Manual类型的SQL Profile 的脚本，执行coe_xfr_sql_profile.sql的过程输入错误sql的sql_id和PLAN_HASH_VALUE。 

SYS@fyl>@coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 9wypc3n066q48


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1601196873        .058

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1601196873

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "9wypc3n066q48"
PLAN_HASH_VALUE: "1601196873"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
on TARGET system in order to create a custom SQL Profile
with plan 1601196873 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.
2、对加入合适hint--index后的sql使用脚本coe_xfr_sql_profile.sql，产生能生成其Manual类型的SQL Profile的脚本。 

SQL>@coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 37bvrs54d4pu5


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3063387565         .01

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3063387565

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "37bvrs54d4pu5"
PLAN_HASH_VALUE: "3063387565"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_37bvrs54d4pu5_3063387565.sql
on TARGET system in order to create a custom SQL Profile
with plan 3063387565 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

错误sql的sql profile内容coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
。。。
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+ no_index(t ind_n)
*/ * from t where n=3
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_9wypc3n066q48_1601196873',
description => 'coe 9wypc3n066q48 1601196873 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
。。。

改写加入hint后的sql profile内容coe_xfr_sql_profile_37bvrs54d4pu5_3063387565.sql
。。。
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+ index(t ind_n)
*/ * from t where n=3
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_37bvrs54d4pu5_3063387565',
description => 'coe 37bvrs54d4pu5 3063387565 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURS
OR_SHARING) */ );
END;
/
。。。
3、用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。
用改写后的sql profile的hint组合替换原来sql profile的hint，并将原sql profile的force_match改为TRUE

4、执行脚本A生成针对原目标的SQL的Manual类型的SQL Profile。 

SYS@fyl>@coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
SYS@fyl>REM
SYS@fyl>REM $Header: 215187.1 coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql 11.4.3.5 2015/05/05 carlos.sierra $
SYS@fyl>REM
SYS@fyl>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
SYS@fyl>REM
SYS@fyl>REM AUTHOR
SYS@fyl>REM carlos.sierra@oracle.com
SYS@fyl>REM
SYS@fyl>REM SCRIPT
SYS@fyl>REM coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
SYS@fyl>REM
SYS@fyl>REM DESCRIPTION
SYS@fyl>REM This script is generated by coe_xfr_sql_profile.sql
SYS@fyl>REM It contains the SQL*Plus commands to create a custom
SYS@fyl>REM SQL Profile for SQL_ID 9wypc3n066q48 based on plan hash
SYS@fyl>REM value 1601196873.
SYS@fyl>REM The custom SQL Profile to be created by this script
SYS@fyl>REM will affect plans for SQL commands with signature
SYS@fyl>REM matching the one for SQL Text below.
SYS@fyl>REM Review SQL Text and adjust accordingly.
SYS@fyl>REM
SYS@fyl>REM PARAMETERS
SYS@fyl>REM None.
SYS@fyl>REM
SYS@fyl>REM EXAMPLE
SYS@fyl>REM SQL> START coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql;
SYS@fyl>REM
SYS@fyl>REM NOTES
SYS@fyl>REM 1. Should be run as SYSTEM or SYSDBA.
SYS@fyl>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SYS@fyl>REM 3. SOURCE and TARGET systems can be the same or similar.
SYS@fyl>REM 4. To drop this custom SQL Profile after it has been created:
SYS@fyl>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9wypc3n066q48_1601196873');
SYS@fyl>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SYS@fyl>REM for the Oracle Tuning Pack.
SYS@fyl>REM
SYS@fyl>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SYS@fyl>REM
SYS@fyl>VAR signature NUMBER;
SYS@fyl>REM
SYS@fyl>DECLARE
  2  sql_txt CLOB;
  3  h SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select /*+ no_index(t ind_n)
  7  */ * from t where n=3
  8  ]';
  9  h := SYS.SQLPROF_ATTR(
 10  q'[BEGIN_OUTLINE_DATA]',
 11  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 12  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
 13  q'[DB_VERSION('11.2.0.1')]',
 14  q'[ALL_ROWS]',
 15  q'[OUTLINE_LEAF(@"SEL$1")]',
 16  q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))]',
 17  q'[END_OUTLINE_DATA]');
 18  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 19  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 20  sql_text => sql_txt,
 21  profile => h,
 22  name => 'coe_9wypc3n066q48_1601196873',
 23  description => 'coe 9wypc3n066q48 1601196873 '||:signature||'',
 24  category => 'DEFAULT',
 25  validate => TRUE,
 26  replace => TRUE,
 27  force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 28  END;
 29  /


PL/SQL procedure successfully completed.


SYS@fyl>WHENEVER SQLERROR CONTINUE
SYS@fyl>SET ECHO OFF;


            SIGNATURE
---------------------
 14002184797686193519




... manual custom SQL Profile has been created




COE_XFR_SQL_PROFILE_9wypc3n066q48_1601196873 completed


验证：
SYS@fyl>select /*+ no_index(t ind_n) */ * from t where n=3;




Execution Plan
----------------------------------------------------------
Plan hash value: 3063387565


--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    82 |  1066 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_N |    82 |  1066 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("N"=3)


Note
-----
   - SQL profile "coe_9wypc3n066q48_1601196873" used for this statement




Two手揣兜
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL reference manual -- vertica
04-05
SQL reference manual -- vertica
Profiles-of-adolescent-stress-The-development-of-the-adolescent
最新发布
06-17
《青少年压力概况：青少年压力发展问卷(ASQ)》的研究主要关注了青少年时期的压力及其对身心健康的影响。在理解和评估青少年健康与幸福感时，压力的重要性已得到广泛认可。然而，近年来，针对青少年压力的测量方法和...
SQL_Profiles 使用讲解
csdn909034的博客
06-10 235
Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在...
SQL_Profiles 使用讲解(二）
csdn909034的博客
06-10 425
在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。...
Manual类型的SQL Profile
cprgi40080的博客
08-11 158
实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定 关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP_SQL...
oracle sql profile(Manual)使用。
cuizu4125的博客
12-11 256
以下模拟如何在不改变sql文本的情况下使用sql profile来改变其执行计划，以期达到优化的目的。 sql profile分Manual ,AUTO两类，先介绍Manual方式的创建。 使用Manual的sql ...
tuned-profiles-realtime-2.11.0-10.el7.noarch.rpm
12-24
官方离线安装包，测试可用。使用rpm -ivh [rpm完整包名] 进行安装
profiles-rest-api
04-04
本篇文章将深入探讨“profiles-rest-api”项目，这是一个用Python实现的REST API，用于处理和管理用户概要文件数据。我们将围绕其设计原则、核心概念和技术栈展开讨论。 1. REST API基础： REST是一种面向资源的...
SQL PROFILES
翔之天空的专栏
10-27 914
本次演示sql profile 的基本用法， 平台基于rhel6 +oracle11.2 版本   第一节建立测试用例 第二节查看sql的执行计划，本次所有试验也是通过sql profile 来使得第二节1部分的sql执行计划变成第二节2部分的执行计划 第三节是通过sql tuning advisor 生成sql profile ，使得sql的执行计划改变 第四节是通过sql tunin
Oracle执行计划的稳定（使用MANUAL类型的SQL PROFILE）
Lissen_Gan的博客
01-13 286
使用MANUAL类型的SQL PROFILE来稳定执行计划 手动类型的SQL PROFILE总的来说就修改HINT，就是将COST较少的HINT部分复制到COST较多的里面去。就是执行计划中的Outline Data部分。 1、环境准备 ...
利用profiles来查看sql 语句执行计划
01-18
用于MYSQL数据库关键SQL语句的资源消耗的量化以及针对性地分析和优化
SQL Profiles-AUTO
Super菜的专栏
05-06 480
http://www.laoxiong.net/sql-profiles-part.html http://www.laoxiong.net/sql-profiles-partii.html SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最
【转】SQL Profiles
wujay
12-12 109
转自：http://www.laoxiong.net/sql-profiles-part.html   Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方，SQL Profiles...
SQL Profiles-PartIII -- profile的控制
事后诸葛亮的程序人生（微信：zq9017197）
11-27 1692
第二部分：profile的控制   对profile的控制比较简单：修改和删除。包DBMS_SQLTUNE提供了两个存储过程来完成这两个操作：ALTER_SQL_PROFILE和DROP_SQL_PROFILE。   BEGIN   DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile'); END; / 1．Profile的修
Sql profiles用法介绍
ckvl39619的博客
06-30 130
DBMS_SQLTUNE优化SQL 是在oracle10g才出来的新特性，使用它能很大程度上方便对sql的分析和优化。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：grant advisor ...
SQL Profiles使用实例
cksddfeinwqlse5952的博客
02-11 80
SQL Profiles使用实例在统计信息不完整或者缺失时，有时优化器会产生错误的判断，产生性能比较低下的执行计划。传统的方式，通过手工添加相关的提示生成正确的执行计划。但对于那些封装好的应用程序，修改代码不是一件容易的事情，相...
分析诊断工具之三：使用show profiles分析SQL性能
weixin_34389926的博客
01-23 530
　　分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进...
sql profile: 如何使用我们自定义的sql 执行计划来固定原sql执行计划 及 如何优化非绑定变量sql
Lixora's DB Home
06-21 797
##测试环境搭建： SQL> create table test (n number ); Table created. SQL> declare           begin            for i in 1 .. 10000 loop                insert into test values(i);                commit;
tuned-profiles-sap-hana
05-16
tuned-profiles-sap-hana是一个针对SAP HANA优化的Linux系统调整工具。这个工具实际上是一个应用程序集，它能够通过针对服务器、网络、磁盘和内存的系统级参数优化Linux系统性能，特别是针对SAP HANA数据处理工作...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值