Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

SQL statements that capture unbound variables

2025-02-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Previously, I have been using the following sql to view the sql of unbound variables, but not allowed

Select hash_value, substr (sql_text, 1,80) from v$sqlarea where substr (sql_text, 1,40) in (select substr (sql_text, 1,40) from v$sqlarea having count (*) > 1 group by substr (sql_text, 1,40)); SELECT substr (sql_text, 1,80), count (1) FROM v$sql GROUP BY substr (sql_text, 1,80) HAVING count (1) > 1 ORDER BY 2

After 10g, oracle changes the v$sql view by adding a new field, FORCE_MATCHING_SIGNATURE, which is interpreted as The signature used when the CURSOR_SHARING parameter is set to FORCE by oracle

The preliminary understanding should be the calculated value when the cursor_sharing of the database is assumed to be force

The interpretation of EXACT_MATCHING_SIGNATURE is Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.

Personal understanding is that when the sql statement enters the database, for some sql that can potentially be shared or the cursor is not shared because of the problem of binding variables, his FORCE_MATCHING_ signature value is exactly the same, while the EXACT_MATCHING_ signature value is different.

Here's a test in 11gr2:

MOE@xbtst SQL > select * from test; DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONMOE@xbtst SQL > alter system flush shared_pool;System altered.MOE@xbtst SQL > select * from test where deptno=10 DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORKMOE@xbtst SQL > select * from test where deptno=20 DEPTNO DNAME LOC- 20 RESEARCH DALLASMOE@xbtst SQL > select * from test where deptno=30 DEPTNO DNAME LOC- 30 SALES CHICAGOMOE@xbtst SQL > select * from test where deptno='10' DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORKMOE@xbtst SQL > select * from test where deptno='20' DEPTNO DNAME LOC- 20 RESEARCH DALLASMOE@xbtst SQL > select * from test where deptno='30' DEPTNO DNAME LOC- 30 SALES CHICAGOMOE@xbtst SQL > var v_id numberMOE@xbtst SQL > exec: v_id: = 10PL/SQL procedure successfully completed.MOE@xbtst SQL > select * from test where deptno=:v_id DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORKMOE@xbtst SQL > exec: v_id: = 20PL/SQL procedure successfully completed.MOE@xbtst SQL > select * from test where deptno=:v_id DEPTNO DNAME LOC- 20 RESEARCH DALLASMOE@xbtst SQL > exec: v_id: = 30PL/SQL procedure successfully completed.MOE@xbtst SQL > select * from test where deptno=:v_id DEPTNO DNAME LOC- 30 SALES CHICAGOMOE@xbtst SQL > set line 123MOE@xbtst SQL > col sql_text format a40MOE@xbtst SQL > set numwidth 30MOE@xbtst SQL > select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE sql_text like'% select * from test%' SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE -select * from test where deptno=20 1674223644458057282 5701787720123824641select * from test where deptno='20' 1674223644458057282 6624213459289620561select * from test where deptno='30' 1674223644458057282 15799720645668840753select * from test where deptno='10' 1674223644458057282 7423854019058606662select * from test where deptno=30 1674223644458057282 6295409922938069091select * from test where deptno=10 1674223644458057282 5918141949209886904select * from test where deptno=:v_id 5038495461207490287 5038495461207490287MOE@xbtst SQL > show parameter cursor_sharNAME TYPE VALUE -cursor_sharing string EXACT

You can see that the FORCE_MATCHING_ signature values of the above sql are all the same in the sql that does not use bound variables, but the EXACT_MATCHING_SIGNATURE is different. Through the above sql, we can find the sql statement that does not use bound variables:

MOE@xbtst SQL > select * 2 from (select sql_text, 3 row_number () over (partition by FORCE_MATCHING_SIGNATURE order by FORCE_MATCHING_SIGNATURE) rn 4 from v$sql 5 where FORCE_MATCHING_SIGNATURE > 0 6 and FORCE_MATCHING_SIGNATURE! = EXACT_MATCHING_SIGNATURE) 7 where rn > 1 SQL_TEXT RN-- select * from test where deptno='30' 2select * from test where deptno='20' 3select * from test where deptno=10 4select * from test where deptno=30 5select * from test where deptno=20 6

Reference: SQL statements about efficiently capturing database unbound variables

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report