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.

Define:

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?

1. SQL_ID

• 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:

CREATE OR REPLACE FUNCTION GEN_SQLID (SQLTEXT IN CLOB)

RETURN VARCHAR2 IS

 BASE_32 CONSTANT VARCHAR2(32) := ‘0123456789abcdfghjkmnpqrstuvwxyz’;

 RAW128 RAW(128);  HEX32 VARCHAR2(32);  LAST16 VARCHAR(16);  FIRST8 VARCHAR2(8);  NEXT8 VARCHAR2(8);

 REV16 VARCHAR(16);  CONVNUM NUMBER;  INDX32 INTEGER;  SQL_ID VARCHAR2(13);

BEGIN

 RAW128 := /* USING MD5 algorithm to generate 128 bit raw value */

 SYS.DBMS_CRYPTO.hash(TRIM(CHR(0) FROM SQLTEXT)||CHR(0), SYS.DBMS_CRYPTO.hash_md5);

 HEX32 := RAWTOHEX(RAW128); /* Convert raw to 32 hex characters */

 LAST16 := SUBSTR(HEX32, 17, 16); /* get last 16 characters */

 FIRST8 := SUBSTR(LAST16, 1, 8); /* first 8 hex characters of 16 */

 NEXT8 := SUBSTR(LAST16, 9, 8); /* next 8 hex characters of 16 */

 /* reversing each pair in their places and then reverse these 4 pairs */

 FIRST8 := SUBSTR(FIRST8, 7, 2)||SUBSTR(FIRST8, 5, 2)||SUBSTR(FIRST8, 3, 2)||SUBSTR(FIRST8, 1, 2);

 NEXT8 := SUBSTR(NEXT8, 7, 2)||SUBSTR(NEXT8, 5, 2)||SUBSTR(NEXT8, 3, 2)||SUBSTR(NEXT8, 1, 2);

 /* concatenate these reversed characters */

 REV16 := FIRST8||NEXT8;

 /* Convert it to number */

 SELECT TO_NUMBER(REV16, ‘xxxxxxxxxxxxxxxx’) INTO CONVNUM FROM DUAL;

 /* 13 pieces base-32 (5 bits each) make 65 bits. we do have 64 bits */

 FOR i IN 1 .. 13

 LOOP

 INDX32 := TRUNC(CONVNUM / POWER(32, (13 – i))); /* index on BASE_32 */

 SQL_ID := SQL_ID||SUBSTR(BASE_32, (INDX32 + 1), 1); /* stitch 13 characters */

 CONVNUM := CONVNUM – (INDX32 * POWER(32, (13 – i))); /* for next piece */

 END LOOP;

 RETURN SQL_ID;

END GEN_SQLID;

/

Let us take an example to verify the function.

Running a query from system schema:

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

    DEPTNO DNAME          LOC

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

        30 SALES          CHICAGO

Identify the SQL_ID generated by Oracle database for this query:

SQL>select prev_sql_id from v$session where username=’SYSTEM’;

PREV_SQL_ID

————-

d9679tr0wgqxa

Now use the function we created on the same SQL statement and verify the output with system generated SQL_ID:

SQL>select GEN_SQLID(‘select * from scott.dept where deptno=30’) SQL_ID from dual;

SQL_ID

——————————————————————————–

d9679tr0wgqxa

Wooh!!! That works.

2. HASH_VALUE

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>select sql_id,hash_value from v$sql where sql_id=’d9679tr0wgqxa’;

SQL_ID        HASH_VALUE

————- ———-

d9679tr0wgqxa 3251100586

SQL statement to generate HASH_VALUE from SQL_ID is:

select
lower(trim(‘&&SQLID’)) sql_id
,trunc(mod(sum((instr(‘0123456789abcdfghjkmnpqrstuvwxyz’,substr(lower(trim(‘&&SQLID’)),level,1))-1)
                       *power(32,length(trim(‘&&SQLID’))-level)),power(2,32))) hash_value
from
dual
connect by
level <= length(trim(‘&&SQLID’));Enter value for sqlid: d9679tr0wgqxa
SQL_ID                                    HASH_VALUE
————- ————————————–
d9679tr0wgqxa                             3251100586

This proves HASH_VALUE generated by database is the same as generated by our SELECT statement.

3.PLAN_HASH_VALUE

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:

SQL>select PLAN_HASH_VALUE,sql_id from v$sql_plan where sql_id=’d9679tr0wgqxa’ and rownum=1;

PLAN_HASH_VALUE SQL_ID

—————      ————-

2852011669    d9679tr0wgqxa

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.