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@xxxxxxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxx>:
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: @OracleSK
Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx> hat am 4. Dezember 2017 um21:48 geschrieben:
environment and working fine to be moved to prod but found it is not
Hi
Is it common that if I transfer any SQL profile created in the lower
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.
cost was 9500K and taking 30Min. After accepting Profile it is reduced to
Dev Env: Before Oracle Tuning Advisor accepted SQL Profile the query
10Min
Test Env: Execution Plan Cose comes 235K and taking 1hr 40 min. Itshows that it has used SQL profile also. The same time was even taken
without using Profile
way. I tried to use Opt_estimate but not able to understand as to how to
I agree with lots of experts comments that SQL profile is not the best
interpret it. It comes as
opt_estimate(@"SEL$1", JOIN, ("Sol_c_tbl"@"$SEL$1", "Sol_c_tbk"@$SEL$1","Sol_c_tba"$SEL$1"), SCALE_ROWS=8.860905062)
opt_estimate(@"SEL$1", JOIN, ("Sol_c_tbk"@"$SEL$1"),SCALE_ROWS=0.212222124)
opt_estimate(@"SEL$1", JOIN, ("Sol_c_tbk"@$SEL$1", "Sol_c_tba"$SEL$1"),SCALE_ROWS=8.869041334)
profile hints to directly tune the environment.
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
--
TIA
Sanjay
//www.freelists.org/ webpage/oracle-l
<//www.freelists.org/webpage/oracle-l>
--
Regards,
Wil
