开发者社区> 猎人笔记> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

V$SQL_SHARED_CURSOR查看硬解析的原因

简介: 在查看ADDM报告的时候,有个改善意见如下: Finding 2: Hard Parse Due to Sharing Criteria Impact is .
+关注继续查看

在查看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

RAW(4 | 8)

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

VARCHAR2(1)

(Y|N) The SQL type does not match the existing child cursor

OPTIMIZER_MISMATCH

VARCHAR2(1)

(Y|N) The optimizer environment does not match the existing child cursor

OUTLINE_MISMATCH

VARCHAR2(1)

(Y|N) The outlines do not match the existing child cursor

STATS_ROW_MISMATCH

VARCHAR2(1)

(Y|N) The existing statistics do not match the existing child cursor

LITERAL_MISMATCH

VARCHAR2(1)

(Y|N) Non-data literal values do not match the existing child cursor

FORCE_HARD_PARSE

VARCHAR2(1)

(Y|N) For internal use

EXPLAIN_PLAN_CURSOR

VARCHAR2(1)

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

BUFFERED_DML_MISMATCH

VARCHAR2(1)

(Y|N) Buffered DML does not match the existing child cursor

PDML_ENV_MISMATCH

VARCHAR2(1)

(Y|N) PDML environment does not match the existing child cursor

INST_DRTLD_MISMATCH

VARCHAR2(1)

(Y|N) Insert direct load does not match the existing child cursor

SLAVE_QC_MISMATCH

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) The existing child cursor is not fully optimized

AUTH_CHECK_MISMATCH

VARCHAR2(1)

(Y|N) Authorization/translation check failed for the existing child cursor

BIND_MISMATCH

VARCHAR2(1)

(Y|N) The bind metadata does not match the existing child cursor

DESCRIBE_MISMATCH

VARCHAR2(1)

(Y|N) The typecheck heap is not present during the describe for the child cursor

LANGUAGE_MISMATCH

VARCHAR2(1)

(Y|N) The language handle does not match the existing child cursor

TRANSLATION_MISMATCH

VARCHAR2(1)

(Y|N) The base objects of the existing child cursor do not match

BIND_EQUIV_FAILURE

VARCHAR2(1)

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

INSUFF_PRIVS

VARCHAR2(1)

(Y|N) Insufficient privileges on objects referenced by the existing child cursor

INSUFF_PRIVS_REM

VARCHAR2(1)

(Y|N) Insufficient privileges on remote objects referenced by the existing child cursor

REMOTE_TRANS_MISMATCH

VARCHAR2(1)

(Y|N) The remote base objects of the existing child cursor do not match

LOGMINER_SESSION_MISMATCH

VARCHAR2(1)

(Y|N) LogMiner Session parameters mismatch

INCOMP_LTRL_MISMATCH

VARCHAR2(1)

(Y|N) Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.

OVERLAP_TIME_MISMATCH

VARCHAR2(1)

(Y|N) Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME

EDITION_MISMATCH

VARCHAR2(1)

(Y|N) Cursor edition mismatch

MV_QUERY_GEN_MISMATCH

VARCHAR2(1)

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

USER_BIND_PEEK_MISMATCH

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) Cursor has typecheck dependencies

NO_TRIGGER_MISMATCH

VARCHAR2(1)

(Y|N) Cursor and child have no trigger mismatch

FLASHBACK_CURSOR

VARCHAR2(1)

(Y|N) Cursor non-shareability due to flashback

ANYDATA_TRANSFORMATION

VARCHAR2(1)

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

INCOMPLETE_CURSORFoot 1

VARCHAR2(1)

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

PDDL_ENV_MISMATCHFoot 2

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) Is top level RPI cursor

DIFFERENT_LONG_LENGTH

VARCHAR2(1)

(Y|N) Value of LONG does not match

LOGICAL_STANDBY_APPLY

VARCHAR2(1)

(Y|N) Logical standby apply context does not match

DIFF_CALL_DURN

VARCHAR2(1)

(Y|N) If Slave SQL cursor/single call

BIND_UACS_DIFF

VARCHAR2(1)

(Y|N) One cursor has bind UACs and one does not

PLSQL_CMP_SWITCHS_DIFF

VARCHAR2(1)

