hash value for sql statement

Question

When we execute any sql statement in Oracle, a hash value is being assigned to that sql statement and stored into the library cache. So, that later, if another user request the same query, then Oracle find the hash value and execute the same execution plan. But, I have one doubt about the hash value. I mean, how hash value gets generated ?, I mean, whether Oracle server uses some algorithms or they just convert the sql string into some numeric value.

Since, I was reading Pro Oracle SQL book, on which it is written that,

select * from employees where department_id = 60;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;

select /* a_comment */ * from employees where department_id = 60;

will return different hash value, because when sql statement executed, then Oracle first converts the string to a hash value. But, when i tried this, then it return same hash value.

SQL> select * from boats where bid=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOATS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | B_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BID"=10)

SQL> SELECT * FROM BOATS WHERE BID=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOATS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | B_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BID"=10)

Justin Cave · Accepted Answer · 2013-04-15T10:04:31.740

In the text of your question, you appear to be describing the sql_id and/or the hash_value. This is the hash of the text of the SQL statement and is what Oracle uses to determine whether a particular SQL statement already exists in the shared pool. What you are showing in your example, however, is the plan_hash_value which is the hash of the plan that is generated for the SQL statement. There is, potentially, a many-to-many relationship between the two. A single SQL statement (sql_id/ hash_value) can have multiple different plans (plan_hash_value) and multiple different SQL statements can share the same plan.

So, for example, if I write two different SQL statements that are querying a particular row from the EMP table, I'll get the same plan_hash_value.

SQL> set autotrace traceonly;
SQL> select * from emp where ename = 'BOB';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='BOB')


SQL> ed
Wrote file afiedt.buf

  1* select * FROM emp WHERE ename = 'BOB'
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='BOB')

If I look in v$sql, however, I'll see that two different sql_id and hash_value values were generated

SQL> set autotrace off;
SQL> ed
Wrote file afiedt.buf

  1  select sql_id, sql_text, hash_value, plan_hash_value
  2    from v$sql
  3   where sql_text like 'select%BOB%'
  4*    and length(sql_text) < 50
SQL> /

SQL_ID        SQL_TEXT                                 HASH_VALUE PLAN_HASH_VALUE
------------- ---------------------------------------- ---------- ---------------
161v96c0v9c0n select * FROM emp WHERE ename = 'BOB'      28618772      3956160932
cvs1krtgzfr78 select * from emp where ename = 'BOB'    1610046696      3956160932

Oracle recognizes that these two statements are different queries with different sql_id and hash_value hashes. But they both happen to generate the same plan so they end up with the same plan_hash_value.

Which means, both sql statement should have different hash value. And, according to Oracle, if hash value is not available in library cache, it will re-parse and generate new plan for the sql statement ? Isn't ? — Ravi, Apr 15 '13 at 09:59
@jWeavers - Correct. I updated the example to display the `hash_value` in addition to the `sql_id`. `hash_value` is a bit old-school, `plan_id` is the easier to use as an identifier rather than using the `address` and `hash_value`. Newer data dictionary tables (like the AWR tables) use the `sql_id` rather than the `hash_value`. — Justin Cave, Apr 15 '13 at 10:07

score 1 · Answer 2 · answered Apr 15 '13 at 09:45

1

I would say that you just proved that the book is wrong in this case. And theoretically it seems better to have the hash indentify the conceptual SQL statement instead of a randomly-capitalized string... And i hope the comments get ignored too when generating the hash. ;-)

answered Apr 15 '13 at 09:45

C.B.

666
3
18

but, that book proved by using `select sql_text, sql_id, child_number, hash_value, address, executions from v$sql where upper(sql_text) like '%EMPLOYEES%';`. – Ravi Apr 15 '13 at 09:46

score 0 · Answer 3 · answered Oct 15 '15 at 01:42

0

set lines 300 col BEGIN_INTERVAL_TIME for a30 select a.snap_id, a.begin_interval_time, b.plan_hash_value from dba_hist_snapshot a, dba_hist_sqlstat b where a.snap_id=b.snap_id and b.sql_id='&sql_id' order by 1;

answered Oct 15 '15 at 01:42

Selvaraj Prakash

1

hash value for sql statement

3 Answers3