客戶一套新業務系統上線，告知資料庫相應非常慢，並傳了一份AWR報告

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.03 In-memory Sort %: 100.00
Library Hit %: 89.24 Soft Parse %: 89.96
Execute to Parse %: 1.40 Latch Hit %: 99.84
Parse CPU to Parse Elapsd %: 24.60 % Non-Parse CPU: 88.36

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 13,275.52 776.46
Logical reads: 24,603.65 1,439.02
Block changes: 86.79 5.08
Physical reads: 7.80 0.46
Physical writes: 4.26 0.25
User calls: 5,693.60 333.01
Parses: 753.93 44.10

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU 29,551.0 68.9
sql execute elapsed time 26,901.8 62.7
parse time elapsed 13,859.0 32.3

從awr報告來看,目前buffer cache配置良好
Buffer Nowait %: 和Buffer Hit %:命中率均達到100%
Physical reads也只有7.8

而從shared pool來看，目前shared pool size配置1,344M 而相應的Library Hit %和Soft Parse %僅僅在90%一下，表現不理想
從解析方面來看每秒Parses:達到753 而每秒的硬解析達到76 過多的硬解析是造成資料庫響應慢的一個重要原因
等待事件中出現了latch: library cache和latch: shared pool說明在shared pool使用上存在著爭用。
並且從AWR報告中也反應出來，資料庫中存在著較多的語句沒有使用繫結變數
現在問題已經很明確了，由於繫結變數沒有合理的使用，導致資料庫中sql的硬解析過多，消耗了過多的CPU資源和shared pool資源
現在的問題就是需要找出這些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;
或者
透過v$sqlarea來查詢：
select hash_value, substr(sql_text,1,80)
from v$sqlarea
where substr(sql_text,1,40) in (select substr(sql_text,1,40)
from v$sqlarea
having count(*) > 50
group by substr(sql_text,1,40));

不過透過以上sql來進行sql捕獲的話會有一個很明顯的問題就是執行速度慢，由於涉及到的sql量比較大，所以會造成sql語句執行緩慢，
較為先進的作法是透過create table from v$sql建立表，並且透過對錶建立索引等等來最佳化查詢
10g之後，oracle對v$sql檢視進行了變更，新增了一個新的欄位FORCE_MATCHING_SIGNATURE
該欄位oracle對於其解釋為The signature used when the CURSOR_SHARING parameter is set to FORCE
初步的理解應該是假定資料庫的cursor_sharing為force時計算得到的值，而EXACT_MATCHING_SIGNATURE的解釋為
Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
個人的理解為當sql語句進入資料庫中時對於一些可以潛在可以共享或者因為繫結變數問題造成遊標沒有共享的sql他的FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值是不同的

我們可以透過一個簡單的測試來看一下
以下有一張表：

SQL> select * from test;

ID
----------
1
2
首先我重新整理shared pool

SQL> alter system flush shared_pool;

System altered.
做幾個查詢操作：
SQL> select * from test where id=1;
ID
----------
1
SQL> select * from test where id=2;
ID
----------
2
SQL> select * from test where id=4;
no rows selected
SQL> select * from test where id='1';
ID
----------
1
SQL> select * from test where id='2';
ID
----------
2
SQL> var v_id number
SQL> exec :v_id := 1
PL/SQL procedure successfully completed.
SQL> select * from test where id=:v_id;
ID
----------
1
SQL> exec :v_id:=2
PL/SQL procedure successfully completed.
SQL> select * from test where id=:v_id;
ID
----------
2
檢視v$sql中的sql語句：
SQL> col sql_text format a40
SQL> set numwidth 30
SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE sql_text like '%select * from test%';

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------- ------------------------------ ------------------------------
select * from test where id=4 15142173931344982766 9756054619608722298
select * from test where id=2 15142173931344982766 3132555961613213627
select * from test where id='1' 15142173931344982766 13165047151983476077

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------- ------------------------------ ------------------------------
select * from test where id='2' 15142173931344982766 9000319709726737374
select * from test where id=:v_id 14699597912112598920 14699597912112598920
select * from test where id=1 15142173931344982766 10881582965512961005
8 rows selected.

/*以上將變數硬編碼至SQL中的遊標，FORCE_MATCHING_SIGNATURE值完全相同，
而EXACT_MATCHING_SIGNATURE值各有不同。
FORCE_MATCHING_SIGNATURE值相同說明在遊標共享FORCE模式下，這些遊標滿足CURSOR SHARING的條件 */
SQL> alter system flush shared_pool;

可以看到以上的sql在沒有使用繫結變數的sql中FORCE_MATCHING_SIGNATURE值均是相同的而EXACT_MATCHING_SIGNATURE是不同的
那麼透過以上的sql我們就可以完善出查詢沒有使用繫結變數的sql語句：
SQL> select * from
(select sql_text,row_number() over(partition by FORCE_MATCHING_SIGNATURE order by FORCE_MATCHING_SIGNATURE) rn
2 from v$sql
3 4 where FORCE_MATCHING_SIGNATURE >0
5 and FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE)
6 where rn>1;

SQL_TEXT RN
---------------------------------------- ------------------------------
select * from test where id=4 2
select * from test where id=2 3
select * from test where id=1 4

SQL_TEXT RN
---------------------------------------- ------------------------------
select * from test where id='1' 5

就能夠查到以上資料庫中沒有做繫結變數的sql了。
在metalink上，也有提供的查詢繫結變數的sql指令碼：
How to Find Literal SQL in Shared Pool [ID 187987.1]
這裡列出指令碼內容可參考，不過個人覺得指令碼一般般，測試環境就幾條sql跑了很久很久

[oracle@rac1 ~]$ more find_literal.sql
set serveroutput on
set linesize 120
--
-- This anonymous PL/SQL block must be executed as INTERNAL or SYS
-- Execute from : SQL*PLUS
-- CAUTION:
-- This sample program has been tested on Oracle Server - Enterprise Edition
-- However, there is no guarantee of effectiveness because of the possibility
-- of error in transmitting or implementing it. It is meant to be used as a
-- template, and it may require modification.
--
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;

begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/

有如下的結論：對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，比如均為大寫（不包括字元常量）後，如果SQL相同，那麼SQL語句的exact_matching_signature就是相同的。對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，比如均為大寫（不包括字元常量），然後去掉SQL中的常量，如果SQL相同，那麼SQL語句的force_matching_signature就是相同的。但是例外的情況是：如果SQL中有繫結變數，force_matching_signature就會與exact_matching_signature一樣的生成標準。

關於高效捕獲資料庫非繫結變數的SQL語句

相關文章