Shared_Pool

v$sgastat 中shared pool miscellaneous 值每天都在增长相应shared pool free 的值一直在减少，造成最后没有free 空间，没发连接数据库的情况。
The most common cause is SQLs not using bind variables. alter system flush shared_pool periodically gives you relief temporarily, or set cursor_sharing to force (for 8i) if their bugs don't bother you. But the ultimate solution is to ask your developers to use bind variables. If the code is from third-party (meaning you can't control), that's out of the question.
一个复杂的查询语句执行时，1500M的share_poor_szie 20分钟就消耗完毕，然后提示查询执行失败。
你有没有观察过你的SESSION，看他们都干了些什么？此外，通过select * from v$db_object_cache where sharable_mem > 10000 你可以知道使用内存数据超过10000的SESSION是哪些

Binding
Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant or literal data, known when your program is compiled. For example, the following SQL statement, which adds an employee to a database contains several literals, such as 'BESTRY' and 2365:

INSERT INTO emp VALUES
(2365, 'BESTRY', 'PROGRAMMER', 2000, 20)


Hard coding a statement like this into an application would severely limit its usefulness. You would need to change the statement and recompile the program each time you add a new employee to the database. To make the program more flexible, you can write the program so that a user can supply input data at run time.
When you prepare a SQL statement or PL/SQL block that contains input data to be supplied at run time, placeholders in the SQL statement or PL/SQL block mark where data must be supplied. For example, the following SQL statement contains five placeholders, indicated by the leading colons (e.g., :ename), that show where input data must be supplied by the program.

INSERT INTO emp VALUES
(:empno, :ename, :job, :sal, :deptno)

经过很长时间在METALINK上的查找，我终于发现问题的关键了。
我遇到的问题本与你大同小异：一个超大SQL语句的执行会快速消耗share_pool_size,然后引发三种错误：
ORA-01037 maximum cursor memory exceeded错误
ORA-01041 internal error. HOSTDEF extension does not exist错误
ORA-04031错误。
然后，SQL执行失败。中间做过很多努力，调整参数如下：
a)open_cursors=50 --&gt 15000
b)sort_area_size=65536 --&gt8M
c)hash_area_szie=131072 --&gt16M
d)star_transformation_enabled = false --&gttrue
e)query_rewrite_enabled=false --&gttrue
f)SHARED_POOL_RESERVED_SIZE=10/ SHARED_POOL_SIZE
但都不能阻止share_poor_size如水般的流逝。

终于我做出了将optimizer_mode改为FIRST_ROWS的决定，使其在执行SQL时，做COST BASE的分析。幸运的事情来了，原有的查询语句消耗内存非常小，十分钟才减少10M左右。换一个更厉害的查询，仍是如此，旅试不爽。

l 关于内存参数的调整

关于参数调整，是oracle的复杂性的一个具体体现。通常来讲，我们更倾向于让客户做statspack报告，然后告诉我们os监控的状况，在这些的信息的基础上，再向客户索取具体的详细信息以诊断问题的所在。系统的调整，现在我们通常采用从等待事件入手的方法。因为一个系统感觉到慢，必然是在某个环节上出现等待，那么我们从等待最多的事件入手逐步诊断并解决问题。

对于内存的调整，相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先观察命中率

数据缓冲区命中率

SQL> select value from v$sysstat where name ='physical reads';

VALUE

----------

14764

SQL> select value from v$sysstat where name ='physical reads direct';

VALUE

----------

50

SQL> select value from v$sysstat where name ='physical reads direct (lob)';

VALUE

----------

0

SQL> select value from v$sysstat where name ='consistent gets';

VALUE

----------

167763

SQL> select value from v$sysstat where name = 'db block gets';

VALUE

----------

14305

这里命中率的计算应该是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

共享池的命中率

SQL> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

hit radio

----------

99.809291

假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存

关于排序部分

SQL> select name,value from v$sysstat where name like '%sort%';

NAME VALUE

---------------------------------------------------------------- ----------

sorts (memory) 67935

sorts (disk) 1

sorts (rows) 7070

SQL>

假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着sort_area_size部分内存较小，可考虑调整相应的参数。

关于log_buffer

SQL> select name,value from v$sysstat

2 where name in('redo entries','redo buffer allocation retries');

NAME VALUE

---------------------------------------------------------------- ----------

redo entries 2325719

redo buffer allocation retries 10

假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log_buffer

通常来说，内存的调整的焦点就集中在这几个方面，更多更详细的内容，建议从statspack入手来一步一步调整。最后关于内存的调整，再强调这一点，一定要结合操作系统来衡量，任何理论都必须要实践来检验。在操作系统中观察 page in/out 状况，发现问题严重，应该考虑调小SGA。

