Re: quick statistics question
- From: Maureen English <maureen.english@xxxxxxxxxx>
- To: David Roberts <big.dave.roberts@xxxxxxxxxxxxxx>
- Date: Thu, 18 Dec 2014 21:08:10 -0900
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> 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>
> wrote:
>
>> Thanks Mark!
>>
>> On Wed, Dec 17, 2014 at 10:06 AM, Powell, Mark <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] *On Behalf Of *Maureen English
>>> *Sent:* Wednesday, December 17, 2014 10:25 AM
>>> *To:* 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:
