New SQL_ID ? Does not mean hard parsing or new EXECUTION plan

Oracle DBA’s often look at SQL_ID in the process of tuning an SQL statement that performs bad. Most of the DBA’s have a myth of “New SQL_ID of a SQL statement results in hard parsing generating new execution plan”. It is true that if the SQL statement is not found in the shared pool it results in hard parsing, but it does not mean that new SQL_ID of a statement results in hard parsing. SQL_ID and HASH_VALUE generated by Oracle database is case sensitive of SQL text but not PLAN_HASH_VALUE.

How Oracle instance generates SQL_ID, HASH_VALUE and PLAN_HASH_VALUE for each SQL statement and its hash value is explained in the blog  3 values you should know when tuning the SQL statement.

Ways of generating EXECUTION PLAN in Oracle Database explains the different ways of generating execution plan of SQL statement.

Lets prove:

1. Let us pick a simple query which results one record from the database. At this moment my shared pool is fresh and empty.

SQL> select * from scott.dept where deptno=30;
  DEPTNO   DNAME          LOC
———-   ————–    ———
     30        SALES           CHICAGO

2. From V$SQL let us identify SQL_ID, HASH_VALUE and PLAN_HASH_VALUE of this SQL statement.

SQL> select SQL_ID, HASH_VALUE, PLAN_HASH_VALUE from v$sql where sql_text like ‘select * from %where deptno=30%’;
SQL_ID           HASH_VALUE       PLAN_HASH_VALUE
————-              ———-               ————–
d9679tr0wgqxa    3251100586         2852011669

3. Now, let’s do some modification to the same query in terms of text (used upper case SCOTT.DEPT) but not the logic and re-run the query.

SQL> select * from SCOTT.DEPT where deptno=30;
 DEPTNO   DNAME         LOC
———-   ————– ————-
    30      SALES        CHICAGO

Definitely output will not change.

4. Let’s capture SQL_ID, HASH_VALUE and PLAN_HASH_VALUE of both the SQL’s we ran.

SQL> select SQL_ID, HASH_VALUE, PLAN_HASH_VALUE from v$sql where sql_text like ‘select * from %where deptno=30%’;
SQL_ID                 HASH_VALUE                PLAN_HASH_VALUE————-          ———-                 —————

ayjbpwwhpywm1      559903329                 2852011669

d9679tr0wgqxa      3251100586               2852011669

We can clearly make out from the output that though SQL_ID and HASH_VALUE changed, PLAN_HASH_VALUE of the query did not change which shows that instance did not generate a new EXECUTION plan.

5. Cleaning up the shared pool and re-run both the queries, then capture SQL_ID, HASH_VALUE and PLAN_HASH_VALUE.

SQL> alter system flush shared_pool;System altered.

SQL> select * from scott.dept where deptno=30;

  DEPTNO   DNAME            LOC

———-   ————–      ————-

     30        SALES           CHICAGO

SQL> select SQL_ID, HASH_VALUE, PLAN_HASH_VALUE from v$sql where sql_text like ‘select * from %where deptno=30%’;

SQL_ID               HASH_VALUE      PLAN_HASH_VALUE

————-                ———-             —————

d9679tr0wgqxa     3251100586        2852011669

SQL> select * from SCOTT.DEPT where deptno=30;

    DEPTNO   DNAME            LOC

———-   ————–   ————-

    30       SALES         CHICAGO

SQL> select SQL_ID, HASH_VALUE, PLAN_HASH_VALUE from v$sql where sql_text like ‘select * from %where deptno=30%’;

SQL_ID               HASH_VALUE         PLAN_HASH_VALUE

 ————-                   ———-                 —————

ayjbpwwhpywm1      559903329            2852011669

d9679tr0wgqxa      3251100586           2852011669

Conclusion:

• New SQL_ID, HASH_VALUE of same SQL statement is generated because of changes in format of the statement.
• Optimizer is intelligent enough to re-use the same EXECUTION plan when there are changes in text not the logic.
• PLAN_HASH_VALUE shows that even when the SQL_ID or HASH_VALUE is new for the same statement with few upper case characters the Optimizer will re-use the same EXECUTION plan.
• HASH_VALUE is always related to SQL_ID and these two values will never change until the database version changes even when SQL is not in the library cache. How?
• PLAN_HASH_VALUE in our example remains unchanged even after flushing shared pool. Why? Will be answered in my next blog