oracle数据库共享池繁忙,数据库异常繁忙，大量library cache lock、cursor: pin S wait on X等待...

”重编译包含 session cache cursor 软软解析的数量么？“

他这个case 主要是对一些PL/SQL对象的 重编译 recompile ，而非对CURSOR游标的

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create or replace procedure mac1 as begin null; end;

2  /

Procedure created.

SQL>

SQL> oradebug setmypid ;

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 1;

Statement processed.

SQL> alter procedure mac1 compile;

Procedure altered.

SQL> oradebug tracefile_name;

/s01/admin/G10R21/udump/g10r21_ora_471.trc

[root@vrh8 udump]# grep delete /s01/admin/G10R21/udump/g10r21_ora_471.trc

delete from procedureinfo$ where obj#=:1

delete from argument$ where obj#=:1

delete from procedurec$ where obj#=:1

delete from procedureplsql$ where obj#=:1

delete from procedurejava$ where obj#=:1

delete from vtable$ where obj#=:1

delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3

delete from idl_char$ where obj#=:1 and part=:2 and version<>:3

delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3

delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3

delete from ncomp_dll$ where obj#=:1 returning dllname into :2

delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3

delete from idl_char$ where obj#=:1 and part=:2 and version<>:3

delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3

delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3

delete from ncomp_dll$ where obj#=:1 returning dllname into :2

delete from idl_ub1$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from idl_sb4$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3

delete from idl_char$ where obj#=:1 and part=:2 and version<>:3

delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3

delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3

delete from ncomp_dll$ where obj#=:1 returning dllname into :2

delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece>:4) and version=:5

delete from error$ where obj#=:1

delete from settings$ where obj# = :1

delete from warning_settings$ where obj# = :1

delete from dependency$ where d_obj#=:1

delete from access$ where d_obj#=:1

对于cursor 游标的编译  一般称之为 parse

对于PL/SQL object的编译 一般称为 compile

若session cached cursor (PGA中)

关于session cached cursor

"游标缓存本身是保存在sga的shared pool共享池中的， 但是SESSION_CACHED_CURSOR指定的是session会话缓存游标的上限，主要体现在PGA的UGA中保存这些缓存游标的地址,如下文：

SQL> oradebug setmypid ;

Statement processed.

SQL> oradebug dump errorstack 4;

Statement processed.

trace content

—– Session Open Cursors —–

—– Session Cached Cursor Dump —–

—– Generic Session Cached Cursor Dump —–

———————————————————–

————– Generic Session Cached Cursors Dump ——–

———————————————————–

hash table=0x7f9baed440f0 cnt=4 LRU=0x7f9baed36ca0 cnt=4 hit=3 max=50 NumberOfTypes=6

type#0 name=DICTION count=0

type#1 name=BUNDLE count=0

type#2 name=SESSION count=4

type#3 name=PL/SQL count=0

type#4 name=CONSTRA count=0

type#5 name=REPLICA count=0

Bucket#051 seg=0x7f9baed44a78 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1

0 cob=0x7f9baed54940 idx=33 flg=0 typ=2 cur=0x7f9baed58308 lru=1 fl=1

Bucket#098 seg=0x7f9baed45348 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1

0 cob=0x7f9baed5c2b0 idx=62 flg=0 typ=2 cur=0x7f9baed5c3e0 lru=1 fl=1

Bucket#123 seg=0x7f9baed457f8 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1

0 cob=0x7f9baed5be18 idx=7b flg=0 typ=2 cur=0x7f9baed5bf48 lru=1 fl=1

Bucket#203 seg=0x7f9baed466f8 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1

0 cob=0x7f9baed5b980 idx=cb flg=0 typ=2 cur=0x7f9baed5bab0 lru=1 fl=1

———————————————————–

——Finished Dumping Generic Session Cached Cursors——

———————————————————–

在dedicated server模式下这些”Session Cached Cursors”在PGA的UGA中， 而在shared server模式下则UGA中SGA中。"

http://www.oracledatabase12g.com ... B5%81%E7%A8%8B.html

若session cached cursor 相关的cursor实体 失效或者被刷出shared pool ， 那么session cached cursor 也将失效。