可以增加共享池大小，
如果從根本解決問題，還需要增加硬體資源。升級oracle版本，最佳化應用部署等才能根本解決。另外：
1、查詢分配情況
SELECT 'shared pool (' || NVL (DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx),'Total') || '):' subpool,
SUM (ksmsslen) BYTES, ROUND (SUM (ksmsslen) / 1048576, 2) mb
FROM x$ksmss WHERE ksmsslen > 0
GROUP BY ROLLUP (ksmdsidx) ORDER BY subpool ASC；

2、查詢一下各個子池的剩餘記憶體
SELECT subpool, NAME, SUM (BYTES), ROUND (SUM (BYTES) / 1048576, 2) mb
FROM (SELECT 'shared pool (' || DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx)
|| '):' subpool, ksmssnam NAME, ksmsslen BYTES
FROM x$ksmss WHERE ksmsslen > 0 AND LOWER (ksmssnam) LIKE LOWER ('%free memory%'))
GROUP BY subpool, NAME ORDER BY subpool ASC, SUM (BYTES) DESC
/

3、透過以下查詢可以詳細列舉不同子池的Free記憶體塊情況
SQL> SELECT ksmchidx "SubPool", 'sga heap(' || ksmchidx || ',0)' sga_heap,
2 ksmchcom chunkcomment,
3 DECODE (ROUND (ksmchsiz / 1000),
4 0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
5 6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K'
6 ) "size",
7 COUNT (*), ksmchcls status, SUM (ksmchsiz) BYTES
8 FROM x$ksmsp WHERE ksmchcom = 'free memory'
9 GROUP BY ksmchidx, ksmchcls, 'sga heap(' || ksmchidx || ',0)',ksmchcom, ksmchcls,
10 DECODE (ROUND (ksmchsiz / 1000),
11 0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
12 6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K' );

如果發現大的記憶體請求在剩餘》10k中很小的話，就有可能報4031錯誤

show parameter cursor

SQL> select a.*,b.name
from v$sesstat a , v$statname b
where a.statistic#=b.statistic#
and a.sid=(select distinct sid from v$mystat)
and b.name like '%parse%'

SELECT substr(sql_text,1,40) "SQLTXT",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2 DESC

新方法：

SELECT FORCE_MATCHING_SIGNATURE，COUNT(*)
FROM V$SQL
GROUP BY FORCE_MATCHING_SIGNATURE

ORDER BY 2 DESC

SQL> select '硬解析' SQL解析型別,

2 ((SELECT value from v$sysstat where name like 'parse count (hard)')/(SELECT value from v$sysstat where name like 'parse count (total)')*100)||'%' 比例

3 FROM DUAL

4 union

5 select '軟解析' SQL解析型別,

6 (100-(SELECT value from v$sysstat where name like 'parse count (hard)')/(SELECT value from v$sysstat where name like 'parse count (total)')*100)||'%' 比例

7 FROM DUAL

8 UNION

9 select '解析發生錯誤' SQL解析型別,

10 ((SELECT value from v$sysstat where name like 'parse count (failures)')/(SELECT value from v$sysstat where name like 'parse count (total)')*100)||'%' 比例

11 FROM DUAL

12 ;

SELECT 1 CODE, SUM(pinhits) 執行軟解析的次數,sum(pins) 總共執行SQL的次數,SUM(pinhits)/sum(pins)*100 SQL解析命中率,
case when SUM(pinhits)/sum(pins)*100<99 then 'Librarycache中SQL解析命中率過低'
ELSE 'Librarycache中SQL解析命中率正常'
END 命中率是否正常
from v$librarycache
UNION
select 2 CODE, sum(gets-getmisses) 執行軟解析的次數,sum(getmisses) 總共執行SQL的次數,100*sum(gets-getmisses)/sum(gets) SQL解析命中率 ,
case when 100*sum(gets-getmisses)/sum(gets)<95 then 'rowcache(資料字典)中SQL解析命中率過低'
ELSE 'rowcache(資料字典)中SQL解析命中率正常'
END 命中率是否正常
from v$rowcache where gets>0；

SQL> select 1 CODE,'硬解析後只執行1次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS=1
2 UNION
3 select 2 CODE, '2硬解析後執行1-5次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>1 AND EXECUTIONS<=5
4 UNION
5 select 3 CODE, '硬解析後執行6-20次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=6 AND EXECUTIONS<=20
6 UNION
7 select 4 CODE, '硬解析後執行21-50次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=21 AND EXECUTIONS<=50
8 UNION
9 select 5 CODE, '硬解析後執行51-100次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=51 AND EXECUTIONS<=100
10 UNION
11 select 6 CODE, '硬解析後執行101-500次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=101 AND EXECUTIONS<=500
12 UNION
13 select 7 CODE, '硬解析後執行501-1000次的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=501 AND EXECUTIONS<=1000
14 UNION
15 select 8 CODE, '硬解析後執行1000次以上的SQL次數' 情況描述,COUNT(*) 重複執行SQL的次數 FROM V$SQL WHERE EXECUTIONS>=1001
16 ;

SELECT SUM((CASE WHEN EXECUTIONS=1 THEN EXECUTIONS ELSE 0 END ))/COUNT(*)*100||'%' sql每次執行都是硬解析的比例
FROM V$SQL;
3

,下面的語句是查詢前面70個字元相同但是整個語句不匹配的語句
你可以根據實際情況取進行修改

select substr(a.SQL_TEXT,1,40),count(*)
from v$sqlarea a
group by substr(a.SQL_TEXT,1,70)
having count(*) > 10 ;

select * from v$statname a , V$sesstat b where a.statistic#=b.statistic# and name like 'parse count%'

已經看到了，根據value比較大的值確定是hard的為硬分析比較多的session，然後檢視這個session是哪個SQL語句

select sql_text,version_count,invalidations,parse_calls,optimizer_mode,parsing_user_id,parsing_schema_id,address,hash_value
from v$sqlarea
where version_count>10;

這就是寫日誌記錄的程式碼,這段程式碼使用了繫結變數,但是version_count卻有7023個.
也就是這個sql有7023個子指標.這是不可想象的.

透過前面幾節的研究我們知道,如果這個sql有7023個子指標
那麼意味著這些子指標都將存在於同一個bucket的連結串列上
那麼這也就意味著,如果同樣sql再次執行,oracle將不得不搜尋這個連結串列以尋找可以共享的sql.
這將導致大量的library cache latch的競爭.

select sql_text,executions,SQL_ID ,BIND_DATA from v$sqlarea order by executions desc;

select sql_text from v$sqlarea where substr(sql_text,1,40)='select gw_msg_id,message1 from mt_bill_0' and rownum<11;

tom給了個方法：

Lbrary cache latch contention is typically caused by NOT using bind variables.
It is due to excessive parsing of statements.

One way to see if this might be the case in your situation is to run a script
like:

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/

以上演示說明了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!

--下面我們就透過FORCE_MATCHING_SIGNATURE來查詢系統中未使用繫結變數的語句...
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
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 desc;
--透過上面查詢出來的FORCE_MATCHING_SIGNATURE值，來查詢sql語句
select t.* from v$sql t where FORCE_MATCHING_SIGNATURE=16456394970215993993;
--延伸：
cursor_sharing有三個可選引數：EXACT、SIMILAR、FORCE。
--語句完全一樣，則利用share pool中的語句，反之則重新生成(預設)。
EXACT：Only allows statements with identical text to share the same cursor.
--語句相似，但系統自行判斷是否利用share pool中的語句(不推薦，bug很多)。
SIMILAR：Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or
the degree to which the plan is optimized.
--語句相似，系統強制利用share pool中的語句(不推薦，bug較多)。
FORCE：Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

TOM

ASKTOM網站提供了一個函式remove_constants，來檢查共享池中的SQL執行情況

1、首先建立一個表，用於存放整理過的資料：
create table t1 as select sql_text from v$sqlarea;
2、給表增加一個欄位：
alter table t1 add sql_text_wo_constants varchar2(1000);
3、建立函式remove_constants:
create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
4、將v$sql檢視中的欄位：sql_text的資料用remove_constants處理後，來更新t1表
update t1 set sql_text_wo_constants = remove_constants(sql_text);
5、查出除了謂詞條件不同的SQL語句和它們的執行次數，這裡是查詢SQL沒有重用超過100次的SQL語句:
select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/

測試：
使用一個迴圈執行1000次某條SQL，每次執行只有謂詞不同：
begin
for i in 1..1000 loop
execute immediate 'select * from t where OBJECT_ID = '||i;
end loop;
end;
/

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2;

SQL_TEXT_WO_CONSTANTS COUNT(*)
--------------------- ------------
SELECT * FROM T WHERE OBJECT_ID = @
1000

可以看到輸出結果中，這條語句被執行了1000次，其中謂詞條件被“@”代替，這樣透過這個函式，可以很容易找到共享池中哪些SQL沒有使用繫結變數。

ORACLE未繫結變數和硬解析過多問題處理

相關文章