RE: Force matching and SQL plan management
- From: Abdul Mohammed <oracle.blog3@xxxxxxxxx>
- To: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Nov 2013 20:46:22 -0800
Jonathan mentioned mentioned below
“It probably reduces the work done during hard parsing if your constructed
profile include hints like leading(), index() and so on – but the optimizer
still has to hard parse the “profile-hinted” SQL. Any saving comes from the
number of execution paths the optimizer avoids considering because the
hints block them.”
if lets say its a 2 table join and we are using SQL Profile(just like you
mentioned with index hints and all that) on it…how much of the reduction
percent wise are we talking here? Are we talking about 40%-50% reduction
during the hard parse process or something less?
Sent from my Windows Phone
------------------------------
From: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>
Sent: 11/22/2013 8:25 PM
To: oracle.blog3@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Force matching and SQL plan management
Hi Abdul,
Jonathan Lewis addresses this specific question in his blog, here:
http://jonathanlewis.wordpress.com/2010/01/12/force_match/
Hope that helps,
-Mark
From: Abdul Mohammed <oracle.blog3@xxxxxxxxx>
Reply-To: "oracle.blog3@xxxxxxxxx" <oracle.blog3@xxxxxxxxx>
Date: Friday, November 22, 2013 at 8:09 PM
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Subject: Force matching and SQL plan management
Hello all,
I am trying to fix a hard parsing issue for database, Code on the app
cannot be changed. So i was thinking to make use of SQL Profiles and use
force_match=true...there are couple of SQL that are troublesome. Same SQL
but uses literal values, so when i create a SQL PROFILE and put
force_match=true(from what i understand, i believe this profile will be
used)...my question is
1. Would putting this in place avoid hard parsing or would it still go
though the hard parse phase(even with profile is set). Do note the
signature(force_matching_signature is same for this type of SQL)
2. Can this be done with SQL Plan Baseline, as i rather use Baseline
instead of profile. As Baseline seems to work for me in the past and
profile sometimes do not work.
Any feedback is appreciated.
Thanks,
Sent from my Windows Phone
Other related posts:
