Re: Sql Profile 12c

Thanks, Stefan and Niall for your great advice. I can share the plan but it is 3 pages and I can see 4 different plan been available and so didn't provided in the thread.  I will surely try to have recent patch in place as also saw with optimizer bugs I saw advice from both of you to have SQL Baseline and I did the same as I saw few queries due to Adaptive features and need your expert opinion    

• 1. Is it possible that even setup SQL Plan baseline will let optimizer to choose a bad plan ? I saw one advice to turn off auto-evolve and if you can provide your advice as for how to do it on SQL Baseline level. I chose one of the best plans but suddenly it started using another plan outside of SQL Baseline and I was only having one Enabled and fixed plan in it. I saw this behavior in multiple queries where I have not created the baseline but they are having multiple plans and suddenly Oracle started using bad plan
• If execution plan shows multiple directives with a reason like Join Cardinality misestimate or Group by Cardinality Misestimate or Single Table Cardinality Misestimate and all of the Dynamic sampling. So any link or direction as for how to interpret and work to tune them so that any missing histogram or other Stats been corrected.

Thanks for your valuable guidance
Sanjay

    On Friday, December 8, 2017 3:02 AM, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:  

 Sanjay
There are a number of bugs in every version of the database, but they aren't usually the reason for performance regressions. These are usually due to one (or more) of the following: 1. Optimizer changes. If moving to 12c then the new adaptive features introduced in 12 are a good candidate. There's a great write up and advice on how to use these features at https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2 . If you are on 12.1 you need a recent patch version *and* to set the parameters appropriately.   2. Statistics changes. In this case it's quite possible that the number and/or type of column histograms you have has changed. Jonathan Lewis has written a short blog series on the changes at https://jonathanlewis.wordpress.com/2013/07/14/12c-histograms/ . Apart from the changes described,  it is worth noting systems tend to get more histograms in 12 than 11 .  You might also just have a different stats gathering setting in 12 than 11, especially if you migrated and didn't upgrade.  A couple of final things, it would be much easier to offer more directed advice if the query and an execution plan output from Dbms_xplan were to be available.   Secondly, your goal seems to be to stabilise a plan. The method for doing that is (as I think was said earlier) to create baselines using DBMS_SPM. You'll probably want to turn off auto-evolve as well, the doc for this is https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#GUID-19E6FFB0-BC7A-4CDB-AE36-6D67C15C7332 and the optimizer blog has a series on SQL Plan Management that would make a good addition to the docs.

On Thu, Dec 7, 2017 at 7:45 PM, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote:

Thanks Stefan/Wil
Is there any bug in 12.1 that is causing SQL to behave badly as the reason been looking to create profile/baseline to fix the execution plan. Currently,one of the SQL is changing execution plan several time. I can see now 4 plan and so tuning is not working when new plan comes up and break the performance. This is Exadata environment. TxSanjay

    On Wednesday, December 6, 2017 6:29 PM, dba Wilson <iamanoracledba_at_gmail.com> wrote:  

 Hi Sanjay,

Agree with Stefan. You need to find out the root cause. Recommend a tool for you, which is being used by me recently :). SQLBooster. You can find the download url and the Power Point I prepared for my dev team.

2017-12-05 19:56 GMT+11:00 Stefan Koehler <contact_at_soocs.de>:

Hello Sanjay,
the problem is not the SQL Profile (they can be very useful) but the Oracle Tuning Advisor. It seems like you just accepted the recommendation by Oracle Tuning Advisor without understanding the root cause.

The SQL Profile by Oracle Tuning Advisor does not fix an execution plan - so it is not very uncommon to have different costs in different environments if they are not EXACTLY the same. The SQL Profile by Oracle Tuning Advisor only provides some scale factors (in your case for joins) but the optimizer is still free to choose from various transformations, access paths and join methods.

So my recommendation: Delete the SQL profile, understand the root cause and fix this problem :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Sanjay Mishra <dmarc-noreply_at_freelists.org> hat am 4. Dezember 2017 um 21:48 geschrieben:
>
> Hi
>
> Is it common that if I transfer any SQL profile created in the lower environment and working fine to be moved to prod but found it is not achieving the same response?  Running the same Exact SQL in both environments and both Dev and Test are newly refreshed with Prod data. The environment is 12c (12.1.0.2) and SQL profile is enabled. The only difference is that Lower environment COSt in explanation where the query is running 5 times faster is more that based Execution plan COST in a production environment.
>
> Dev Env: Before Oracle Tuning Advisor accepted SQL Profile the query cost was 9500K and taking 30Min. After accepting Profile it is reduced to 10Min
> Test Env: Execution Plan Cose comes 235K and taking 1hr 40 min.  It shows that it has used SQL profile also. The same time was even taken without using Profile
>
> I agree with lots of experts comments that SQL profile is not the best way. I tried to use Opt_estimate but not able to understand as to how to interpret it. It comes as
> opt_estimate(_at_"SEL$1", JOIN, ("Sol_c_tbl"_at_"$SEL$1", "Sol_c_tbk"@$SEL$1", "Sol_c_tba"$SEL$1"), SCALE_ROWS=8.860905062)
> opt_estimate(_at_"SEL$1", JOIN, ("Sol_c_tbk"_at_"$SEL$1"), SCALE_ROWS=0.212222124)
> opt_estimate(_at_"SEL$1", JOIN, ("Sol_c_tbk"_at_$SEL$1", "Sol_c_tba"$SEL$1"), SCALE_ROWS=8.869041334)
>
>  I got the above from one of the Frank blogs from dbi Services
>
> Any clues as instead of SQL profile I can check more based on above profile hints to directly tune the environment.
>
> TIA
> Sanjay

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






-- 
Regards,
Wil


   



-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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