避免ORA-04031错误与SHARED POOL调整

ORA-04031错误&SHARED POOL调整

1.ORACLE Bug导致的ORA-04031:

BUG	Description	Workaround	Fixed
<Bug:1397603>	ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles.	_db_handles_cached = 0	8172, 901
<Bug:1640583>	ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access	Not available	8171, 901
Bug:1318267 Not Public	INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031	_SQLEXEC_PROGRESSION_COST=0	8171, 8200
Bug:1193003 Not Public	Cursors may not be shared in 8.1 when they should be	Not available	8162, 8170, 901
<Bug:2104071>	ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS)	None-> This is known to affect the XML parser.	8174, 9013, 9201
<Note:263791.1>	Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset		9205

2.编译java代码时出现ORA-4031错误

出 现ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")增大JAVA_POOL_SIZE到一个合适的值即可（一般100m足够）。

3.LARGE_POOL_SIZE 过小导致ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame")，增大LARGE_POOL_SIZE即可。

4.ORA-04031错误的高级分析

SESSION级：

SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 3';

INSTANCE级：

初始化参数中设置

event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"

5.调整SHARED_POOL_SIZE以避免ORA-04031错误。

1)使用DBMS_SHARED_POOL.KEEP把经常运行的package keep到共享池中。

2) 调整参数SHARED_POOL_RESERVED_SIZE大致为SHARED_POOL_SIZE的10％；但如果SHARED_POOL_SIZE 很大的话可以适当调小该参数；如果 SHARED_POOL_RESERVED_MIN_ALLOC低于default值的话，适当调大该参数，因为 SHARED_POOL_RESERVED_MIN_ALLOC较低会导致更多共享语句从SHARED_POOL_RESERVED_SIZE所指定的区 域中分配内存。

3)一些用于定位问题的脚本

A.定位应该使用绑定变量的sql语句

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

40表示sql语句的前40个字符是一样的，5表示执行次数小于5次，30表示在shared_pool_size中出现不下30次。

B.以sys用户执行以下语句

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0
/

X$KSMLRU 表显示那段内存的分配导致大多数的chunks从共享内存中aged out。

C.获得library cache hit
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE
/

D.显示在free list中可用的chunks
select '0 (<140)' BUCKET, KSMCHCLS, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, 20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, 1000*trunc(KSMCHSIZ/1000)
/

E.检查高版本计数的sql语句
SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
/


F.检查使用了多量的shared memory的sql语句

SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > memsize
/

参考文档：

【metalink 】Diagnosing and Resolving Error ORA-04031

【metalink 】Understanding and Tuning the Shared Pool

关于hard parses以及latches争用

sth about hard parses & latches.

2004-10-18

过多的hard parses会引起消耗系统资源,降低查询响应时间/latches争用/会引起shared_pool的碎片
以下查询用于监测执行过多hard parses的程序
column program format a20
select /*+ rule*/ s.program,count(*) users,sum(t.value) parses,sum(t.value)/count(*) parses_per_session,
sum(t.value)/(sum(sysdate - s.logon_time)*24) parses_per_hour
from v$session s,v$sesstat t
where t.statistic# = 153
and s.sid = t.sid
group by s.program having sum(t.value)/count(*) > 2.0
order by parses_per_hour desc
/

-- session等待事件
select /* +rule*/ substr(event,1,30) event , count(*)
from v$session_wait
where wait_time = 0
group by substr(event,1,30),state
/
如果latch free持续出现,表明有latch争用.
-- 下面查询获得latches存在争用
select /* +rule*/ name,count(*)
from v$latchholder
group by name
/
如果library cache或者shared pool持续出现,表明存在争用.

使用statspack等工具监测系统,在wait events事件中检查latch free事件,
如果事件的等待时间比例高于1%,表明存在latch争用.
假如很少的sql语句没有使用绑定变量,那么问题就可能就是shared_pool太小了.
通常的解决方法:
1.如果真的是shared pool大小.对于小范围的争用问题,增大20%是合适的.对于比较严重的问题,增大50%
2.考虑减少参数OPTIMIZER_MAX_PERMUTAIONS参数,该参数控制cbo用于决定最优执行计划时尝试的
最大的执行计划的数目.默认是80000
3.定期的flush shared pool,用于减少碎片.
4.把经常使用的PL/SQL函数和包pin到shared pool中.

2004-11-09

This script which is from oracle support provides information on latch performance.

========
Script 1:
========

