RE: Force matching and SQL plan management
Date: Fri, 22 Nov 2013 20:46:22 -0800
Message-ID: <7602101967822754840_at_unknownmsgid>
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_at_proquest.com> Sent: 11/22/2013 8:25 PM
To: oracle.blog3_at_gmail.com; oracle-l_at_freelists.org 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_at_gmail.com> Reply-To: "oracle.blog3_at_gmail.com" <oracle.blog3_at_gmail.com> Date: Friday, November 22, 2013 at 8:09 PM To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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
- 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)
- 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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 23 2013 - 05:46:22 CET