loading
hello dummmy text
07Oct

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
  1. 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
  1. 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.

  1. 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.

  1. 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 🙂

4 Comment

  1. Suntrupth

    That is quite not right.

    SQL> alter system flush shared_pool;

    System altered.

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

    no rows selected

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

    DEPTNO DNAME LOC
    ———- ————– ————-
    30 SALES CHICAGO

    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

    SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like ‘select * from %where deptno=30%’;

    SQL_ID EXECUTIONS LOADS PARSE_CALLS
    ————- ———- ———- ———–
    ayjbpwwhpywm1 1 1 1
    d9679tr0wgqxa 1 1 1

    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

    SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like ‘select * from %where deptno=30%’;

    SQL_ID EXECUTIONS LOADS PARSE_CALLS
    ————- ———- ———- ———–
    ayjbpwwhpywm1 2 1 2
    d9679tr0wgqxa 1 1 1

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

    DEPTNO DNAME LOC
    ———- ————– ————-
    30 SALES CHICAGO

    SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like ‘select * from %where deptno=30%’;

    SQL_ID EXECUTIONS LOADS PARSE_CALLS
    ————- ———- ———- ———–
    f0ss08b66jdtv 1 1 1
    ayjbpwwhpywm1 2 1 2
    d9679tr0wgqxa 1 1 1

    You can notice that d9679tr0wgqxa is still being loaded the first time, i.e hard pace. Hard parse does take place even if the PLAN_HASH_VALUE isn’t changing.

    A little detail about Hard Parse:
    The text of the SQL statement being executed is hashed.

    If no matching hash value found, perform a hard parse.

    If matching hash value exists in shared pool, compare the text of matched statement in shared pool with the statement which has been hashed. They need to be identical character for character, white spaces, case, comments etc.

    Reply
  2. Pawan Kumar Yaddanapudi

    Loads = 1 says that it has been hard parsed ONCE so far, It doesn’t mean that it is undergoing hard parse everytime when you see 1 in the LOADS column. There is a cross relation with HASH_VALUE, PLAN_HASH_VALUE and then HARD PARSING which i wish to get the blog up ASAP.

    Reply
  3. Suntrupth

    Yes, the point is that if you are changing the SQL_TEXT, despite having the same execution plan, you are still going to have a hard parse the first time.
    SQL_ID, address in v$sqlarea, defines your parent cursor.. so select * from SCOTT.DEPT and select * from SCOTT.dept despite having the same PLAN_HASH_VALUE are still hard parsed. So your statement “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.” is not true. Hope it makes sense now.

    Reply

  4. Can you really flush Oracle SHARED_POOL? | OrSkl

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed