Re: Cost Baseed Optimizer and Schema Stats

"news.verizon.net" <avdbi_at_hotmail.com> wrote in message news:<nMOmc.28553$wY.27196_at_nwrdny03.gnilink.net>...
> "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:409b3fba$1_at_olaf.komtel.net...
> > Hi there,
> >
> > I am running Oracle9i under Windows 2000 Server and I have the following
> > performance problem in connection with cost based optimization (CBO) and the
> > schema statistics:
> >
> > One of the applications talking to my Oracle server performs a lengthy
> > import of data over night. I am not referring to Oracle's imp tool here, the
> > application imports data through SQL (OCI) commands while reading from
> > import files.
> >
> > The whole import is supposed to take around 2.5 to 3.5 hours. Since a
> > while ago the schema had no partitioned tables, so rule based optimization
> > was used in all SQL statements. Back then performance was as described
> > above: 2.5-3.5 hours import time. Now partitioned tables have been
> > introduced into the data model, so the SQL commands during the import are
> > now CBO because partitioned tables require CBO. I get the following
> > behaviour:
> >
> > After starting the import in the evening, performance looks good and users
> > go home in good spirits. Estimated remaining time is normal. After returning
> > the next moring the import isnt even half way done and says it needs another
> > 11 hours or so. Client, Oracle Server and network connection are idle. (By
> > the way no schema statistics available at that point of time). Now when I
> > create a Schema statistics (dbms_stats.gather_schema_stats('schema_name'))
> > while the import is running, it suddenly gets accelerated as the statistics
> > becomes available. Estimated remaining time drops significantly. Immediately
> > after that I delete the statistics I just gathered but the estimated
> > remaining time drops further... to a value expected. The import is "alive"
> > again.
> >
> > why is that and how can I make sure the import DOES NOT SLOW DOWN over
> > night ?
> >
> > Thanks a lot,
> >
> > AH
> > :(
> >
> >
>
> How can you expect us to help you when you do not tell us what exactly is this import process .. the code etc.
>
> Trace the import using sql_trace or 10046 event .. find out why/where its slowing down and then fix the problem.
>

If you know what the statistics might be (take a swag based on past loads) use dbms_stats to spoof Oracle into thinking that is what the stats are right now. Or export them using dbms_stats the next time you take them and use that to import the stats just before loading.

Just out of curiosity, why do you delete them if you are using the partitioning option?

Mike
> My guess is that the import first emties some table and then selects from it and inserts into it.
> So the statistics on the table vary during the load. And thus analyzing the tables makes the statistics
> more current and the queries use a better execution plan.
> You could either try hints to maintain a stable execution plan. Or try montiring on the tables whose data change
> a lot during the load .. and run (at regular intervals during the load) the dbms_stats.gather_schema_stats with
> an option to gather stale statistics.
>
> Anurag
Received on Sat May 08 2004 - 09:36:07 CDT