Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: CBO optimizer

Re: CBO optimizer

From: Sybrand Bakker <>
Date: Thu, 02 Dec 2004 22:10:20 +0100
Message-ID: <>

comments embedded

On Thu, 2 Dec 2004 21:34:05 +0100, "qwerty" <> wrote:

>i'm working with a 9.2 db server, on this server we have a small number of
>static tables (i.e. containing configuration values) and a large number of
>dynamic tables.
>Such dynamic tables may be very small (less than 100 of records) and some
>minutes later can be large (more than 100.000 of rercords) and than again
>can become small and so on....
>With this kind of tables, i found very difficult work with the CBO
>optimization, infact, if the analyze (using package dbms_stats) is running
>while the tables are empty, the CBO will use a Full scan table, obviously
>this is not good when the table are full of data (more than 100.000 of

it depends. If 100000 records do not exceed say 256k a ft isn''t a problem

>I know that oracle suggest to calculate statistics often and when is known
>that the data has changed a lot, but with my db, those statistcs take more
>than 1hour to run (calculating them only on the dynamic tables).. which is
>too much... I cannot wait such time before let the application work ...

dbms_stats doesn't lock your tables. There is no reason why you would shutdown the application.
I analyze a few Gig worth of data using dbms_stats in less than 15 miutes, maybe your hardware is just too slow?
>I'm facing with different solutions (i cannot estimate statistics when the
>application loads the table, because i cannot change its code):
Not true. Technically speaking you CAN analyze a table under load.

>1) estimate the statistics in order to reduce the time of execution and
>executing them very often, this means sometime the application perform well
>and sometime no.
>2) compute statistcs only when the table is full of data, one time, and
>never (or rarely) recalculate them, this means that the application can
>perform well if the actual data is quite "similar" to the data on which the
>statistic has been calculated, this method should probably let a better
>Are there other solutions to this problem???, can you give me suggestions

alter table <table_name> monitoring
This will make your the table is only being analyzed when a certain percentage of the table has changed. You accomplish that by making sure the options parameter of gather_schema_stats has been set to 'GATHER STALE'
Works like a charm.

>Best regards

Sybrand Bakker, Senior Oracle DBA
Received on Thu Dec 02 2004 - 15:10:20 CST

Original text of this message