Re: Gathering statistics for a specific schema

I agree with the inference Nancy is making. Isabel, if you want a 9i environment under 10g, you can certainly do that by altering various parameters (depending on which parts of your environment you want to affect). You have to decide whether you truly want the 9i "features" back, or do you want to try moving forward with 10g features and tuning the best you can? We can help you make that decision by giving you opinions and facts, but only you (and your colleagues) can make that decision.

You stated that "most" queries have problems. In our situation, it would be more accurate to say that a few queries suddenly become excessively problematic, making it look like "most" queries had gone south, when in fact, it was but a small population. With auto gathered stats, you have to realize that your query plans may change whenever stats are modified. As Nancy said, you can lock the stats and thus lock in a query plan (more or less), but there are ramifications to that as well. Do you use cursor sharing or bind variables? In a different vein, have you collected system statistics (ie, cpu speed, io rates, etc)? To quote others, you really need to tell the CBO the truth, and nothing but the truth, if you want the best performance. Which optimizer_mode are you using (FIRST_ROWS has some big defects that can use indexes inappropriately)? Even though RBO is desupported, it still lives, and you can take advantage of it for a short time to help stabilize plans as well.

In terms of stats and the CBO, much has been said. The CBO is complicated and there are many interacting dependancies between it and other areas of the database (not just stats, but the AWR as well). It has been suggested (by Andrew Holdsworth, I believe, maybe others as well) that using most of the default features of 10g will get you through about 90% of your workload satisfactorily, and you can focus on the other 10% using any of a myriad different methods.

You are starting with a really big paint brush, and hopefully you can refine that brush and find a way to be more surgical such that your system works better all around.

On 10/3/06, Malpass, Nancy <nancy.malpass_at_cingular.com> wrote:
>
> There is a package called DBMS_LOCK - you can lock statistics at the
> schema level. Meaning GATHER AUTO SYS STATS PROC won't update the
> dba_tab_statitiscs with new stats.
>
> You could then tune schema by schema with fresh statistics. Although
> Oracle does advise gather new stats before you upgrade to 10G - In 10G as
> you know - monitoring is auto set and the gather is based on the stale flag.
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *crcbedoy_at_proteccion.com.co
> *Sent:* Tuesday, October 03, 2006 10:01 AM
> *To:* sacrophyte_at_gmail.com
> *Cc:* oracle-l; oracle-l-bounce_at_freelists.org
> *Subject:* Re: Gathering statistics for a specific schema
>
>
> Hi Charles,
>
>
> Well, this schema owns the core business aplication and we are migrating
> it from 9i to 10g, the problem is that this application presents problems
> after migrating it to 10g, problems with most of the queries it does, that's
> why the application provider and we want to follow the whole schema
> performance in 10g to be able to take decisions about what we should tune in
> the database or the application itself, because this is not the only schema
> we have, we have about 15 other different schemas being migrated to 10g.
>
>
> Regards,
>
> Isabel Bedoya Gómez
> Gestión de Tecnología
> Tel. 2307500 ext: 4077
>
>
> *"Charles Schultz" <sacrophyte_at_gmail.com>*
> Enviado por: oracle-l-bounce_at_freelists.org
>
> 03/10/2006 09:42 Por favor, responda a
> sacrophyte_at_gmail.com
>
> Para
> crcbedoy_at_proteccion.com.co cc
> oracle-l <oracle-l_at_freelists.org> Asunto
> Re: Gathering statistics for a specific schema
>
>
>
>
>
>
> There are many things you can do with stats, and some of them are
> downright really cool. But taking a step back from the syntax, let me ask,
> why or how do you know you "need" the stats for a specific schema? Have you
> identified a query and/or a column that is/are performing poorly because of
> missing or inaccurate stats (or even accurate stats for that matter)? Once
> you answer these questions, you can then proceed to answer other questions
> like "What kind of stats do you need, in particular?"
>
> On 10/3/06, *crcbedoy_at_proteccion.com.co* <crcbedoy_at_proteccion.com.co> <*crcbedoy_at_proteccion.com.co
> * <crcbedoy_at_proteccion.com.co>> wrote:
>
> Hi,
>
>
> I need your help, I am new to the usage of statistics and I need to get
> the statistics for a specific schema. I know that by using the package
> DBMS_STATS.GATHER_SCHEMA_STATS I can the statistics, but I don't know how to
> manipulate them. Could you guys give me a clue about what should I do or
> where can I find information about using this packege and manipulating
> information given by it?
>
>
> Thanks a lot for your help:)
>
> Isabel Bedoya Gómez
> Gestión de Tecnología
> Tel. 2307500 ext: 4077
>
>
>
> --
> Charles Schultz
>

-- 
Charles Schultz

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