关于sql_profile中的绑定变量(r4笔记第57天)

使用sql_profile来调优一些紧急的性能sql可以起到立竿见影的效果,如果sql语句本身结构就很清晰,简单,略作修改就能得到调优后的sql语句。 但是如果语句中含有绑定变量,如果要得到调优后的sql_id就有些困难了。 比如我们存在下面的sql语句。

SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT 1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))

需要添加一个hint 得到一个新的sql_id.

SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT /*+ unnest parallel(payer,4) full(payer)*/1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))

如果使用explain plan可以得到包含绑定变量的执行计划,但是却无法得到对应的sql_id 比如sql_id 为74pzzzjddkyd4

74pzzzjddkyd4 SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT FROM   BL1_CUSTOMER CUST        ,BL1_CYCLE_CUSTOMERS CY             
                        ...
              Q_TYPE IS NULL)   AND  EXISTS (SELECT 1                FROM   BL1_CYC_PAYER_POP PAYER
                     ...

使用explain plan for之后可以得到一个执行计划情况,但是sql_id却是不同的。

73d1q5xd835kt explain plan for
              SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
                ...
 AND EXISTS
               (SELECT 1
                ...

对于这种情况,可以使用variable 来实现。 存在几个变量,然后手工赋值,执行一下,也可以中途停止,就能够从v$sql里面抓到对应的sql_id variable periodKey number; variable cycleSeqNo number; variable cycleSeqRun number; exec :periodKey:=61; exec :cycleSeqNo:=4106; exec :cycleSeqRun:=0; 然后执行修改后的语句,这样我们就得到了添加了Hint之后的sql语句。 如果我们需要修改的sql语句中的变量是:1 :2之类的,比如: select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s p step) */ ... s.WORKER FROM TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE s.root2proc_inst = :1 AND s.step2step = step.objid AND ( NOT (step.step_type = 4)) AND ( s.assignee = 'BpmInServer' OR s.assignee = 'BpmInServerSmThr' OR s.assignee = 'BpmJms') AND s.committer is NOT NULL AND ( s.status in (50 )) 这样通过variable就会出错了。只使用数字来作为变量还是不合规则的。 我们可以尝试使用如下的一个简单pl/sql来实现。 比如存在一个变量,我们就在 cursor中定义一个字段,存在多个变量就定义多个字段,最后在execute immediate的后面使用using子句来完成。

declare
cursor temp_cur is select '100' id from dual;
begin
for i in temp_cur loop
execute immediate 'select  /*+ leading(s) index(s TABLE_BPM_STEP_INST_1IX) use_nl(s step) */ 
s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS,
s.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT,
s.CURR_FAULT2FAULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM,
s.HAS_DEPENDENTS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS,
s.ITER_COUNT, s.NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS,
s.OBJID, s.OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST,
s.ROOT2PROC_INST, s.START_TIME, s.STATUS, s.STATUS_CHANGE_TIME,
s.STEP2STEP, s.TARGETED_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME,
s.WORKER  FROM TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE
s.root2proc_inst = :1  AND s.step2step = step.objid AND ( NOT
(step.step_type = 4)) AND ( s.assignee = ''BpmInServer''       OR
s.assignee = ''BpmInServerSmThr''       OR s.assignee = ''BpmJms'') AND
s.committer is NOT NULL AND ( s.status in (50                    ))' using i.id;
end loop;
end;
/

通过v$sql即可得到对应的sql_id 目前自己使用的是这两种方式来解决绑定变量的问题,如果有更好的,希望拍砖。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-02-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(35)-文章发布系统②-构建项目

注:阅读本文,需要阅读本系列的之前文章 代码生成器下载地址(文章开头处)  接下来我们建立数据库的表和各层的代码 我们只需要两张表,文章列表(MIS_Artic...

2469
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

3025
来自专栏Jerry的SAP技术分享

How to update BOL entity property value via ABAP code

Suppose I have one product with ID I042416 which could be found in CRM WebClient...

780
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

1294
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

723
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

3022
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2444
来自专栏Jerry的SAP技术分享

使用ABAP(ADBC)和Java(JDBC)连接SAP HANA数据库

在表DBCON里维护一条记录,指向HANA数据库。con_ENV里填入HANA数据库的主机名和端口号。如vmXXXX:30015

4223
来自专栏芋道源码1024

数据库[分库分表]中间件 Sharding-JDBC 源码分析 —— SQL 解析(四)之插入SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. InsertStatement 3. #parse() 3.1 #parseI...

4337
来自专栏乐沙弥的世界

SQL,PL/SQL 数据类型一览表

The following is a list of datatypes available in Oracle.

1103

扫码关注云+社区

领取腾讯云代金券