RE: SQL Profiles and Performance

Jed,

This concerns sql plan baselines not sql profiles - they both use the same 
underlying tables (sqlobj$, sqlobj$data, sqlobj$auxdata) but there are some 
significant differences.
SQL Profiles are designed to provide statistical adjustments to the optimizer. 
Baselines are there to enforce a particular plan.



There were some nasty overheads related to the recursive merge of executions 
statistics into the tables mentioned above in version 11.2.0.2 but those are 
fixed in 11.2.0.3.

With that bug fixed, the overhead should be minimal. 
What happens is that the optimizer still does the optimisation steps it 
normally does but if the best (by cost) plan that it comes up with does not 
match the plan hash in the baseline then it will store the other plan for 
future evolution and revert to the baselined plan - the idea being that it 
gives you stability, let's you know if it has come up with something that looks 
better and then later can verify whether it actually is better or not in 
reality.
This extra work does not come for free obviously, but really it should be 
minimal.

Bottom line - if these features are not causing any problems then if it were me 
then I'd stick with it for the moment, particularly as you only have a prod 
environment.
(Having said that, having gone through the pain of the recursive merge issue in 
11.2.0.2, I'm running with use=TRUE and captureúLSE so that I only have a 
select few baselined plans.)

Cheers,
Dominic

> From: Jed_Walker@xxxxxxxxxxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> Subject: SQL Profiles and Performance
> Date: Fri, 13 Apr 2012 13:06:53 +0000
> 
> Has anyone had experience with whether using SQL Profiles 
> (optimizer_capture_sql_plan_baselines= TRUE, 
> optimizer_use_sql_plan_baselines=TRUE) is detrimental to overall performance. 
> I'm talking about problems I've heard with plan changing unexpectedly, but 
> with overall performance. We have a system that has this set and I'm 
> wondering if it might perform better without it. (This is 11.2.0.3.0 6-node 
> RAC on RedHat 5.6)
> What I'm wondering though is if SQL profiles functioanality has much overhead 
> that might impact overall performance? This is OLTP and I wonder if things 
> might be better without them?  (I have production only so testing means 
> getting maintenance and turning it off)
> 
> Thanks,
> 
> Jed S. Walker, OCP
> Principal Engineer, Databases
> National Video Advanced Services
> Office:    303.267.6759
> P Please only print this email if necessary. Consider the environment and 
> cost.
> 
> CONFIDENTIAL NOTICE
> This electronic mail transmission and any accompanying documents contain 
> information belonging to the sender, which may be confidential and legally 
> privileged.  If you are not the intended recipient, any disclosure, copying, 
> distribution or action taken in reliance on the message is strictly 
> prohibited.  If you have received this message in error, please delete it 
> immediately. Thank You
> 
> 
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
                                          
--
http://www.freelists.org/webpage/oracle-l

Other related posts: