Tuning SQL statement is one of the major activities in performance tuning for every Oracle DBA. We would refer execution plan, SQL tuning advisor, SQL access advisor, Object statistics etc… to start identifying the issue with the SQL statement. In this process we often come across SQL_ID, HASH_VALUE and PLAN_HASH_VALUE details and use them for issue identification/resolution. This blog covers in detail on what exactly these 3 values mean and how Oracle database generates them.
To see how to generate execution plan, refer Ways of generating EXECUTION PLAN in Oracle Database.
1. SQL_ID: It represents SQL statement with unique set of 13 alpha numeric characters.
2. HASH_VALUE: Is unique numeric value to identify/search SQL statement in shared pool.
3. PLAN_HASH_VALUE: Is the numeric value for each new execution plan optimizer generates.
How are they generated?
• Oracle database uses MD5 algorithm on SQL text and generates 128 bit raw value. We use DBMS_CRYPTO package to generate this 128 bit raw value in our example later.
• Then it converts this raw value to 32 hex characters. We use RAWTOHEX function to do this in our example later.
• Then it picks up last 16 characters. We use SUBSTR to this job.
• From these 16 characters, first 8 characters and second 8 characters are bundled and reversed with in those 8 characters. We use SUBSTR to get this done.
• Then reversed characters are concatenated. We use || operator to do this.
• Then these hex characters are converted to the number. We use TO_NUMBER function.
• And then we index on BASE_32 to generate 13 alpha numeric characters.
Function to generate SQL_ID:
Let us take an example to verify the function.
Running a query from system schema:
Identify the SQL_ID generated by Oracle database for this query:
Now use the function we created on the same SQL statement and verify the output with system generated SQL_ID:
Wooh!!! That works.
HASH_VALUE is the unique SQL statement numeric value generated for every SQL statement runs in the database. This value is compared with the one in LIBRARY CACHE to identify if the query has to undergo hard parsing or soft parsing.
Oracle database first generates HASH_VALUE of SQL text and then generates SQL_ID of that statement.
Considering we already have SQL_ID in hand, run the below function to generate HASH_VALUE.
Let us prove it with the example.
Hash value generated by Oracle database for the SQL statement we ran before is:
SQL statement to generate HASH_VALUE from SQL_ID is:
This proves HASH_VALUE generated by database is the same as generated by our SELECT statement.
PLAN_HASH_VALUE is the distinct numeric value generated by Optimizer when generating a new execution plan of SQL statement.
How do we identify PLAN_HASH_VALUE of a query:
This query works only when SQL details are available in shared pool.
There are multiple scenarios to understand how PLAN_HASH_VALUE is related to SQL statement execution. It will be covered very much in detail in my next blog.