Re: Sql Profile 12c

Sanjay,

You can check the reason why a new child cursor was created for a sql statement by checking v$sql_shared_cursor.

If you want help identifying the specific problem you are having then you’ll need to share at least the SQL and execution plan you are seeing and the execution plan you were expecting to see.

If plan stability is more important than letting the optimizer decide for you, then dbms_spm (SQL Plan Baselines) are what you want. But, echoing what’s been said, if the optimizer thinks a certain plan should be used then there’s usually a reason.

Hope that helps,
Andy

On Thu, 7 Dec 2017 at 19:47, 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
>
>
>

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