【SQL 基础】游标（curosr）

在最终用户程序（Client Application）中，游标（curosr）通常指和SQL语句关联的一个数据结构,
用于关联本地数据和存储存在数据库库缓存中的可执行语句等信息。
如在PL/SQL或Precompilers产品中对SQL语句的声明等。例如：以下SQL语句，在程序中都会生成一个程序游标（Program Cursor）。显式的游标：（显式定义一个游标）
EXEC SQL DECLARE SEL_CURS CURSOR FOR...隐式的游标：（没有明确定义游标，但会由内部自动生成一个游标）
EXEC SQL INSERT...

包含了【解析过的语句】以及【在处理该语句时需要使用的其它信息】的内存区域的句柄（handle）
或者名称。即:游标指SQL文在执行时使用的区域的本身或其名称。
一般Oracle在执行SQL时会自动地分配和释放游标。

私有游标（private cursor）:保存在SQL语句执行的用户会话进程（UGA）中，
包含着指向相关SQL语句的共享游标（shared cursor）地址的数据。（Client端）共享游标（shared cursor） :保存在库缓存（Library cache）中的，
包含SQL语句的文本以及执行计划等信息的数据集合。（Server端）

0. 查看是否有通过PL/SQL或Precompilers产品中的功能保存着共享游标的地址，
如果有则使用（程序端的游标缓存）。

1. 查看是否有打开着的私有游标（private cursor），
如果有则使用。（Client端）2. 如果1.中无打开着的私有游标，并且设定了SESSION_CACHED_CURSORS*参数的话，
查看会话中是否有缓存着的私有游标（private cursor），
如果有则使用（Client端的游标缓存）（软软解析）3. 如果2.中没有找到缓存着的私有游标，通过把SQL文转换为Hash值，
来查看在库缓存（Library cache）中是否有相同SQL文的共享游标，
如果有则使用以前的解析结果并再打开该游标。（软解析）4. 如果3.中找不到相同SQL文的共享游标，则装载SQL文到库缓存（Library cache），
重新进行解析。（硬解析）

---监视会话缓存状况
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count';---监视会话缓存的SQLselect c.user_name, c.sid, sql.sql_text
from   v$open_cursor c, v$sql sql
where  c.sql_id=sql.sql_id ；

父游标（Parent cursor）：主要包SQL 或PL/SQL 语句的文本内容等和特定的SCHEMA无关的信息。
子游标（Child cursor） ：主要包括SCHEMA、执行计划等信息。
所以对于一条SQL文可能会存在多个子游标。

v$sqlarea ：保存的是父游标的sql信息；列VERSION_COUNT，表示该父游标下的子游标个数。
v$sql     ：保存的是子游标的sql的信息；列CHILD_NUMBER，表示子游标的编号。

Column                     Description
SQL_ID                     SQL identifier
ADDRESS                    Address of the parent cursor
                          （Join to v$sqlarea.ADDRESS Version >=10g）    
KGLHDPAR                   （Join to v$sqlarea.ADDRESS Version <=9.2）    
CHILD_ADDRESS              Address of the child cursor
UNBOUND_CURSOR             (Y|N) The existing child cursor was not fully built
                          (in other words, it was not optimized)
