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

This article shows you how to understand v$sql 's exact_matching_signature and force_matching_signature.txt. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

[20120918] understanding exact_matching_signature and force_matching_signature.txt of v$sql and understanding exact_matching_signature and force_matching_signature of v$sql is of great significance to the use of sql profile and SPM. I have never understood this very well. If I do a test today, it may not be comprehensive ^ _ ^. SQL > select * from v$version; BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionPL/SQL Release 11.2.0.1.0-ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0-ProductionNLSRTL Version 11.2.0.1.0-Production

1. Set up a test example: create table t as select rownum id1,trunc ((rownum-1) / 10) + 1 id2,'test' name from dual connect by level means exact match force= > it is estimated that it will match after binding.

Alter system flush shared_pool;select / * + findme * / name from t where id1= 42 / select / * + Findme * / name from t where id1= 43: select > @ a R SQL_ID PLAN_HASH_VALUE SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

1 373zfvx9h7xah 1183254286 select / * + findme * / name from t where id1= 42 4375557158775460903 698688741679991361 2 5nyk32fjxxqkc 1183254286 select / * + Findme * / name from t where id1= 43 709458512451441419698688741679991361mura-imagine changing the constant into a variable, where the FORCE_MATCHING_SIGNATURE is the same. SQL > alter system flush shared_pool;SQL > variable v_id1 number; SQL > variable v_idx number; SQL > exec: v_id1: = 42; SQL > exec: v_idx: = 43 SQL > select / * + Findme * / name from t where id1=: v_id1 and id2=1;SQL > select / * + Findme * / name from t where id1=: v_id1 and id2=2;SQL > @ a R SQL_ID PLAN_HASH_VALUE SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

1 b3x2pcgkxaxft 1183254286 select / * + Findme * / name from t where id1=: v_id1 and id2=2 7008393373276421720 7008393373276421720 2 ckr7rh2zfrwcv 1183254286 select / * + Findme * / name from t where id1=: v_id1 and id2=1 14799038700516685754 14799038700516685754 color-but this is different if there is a binding variable in the statement. And at this time EXACT_MATCHING_SIGNATURE = FORCE_MATCHING_SIGNATURE.-- is a bit messy, google found an article, summarized by Lao Xiong, copied it as follows. Http://www.laoxiong.net/sql-profiles-part.html to the SQL statement, remove the repeated spaces (excluding character constants), and convert the case to the same, for example, after all capitalization (excluding character constants), if the SQL is the same, then the exact_matching_signature of the SQL statement is the same. For the SQL statement, remove the repeated spaces (excluding character constants), convert the case to the same, for example, all uppercase (excluding character constants), and then remove the constants in SQL. If the SQL is the same, then the force_matching_signature of the SQL statement is the same. But the exception is that if there are bound variables in SQL, force_matching_signature will generate the same criteria as exact_matching_signature. The above content is how to understand the exact_matching_signature and force_matching_signature.txt of v$sql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.