Re: Gathering statistics for a specific schema

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: "Malpass, Nancy" <nancy.malpass@xxxxxxxxxxxx>
  • Date: Tue, 3 Oct 2006 11:16:39 -0500

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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto: oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *crcbedoy@xxxxxxxxxxxxxxxxx *Sent:* Tuesday, October 03, 2006 10:01 AM *To:* sacrophyte@xxxxxxxxx *Cc:* oracle-l; oracle-l-bounce@xxxxxxxxxxxxx *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@xxxxxxxxx>* Enviado por: oracle-l-bounce@xxxxxxxxxxxxx

03/10/2006 09:42   Por favor, responda a
sacrophyte@xxxxxxxxx

   Para
crcbedoy@xxxxxxxxxxxxxxxxx  cc
oracle-l <oracle-l@xxxxxxxxxxxxx>  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@xxxxxxxxxxxxxxxxx* <crcbedoy@xxxxxxxxxxxxxxxxx> 
<*crcbedoy@xxxxxxxxxxxxxxxxx
* <crcbedoy@xxxxxxxxxxxxxxxxx>> 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

Other related posts: