如何查詢數(shù)據(jù)庫(kù)中未使用綁定變量的SQL語(yǔ)句?
[TOC]
? 背景:
? 我們會(huì)經(jīng)常在一些客戶的AWR報(bào)告中,能夠看到如下類似信息,SQL的前綴部分都一樣,執(zhí)行時(shí)間也基本一致,詳細(xì)查看具體的SQL_TEXT后,我們可以看到僅僅是where條件中的變量值不一樣,其他都一樣,沒(méi)有采用綁定變量的方式,而是一個(gè)個(gè)常量。

對(duì)于這種在AWR報(bào)告中,能夠體現(xiàn)出來(lái)的,我們比較容易發(fā)現(xiàn),可及時(shí)反饋給開(kāi)發(fā)人員處理。但是在一些環(huán)境中,系統(tǒng)中其實(shí)存在較多的未使用綁定變量的SQL,但是在AWR報(bào)告TOP SQL部分,并沒(méi)有體現(xiàn)出來(lái),這個(gè)時(shí)候,就需要我們?nèi)?shù)據(jù)庫(kù)中手動(dòng)撈取。
當(dāng)Oracle中存在大量的類似sql,基本結(jié)構(gòu)一樣,僅where條件的取值不一樣時(shí),我們應(yīng)該采用綁定變量的方法,來(lái)減少sql的硬解析,能夠提高數(shù)據(jù)庫(kù)性能,避免出現(xiàn)shared pool相關(guān)的等待事件,同時(shí)也能節(jié)約CPU資源。
下面我們通過(guò)案例,如果查詢數(shù)據(jù)庫(kù)中未使用綁定變量的SQL?
1. 構(gòu)造環(huán)境
-- 創(chuàng)建測(cè)試表,并插入數(shù)據(jù)
SQL> set timing on
SQL> drop table t purge;
Table dropped.
Elapsed: 00:00:00.18
SQL> create table t(x int);
Table created.
Elapsed: 00:00:00.04
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate
5 'insert into t values (:x)' using i;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> select count(*) from t;
COUNT(*)
----------
1000
Elapsed: 00:00:00.00
2. 案例演示
– 方便演示,我們清理一下shared pool
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.12
2.1 未使用綁定變量
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate
5 'select /*tag1*/ count(*) from t where x= '||i||'';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
2.2 使用綁定變量
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate
5 'select /*tag2*/ count(*) from t where x= :1' using i;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
3. 獲取未使用綁定變量的SQL
方法一:通過(guò)force_matching_signature分析
? 10g以后v$SQL動(dòng)態(tài)性能視圖增加了FORCE_MATCHING_SIGNATURE列,其官方定義為:
The signature used when the CURSOR_SHARING parameter is set to FORCE,也就是Oracle通過(guò)將原SQL_TEXT轉(zhuǎn)換為可能的FORCE模式后計(jì)算得到的一個(gè)SIGNATURE值。我們知道,如果cursor sharing設(shè)置為force , oracle將類似的SQL的謂詞用一個(gè)變量代替,同時(shí)將它們看做同一條SQL語(yǔ)句處理。
SQL> set numwidth 20
SQL> select force_matching_signature,count(1) from v$sql group by force_matching_signature order by count(1) desc;
FORCE_MATCHING_SIGNATURE COUNT(1)
------------------------ --------------------
11420119939742656807 1000
0 14
4650186927289073934 2
6434074771071323635 2
1435631005720608251 1
12313764141852424472 1
9194475184364435657 1
12005390545862180746 1
SQL> select sql_text from v$sql where force_matching_signature=11420119939742656807 and rownum<10;
SQL_TEXT
--------------------------------------------------------------------------------
select /*tag1*/ count(*) from t where x= 782
select /*tag1*/ count(*) from t where x= 497
select /*tag1*/ count(*) from t where x= 286
select /*tag1*/ count(*) from t where x= 937
select /*tag1*/ count(*) from t where x= 458
select /*tag1*/ count(*) from t where x= 969
select /*tag1*/ count(*) from t where x= 637
select /*tag1*/ count(*) from t where x= 161
select /*tag1*/ count(*) from t where x= 742
9 rows selected.
Elapsed: 00:00:00.00
將上面SQL整合到一起,如下:
select sql_text, sql_id, executions, parse_calls,force_matching_signature from v$sql where force_matching_signature in
(select force_matching_signature from (
select force_matching_signature,count(1) from v$sql group by force_matching_signature order by count(1) desc
) where rownum<10);

方法二:通過(guò)sql文本分析
select substr(sql_text,1,50),count(1) from v$sql group by substr(sql_text,1,50) order by count(1) desc;

4. 思考綁定變量性能
4.1 未使用綁定變量
select t.sql_text, t.sql_id, t.executions, t.parse_calls,force_matching_signature
from v$sql t
where sql_text like 'select /*tag1*/ count(*) from t%';

結(jié)論:產(chǎn)生1000個(gè)的類似sql,基本結(jié)構(gòu)一樣,僅where條件的取值不一樣,每個(gè)SQL都執(zhí)行一次,解析一次。
4.2 使用綁定變量
select t.sql_text, t.sql_id, t.executions, t.parse_calls,force_matching_signature
from v$sql t
where sql_text like 'select /*tag2*/ count(*) from t%';

結(jié)論:1. 僅產(chǎn)生1個(gè)SQL,執(zhí)行1000次,僅解析一次。和未使用綁定變量相比,性能更好。
? 2. 從案例演示中,2個(gè)SQL執(zhí)行時(shí)間來(lái)看,使用綁定變量的方式也比未使用綁定變量的方式快很多。




