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

Vivek,

> 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
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 cache
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
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) ===

--
http://www.freelists.org/webpage/oracle-l