Re: Sql Profile 12c

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.
>
> Tx
> Sanjay
>
>
> 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
> <http://www.freelists.org/webpage/oracle-l>
>
>
>
>
>
> --
> Regards,
> Wil
>
>
>

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

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