Re: Some questions about statistics

<Frederic Payant> wrote in message
news:a98mqukrink5la2d88bop35r7csngiesdf_at_4ax.com...
> Hi,
>
> I'm running Oracle 8.1.7 on a very large OLTP database (600GB)
> and I'm answering myself some questions about satistics :
> 1- does it make sense to gather statistics on less than 1% of
> a table containing more than 10M rows ?

If 1% is representative then yes. This may or may not be the case depending on your data distribution but it is certainly possible, opinion pollsters can generate representative sample for a data population of 50M with a sample size of circa 1000 as an example.

> 2- Is that true that, on partitioned tables, statistics are
> kept by partition, and so, on daily managed partition, is it
> enough to gather statistics on the partition of the day ? (these
> tables are never updated, inserted and read only)
> 3- Is there a way to cumulate statistics. I mean that, on a
> table that is fewly updated, can I gather 1% statistics every
> day and have 100% statistics done after 100 days ?

No.

> 4- Are statistics eternally valid. I think about very rarely
> updated tables. If I gather statistics 1 time a year, will
> these statistics still be used by CBO during all the year or
> will they be considered as out of date after a while ?

They are always considered by the optimiser to be valid, so if tables don't change then they don't need updating, if the tables do change however then this can have unfortunate side effects :(

>
> Last but not least : I've, in my production DB a lot of
> migrated/chained rows (table fetch continued row = 3576094)
> I found on the NG a little script which extract and correct
> migrated rows but how can I estimate the space needed by the
> table "CHAINED_ROWS", or, said differently, how can I know the
> number of chained/migrated rows in my database ? (because in a
> production environment, I don't want to have a TABLESPACE FULL
> error)

searching tahiti.oracle.com for chained rows will lead you to: Listing Chained Rows of Tables and Clusters You can look at the chained and migrated rows of a table or cluster using the ANALYZE statement with the LIST CHAINED ROWS option. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS option.

To create an appropriate table to accept data returned by an ANALYZE...LIST CHAINED ROWS statement, use the UTLCHAIN.SQL script provided with Oracle. The UTLCHAIN.SQL script creates a table named CHAINED_ROWS in the schema of the user submitting the script.

After a CHAINED_ROWS table is created, you can specify it when using the ANALYZE statement. For example, the following statement inserts rows containing information about the chained rows in the EMP_DEPT cluster into the CHAINED_ROWS table:

ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO chained_rows; The name and location of the UTLCHAIN.SQL script are operating system-dependent; see your operating system-specific Oracle documentation.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************