可以增加共享池大小，
如果从根本解决问题，还需要增加硬件资源。升级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没有使用绑定变量。