Oracle中如何查詢未使用繫結變數的SQL語句？
Oracle中如何查詢未使用繫結變數的SQL語句？
利用V$SQL 檢視的 FORCE_MATCHING_SIGNATURE 欄位可以識別可能從繫結變數或 CURSOR_SHARING 獲益的 SQL 語句。如果 SQL 已使用繫結變數或者 CURSOR_SHARING ，那麼 FORCE_MATCHING_SIGNATURE 在對其進行標識時將給出同樣的簽名。換句話說，如果兩個 SQL 語句除了字面量的值之外都是相同的，它們將擁有相同的 FORCE_MATCHING_SIGNATURE ，這意味著如果為它們提供了繫結變數或者 CURSOR_SHARING ，它們就成了完全相同的語句。所以，使用 FORCE_MATCHING_SIGNATURE 欄位可以識別沒有使用繫結變來的 SQL 語句。
可以使用如下的SQL 語句來查詢：
with force_mathces as
(select l.force_matching_signature,
max(l.sql_id || l.child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking,
count(*) counts
from v$sql l
where l.force_matching_signature <> 0
and l.parsing_schema_name <> 'SYS'
group by l.force_matching_signature
having count(*) > 10)
select v.sql_id,
v.sql_text,
v.parsing_schema_name,
fm.force_matching_signature,
fm.ranking,
fm.counts
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 50
order by fm.ranking;
SELECT *
FROM (SELECT a.PARSING_SCHEMA_NAME,
substr(sql_text, 1, 60),
count(1) counts,
dense_rank() over(order by count(*) desc) ranking
FROM v$sql a
where a.PARSING_SCHEMA_NAME <> 'SYS'
GROUP BY a.PARSING_SCHEMA_NAME, substr(sql_text, 1, 60)
HAVING count(1) > 10)
where ranking <= 50;
測試SQL如下所示：
declare
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
v_sql clob;
begin
dbms_output.put_line('*********使用字面量************');
for vrt_emp in (select * from scott.emp) loop
v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =' ||
vrt_emp.empno;
execute immediate v_sql
into v_ename, v_sql;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
dbms_output.put_line('');
dbms_output.put_line('*********使用繫結變數************');
for vrt_emp in (select * from scott.emp) loop
v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =:empno';
execute immediate v_sql
into v_ename, v_sql
using vrt_emp.empno;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
end;
select v.sql_text, v.sql_id, v.force_matching_signature
from v$sql v
where v.sql_text like
'select e.ename,e.sal from scott.emp e where e.empno%';
Oracle在解析SQL語句的時候，如果在共享池中發現匹配的SQL語句，就可以避免掉解析的大部分開銷。在共享池中找到匹配的SQL語句所對應的解析被稱為軟解析(soft parse)。如果沒有找到匹配的SQL語句，則必須進行硬解析（hard parse）。
硬解析不僅耗費CPU時間，在有大量會話想要同時快取SQL語句到共享池時還會造成爭用。透過使用繫結變數，可以最小化解析的代價。
1.CURSOR_SHARING引數
該引數轉換SQL語句中的字面值到繫結變數。轉換值提高了遊標共享，且可能會影響SQL語句的執行計劃。最佳化器是基於繫結變數的存在生成執行計劃，而不是實際字面量值。
CURSOR_SHARING決定什麼型別的SQL語句可以共享相同的遊標。CURSOR_SHARING引數有三個值：
FORCE：只要有可能，字面量就會被替換為繫結變數。
SIMILAR：只有當替換不會影響到執行計劃時，才會將字面量替換為繫結變數
EXACT：這是預設值。不將字面量替換為繫結變數。
注意：不推薦修改CURSOR_SHARING引數的預設值。如果實在無法修改現有應用的程式碼，可以透過設定CURSOR_SHARING引數來指示Oracle透明地將字面量替換為繫結變數。
2.識別沒有使用繫結變數的SQL語句
利用v$sql檢視的FORCE_MATCHING_SIGNATURE欄位，可以識別可能從繫結變數或CURSOR_SHARING獲益的SQL語句。如果SQL已使用繫結變數或者CURSOR_SHARING,則FORCE_MATCHING_SIGNATURE在對其進行標識時將給出同樣的簽名。換句話說，如果兩個SQL語句除了字面量的值之外都是相同的，它們將擁有相同的FORCE_MATCHING_SIGNATURE,這意味著如果為他們提供了繫結變數或者CURSOR_SHARING,它們就成了完全相同的語句。
使用FORCE_MATCHING_SIGNATURE識別沒有使用繫結變來的SQL語句。
with force_mathces as
(select l.force_matching_signature mathces,
max(l.sql_id || l.child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking
from v$sql l
where l.force_matching_signature <> 0
and l.parsing_schema_name <> 'SYS'
group by l.force_matching_signature
having count(*) > 10)
select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 5
order by fm.ranking;
3.透過執行動態SQL語句獲取繫結變數的好處
透過執行動態SQL語句，比較字面量和繫結引數對SQL解析的影響。
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_sql clob;
begin
dbms_output.put_line('*********使用字面量************');
for vrt_emp in (select * from emp) loop
v_sql := 'select e.ename,e.sal from emp e where e.empno =' ||
vrt_emp.empno;
execute immediate v_sql
into v_ename, v_sql;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
dbms_output.put_line('');
dbms_output.put_line('*********使用繫結變數************');
for vrt_emp in (select * from emp) loop
v_sql := 'select e.ename,e.sal from emp e where e.empno =:empno';
execute immediate v_sql
into v_ename, v_sql
using vrt_emp.empno;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
end;
查詢v$sql檢視，比較執行結果：
SQL> select v.sql_text, v.sql_id, v.force_matching_signature
2 from v$sql v
3 where v.sql_text like 'select e.ename,e.sal from emp e where e.empno %';
SQL_TEXT SQL_ID FORCE_MATCHING_SIGNATURE
--------------------------------------------------------- -------------- ------------------------
select e.ename,e.sal from emp e where e.empno =7782 766syjydcn5fh 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7499 6ymy4hcb386vt 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7934 3t96y707p8by7 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7902 f9pyzxf7tnuzw 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7654 fvk1fzmrvjc4j 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7788 gsmatg9f4jd2z 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7566 4q9pzzpvvdpuu 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7369 3xhqmvm5vdqy0 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7698 bjjjw0gzaprzv 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7876 8nd8v8mrzxw4w 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7521 5tnyy066zfk1b 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7844 4kd7jb013g2zz 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7900 grx9sh4fwrcwx 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =:empno 20wmyr4cvrr6k 3.49355109645567E18
select e.ename,e.sal from emp e where e.empno =7839 6u2ajyu05gw9s 1.27397653964533E19
在v$sql檢視中，發現使用字面量的SQL語句有14條，而使用繫結變數的SQL語句只有一條。其中使用字面量的SQL語句除以了字面量值不同之外，其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假設該SQL語句使用繫結變數或者CURSOR_SHARING得到的，因此透過FORCE_MATCHING_SIGNATURE欄位識別沒有繫結變數的SQL語句。
從10G開始可以透過如下方式查詢未使用繫結變數的語句
select FORCE_MATCHING_SIGNATURE, count(1)
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;
10G以上透過如下過程可以查詢對未使用繫結變數的語句
create table shsnc.long_sql（sql_text clob,
FORCE_MATCHING_SIGNATURE number,count number)
create or replace procedure query_sql is
cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100
order by 2;
v_fms number;
v_sql01 varchar2(3999);
v_sql02 varchar2(3999);
begin
for i in fms loop
v_sql01:='insert into shsnc.long_sql(FORCE_MATCHING_SIGNATURE,sql_text) select FORCE_MATCHING_SIGNATURE,sql_fulltext from (select FORCE_MATCHING_SIGNATURE,sql_fulltext from v$sql where FORCE_MATCHING_SIGNATURE='||i.fms||' and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2';
v_sql02:='update shsnc.long_sql set count='||i.count ||' where FORCE_MATCHING_SIGNATURE='||i.fms;
execute immediate v_sql01;
commit;
execute immediate v_sql02;
commit;
end loop;
end;
/
10g以後v$SQL動態效能檢視增加了FORCE_MATCHING_SIGNATURE列，其官方定義為”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle透過將原SQL_TEXT轉換為可能的FORCE模式後計算得到的一個SIGNATURE值。這麼說可能不太形象，我們來具體看一下:
SQL> create table YOUYUS (t1 int);
Table created.
SQL> alter system flush shared_pool;
System altered.
SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected
SQL> col sql_text format a55;
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_a%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=2 4.59124694481197E18 1.00267830752731E19
select /*test_matching_a*/ * from YOUYUS where t1=3 4.59124694481197E18 1.61270448861426E19
select /*test_matching_a*/ * from YOUYUS where t1=1 4.59124694481197E18 1.36782048270058E18
/*以上將變數硬編碼至SQL中的遊標，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同說明在遊標共享FORCE模式下，這些遊標滿足CURSOR SHARING的條件 */
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=FORCE;
Session altered.
SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_a%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=:"SYS_B_0" 4.59124694481197E18 4.59124694481197E18
/*FORCE模式下將SQL文字中的變數值轉換成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也隨之等同於FORCE_MATCHING_SIGNATURE了*/
利用FORCE_MATCHING_SIGNATURE捕獲非繫結變數SQL
2010/08/19 BY MACLEAN LIU 9條評論
做為一個DBA，你大概習慣了定期要抓取資料庫中的非繫結變數SQL，這些SQL經常扮演著一箱蘋果中蛀蟲的角色。
看到下列SQL你必定覺得眼熟:
SELECT substr(sql_text, 1, 80), count(1)
FROM v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING count(1) > 10
ORDER BY 2
是的，以上這段抓取literal sql的指令碼大約從8i時代就開始流行了，在那時它很popular也很休閒，使用它或許還會給你的僱主留下一絲神秘感。不過今天我要告訴你的是，它徹底過時了，落伍了，已經不是fashion master了。
10g以後v$SQL動態效能檢視增加了FORCE_MATCHING_SIGNATURE列，其官方定義為”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle透過將原SQL_TEXT轉換為可能的FORCE模式後計算得到的一個SIGNATURE值。這麼說可能不太形象，我們來具體看一下:
SQL> create table YOUYUS (t1 int);
Table created.
SQL> alter system flush shared_pool;
System altered.
SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected
SQL> col sql_text format a55;
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_a%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=2 4.59124694481197E18 1.00267830752731E19
select /*test_matching_a*/ * from YOUYUS where t1=3 4.59124694481197E18 1.61270448861426E19
select /*test_matching_a*/ * from YOUYUS where t1=1 4.59124694481197E18 1.36782048270058E18
/*以上將變數硬編碼至SQL中的遊標，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同說明在遊標共享FORCE模式下，這些遊標滿足CURSOR SHARING的條件 */
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=FORCE;
Session altered.
SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected
SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_a%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=:"SYS_B_0" 4.59124694481197E18 4.59124694481197E18
/*FORCE模式下將SQL文字中的變數值轉換成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也隨之等同於FORCE_MATCHING_SIGNATURE了*/
以上演示說明了FORCE_MATCHING_SIGNATURE列可以幫助我們找出那些潛在可以共享的遊標(也包括了因非繫結問題造成的遊標無法共享)，現在我們利用它來完善捕獲非繫結變數SQL的指令碼：
SQL> alter system flush shared_pool;
System altered.
SQL> select /*test_matching_b*/ * from YOUYUS where t1=1;
no rows selected
SQL> select /*test_matching_b*/ * from YOUYUS where t1='1'; //我有引號，我與眾不同！
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_b%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_b*/ * from YOUYUS where t1='1' 1.43666633406896E19 1.83327833675856E19
select /*test_matching_b*/ * from YOUYUS where t1=1 1.43666633406896E19 8.05526057286178E18
/*多餘的引號也會導致遊標無法共享，此時的FORCE_MATCHING_SIGNATURE 也會是一致的*/
select FORCE_MATCHING_SIGNATURE, count(1)
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;
Enter value for a: 10
old 6: having count(1) > &a
new 6: having count(1) > 10
FORCE_MATCHING_SIGNATURE COUNT(1)
------------------------ ----------
8.81463386552502E18 12
So We find it!
在這裡再推薦一種來自MOS，find Literal SQL的方法:
How to Find Literal SQL in Shared Pool
Applies to:
PL/SQL – Version: 8.1.7 to 10.2
Information in this document applies to any platform.Goal
There is no direct way to query the dictionary for literal SQL only.
However the following example will try to exclude all SQL statements in the
shared pool that do use bind variables.There still might be situations, with statements using subqueries, where the
example still will show SQL statements using bind variables.
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成 ● 最新修改時間：2019-09-01 06:00 ~ 2019-09-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。
........................................................................................................................ |
 |
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2657578/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- V$sql查詢未使用繫結變數的語句SQL變數
- SQL查詢語句 (Oracle)SQLOracle
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- ORACLE結構化查詢語句Oracle
- SQL單表查詢語句總結SQL
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- SQL mother查詢語句SQL
- sql查詢語句流程SQL
- 【VIEW】Oracle如何查詢固定檢視的定義或底層sql語句ViewOracleSQL
- 一條 SQL 查詢語句是如何執行的？SQL
- mysql查詢效率慢的SQL語句MySql
- 輸入的查詢 SQL 語句，是如何執行的？SQL
- SQL語句將查詢結果插入到另一張表中SQL
- postgresql dba常用sql查詢語句SQL
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- SQL Server解惑——查詢條件IN中能否使用變數SQLServer變數
- 在oracle中，select語句查詢欄位中非純數字值Oracle
- SQL Server 查詢歷史執行的SQL語句SQLServer
- SQL基礎的查詢語句烈鉍SQL
- Laravel 框架查詢執行的 SQL 語句Laravel框架SQL
- 在mysql查詢效率慢的SQL語句MySql
- Oracle常用的系統查詢語句整理Oracle
- SQL語句替換查詢結果的的寫法舉例SQL
- sql宣告變數，及if -else語句、while語句的用法SQL變數While
- SQL Server 語句日期格式查詢方法SQLServer
- SQL Server-簡單查詢語句SQLServer
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- oracle查詢語句查詢增加一列內容Oracle
- 找到Oracle資料庫中效能最差的查詢語句BSOracle資料庫
- SQL 查詢語句的執行順序解析SQL
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- Sql語句本身的優化-定位慢查詢SQL優化
- 記一個實用的sql查詢語句SQL
- JAVA 中使用 SQL 語句查詢 EXCEL 檔案資料JavaSQLExcel
- Java 中如何使用 SQL 查詢 TXTJavaSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- Mybatis 查詢語句結果集總結MyBatis
- Oracle基本SQL語句OracleSQL