查询oracle绑定变量值,通过v$sql_bind_capture 查看绑定变量。

住在古墓里的人

于 2021-04-05 13:09:56 发布

阅读量1.7k

点赞数 1

文章标签：查询oracle绑定变量值

ops$admin@CRMG>select sum(SHARABLE_MEM/1024/1024) from v$sql where sql_id='576c1s91gua19';

SUM(SHARABLE_MEM/1024/1024)

---------------------------

169.904795

今天数据库的AWR报告里，出现了一条SQL占用共享池内存达到了150M。

是条insert语句。又是绑定变量分级惹的祸。

初步的方案是想让应用只插入不为空的值，通过减少插入值的个数来规避这个问题。

INSERT INTO BPM_PROCINST ( ID, GMT_MODIFIED, MODIFIER, IS_DELETED, VERSION, PROCDEF, STATE, PROCSTART_TIME, PROCEND_TIME, OLD_PROCINST, SUPER_PROCINST, SUPER_NODENAME, SAVE_SIGN, LINE_NOTE, REQUEST_PERSONID, REQUEST_PERSONNAME, REQUEST_ROLE, REQUEST_ORGID, REQUEST_ORGPATH, DOC_NO, SYSTEM_TYPE, DOC_TYPE, SUB_DOCTYPE, DOC_OBJTYPE, DOC_OBJID, CUST_MEMBER, CUST_ID, CUST_NAME, FLAG, COL1, COL2, COL3 ) VALUES ( :1, sysdate, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31 )

我想确定下，这些绑定变量平时是不是都有值，如果每次都有值，那就不能通过上面的方法来解决了。

ops$admin@CRMG>select snap_id, name, position, value_string,last_captured,WAS_CAPTURED from dba_hist_sqlbind

2 where sql_id = '576c1s91gua19' and snap_id='20433';

SNAP_ID NAME POSITION VALUE_STRING LAST_CAPTURED WAS_CA

---------- -------- ---------- ------------------------------ ------------------- ------

20433 :1 1 NO

20433 :2 2 NO

20433 :3 3 NO

20433 :4 4 NO

20433 :5 5 NO

20433 :6 6 NO

20433 :7 7 NO

20433 :8 8 NO

20433 :9 9 NO

20433 :10 10 NO

20433 :11 11 NO

20433 :12 12 NO

20433 :13 13 NO

20433 :14 14 NO

20433 :15 15 NO

20433 :16 16 NO

20433 :17 17 NO

20433 :18 18 NO

20433 :19 19 NO

20433 :20 20 NO

20433 :21 21 NO

20433 :22 22 NO

20433 :23 23 NO

20433 :24 24 NO

20433 :25 25 NO

20433 :26 26 NO

20433 :27 27 NO

20433 :28 28 NO

20433 :29 29 NO

20433 :30 30 NO

20433 :31 31 NO

发现根本就没捕获到WAS_CAPTURED的值都是NO。

查看资料后，原来绑定变量捕获，只能对where后面的字段有效。

UPDATE BPM_BUSI_DATA

SET MODIFY_TIME = sysdate,

COL4 = :1,

COL15 = :2,

COL16 = :3,

APPROVE_TIME = :4,

CUST_ID = :5,

COL1 = :6,

COL2 = :7,

COL3 = :8,

COL6 = :9,

COL7 = :10,

COL8 = :11,

COL9 = :12,

COL11 = :13,

COL13 = :14,

COL14 = :15,

DATATYPE = :16,

REQUEST_PE RSONID = :17,

REQUEST_PERSONNAME = :18,

REQUEST_ROLE = :19,

REQUEST_ORGID = :20,

REQUEST_ORGNAME = :21,

ATTACH = :22,

SYSTEM_SRC = :23,

DOC_NO = :24,

DOC_TYPE = :25,

DOC_OBJTYPE = :26,

DOC_OBJID = :27,

DOC_DEPT = :28,

CUST_NAME = :29,

REQUEST_NOTE = :30,

COL5 = :31,

COL10 = :32,

COL12 = :33,

COL17 = :34,

COL18 = :35,

COL19 = :36,

COL20 = :37,

APPROVE_RESULT = :38,

COL21 = :3 9,

COL22 = :40,

COL23 = :41,

COL24 = :42,

COL25 = :43,

COL26 = :44,

COL27 = :45

WHERE ID_ = :46

这条SQL就是有id=：46这个会被捕获。

SNAP_ID WAS_CA NAME POSITION VALUE_STRING LAST_CAPTURED

---------- ------ -------- ---------- ------------------------------ -------------------

20433 NO :1 1

20433 NO :2 2

20433 NO :3 3

20433 NO :4 4

20433 NO :5 5

20433 NO :6 6

20433 NO :7 7

20433 NO :8 8

20433 NO :9 9

20433 NO :10 10

20433 NO :11 11

20433 NO :12 12

20433 NO :13 13

20433 NO :14 14

20433 NO :15 15

20433 NO :16 16

20433 NO :17 17

20433 NO :18 18

20433 NO :19 19

20433 NO :20 20

20433 NO :21 21

20433 NO :22 22

20433 NO :23 23

20433 NO :24 24

20433 NO :25 25

20433 NO :26 26

20433 NO :27 27

20433 NO :28 28

20433 NO :29 29

20433 NO :30 30

20433 NO :31 31

20433 NO :32 32

20433 NO :33 33

20433 NO :34 34

20433 NO :35 35

20433 NO :36 36

20433 NO :37 37

20433 NO :38 38

20433 NO :39 39

20433 NO :40 40

20433 NO :41 41

20433 NO :42 42

20433 NO :43 43

20433 NO :44 44

20433 NO :45 45

20433 YES :46 46 113423105 2011-03-09 00:35:39

20433 YES :46 46 222439851 2011-03-08 16:30:41

dba_hist_sqlbind这个视图查看的是AWR报告里的绑定变量值。

还有个视图v$sql_bind_capture，查看是当前的捕获。捕获的间隔有一个隐含参数控制。默认是900秒，才会重新开始捕获。

sys@CRMG>SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

3 WHERE x.inst_id = USERENV ('Instance')

4 AND y.inst_id = USERENV ('Instance')

5 AND x.indx = y.indx

6 AND x.ksppinm LIKE '%&par%'

7 /

Enter value for par: bind_ca

old 6: AND x.ksppinm LIKE '%&par%'

new 6: AND x.ksppinm LIKE '%bind_ca%'

NAME VALUE DESCRIB

------------------------------ -------------------- ------------------------------------------------------------

_cursor_bind_capture_area_size 400 maximum size of the cursor bind capture area

_cursor_bind_capture_interval 900 interval (in seconds) between two bind capture for a cursor

住在古墓里的人

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
查询oracle绑定变量值,通过v$sql_bind_capture 查看绑定变量。

ops$admin@CRMG>select sum(SHARABLE_MEM/1024/1024) from v$sql where sql_id='576c1s91gua19';SUM(SHARABLE_MEM/1024/1024)---------------------------169.904795今天数据库的AWR报告里，出现了一条SQL占用共享池内存达到了150M。是条inser...
复制链接

扫一扫