RE: quick statistics question
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Dec 2014 21:24:51 +0000
Sorry, I haven't been following this thread so this suggestion may have been
made already.
There are always a few changes in the optimizer that make a big difference to a
few people on the upgrade. If you haven't tried it yet a temporary workaround
(before you get to your 11.2.0.4 upgrade, and have time for full regression
testing) might be to restart the database with optimizer_features_enable set to
your 10g version.
I've recently blogged about a couple of optimizer changes on the 10g/11g
upgrade that could make a big difference to plans - you might like to check to
see if any of the bits of sample code match the type of queries where you're
having problems.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Maureen English [maureen.english@xxxxxxxxxx]
Sent: 19 December 2014 21:18
To: mark.powell2@xxxxxx
Cc: oracle-l-freelists
Subject: Re: quick statistics question
Thanks Mark.
Yes, I agree that 11.2.0.2 isn't the best release to be on. We tried to
schedule an upgrade, but found that 11.2.0.4 on our
current OS won't allow us to put a database in archivelog mode. We have an OS
upgrade scheduled for early January.
This might be a little easier if there weren't so many problem queries...and if
things that ran okay yesterday still ran okay
today, but that's not the case, either....
- Maureen
On Fri, Dec 19, 2014 at 3:46 AM, Powell, Mark
<mark.powell2@xxxxxx<mailto:mark.powell2@xxxxxx>> wrote:
If performing full computes on the tables involved in the problem queries did
not help then look at the plans for the queries and look to see if Oracle is
performing a common transformation. IF you can find the issue is with a
specific CBO transformation then there may be a hidden parameter to disable the
option or you might be able to see a way to hint the SQL to get around the
problem. Also 11.2.0.2 is not the best release to be on. 11.2.0.3 or 11.2.0.4
would likely be better since there were several minor CBO issues fixed in these
later releases.
From: Maureen English
[mailto:maureen.english@xxxxxxxxxx<mailto:maureen.english@xxxxxxxxxx>]
Sent: Friday, December 19, 2014 1:08 AM
To: David Roberts
Cc: Powell, Mark; oracle-l-freelists
Subject: Re: quick statistics question
We're on 11.2.0.2.
I did gather system statistics...maybe that helped a little. I didn't look for
histograms because I hoped Oracle would just do the
right thing...probably a little too optimistic of me to think that, though.
I'll look for histograms tomorrow. I remember from when
we upgraded to 10g, the thought was that histograms were bad.
David, what kinds of things did you do to resolve your performance problems?
Are you still using Oracles Automatic stats gathering
job, or did you create your own?
I was hoping to be able to use Oracle's stats gathering. It's been almost a
week and things are not really improving. Some things
are really fast, but those complex queries against views and materialized views
using joins and subselects are not even finishing.
Twice I've had to kill 10 processes that a user started up because the first
one didn't finish, then the next didn't finish, etc. The cpu
usage was creeping up to 90% and it wasn't going to be long before it hit 100%.
We do have a ticket (or 3) open with Oracle, and we did provide a sqltrace
output to them. So far, their suggestion is to unset any
initialization parameters that are not set to the default.
- Maureen
On Wed, Dec 17, 2014 at 3:27 PM, David Roberts
<big.dave.roberts@xxxxxxxxxxxxxx<mailto:big.dave.roberts@xxxxxxxxxxxxxx>> wrote:
We are a year on from a painful 11g upgrade. You don't provide a lot of detail,
so my suggestions will be speculative.
I would enquire if you have gathered system statistics. I would also enquire if
there are histogram stats on any of the columns on the joins in the poorly
performing queries.
System statistics can be quite problematic if not representative. 11g is a lot
keener on generating histogram stats than 10g, histogram stats can lead to
quite unpredictable performance changes.
The above advice is speculative, if you provide specific oracle version numbers
and example plans then we may be able to help you more.
Dave
On 17 Dec 2014 21:29, "Maureen English"
<maureen.english@xxxxxxxxxx<mailto:maureen.english@xxxxxxxxxx>> wrote:
Thanks Mark!
On Wed, Dec 17, 2014 at 10:06 AM, Powell, Mark
<mark.powell2@xxxxxx<mailto:mark.powell2@xxxxxx>> wrote:
You do not have to delete the statistics before you force the statistics to be
re-gathered if you are going to use gather_table_statistics. If will replace
what is there.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Maureen English
Sent: Wednesday, December 17, 2014 10:25 AM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: quick statistics question
We just migrated our reporting instance from 10g to 11g and I planned to let
Oracle do all the
statistics gathering using the automatic jobs. Unfortunately, I also imported
the statistics from
the old database as part of the migration and now I have queries that are
consistently hanging.
I think I need a sanity check. Does it make sense to delete the statistics on
the tables
involved in these queries, and then gather them with the basic gather table
stats statement?
Since these are all materialized views that get refreshed (either complete or
fast) just about
every night, I was hoping that Oracle would just decide that statistics needed
to be generated
on them after they were refreshed.
- Maureen
Other related posts:
