RE: How to match hash_value

Brandon,

It's been a while since I did this, but, if I recall correctly, you
don't need to match the SQL exactly.

You've got the original SQL, with a unique hash value, and a bad plan.
You've got the modified SQL, with a unique hash value, and a (hopefully)
improved plan.

Now, you create outlines for both, and then swap the plans.  You
shouldn't be concerned that the two plans have different, unique hash
values.

See MetaLink Doc ID 132547.1 for more details...

Hope that helps, 

-Mark


--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: Friday, February 09, 2007 3:53 PM
To: oracle-l-freelists
Subject: How to match hash_value

Hi List,
 
I'm trying to do the ol' stored outline swap-a-roo trick and I'm trying
to mimic the query coming from an application, but can't get it to come
out with the exact same hash_value even though I have the exact same
query text, with the same case and it appears to have the same white
space as you can see below, so why don't they have the same hash_value?
Any ideas what I'm missing?  I suspect it may be a difference in the
white spaces, e.g. I have spaces where the original has a tab, but how
can I find out what the white spaces are supposed to be?

Thanks,
Brandon

 
SQL> select hash_value, sql_text from v$sql where hash_value in
(3486651370,1343167348);

HASH_VALUE
----------
SQL_TEXT
------------------------------------------------------------------------
--------
3486651370
SELECT s1.spec_template_id,s1.spec_id,s2.spec_template_id
ass_spec_template_id,
 s2.spec_id ass_spec_id,s1.company_id,
s1.project_id,s1.spec_class_type,s2.spec
_class_type ass_spec_class_type,sa.ass_type  FROM IBS_SPEC s1, IBS_SPEC
s2, IBS_ SPEC_ASSIGNMENTS sa  WHERE s1.spec_id = sa.spec_id  AND
s2.spec_id = :1 AND sa.
ass_spec_id = :2

1343167348
SELECT s1.spec_template_id,s1.spec_id,s2.spec_template_id
ass_spec_template_id,
 s2.spec_id ass_spec_id,s1.company_id,
s1.project_id,s1.spec_class_type,s2.spec
_class_type ass_spec_class_type,sa.ass_type  FROM IBS_SPEC s1, IBS_SPEC
s2, IBS_ SPEC_ASSIGNMENTS sa  WHERE s1.spec_id = sa.spec_id  AND
s2.spec_id = :1 AND sa.
ass_spec_id = :2

Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.

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


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


Other related posts: