Re: Is Cursor Plan an SQL Trace Execution Plan ? ........... Basic Qs

  • From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
  • To: VIVEK_SHARMA@xxxxxxxxxxx
  • Date: Fri, 07 Sep 2007 08:43:51 +0200


> Qs 1 Why is the Old Hash Value i.e 3246265085the SAME for the SAME SQL
> Script for BOTH the Databases i.e NON-RAC & 2-Node RAC-ASM?

the HASH value is calculated over the SQL text. i.e, adding a space to
the statement would change the hash value, as would changing a comment
or the case of a character. It's a checksum calculated over the ASCII
values of the characters forming the statement. This way it's easy to
find out whether an identical statement is parsed and optimized before.
The hash value has nothing to do with the execution plan.

Maybe you confuse the hash value and the PLAN hash value. The latter is
a 'checksum' for the plan itself.

>From the docs (description fo V$PLAN_HASH):

V$SQLAREA_PLAN_HASH displays statistics on shared SQL area (V$SQL) by
grouping on the SQL_ID and PLAN_HASH_VALUE columns. It can potentially
create several rows for one parent cursor, one for each distinct value
of the column PLAN_HASH_VALUE.

HASH_VALUE NUMBER Hash value of the parent statement in the library
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library
PLAN_HASH_VALUE NUMBER Numerical representation of the SQL plan for this
cursor. Comparing one PLAN_HASH_VALUE to another easily identifies
whether or not two plans are the same (rather than comparing the two
plans line by line)

Best regards,

Carel-Jan Engel

If you think education is expensive, try ignorance. (Derek Bok)

Other related posts: