什么是Library cache（库缓存）
学习过ORACLE体系结构的我们都明白library cache是Oracle共享池（share pool）中的重要组成部分，主要存放shared curosr（SQL）和PLSQL对象（function，procedure，trigger）的信息，以及这些对象所依赖的table，index，view等对象的信息。
Library存放的信息单元都叫做对象，这些对象可以分为两类：
1. 存储对象
2. 过渡对象(游标Cursor,这里的游标指生成的可执行的对象， 运行相同SQL的多个进程可以共享该SQL产生的游标，节省内存。)
A. 用户提交的SQL
B. SQL语句相关的解析树
C. 执行计划
D. 用户提交的PL/SQL程序块(包括匿名程序块，procedure，packages，function等)
E. PL/SQL对象依赖的table,index,view等对象
F. 控制结构：lock,pin,dependency table 等
share pool简单描述：
由于SQL的重复使用率很高，如果对于新的一条SQL语句都进行重复的解析工作就会造成性能的浪费。 因此oracle会将用户提交的SQL语句缓存在内存中，解析SQL语句时都会先在内存中检索是否有相同的SQL语句如果相同则可以减少最重要的解析工作〔也就是生成执行计划)从而节省了大量的资源；反之如果没有找到相同的SQL语句，则必须重新从头到尾进行完整的解析过程，并将解析结果存放在内存当中。而内存之中这部分存放SQL语句的部分就叫做共享池( shared pool)当然shared pool里不仅仅是SQL语句，还包括管理shared pool的内存结构以及执行计划、控制信息等等内存结构，oracle通过内存结构管理。
提交的SQL语句，在shared pool中是找不到匹配的项，需要从头解析。硬解析是资源集中型操作，因此扩展性不强，因为一个语句解析操作会包含大量复杂的工作，大量的硬解析会拉低数据库性能。
提交的SQL语句，在shared pool中能够找到匹配的项，无需重复解析。这个匹配的项可以是另一个用户之前执行后生成的。SQL语句被共享，实现性能提升。
library cache pin和library cache lock
上文提到过一个实例中的library cache存放shared curosr（SQL）和PLSQL对象（function，procedure，trigger）的信息，以及这些对象所依赖的table，index，view等对象的信息。而这些对象不能在他们被使用的时候被改变,所以在使用这些对象的时候，系统会给其分配一个机制锁library cache locks /pins的机制锁住。一个会话中,需要使用一个对象,会在该对象上先得到一个lock(null, shared or exclusive模式的)这是为了,防止其他会话重复访问这个对象。
locks/pins会在SQL语句执行期间一直保持,在结束的时候才释放。Lock锁的等级比pin要高，不过总的来说，library cache pin和library cache lock一样都是用于共享池share pool的并发控制的，可以看作是一种锁。但是library cache pin用于管理cache 的一致性。要想访问library cache 中的对象，必须先lock住library cache object handle，然后pin住对象的data heap本身。pin和lock请求在被许可之前都需要一直等待。这就有可能引起资源的竞争，因为两种请求都不存在NOWAIT请求模式。
library cache lock模式
- library cache lock有三种模式，分别为share、exclusive、null。
share模式的锁：只是读取object对象的情况
exclusive模式的锁：修改object对象的情况
Null锁：维护object的依赖对象，在访问object的时候会一直持有。
注意：Null锁在持有期间，object的依赖对象可以被其他进程访问甚至修改，通常，如果依赖对象被修改，则Null锁被打破，object会被标记为invalid。object下次被访问时，则需要重新解析成二进制可执行文件。
- library cache pin模式
library cache pin有两种模式，分别是share和exclusive。
share模式：和lock一样，用于只读访问
exclusive模式：和lock一样，用于修改。
注意：但无论只读访问还是修改，总是要先获取object 的share lock，进行错误检查和安全性检查，然后，如果需要修改object，再追加exclusive lock。如果一个object只需要只读访问，就绝不会被exclusive pin。
可能遇到的问题
在获取library cache lock的过程中，如果发生争用，则等待library cache lock事件。当大量library cache lock等待出现时，很可能对数据库的性能造成较大的影响。
产生library cache lock的原因通常有三种：登录密码错误尝试过多、热表收集统计信息和SQL解析失败。
实际案例参考
* select p1,p2,p3 from v$session where event like 'library cache%' group by p1,p2,p3
P1 P2 P3
-------------- -------------- --------------
49601574488 49352185544 5177346
49601574488 49446978280 5177346
49601574488 48907112336 5177347
49601574488 49311819416 5177346
49601574488 49402524488 5177346
49601574488 49562210808 5177346
49601574488 49585118672 5177346
49601574488 49365878936 5177346
49601574488 49458574840 5177346
49601574488 49600343744 5177346
49601574488 49760218560 5177346
49601574488 49366181232 5177346
49601574488 49769319336 5177346
49601574488 49594074392 5177347
49601574488 48915512304 5177346
49601574488 49315806344 5177346
49601574488 49590133056 5177346
SQL> @dec 5177346
DEC HEX
----------------------------------- --------------------
5177346.000000 4F0002
SQL> @hex 4f
DEC HEX
----------------------------------- --------------------
79.000000 4F
SQL> SELECT indx,kglstdsc FROM x$kglst WHERE kglsttyp='NAMESPACE' and indx=79;
INDX KGLSTDSC
-------------- ----------------------------------------------------------------
79 ACCOUNT_STATUS 《《《《
SQL> show parameter event
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
event string
xml_db_events string enable
-- 创建密码失败认证的trigger 打印 客户端信息
diag/clients/user_oracle/host_2108801384_80
adrci> set home diag/rdbms/echandb/echandb1
adrci> show alert -tail -f
2022-09-08 17:03:49.937000 +08:00
2022-09-08 17:03:49 Password Erro: logon denied from 10.230.158.4 47855 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:03:55.961000 +08:00
2022-09-08 17:03:55 Password Erro: logon denied from 10.230.158.4 48567 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:02.965000 +08:00
2022-09-08 17:04:02 Password Erro: logon denied from 10.230.158.4 48078 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:02 Password Erro: logon denied from 10.230.158.4 49486 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:02 Password Erro: logon denied from 10.230.158.4 50508 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:04.050000 +08:00
2022-09-08 17:04:04 Password Erro: logon denied from 10.230.158.4 52199 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:05 Password Erro: logon denied from 10.230.158.4 52201 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:07.073000 +08:00
2022-09-08 17:04:07 Password Erro: logon denied from 10.230.158.4 52203 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:10.092000 +08:00
2022-09-08 17:04:10 Password Erro: logon denied from 10.230.158.4 52522 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:14.097000 +08:00
2022-09-08 17:04:14 Password Erro: logon denied from 10.230.158.4 52258 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:14 Password Erro: logon denied from 10.230.158.4 53185 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:14 Password Erro: logon denied from 10.230.158.4 53191 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:14 Password Erro: logon denied from 10.230.158.4 53197 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
2022-09-08 17:04:15.129000 +08:00
2022-09-08 17:04:15 Password Erro: logon denied from 10.230.158.4 53195 User:root with JDBC Thin Client – JDBC Thin Client dbuser:HBPLAT
错误密码认证来自10.230.158.4 IP, 用户名 HBPLAT
SQL> select account_status from dba_users where username='HBPLAT';
ACCOUNT_STATUS
--------------------------------
OPEN
SQL> select * from dba_users where username='HBPLAT';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP
----------- -------------- ------------------------------ -------------------------------- ----------------- ----------------- ------------------------------ ------------------------------ ----------------- ------------------------------ ------------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PASSWORD E AUTHENTI
-------- - --------
HBPLAT 484 OPEN TBS_NGESHOP_DAT TEMP 20191127 15:12:03 ECHAN DEFAULT_CONSUMER_GROUP
10G 11G N PASSWORD
SQL> select * from dba_profiles where PROFILE='ECHAN';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
ECHAN PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION1
ECHAN PASSWORD_GRACE_TIME PASSWORD UNLIMITED
ECHAN PASSWORD_LOCK_TIME PASSWORD UNLIMITED
ECHAN PASSWORD_REUSE_MAX PASSWORD UNLIMITED
ECHAN PASSWORD_REUSE_TIME PASSWORD UNLIMITED
ECHAN PASSWORD_LIFE_TIME PASSWORD UNLIMITED
ECHAN FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED <<<<<<<
ECHAN PRIVATE_SGA KERNEL UNLIMITED
ECHAN CONNECT_TIME KERNEL UNLIMITED
ECHAN IDLE_TIME KERNEL UNLIMITED
ECHAN LOGICAL_READS_PER_CALL KERNEL UNLIMITED
ECHAN LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
ECHAN CPU_PER_CALL KERNEL UNLIMITED
ECHAN CPU_PER_SESSION KERNEL UNLIMITED
ECHAN SESSIONS_PER_USER KERNEL UNLIMITED
ECHAN COMPOSITE_LIMIT KERNEL UNLIMITED
16 rows selected.
当前用户是无限错误密码尝试，所以未导致用户锁定。
