詳解Oracle數(shù)據(jù)庫如何查找未使用綁定變量的SQL語句

Oracle在解析SQL語句的時候，如果在共享池中發(fā)現(xiàn)匹配的SQL語句，就可以避免掉解析的大部分開銷。在共享池中找到匹配的SQL語句所對應(yīng)的解析被稱為軟解析(soft parse)。如果沒有找到匹配的SQL語句，則必須進行硬解析（hard parse）。

硬解析不僅耗費CPU時間，在有大量會話想要同時緩存SQL語句到共享池時還會造成爭用。通過使用綁定變量，可以最小化解析的代價。

Oracle中有沒使用綁定變量對于是否需要多次解析的影響是很大的，很多時候我們都要求開發(fā)變量盡量都使用綁定變量，但畢竟是要求，有人不遵守的話，我們也沒轍，那么可以怎么去查找到這些未使用綁定變量的sql語句呢？

利用V$SQL 視圖的 FORCE_MATCHING_SIGNATURE 字段可以識別可能從綁定變量或CURSOR_SHARING獲益的SQL語句。

如果 SQL 已使用綁定變量或者 CURSOR_SHARING ，那么FORCE_MATCHING_SIGNATURE 在對其進行標(biāo)識時將給出同樣的簽名。換句話說，如果兩個SQL語句除了字面量的值之外都是相同的，它們將擁有相同的FORCE_MATCHING_SIGNATURE，這意味著如果為它們提供了綁定變量或者CURSOR_SHARING，它們就成了完全相同的語句。

所以，使用FORCE_MATCHING_SIGNATURE字段可以識別沒有使用綁定變來的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;

1、通過執(zhí)行動態(tài)SQL語句，比較字面量和綁定參數(shù)對SQL解析的影響（注意用scott用戶）

set serveroutput on;

?

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;

/

2、查詢v$sql視圖，比較執(zhí)行結(jié)果：

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 emp e where e.empno %';

在v$sql視圖中，發(fā)現(xiàn)使用字面量的SQL語句有14條，而使用綁定變量的SQL語句只有一條。其中使用字面量的SQL語句除以了字面量值不同之外，其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假設(shè)該SQL語句使用綁定變量或者CURSOR_SHARING得到的，因此通過FORCE_MATCHING_SIGNATURE字段識別沒有綁定變量的SQL語句。

1、從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;

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動態(tài)性能視圖增加了FORCE_MATCHING_SIGNATURE列，其官方定義為”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通過將原SQL_TEXT轉(zhuǎn)換為可能的FORCE模式后計算得到的一個SIGNATURE值。

覺得有用的朋友多幫忙轉(zhuǎn)發(fā)哦！后面會分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注下~