2

Oracle 的 SQL PLAN_HASH_VALUE=0

Kamus 2019-03-08
373
摘要:很多时候发现SQL的PLAN_HASH_VALUE=0,请问是什么意思?...

问题描述

很多时候发现SQL的PLAN_HASH_VALUE=0,请问是什么意思?

PIC 1.jpg


专家解答

这个是正常现象,主要发生在如下情况:

1、不带查询的INSERT/DELETE语句时,这些SQL无需执行计划

2、带绑定变量的SQL,仅进行了解析而没有实际执行,执行计划延迟生成。


参考MOS文档:Reasons for a SELECT to have a PLAN_HASH_VALUE of Zero (文档 ID 1587381.1)

The V$SQL view externalises various information about SQL statements that are being stored in the shared pool to users and one of these is the PLAN_HASH_VALUE which is a reference to the access path that has been generated for the a query. This is usually a large number but sometimes it is zero. When you are looking at a DELETE or an INSERT without a query, then a zero PLAN_HASH_VALUE is quite normal as there is no plan. In these cases no access path has been calculated and none is needed.

However it is possible to sometimes see a SELECT with a zero PLAN_HASH_VALUE which is less easy to explain.

The main reason for this is  key to this is that the query uses bind variables and the cursor has been parsed BUT the generation of an execution plan has been deferred until execution time since that is when the bind values will be supplied (which may have a significant bearing on the access path generated). Until the query is 'bound' and executed, there is no plan and so the PLAN_HASH_VALUE will be zero.

「喜欢文章,快来给作者赞赏墨值吧」

评论

1
2
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...