Re: Some questions about statistics

In article <a98mqukrink5la2d88bop35r7csngiesdf_at_4ax.com>, Frederic Payant <> writes:
>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 ?
>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 ?
>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 ?
>
>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)
>
>Thanks for your help
>
>Regards
>Frederic PAYANT - junior DBA ;-)

Fred, there is no direct relationship between chained rows and tablespace full errors. I have seen tables with heavy row chainging that once reogranized took just about the same amount of space as before.

Row chaining is only an issue when the number of chained rows in a table exceeds an acceptable percentage and the row chaining is avoidable. For some tables the row chaining may be purely due to the existence of long rows from the use of long data types, insteam lob usage, or varchar2(4000) where the full length is used creating rows longer than the usable Oracle block size. Re-organization in this case is generally a waste of time.

The dbms_stats utility does not count chained rows but the analyze command does. And speaking of statistics Oracle CBO statistics remain in use from creation until they are updated or deleted. In the case of a truely static table they are good forever.

How big a percentage sample you need depends on the uniformity of the data and the DML activity pattern. In general the more uniform the data in size of data values and in data value distribution for indexed columns the smaller the sample size that will produce good results.

set echo off

--
-- SQL*Plus script to find all tables where the number of chained rows
-- exceed 1/2 of 1 percent, i.e., 1 in 200 rows chained or other desired
-- limit: 1/200 = .005  1/500 = .002  1/1000 = .001
--
--  19980123  M D Powell 
--  20021015  Mark D Powell   Chg rem to --, match percentage n text 2 code
--
column owner format a12
column CHAINED format 990.99 heading "Percentage"
select table_name  "Table Name",
       owner       "Owner",
       num_rows    "Num Rows",
       chain_cnt   "Chain Count",
       round((chain_cnt/num_rows) * 100,2) "CHAINED"
from sys.dba_tables
where ( chain_cnt / num_rows ) > .002
and num_rows > 0
/

HTH -- Mark D Powell --

 -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web  -----
  http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
   NewsOne.Net prohibits users from posting spam.  If this or other posts
made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net