CHILD_NUMBER               Child number
SQL_TYPE_MISMATCH          (Y|N) The SQL type does not match the existing child cursor
OPTIMIZER_MISMATCH         (Y|N) The optimizer environment does not match the existing child cursor
OUTLINE_MISMATCH           (Y|N) The outlines do not match the existing child cursor
STATS_ROW_MISMATCH         (Y|N) The existing statistics do not match the existing child cursor
LITERAL_MISMATCH           (Y|N) Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH  
EXPLAIN_PLAN_CURSOR        (Y|N) The child cursor is an explain plan cursor and should not be shared
FORCE_HARD_PARSE           (Y|N) For internal use
BUFFERED_DML_MISMATCH      (Y|N) Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH          (Y|N) PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH        (Y|N) Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH          (Y|N) The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)
TYPECHECK_MISMATCH         (Y|N) The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH         (Y|N) Authorization/translation check failed for the existing child cursor
BIND_MISMATCH               (Y|N) The bind metadata does not match the existing child cursor
DESCRIBE_MISMATCH          (Y|N) The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH          (Y|N) The language handle does not match the existing child cursor
TRANSLATION_MISMATCH       (Y|N) The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH  
INSUFF_PRIVS              (Y|N) Insufficient privileges on objects referenced by the existing child cursor
BIND_EQUIV_FAILURE        (Y|N) The bind value's selectivity does not match that used to optimize the existing child cursor
INSUFF_PRIVS_REM          (Y|N) Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH     (Y|N) The remote base objects of the existing child cursor do not match
LOGMINER_SESSION_MISMATCH   (Y|N) LogMiner Session parameters mismatch
INCOMP_LTRL_MISMATCH      (Y|N) Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
OVERLAP_TIME_MISMATCH     (Y|N) Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME
SQL_REDIRECT_MISMATCH  
MV_QUERY_GEN_MISMATCH      (Y|N) Internal, used to force a hard-parse when analyzing materialized view queries
EDITION_MISMATCH           (Y|N) Cursor edition mismatch
USER_BIND_PEEK_MISMATCH    (Y|N) Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan
TYPCHK_DEP_MISMATCH        (Y|N) Cursor has typecheck dependencies
NO_TRIGGER_MISMATCH        (Y|N) Cursor and child have no trigger mismatch
FLASHBACK_CURSOR           (Y|N) Cursor non-shareability due to flashback
LITREP_COMP_MISMATCH       (Y|N) Mismatch in use of literal replacement
ANYDATA_TRANSFORMATION     (Y|N) Is criteria for opaque type transformation and does not match
PDDL_ENV_MISMATCH          (Y|N) Environment setting mismatch for parallel DDL cursor (that is, one or more of the following parameter values have changed: PARALLEL_EXECUTION_ENABLED, PARALLEL_DDL_MODE, PARALLEL_DDL_FORCED_DEGREE, or PARALLEL_DDL_FORCED_INSTANCES)
INCOMPLETE_CURSOR          (Y|N) Cursor is incomplete: typecheck heap came from call memory
TOP_LEVEL_RPI_CURSOR       (Y|N) Is top level RPI cursor
DIFFERENT_LONG_LENGTH      (Y|N) Value of LONG does not match
LOGICAL_STANDBY_APPLY      (Y|N) Logical standby apply context does not match
DIFF_CALL_DURN             (Y|N) If Slave SQL cursor/single call
BIND_UACS_DIFF             (Y|N) One cursor has bind UACs and one does not
PLSQL_CMP_SWITCHS_DIFF     (Y|N) PL/SQL anonymous block compiled with different PL/SQL compiler switches
CURSOR_PARTS_MISMATCH      (Y|N) Cursor was compiled with subexecution (cursor parts were executed)
STB_OBJECT_MISMATCH        (Y|N) STB has come into existence since cursor was compiled
ROW_SHIP_MISMATCH  
CROSSEDITION_TRIGGER_MISMATCH   (Y|N) The set of crossedition triggers to execute might differ
PQ_SLAVE_MISMATCH         (Y|N) Top-level slave decides not to share cursor
TOP_LEVEL_DDL_MISMATCH    (Y|N) Is top-level DDL cursor
MULTI_PX_MISMATCH         (Y|N) Cursor has multiple parallelizers and is slave-compiled
BIND_PEEKED_PQ_MISMATCH   (Y|N) Cursor based around bind peeked values
MV_REWRITE_MISMATCH       (Y|N) Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
ROLL_INVALID_MISMATCH     (Y|N) Marked for rolling invalidation and invalidation window exceeded
OPTIMIZER_MODE_MISMATCH   (Y|N) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)
PX_MISMATCH (Y|N) Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.
MV_STALEOBJ_MISMATCH      (Y|N) Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built
FLASHBACK_TABLE_MISMATCH    (Y|N) Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
PLSQL_DEBUG               (Y|N) Value of the PLSQL_DEBUG parameter for the current session does not match the value used to build the cursor
LOAD_OPTIMIZER_STATS      (Y|N) A hard parse is forced in order to initialize extended cursor sharing
ACL_MISMATCH              (Y|N) Cached ACL evaluation result stored in the child cursor is not valid for the current session or user
FLASHBACK_ARCHIVE_MISMATCH  (Y|N) Value of the FLASHBACK_DATA_ARCHIVE_INTERNAL_CURSOR parameter for the current session does not match the value used to build the cursor
LOCK_USER_SCHEMA_FAILED    (Y|N) User or schema used to build the cursor no longer exists. Note: This sharing criterion is deprecated
REMOTE_MAPPING_MISMATCH    (Y|N) Reloaded cursor was previously remote-mapped and is currently not remote-mapped. Therefore, the cursor needs to be reparsed.
LOAD_RUNTIME_HEAP_FAILED    (Y|N) Loading of runtime heap for the new cursor (or reload of aged out cursor) failed
HASH_MATCH_FAILED           (Y|N) No existing child cursors have the unsafe literal bind hash values required by the current cursor
PURGED_CURSOR               (Y|N) Child cursor is marked for purging
BIND_LENGTH_UPGRADEABLE     (Y|N) Bind length(s) required for the current cursor are longer than the bind length(s) used to build the child cursor
USE_FEEDBACK_STATS          (Y|N) A hard parse is forced so that the optimizer can reoptimize the query with improved cardinality estimates
REASON                      Child number, id, and reason the cursor is not shared. The content of this column is structured using XML.

12.1.0.1 (Base Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 12 for Exadata Database
11.1.0.7.10 Database Patch Set Update
11.2.0.1 Patch 9 on Windows Platforms
11.1.0.7 Patch 43 on Windows Platforms

- 11.1.0.7
_cursor_features_enabled=18
event =  "106001 trace name context forever, level XXX  "- 11.2.0.1
_cursor_features_enabled=34
event = "106001 trace name context forever, level XXX "- 11.2.0.2
_cursor_features_enabled=1026
event =  "106001 trace name context forever, level XXX "- 11.2.0.3以后
_cursor_obsolete_threshold=XXX* 其中XXX 为限定的子游标个数

11.2.0.3: 100
11.2.0.4以后: 1024