shared_pool达到100G，出现library cache latch阻塞

bobjoy

现象：shared_pool接近100g，超过了buffer cache。v$sql中有接近200w的记录。
          有时会出现大量的latch:library cache等待，导致系统业务无法进行。尤其是查询v$session视图也会被阻塞。
           系统的开发工作确实是不太好，硬解析很多，绑定变量正在优化。
           系统并未发现04031。
信息：
   版本：10.2.0.4.4
   系统：hpux 11.31 内存280G
   日志：awr报告，以及我做一个v$session查询被阻塞的10046 trace。

疑问：出现阻塞时，查看v$sgastat中的shared_pool还是有很多的free内存的。
          什么时机才会把share pool中的语句交换出现？
          虽然硬解析多，但是share pool扩张的太大了，oracle是否存在管理成本。

[ 本帖最后由 bobjoy 于 2012-8-10 01:48 编辑 ]

Maclean Liu(刘相兵

Parses:         614.64         17.22
Hard parses:         61.27         1.72
Soft Parse %:         90.03  

每秒 硬解析61次 ，软解析率 90%,这是一个 高并发 但是解析优化做的有问题的应用

Event        Waits        Time(s)        Avg Wait(ms)        % Total Call Time        Wait Class
latch: library cache         951,604         141,921         149         60.6        Concurrency
latch: shared pool         522,808         35,822         69         15.3        Concurrency


主要等待是 解析等待， 硬解析占 16%的db time , 总解析时间占48.8%

sql execute elapsed time        69,563.36        29.72
hard parse elapsed time        37,274.06        15.93
parse time elapsed        114,247.58        48.82   

Maclean Liu(刘相兵

b058ymxj1rvkg
SELECT sql_id, sql_text from v$sql WHERE sql_id in (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
0ayugm2ag2ag2
select * from gv$session where event like '%latch: shared pool%'






explain plan set statement_id = 'SGPMS' for select null from dual 这样一个语句居然占用了8MB

Maclean Liu(刘相兵

shared pool的 sql area 占用了大约45GB的空间

advisor:

1. 考虑使用cursor_sharing=FORCE

2. 考虑调大session_cached_cursors 目前为40

3. 考虑关闭shared pool duration 特性

bobjoy

谢谢！
我们先优化应用，然后调整session_cached_cursor看看效果如何。
shared pool duration 的特性还不是很明白。

fufuh2o

session_cached_cursor 只能缓解,不能解决,可跑下sgastatx看下