Re: quick statistics question

Believe it or not, I thought of doing that! Since Oracle Support was pushing us to remove some non default parameters and I was leaning toward getting rid of historgrams on all of the tables and regenerating statistics in hopes of getting things working again, I didn't do anything with that parameter.

I was able to unset, for my session, about 10 parameters that we had set to non default values and run a query that was a good example of the problem. I was shocked to see it finish in 5 minutes...just what the user told me it was supposed to do.

Tomorrow the parameters will be unset with a database bounce and we'll do some more testing.

Lesson learned: Just because a vendor says to set parameters in a database in order to make things run smoothly, don't assume that those same parameters should be set in another database just because it contains much of the same data (materialized views).

• Maureen

On Fri, Dec 19, 2014 at 12:24 PM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
> 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
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Maureen English [maureen.english_at_alaska.edu]
> *Sent:* 19 December 2014 21:18
> *To:* mark.powell2_at_hp.com
> *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_at_hp.com> 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_at_alaska.edu]
>> *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_at_googlemail.com> 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_at_alaska.edu>
>> wrote:
>>
>> Thanks Mark!
>>
>>
>>
>> On Wed, Dec 17, 2014 at 10:06 AM, Powell, Mark <mark.powell2_at_hp.com>
>> 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Maureen English
>> *Sent:* Wednesday, December 17, 2014 10:25 AM
>> *To:* oracle-l_at_freelists.org
>> *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
>>
>>
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l