Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to view binding variables through v$sql_bind_capture in sql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Recently, in the process of troubleshooting CRMG performance SQL, a large number of SQL need to determine the value of binding variables, which is too inefficient to contact developers one by one. There are also some technical means, such as 10046 Magnum LOGMINER, which is extremely complicated.

Most people know that through the v$sql_bind_capture view, you can view binding variables, but this view is not very powerful and can only capture the binding variable value of the last record.

And there is an implicit parameter control between the two captures. The default is 900 seconds before the capture starts again. Within 900, changes in the value of the binding variable are not reflected in this view.

After 10g, you can view the binding variable value of SQL recorded in the AWR report as follows.

Select snap_id, name, position, value_string,last_captured,WAS_CAPTURED from dba_hist_sqlbind where sql_id = '576c1s91gua19' and snap_id='20433'

-SNAP_ID is the snapshot ID reported by AWR.

-name, the name of the bound variable

-position, which binds the position of the value in the SQL statement, marked with 1, 2, and 3

-value_string, which is the value of the bound variable.

-, last_captured, the last time captured

-WAS_CAPTURED, whether the binding is captured or not, the binding before the where clause is not captured.

The power of the dba_hist_sqlbind view is that it records the binding variable value of the SQL in each AWR report, which is also obtained from the v$sql_bind_capture sample when the AWR is generated.

From this view, we can get more values of binding variables, which are generally sufficient for us to troubleshoot problems.

It is also important to note that the binding variables recorded in these two views only capture the bindings following the where condition, which you need to be aware of when used.

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 900interval (in seconds) between two bind capture for a cursor

This is the end of this article on "how to view binding variables through v$sql_bind_capture in sql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.