SET ECHO off
REM NAME: TFSLATCH.SQL
REM USAGE:"@path/tfslatch"
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Virag Saksena, Craig A. Shallahamer, Oracle US
REM (c)1994 Oracle Corporation
REM ------------------------------------------------------------------------
REM Main text of script follows

ttitle -
center 'Latch Contention Report' skip 3

col name form A25
col gets form 999,999,999
col misses form 999.99
col spins form 999.99
col igets form 999,999,999
col imisses form 999.99

select name,gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets
,immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets
/

========
Script2:
========

SET ECHO off
REM NAME: TFSLTSLP.SQL
REM USAGE:"@path/tfsltslp"
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Virag Saksena, Craig A. Shallahamer, Oracle US
REM (c)1994 Oracle Corporation
REM ------------------------------------------------------------------------
REM Main text of script follows:

col name form A18 trunc
col gets form 999,999,990
col miss form 90.9
col cspins form A6 heading 'spin|sl06'
col csleep1 form A5 heading 'sl01|sl07'
col csleep2 form A5 heading 'sl02|sl08'
col csleep3 form A5 heading 'sl03|sl09'
col csleep4 form A5 heading 'sl04|sl10'
col csleep5 form A5 heading 'sl05|sl11'
col Interval form A12
set recsep off

select a.name
,a.gets gets
,a.misses*100/decode(a.gets,0,1,a.gets) miss
,to_char(a.spin_gets*100/decode(a.misses,0,1
,a.misses),'990.9')||
to_char(a.sleep6*100/decode(a.misses,0,1
,a.misses),'90.9') cspins
,to_char(a.sleep1*100/decode(a.misses,0,1
,a.misses),'90.9')||
to_char(a.sleep7*100/decode(a.misses,0,1
,a.misses),'90.9') csleep1
,to_char(a.sleep2*100/decode(a.misses,0,1
,a.misses),'90.9')||
to_char(a.sleep8*100/decode(a.misses,0,1
,a.misses),'90.9') csleep2
,to_char(a.sleep3*100/decode(a.misses,0,1
,a.misses),'90.9')||
to_char(a.sleep9*100/decode(a.misses,0,1
,a.misses),'90.9') csleep3
,to_char(a.sleep4*100/decode(a.misses,0,1
,a.misses),'90.9')||
to_char(a.sleep10*100/decode(a.misses,0,1
,a.misses),'90.9') csleep4
,to_char(a.sleep5*100/decode(a.misses,0,1
,a.misses),'90.9')||
to_char(a.sleep11*100/decode(a.misses,0,1
,a.misses),'90.9') csleep5
from v$latch a
where a.misses <> 0
order by 2 desc
/

我注意到
shared pool miscellaneous 147161588
这个是不正常的.,
有可能是bug或者内存有泄漏.
你过一段时间再检查一下, 看看这个值是不是越来越大!
如果是, 联系oracle support, 或者打上最新的OS 和oracle patch.

不断增长就是有麻烦.
正常情况下,这个部分是很少的. 几M就最多了.
它不释放, 所以shared pool空闲空间变小, 最终出错.
联系oracle support, 或者升级, patch吧.

如果你配置了mts
1：mts配置你确信是正确的？
2：配置了mts，large_pool_size 必须加大，shared_pool_size 可减小

large_pool_size = 614400 改为150M
shared_pool_size = 253440000 可减少150M

log_buffer = 5632000
改为最大3m就够了
再大没有任何意义
甚至，更小一点就ok了

share pool里有两部分library cache and data dictionary cache ,data dictionary cache 是基本固定的，有变化的是library cache,当有大量不重复的SQL时，会使library cache free mem变的很小，而这些一般是不会自动释放的，你可以手动flush；oracle保存这些是为了能让你下次执行同样的语句时不用再解释，而直接执行，一 般来讲你没有必要关心它空余，多少，如果不够，他会自动把最近很少用的清掉，不知道你明白没

应该说alter system flush shared_pool;可以暂时解决shared_pool中的碎片问题，大量的不能共享的SQL很快又会使碎片出现。解决办法是优化SQL，Keep 经常使用的包，cursor_sharing参数，在程序中注意共享cursor等等。这个命令不会影响DB的可用性吧，顶多造成短时间的性能下降，因为 parse过的SQL都给清出去了。

[@more@] http://blog.itpub.net/post/11/3265

http://blog.csdn.net/biti_rainy/archive/2004/07/03/learn_oracle_20040703_7.aspx

http://metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=263791.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/205087/viewspace-790579/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/205087/viewspace-790579/