V$SQL_SHARED_CURSOR檢視硬解析的原因

在檢視ADDM報告的時候，有個改善意見如下:

Finding 2: Hard Parse Due to Sharing Criteria

Impact is .13 active sessions, 30.49% of total activity.

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

SQL statements with the same text were not shared because of cursor

environment mismatch. This resulted in additional hard parses which were

consuming significant database time.

Common causes of environment mismatch are session NLS settings, SQL trace

settings and optimizer parameters.

Recommendation 1: Application Analysis

Estimated benefit is .13 active sessions, 30.49% of total activity.

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

Action

Look for top reason for cursor environment mismatch in

V$SQL_SHARED_CURSOR.

解讀如下：

一、問題說明：SQL在解析的時候如果庫緩沖池中已經有意義的SQL語句，這時候就用通過一個父遊标，如果相應的環境資訊也一樣，那麼就公用一個子遊标，這個時候就是軟解析。從ADDM報告顯示出來的資訊表明，父遊标的資訊都是一樣的，但是由于某種原因導緻了不能共用子遊标，而産生了硬解析；

二、原因查找：AWR報告也提供了查找原因的方法，通過檢視視圖V$SQL_SHARED_CURSOR

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

V$SQL_SHARED_CURSOR這個視圖解釋為什麼一個特定的子遊标不能共享已經存在的子遊标，視圖按照列的原因進行分類，這些列有兩個選項Y|N；

Column

Datatype

Description

SQL_ID

VARCHAR2(13)

SQL identifier

ADDRESS

RAW(4 | 8)

Address of the parent cursor

CHILD_ADDRESS

Address of the child cursor

CHILD_NUMBER

NUMBER

Child number

UNBOUND_CURSOR

VARCHAR2(1)

(Y|N) The existing child cursor was not fully built (in other words, it was not optimized)

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

FORCE_HARD_PARSE

(Y|N) For internal use

EXPLAIN_PLAN_CURSOR

(Y|N) The child cursor is an explain plan cursor and should not be shared

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

BIND_EQUIV_FAILURE

(Y|N) The bind value's selectivity does not match that used to optimize the existing child cursor

INSUFF_PRIVS

(Y|N) Insufficient privileges on objects referenced by 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

EDITION_MISMATCH

(Y|N) Cursor edition mismatch

MV_QUERY_GEN_MISMATCH

(Y|N) Internal, used to force a hard-parse when analyzing materialized view queries

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

ANYDATA_TRANSFORMATION

(Y|N) Is criteria for opaque type transformation and does not match

(Y|N) Cursor is incomplete: typecheck heap came from call memory

(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)

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 is an internal name for a SQL Management Object Mismatch. A SQL Management Object Mismatch means that either a SQL plan baseline, or a SQL profile, or a SQL patch has been created for your SQL statement between the executions. Because a cursor is a read-only entity, a hard parse is forced to be able to create a new cursor that contains information about the new SQL management object related to this SQL statement.

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

LITREP_COMP_MISMATCH

(Y|N) Mismatch in use of literal replacement

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

根據上面的視圖，很快就可以找到不能共享子遊标的原因。

總結：最好的學習是實踐，最好的實踐機會是系統剛上線的時候，有幸經曆了這麼一個時刻，把之前快忘記的ORACLE知識又重新腦補了一下，也是以把之前比較零星的一些知識拼湊成了一個知識面。

*********************************************************************************************************************

本文作者：JOHN QQ：1916066696 （請備注資料庫）

******************************************************************************************************