[Oracle-l] Is Cursor Plan an SQL Trace Execution Plan ? ........... Basic Qs

Vivek,



Qs 1 How is the Cursor Plan for the following SQL to be Generated?



by querying v$sql_plan and v$sql_plan_statistics for this hash, you will
obtain one or more plans depending the number of childs of the parent
cursor



Qs 2 Is Cursor Plan same as Execution Plan (obtained from SQL Trace)?

NOTE - The respective NON-RAC Database, (existing on a Solaris Unix
Mounted Filesystem), has already been Bounced.



the plan in the sql trace (not one obtained via running explain = param
of tkprof) will correspond to one or more of the plans generated in
v$sql_plan... if the plan don't have flushed out when you do the query



Statspack excerpt taken during a Benchmark Run of Application
Transactions on this NON-RAC Database:-



CPU CPU per Elapsd

Old

Time (s) Executions Exec (s) %Total Time (s) Buffer Gets
Hash Value

------------ ---------- ------ ---------- ---------------

364.58 9,170 0.04 15.3 382.19 3,063,141
3246265085

Module: lisrvr-fin-listval_at_speaixp5lp11 (TNS V1-V3)

SELECT COUNT(*) FROM ICI WHERE ICI.BANK_ID = :1 AND SOL_ID

:2 AND ZONE_CODE = :3 AND ZONE_DATE = TO_DATE( :4,'DD-

MM-YYYY HH24:MI:SS') AND INST_NUM = :5





CASE 2 -



A Cold Copy of the above NON-RAC Database was taken into another Mounted
Filesystem partition and Converted using RMAN (Detailed Command given
below) into a 2-Node RAC-ASM Database (existing on RAW Device).



On Repeating the Benchmark Run of the SAME Application Transactions on
this 2-Node RAC ASM Database, using the SAME Application INPUT DATA
Values, the Statspack generated had the SAME "Old Hash Value" i.e.
3246265085 for the SAME SQL Statement (though with Different Comparative
Values of CPU Time (s), Executions, CPU per Exec (s), %Total, Elapsed
Time (s), Buffer Gets).



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



Nope on this



regards,



alvaro

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely for the use of the addressee(s). If you are not the intended
recipient, please notify the sender by e-mail and delete the original
message. Further, you are not to copy, disclose, or distribute this
e-mail or its contents to any other person and any such actions are
unlawful. This e-mail may contain viruses. Infosys has taken every
reasonable precaution to minimize this risk, but is not liable for any
damage you may sustain as a result of any virus in this e-mail. You
should carry out your own virus checks before opening the e-mail or
attachment. Infosys reserves the right to monitor and review the content
of all messages sent to or from this e-mail address. Messages sent to or
from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Vivek,
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.
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)
===

Vivek

The question Alvaro didn't answer:

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

The hash value is calculated from the SQL text only. The SQL text is the same on both databases therefore so is the hash value. The hash value is just a convenient way of "labeling" the SQL so that instead of having to compare what can be very large text strings (eg 4000 chars or more) you can match (and index) on a short hash, and then use the text itself only to distinguish the rare cases where two different SQL statements have the same hash value and so would collide in a hash table.

Regards Nigel

Nigel,

I guess you are talking about the multiple SQLs hashing to single
hash value. This bug is fixed long back (IIRC it is around 7.3.4 days)
and now the COMPLETE SQL is used for hashing. This was due to the fact
the first 4k and last 4k text were used to generate the hash value and
some packaged applications (esp: Oracle Ebusinesssuite) used to have
same predicates and selects in the reports and used to create lots of
issues on hash collision. Now the chance for these kind of hash
collisions are very remote (and may warrant a new bug!)

-Gopal