Re: DB performance after upgrade from 9i to 11gR2
- From: ankit.thakwani@xxxxxxxxx
- To: iggy_fernandez@xxxxxxxxxxx,"baitha@xxxxxxxxxxxxxxxxx" <baitha@xxxxxxxxxxxxxxxxx>,"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 May 2014 05:04:06 +0000
Hi,
I noticed that the parameter cursor_sharing is set to 'FORCED', is your app not
using Bind Variables?
Sent on my BlackBerry® from Vodafone
-----Original Message-----
From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
Sender: oracle-l-bounce@xxxxxxxxxxxxx
Date: Thu, 8 May 2014 21:59:28
To: baitha@xxxxxxxxxxxxxxxxx<baitha@xxxxxxxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<oracle-l@xxxxxxxxxxxxx>
Reply-To: iggy_fernandez@xxxxxxxxxxx
Subject: RE: DB performance after upgrade from 9i to 11gR2
I agree. That's why it is such a difficult situation. We can't solve a problem
without data.
Did you send me any explain plans? I haven't received any.
BTW, it is perfectly normal and expected for query plans to be different in QA
and prod (the statistics are different after all, even if the collection method
is the same), and it is perfectly normal and expected for query plans to change
from day to day. The only way to force query plans to stay the same is to
eliminate the causes of variability such as nightly collection of statistics
and bind variable peeking. Or to use techniques such as stored outlines or sql
plan management.
Also, it is perfectly normal and expected for small changes in data
distribution to make big changes in query plans.
I'm willing to look at some query plans but I would question what you expect to
achieve. Your environments are not identical. QA has different data than prod,
different bind variables than prod, different CPU count than prod. Also EXPLAIN
PLAN is essentially useless unless it is produced by actually running the query
i.e. DBMS_XPLAN.DISPLAY_CURSOR.
And, as Carlos would say, a query plan is not enough; one needs all the
ancillary information that SQLTXPLAIN collects. Carlos has offered to look at a
SQLTXPLAIN collection from QA and prod.
I noticed from one of your blog posts that Oracle support is focusing on
differences in query plans; they are focusing on ensuring that you have given
the optimizer the information it needs (and then hope for the best).
Iggy
From: baitha@xxxxxxxxxxxxxxxxx
To: iggy_fernandez@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: DB performance after upgrade from 9i to 11gR2
Date: Fri, 9 May 2014 00:31:35 +0000
Iggy,
Thanks for offering to help and regarding your comments, I believe you must
know that company policies restrict sharing certain information. BTW, did you
get
a chance to look at the explain plans I provided?
Thanks
BA
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Iggy Fernandez
Sent: Thursday, May 08, 2014 12:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: DB performance after upgrade from 9i to 11gR2
It's a real temptation to construct solutions without getting the full history
of the problem, not reviewing diagnostics (statspack, AWR, RDA, SQLTPLAIN,
etc), and without a determination
of the root cause. It's also very tempting to let the patient tell the doctor
what the root cause is.
The best book on Oracle performance that I ever read was The Art of SQL Tuning
by Chris Lawson. The technical content is outdated now but I've never forgotten
the problem solving methodology.
P for problem: Get as much information and history as you can from the person
reporting the problem
O for observations: Collect objective data such as statspack, AWR, RDA,
SQLTXPLAIN, etc
I for implications: Make a judgement on the root cause based on the wealth
observations collected in the previous step.
S for solution: Finally, we construct a solution.
E for execution: Execution may not be a slam dunk. Change control. Minimize
downtime. etc
D for documentation: Document to help others down the road. Take credit.
In this case, the patient is giving us information in dribbles and telling us
that the root cause is changed plans without presenting any evidence to back up
the claim. Oracle support seems
to be making random guesses too.
Apologies for being preachy instead of making a guess based on little
information. Apologies especially to Bheemsen. I know that it is an extremely
hard place to be in and management is probably
belching fire at this point.
Iggy
Date: Thu, 8 May 2014 21:21:33 +0200
Subject: Re: DB performance after upgrade from 9i to 11gR2
From: exriscer@xxxxxxxxx
To: baitha@xxxxxxxxxxxxxxxxx
CC: mark.powell2@xxxxxx;
oracle-l@xxxxxxxxxxxxx
hi
the first thing you should do is get rid of histograms then check
in 11g it's simple to restore statistics so when things goes wrong you can
always go back quickly
or
use the pending statistics feature, gather stats and dont publish them then
fire a couple of sqlplus sessions with optimizer_use_pending_statistics set to
TRUE and test your queries, if they are good then publish, if not good jsut
delete them with dbms_stats
thanks
Other related posts:
