cursor

一，.Shared Cursor

1.v$sqlarea:查询父游标

    v$sql：查询子游标

2.父游标NAME为SQL文本，子游标NAME为空，NAMESPACE均为CRSR

3.SQL解析树和执行计划在子游标的Heap 6中

4.父游标Heap 0 的child table 存子游标地址

SQL查询执行计划过程

1.根据NAME和NAMESPACE进行哈希运算（Hash运算对大小写敏感）

2.匹配Hsh Bucket

3.匹配父游标（匹配NAME里存的SQL文本），找不到就生成父游标及其子游标

    注意：文本相同，用户不同，也要生成新的子游标

4.匹配子游标，并拿去执行计划，找不到就生成子游标挂载父游标下

二.硬解析

a.找不到父游标，需要生成父游标及其子游标

b.找到父游标，找不到子游标，需要生成子游标

硬解析缺点：

1.shared pool latch 争用，导致CPU100%

    硬解析需要在shared pool分配内存，用于生成父游标或者子游标，分配内存时需要持有shared pool latch，并发时容易引起争用。

2. Library Cache Latch和mutex争用

    硬解析需要访问句柄链表，这个动作需要持有相关latch和mutex，并发下引起争用。

硬解析过程：

    1.持有Library cache latch，扫描句柄链表，查找父游标

    2.释放Library cache latch（因为没找到）

    3.持有Library cache latch下持有Shared pool Latch 申请内存

    4.写入父游标或子游标

    5.释放Shared pool Latch

    6.释放Library cache latch

三.Session Cursor

1.session cursor缓存在PGA

2.不共享。

3.session_cached_cursor决定缓存在pga的cursor个数

4.SQL查询执行计划过程与shared cursor一样

四.软解析

软解析就是在share pool找到相应父游标和子游标，所以只需短时间持有Library cache latch，不需持有Shared pool Latch

五.软软解

1.直接在PGA匹配到了session cursor，无须去SGA匹配，省去大量闩锁申请持有时间。

2.直接使用标记为soft close的session cursor下的执行计划，省去游标的open阶段。

3.相同SQL执行解析三次以上，session cursor会被拷贝缓存在PGA中

六.总结SQL执行过程

1.根据NAME和NAMESPACE进行哈希运算（Hash运算对大小写敏感）

2.在PGA中匹配Hash Bucket（匹配session cursor），匹配到走软软解析，匹配不到去SGA匹配（匹配shared cursor）

3.在SGA匹配到走软解析，并把相应的父子游标拷贝生成到PGA；匹配不到在SGA分别生成父游标子游标，即硬解析

如果相同SQL执行解析三次以上，session cursor会被拷贝缓存在PGA中

4.SQL执行完毕后，按需求关闭session cursor

    参数session_cached_cursor=0时：session cursor直接close

    参数session_cached_cursor>0时：session cursor标记为soft close

    区别：soft close省去了打开cursor的open阶段（最耗时的阶段）

七.相关参数

    1.OPEN_CURSOR

        单个session中同时OPEN的session cursor总数

        查询：select count(*) from v$open_cursor 

        超出报错：OR-1000 maximun open cursors exceeded.

    2.session_cached_cursor

        单个session能够以soft close状态存在的session cursor数量

        超出无报错：LRU算法age out出不常用的session cursor

1.2.18Cursor_sharing设置

强制重用cursor，可以减少对shared_pool的争用

alter system set cursor_sharing=force sid='*' scope=spfile;

show parameter cursor_sharing;

1.2.19Open_cursors设置

设定每个session最多能同时打开多少个cursor

alter system set open_cursors=500 sid='*' scope=spfile;

1.2.20Session_cached_cursors设置

设定每个session最多可以缓存多少个关闭掉的cursor

Alter system set session_cached_cursors=200 sid='*' scope=spfile;

show parameter session_cached_cursors;

八.绑定变量

作用：减少硬解析（因Hash算法限制，SQL文本只要有一点不同都会导致Hash值不同，触发硬解析。对相同类型SQL非常不友好）

适用：OLTP（高并发，多重复类似SQL）

不适用：OLAP/DSS

九.绑定变量窥探

使用绑定变量后，所有解析均为例如对select * from t1 where n=:x诸如SQL解析，

不会随:x的不同解析出不同SQL，即形如上述SQL的都使用同一个执行计划。

问题：随着绑定变量输入值得不同，可选择率和结果集行数是不同的，最优执行计划也不同

1.窥探原理：

    硬解析时，oracle会实际窥探绑定变量的输入值，并且按照本次输入值的可选择率和结果集行数，生成最优的执行计划，但是之后绑定变量传入新值时，沿用之前硬解析时生成的执行计划

2.缺点：

    依赖第一次硬解析时的传入变量值，如果第一次传入值与以后大部分传入值得可选择率和结果集行数有较大差异，由于执行计划被固定下来，所以会可能走错误的执行计划，引起性能问题

3.重新窥探方法：（让SQL重新硬解析）

    1.DDL

       加个comment

        comment on table t1 is 'XXXXXXX'

    2.删除该sql的shared cursor

        select address,hash_value from v$sqlarea where sql_text like '%%'

        exec sys.dbms_shared_pool.purge('address,hash_value','c')；

4.注意：

    1.如果不适用绑定变量窥探，则选择率默认5%；

    2.绑定变量不可过多，查询替换时改写过多，导致整体SQL执行时间变长

5.绑定变量值的查询

    5.1硬解析

    5.2软解析/软软解：15min

1.

select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like '%%';

2.

select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='';

select snap_id,dbms_sqltune.extract_bind(bind_data,1).value_string bind1,dbms_sqltune.extract_bind(bind_data,2).value_string bind2 from dba_hist_sqlstat where sql_id='' order by snap_id;

select snap_id,name,position,value_string,last_captured from dba_hist_sqlbind where sql_id = '' order by snap_id

十.游标共享

    在SQL未使用绑定变量的情况下，不改变SQL文本，系统产生绑定变量替换where或者value后的值

    1.常规游标共享

        1.1.EXACT

            ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=BOTH;

            不使用游标共享，则每一个SQL硬解析一次，

        1.2SIMILAR

            ALTER SYSTEM SET cursor_sharing='SIMILAR' SCOPE=BOTH;

            会替换where和value后的值为绑定变量值，但是出现一下oracle认为不安全的谓词条件时，仍然会执行硬解析，出现父游标下挂许多子游标情况，不建议使用。

            12c后取消该参数值，影响数据泵效率。（10g之后自动收集直方图，直方图为不稳定因素）

            不安全的谓词条件：>  >=  < <=  between and  like% 直方图

        1.3FORCE

            ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;

            替换where和value后的值为绑定变量值，强制无条件重用执行计划

    2.自适应游标

            启动绑定变量窥探的前提下，生成多个子游标，供SQL自适应选择

        1.再次硬解析条件：

            1.1所耗费资源如逻辑读，CPU时间，结果集行数发生较大改变

            1.2谓词条件的可选择率是否在之前生成的范围内

        2.child-cursor标记为Bind-Sensitive条件（可能会随传入值变化而改变执行计划）

            2.1启动绑定变量窥探

            2.2SQL使用绑定变量（游标共享或者cursor_sharing打开）

            2.3不安全的谓词条件：范围查询，直方图统计信息

        3.Bind-Sensitive变为Bind-aware条件（确定会随传入值变化而改变执行计划）

            3.1已经标记为Bind-Sensitive

            3.2所耗费资源如逻辑读，CPU时间，结果集行数发生较大改变

            3.3谓词条件的可选择率是否在之前生成的范围内

         4.步骤

--查询sql_id

select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like '%%';

--查询子游标的runtime，执行次数，自适应游标相关属性

select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='';

--查询执行计划

select * from table(dbms_xplan.display_cursor('sql_id',0,'advanced'));

--查询子游标的选择率上下限

select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id ='';

        2.5缺点

            1.可能导致一定数量的额外的硬解析

            2.可能有过多的child_cursor挂载父游标上，增加软解析匹配child_cursor的工作量

        2.6关闭

--不会标记为bind_sensitive

_OPTIMAZER_EXTEND_CURSOR_SHARING

--不会标记为bind_aware

_OPTIMIZER_ADAPTIVE_CURSOR_SHARING