WHY we see PLAN_HASH_VALUE of Zero for a SELECT statement in Oracle

The V$ SQL view comes with information about SQL statements that are being stored in the participated 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 query. This is generally a large number but occasionally it's zero. When you're looking at a DELETE or an INSERT without a query, also a zero PLAN_HASH_VALUE is quite normal as there's no plan. In these cases, no access path has been calculated and none is required.

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 vital 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's when the bind values will be supplied (which may have a significant relevance on the access path generated). Until the query is bound and executed, there's no plan and so the PLAN_HASH_VALUE will be zero.

Example:

If we create a cursor containing a bind variable parse it, but then do not execute it and do not link any bind values to it, then it will defer the parsing until execution time (when you supply the binds). Here we have used DBMS_SQL.PARSE() to open a cursor using a test SQL statement containing a bind variable: 'select dummy zero from dual where dummy > :x' :

SQL> declare

cursor_name INTEGER;

begin

cursor_name := dbms_sql.open_cursor;

DBMS_SQL.PARSE(cursor_name, 'select dummy zero from dual where dummy > :x', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;

PL/SQL procedure successfully completed.

At this point, there is an entry placed in V$SQL for the parsed SQL statement, but because the plan generation has been deferred, the PLAN_HASH_VALUE is zero:

SQL> select sql_id ,sql_text, PLAN_HASH_VALUE from v$sql where sql_text like 'sel%zero%';