_cursor_obsolete_threshold（面试）

_cursor_obsolete_threshold参数控制共享池单个SQL游标数量，当SQL游标数操作该参数设置的值后会自动将父游标失效掉，重新生成新的父游标，但是SQL占用的内存在过期的游标没有被共享池purge出去的时候，内存并不会减少。当某一个子游标的SQL并发非常高的时候，这个子游标又被频繁标记为过期，那么重新解析的时候可能会出问题，就会引发问题。

SQL 执行过程

客户端进程将 SQL 语句通过监听器发送到 Oracle, 触发一个 Server process 生成，来对该客户进行服务。Server process 得到 SQL 语句之后，对 SQL 语句进行 Hash 运算，然后根据 Hash 值先到当前会话的 PGA 中查找是否存在匹配的缓存会话游标（Session Cursor），如果 PGA 中不存在则到 library cache 中查找是否存在匹配的父游标（Parent Cursor）和子游标（Child Cursor），如果都不存在则重新开始解析目标 SQL，选择最优执行计划，执行 SQL 并将结果返回给客户端。

硬解析

在当前会话的 PGA 中找不到匹配的缓存会话游标，在 SGA 的库缓存（Library Cache）中没有找到匹配的父游标或是找到了匹配的父游标没有找到对应子游标。那么 oracle 就会重新开始解析该目标 SQL，那么 Oracle 就会新生成一个会话游标和一对共享游标（即父游标和子游标）解析目标 SQL，这种方式我们称之为硬解析。

硬解析执行步骤

语法检查
权限与对象检查
优化器生成多个执行计划
选择最优的执行计划
将执行计划，SQL 文本等装载进 library cache 中的 heap 中

软解析

在当前会话的 PGA 中找不到匹配的缓存会话游标，但在库缓存中找到了匹配的父游标和子游标，那么 Oracle 会新生成一个会话游标并重用刚刚找到的父游标和子游标，直接调用解析树和执行计划解析目标 SQL，这种方式我们称之为软解析。
软解析（Soft Parse)是指Oracle在执行目标SQL时，在Library Cache中找到了匹配的父游标（Parent Cursor）和子游标（Child Cursor），并将存储在子游标中的解析树和执行计划直接拿过来重用而无须从头开始解析的过程。和硬解析相比，软解析的优势主要体现在如下这几个方面：
（1）软解析不会导致Shared Pool Latch的争用。因为软解析能够在库缓存中找到匹配的Parent Cursor和Child Cursor，所以它不需要生成新的Parent Cursor和Child Cursor.这意味着软解析根本就不需要持有Shared Pool Latch以便在Shared Pool中申请分配一块共享内存区域，既然不需要持有Shared Pool Latch，自然不会有Shared Pool Latch的争用，即Shared Pool Latch的争用所带来的系统性能和可扩展性的问题对软解析来说并不存在。
（2）软解析虽然也可能会导致库缓存相关Latch（如Library Cache Latch）和Mutex的争用，但软解析持有库缓存相关Latch的次数要少，而且软解析对某些Latch（如Library Cache Latch）的持有时间会比硬解析短，这意味着即使产生了库缓存相关Latch的争用，软解析的争用程度也没有硬解析那么严重，即库缓存相关Latch和Mutex的争用所带来的系统性能和可扩展性的问题对软解析来说要比硬解析少很多。我们在3.1.12节中己经介绍过：硬解析会先持有LibraryCacheLatch，并且在不释放LibraryCacheLatch的情况下持有Shared Pool Latch以便从Shared Pool中申请分配内存，成功申请后就会释放Shared Pool Latch，最后再释放Library Cache Latch。而软解析是不需要持有Shared Pool Latch的，所以与软解析比起来，硬解析持有Library Cache Latch的时间会更长，当然对Library Cache Latch争用的程度就会更严重。

正是基于上述两个方面的原因，如果OLTP类型的系统在执行目标SQL时能够广泛使用软解析，那么系统的性能和可扩展性就会比全部使用硬解析时有显著的提升，执行目标SQL时需要消耗的系统资源（主要体现在CPU上）也会显著降低。

软软解析

在当前会话的 PGA 中找到了匹配的缓存会话游标，那么此时 Oracle 就不再需要新生成一个会话游标，并且也不再需要像软解析那样得去 SGA 的库缓存中查找匹配的父游标了，因为 Oracle 此时可以重用找到的匹配会话游标，并且可以通过这个会话游标直接访问到该 SQL 对应的父游标获取解析树和执行计划解析目标 SQL，这种方式我们称之为软软解析。
软软解析前提条件
1、session_cached_cursors大于0
2、同一个session执行超过三次目标sql

