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.
- Let us pick a simple query which results one record from the database. At this moment my shared pool is fresh and empty.
- From V$SQL let us identify SQL_ID, HASH_VALUE and PLAN_HASH_VALUE of this SQL statement.
- 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.
Definitely output will not change.
- Let’s capture SQL_ID, HASH_VALUE and PLAN_HASH_VALUE of both the SQL’s we ran.
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.
- Cleaning up the shared pool and re-run both the queries, then capture SQL_ID, HASH_VALUE and PLAN_HASH_VALUE.
- 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 🙂