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@xxxxxxxxx> 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@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.
TxSanjay
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 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(@"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)
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