(Y|N) PL/SQL anonymous block compiled with different PL/SQL compiler switches

CURSOR_PARTS_MISMATCH

VARCHAR2(1)

(Y|N) Cursor was compiled with subexecution (cursor parts were executed)

STB_OBJECT_MISMATCH

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) The set of crossedition triggers to execute might differ

PQ_SLAVE_MISMATCH

VARCHAR2(1)

(Y|N) Top-level slave decides not to share cursor

TOP_LEVEL_DDL_MISMATCH

VARCHAR2(1)

(Y|N) Is top-level DDL cursor

MULTI_PX_MISMATCH

VARCHAR2(1)

(Y|N) Cursor has multiple parallelizers and is slave-compiled

BIND_PEEKED_PQ_MISMATCH

VARCHAR2(1)

(Y|N) Cursor based around bind peeked values

MV_REWRITE_MISMATCH

VARCHAR2(1)

(Y|N) Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view

ROLL_INVALID_MISMATCH

VARCHAR2(1)

(Y|N) Marked for rolling invalidation and invalidation window exceeded

OPTIMIZER_MODE_MISMATCH

VARCHAR2(1)

(Y|N) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)

PX_MISMATCH

VARCHAR2(1)

(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

VARCHAR2(1)

(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

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) Mismatch in use of literal replacement

PLSQL_DEBUG

VARCHAR2(1)

(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

VARCHAR2(1)

(Y|N) A hard parse is forced in order to initialize extended cursor sharing

ACL_MISMATCH

VARCHAR2(1)

(Y|N) Cached ACL evaluation result stored in the child cursor is not valid for the current session or user

FLASHBACK_ARCHIVE_MISMATCH

VARCHAR2(1)

(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 (请备注数据库)

ORACLE技术博客:ORACLE 猎人笔记 http://blog.itpub.net/12679300/

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RH358管理DNS和DNS服务器--使用Unbound配置缓存名称服务器
RH358管理DNS和DNS服务器--使用Unbound配置缓存名称服务器
151 0
【EventBus】EventBus 源码解析 ( 注册订阅者 | 注册订阅方法详细过程 )
【EventBus】EventBus 源码解析 ( 注册订阅者 | 注册订阅方法详细过程 )
35 0
【EventBus】EventBus 源码解析 ( 注册订阅者 | 注册订阅方法详细过程 )
【EventBus】EventBus 源码解析 ( 注册订阅者 | 注册订阅方法详细过程 )
28 0
spring boot 2.0以上为Eureka添加用户认证-查看Eureka的元数据
Eureka Server(注册中心)是运行匿名访问的,本节为了安全,添加一个需要登陆才能访问的功能。同时在spring boot 2.0版本可以查看元数据。
29 0
Tomcat实现热部署、热加载原理解析(下)
Tomcat实现热部署、热加载原理解析
98 0
shell 命令管理tomcat
我们一般在linux 上的生产环境管理tomcat 是使用tomcat 目录下面的几个sh 文件来控制的,有时候tomcat 进程会没有完全结束,然后我们再次启动很容易咋成启动二个tomcat 这里集成一个shell 命令来管理。 # description: Auto-starts tomcat Dir="/tomcat/" echo $Dir RETVAL="0
1212 0
基于HT for Web矢量实现2D叶轮旋转
之前在拓扑上的应用都是些静态的图元,今天我们将在拓扑上设计一个会动的图元——叶轮旋转。 我们先来看下这个叶轮模型长什么样   从模型上看,这个叶轮模型有三个叶片,每一个叶片都是不规则图形,显然无法用上我们HT for Web的基础图形来拼接,那么我们该怎么做呢?很简单,在HT for Web中提供了自定义图形的方案,我们可以通过自定义图形来绘制像叶片这种不规则图形。
928 0
CentOS下yum安装mysql,jdk以及tomcat
首先说明,服务器是阿里云的,centos6.3_64位安全加固版。首先需要登陆进来,使用的是putty,因为最初的时候,Xshell登陆会被拒绝。 0. 创建个人文件夹 # 使用 yum 安装tomcat 和 mysql # 创建文件夹 cd /usr/local mkdir renfufei 1.
1022 0
+关注
猎人笔记
开源技术专家
237
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载