软软解析（Soft Soft Parse）是指若参数SESSION_CACHED_CURSORS的值大于0，并且该会话游标所对应的目标SQL解析和执行的次数超过3次，则此时该会话游标会被直接缓存在当前会话的PGA中的。若该SQL再次执行的时候，则只需要对其进行语法分析、权限对象分析之后就可以直接从当前会话的PGA中将之前缓存的匹配会话游标直接拿过来用就可以了，这就是软软解析。 当一个SQL语句以硬解析的方式解析和执行完毕后，这个目标SQL所对应的共享游标（Shared Cursor）就己经被缓存在库缓存中，它所对应的会话游标（Session Cursor）也已使用完毕，这时候会根据参数SESSION_CACHED_CURSORS的不同而存在如下这两种情况：
①　如果参数SESSION_CACHED_CURSORS的值等于0，那么会话游标就会正常执行Close操作。在这种情况下，当同一条目标SQL再次重复执行时（显然是软解析），此时是可以找到匹配的共享游标的，但依然找不到匹配的会话游标（因为之前硬解析时对应的会话游标己经被Close掉了），这意味着Oracle还必须为该SQL新生成一个会话游标，并且该会话游标还会再经历一次Open、Parse、Bind、Execute、Fetch和Close中的一个或多个阶段。
②　如果参数SESSION_CACHED_CURSORS的值大于0，并且该会话游标所对应的目标SQL解析和执行的次数超过3次，那么Oracle就不会对会话游标执行Close操作，而是会将其标记为Soft Closed，同时将其缓存在当前会话的PGA中。这样做的好处是，当目标SQL再次被重复执行时，此时共享游标和会话游标就都能够找到匹配记录了，这意味着Oracle己经不需要为该SQL再新生成一个会话游标，而是只需要从当前会话的PGA中将之前己经被标记为Soft Closed的匹配会话游标直接拿过来用就可以了。显然，和软解析比，此时Oracle就省掉了Open一个新的会话游标所需要耗费的资源和时间。另外，Close一个现有会话游标也不需要做了（只需要将其标记为Soft Closed，同时将其缓存在当前会话的PGA中就可以了）。当然，剩下的Parse、Bind、Execute、Fetch还是需要做的，这个过程就是所谓的“软软解析”。 从上述分析过程可以看出，软软解析与软解析比起来，其好处主要体现在如下两个方面：
①　和软解析比，软软解省去了OPEN一个新的会话游标和CLOSE一个现有会话游标所需要耗费的资源和时间。
②　和软解析比，软软解析在持有库缓存相关Latch的次数方面会更少。这是因为缓存在PGA中的会话游标所在的Hash Bucket中己经存储了目标SQL的父游标的库缓存对象句柄地址，Oracle根据这个库缓存对象句柄地址就可以直接去库缓存中访问对应的父游标了，而不再需要先持有库缓存相关Latch，再去库缓存的相应Hash Bucket的父游标所在的库缓存对象句柄链表中查找匹配的父游标了，所以软软解析在持有库缓存相关Latch的次数方面会比软解析要少。

父游标和子游标

每个SQL在共享池中会有一个或多个父游标，一个父游标会有一个或多个子游标，总有效子游标数受_cursor_obsolete_threshold参数控制

模拟_cursor_obsolete_threshold参数对解析的影响

设置参数为只5

alter system set “_cursor_obsolete_threshold”=5 scope=spfile;

第一次加载SQL

select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;
var x char(1);
exec :x :=‘1’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第二次加载SQL

var x char(20);
exec :x :=‘1’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第三次加载SQL

var x char(50);
exec :x :=‘12’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第四次加载SQL

var x char(700);
exec :x :=‘121’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第五次加载SQL

var x char(1500);
exec :x :=‘124323’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

注意我们_cursor_obsolete_threshold参数设置为5,如果后续SQL则不会生产新的子游标。

重用一次

var x char(1500);
exec :x :=‘124323’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

超过五次后，会生成新的父游标，之前的均失效掉

var x varchar2(2);
exec :x :=‘21’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第二次重新加载

var x varchar2(30);
exec :x :=‘2123244234242424242342343’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第三次重新加载

var x varchar2(50);
exec :x :=‘212324423424242923445235325252332254242342343’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第四次重新加载

var x char(700);
exec :x :=‘121’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

第五次重新加载

var x char(1700);
exec :x :=‘121’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

再次超过五次后，有生成新的父游标

var x number;
exec :x :=‘1221’;
select /abc/ * From test where id=:x;
set linesize 500
select t.sql_id, t.ADDRESS, t.CHILD_ADDRESS, t.LOADED_VERSIONS, t.LOADS,EXECUTIONS,IS_OBSOLETE
from v$sql t
where t.sql_id = ‘1ftz12tzbzf2m’
order by t.ADDRESS,t.CHILD_ADDRESS;

结构如下图

假设某一个子游标的SQL执行频次特别高，而它被过期了，那么就需要重新生成新的子游标，那么就需要重新硬解析，会造成会话积压。

解决方案

_cursor_obsolete_threshold的值必须大于高频语句的version count数据，防止频繁失效，频繁硬解析，造成解析性能问题。