暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片

Oracle 的 SQL PLAN_HASH_VALUE=0

原创 Kamus 2019-03-08
2402

问题描述

很多时候发现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.

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论