cursor:pin S

lanseguhun

数据库发生大量的cursor:pin S 等待，有4个SQL执行次数较多，排出更改sql的方法，有什么其它优化方式?
已经打过apply patch 6904068 。附件AWR

Maclean Liu(刘相兵

_optimizer_adaptive_cursor_sharing        FALSE         
_optimizer_extended_cursor_sharing        UDO         
_optimizer_extended_cursor_sharing_rel        NONE

你试图禁用 11g acs 但是从AWR 看 你的 _optimizer_extended_cursor_sharing=UDO?

UNO是做什么？

据我所知 _optimizer_extended_cursor_sharing  典型值是 NONE


Lots of child cursors may be produced for a query with bind variables
appearing in at least two simple relational predicates
(eg. col <op> :b).  

v$sql_cs_selectivity shows that there are multiple equivalent / overlapping
ranges for a single cursor.  The number of ranges gets bigger and bigger
as the query is run more times.

Workaround
  Set "_optimizer_extended_cursor_sharing" to "none".

lanseguhun

_optimizer_extended_cursor_sharing    默认值是     UDO  
当时我试图更改过none，结果系统性能下降的一蹋糊涂只有以前的1/3

clevernby

1）换更好的机器，+CPU
2）减少SQL调用次数

我建议还是改SQL吧，小改也不行吗？这么原则啊..

Maclean Liu(刘相兵

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm like '%extended%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
_optimizer_extended_cursor_sharing
UDO
optimizer extended cursor sharing


看了下 _optimizer_extended_cursor_sharing  默认确实是 UDO


就AWR 看 硬解析非常少 ，

Library Hit %:         98.43        Soft Parse %:         100.00
Execute to Parse %:         97.93        Latch Hit %:         98.96

cursor pin s主要发生在 kksfbc即寻找子游标上， 且 就SQL ordered by Version Count 没有大量version count的游标

Mutex Type        Location        Sleeps        Wait Time (ms)
Cursor Pin        kksfbc [KKSCHLFSP2]        46,522        0
Cursor Pin        kksLockDelete [KKSCHLPIN6]        37,270        -101


但是该库是Linux x86 64-bit + 11.2.0.1

在11.2.0.1 上存在一些cursor pin s的bug :


Hdr: 12532025 11.2.0.1 RDBMS 11.2.0.1 DICTIONARY PRODID-5 PORTID-212
Abstract: CURSOR: PIN S EVENT CAUSING DB HANG

Hdr: 11767654 11.2.0.1 RDBMS 11.2.0.1 SHRD CRSRS PRODID-5 PORTID-212
Abstract: HUGE WAITS FOR "CURSOR: PIN S"

Hdr: 11767654 11.2.0.1 RDBMS 11.2.0.1 SHRD CRSRS PRODID-5 PORTID-212
Abstract: HUGE WAITS FOR "CURSOR: PIN S"


建议你先升级到 11.2.0.3.2 再观察

lanseguhun

正在处理oracle进程的优先级，貌似有些效果了，cursor：pin S从39%降到20%，事务量提高了10%