Shulou(Shulou.com)05/31 Report--

This article mainly explains "sql finds sentences that do not use bound variables". Friends who are interested may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "sql to find statements that do not use bound variables".

1. Search method:

Starting with ORACLE10G, the column FORCE_MATCHING_SIGNATURE:Signature used when the CURSOR_SHARING parameter is set to FORCE; is added to the V$SQL view (when the CURSOR_SHARING parameter is set to FORCE, the resulting execution plan is the same)

Test 1: the value of EXACT cursoring

SQL > show parameter cursor_sharing

NAME TYPE VALUE

Cursor_sharing string EXACT

SQL > SELECT / * john*/* from bas.john where name=' Cao Dingfang'

SQL > SELECT / * john*/* from bas.john where name=' Zhang Jiexiong'

SQL > SELECT / * john*/* from bas.john where name=' practice to be Bin'

Select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql twhere sql_text like'%john%'

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

-

SELECT / * john*/* from bas.john where name=' Cao Dingfang '1.58728914732591E19 8.65016113461197E18

SELECT / * john*/* from bas.john where name=' Zhang Jiexiong '1.58728914732591E19 1.55664749687513E19

SELECT / * john*/* from bas.john where name=' becomes Bin '1.58728914732591E19 6.34382341893161E18

Test 1 shows that when the name value is different, the FORCE_MATCHING_SIGNATURE value is the same, but the EXACT_MATCHING_SIGNATURE value is different, indicating that the execution plan can be shared in cursor_sharing=force; in cursor_sharing=exact, the execution plan cannot be shared; after we use bound variables, we can also achieve the effect of execution plan sharing.

Test 2: the value of FORCE cursoring

1. Execute the statement: alter system set cursor_sharing=force

SQL > alter system set cursor_sharing=force

System altered.

SQL > show parameter cursor_sharing

NAME TYPE VALUE

Cursor_sharing string FORCE

SQL > alter system flush shared_pool

System altered.

2. Execute three query statements as well

SQL > SELECT / * john*/* from bas.john where name=' Cao Dingfang'

SQL > SELECT / * john*/* from bas.john where name=' Zhang Jiexiong'

SQL > SELECT / * john*/* from bas.john where name=' practice to be Bin'

3. Find the sentence situation of V$SQL

Select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like'% john%'

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

-

SELECT / * john*/* from bas.john where name=: "SYS_B_0" 1.58728914732591E19 1.58728914732591E19

Test 2 shows that when we set cursor_sharing to force, we find that the statement execution plan is shared and bind variables are used automatically (at this time, only one statement related to it is queried in the v$sql view).

Test 3. The cursor_ sharing value is EXACT, but binding variables are used.

1. Execute the statement: alter system set cursor_sharing=EXACT

SQL > alter system set cursor_sharing=force

System altered.

SQL > show parameter cursor_sharing

NAME TYPE VALUE

Cursor_sharing string EXACT

SQL > alter system flush shared_pool

System altered.

2. Use binding variables to query

SQL > SELECT / * john*/* from bas.john where name=:a

3. Find the sentence situation of V$SQL

Select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql twhere sql_text like'%john%'

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

-

SELECT / * john*/* from bas.john where name=:a 1.4513622515145E19 1.4513622515145E19

Summary of test 3: when we set cursor_sharing to EXACT and bind the variable query, we found that the statement execution plan is shared

II. Solutions

We have learned from the above tests that if the FORCE_MATCHING_SIGNATURE value in V$SQL is the same as the SQL statement, then you can bind variables to optimize accordingly.

Select to_char (FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count (1) as counts

From v$sql

Where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

Group by FORCE_MATCHING_SIGNATURE

Having count (1) > & a

Order by 2 desc

At this point, I believe you have a deeper understanding of "sql to find statements that do not use bound variables". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.