Re: script for statistics gather DBMS_STATS

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 23 Mar 2007 08:53:44 -0700
Message-ID: <1174665222.310356@bubbleator.drizzle.com>

Andrea wrote:

> On 23 Mar, 14:33, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:

>> On Mar 23, 8:39 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
>>
>>> hi,
>>> sometimes i launch dbms_stats.gather_schema.... for perform CBO of my
>>> oracle 9i DB.
>>> After that, i verify the number of rows, avg. row length and number of
>>> blocks of each table from user_tables. But this is very slow and
>>> expensive, because i must to read data for single table.
>>> I would like to know if there is some sql script for retrive summary
>>> information of all table statistics.
>>> thanks!
>>> bye
>>> andrew
>> Why exactly are you attempting to verify by querying user tables the
>> results of a dbms_stats operation?

> 
> 
> select
> initial_extent,NUM_ROWS,AVG_ROW_LEN,blocks,CHAIN_CNT,last_analyzed
> from user_tables ...
> 
> i need this values for verify chianed rows:
> 
> (chain_cnt/num_rows) > 0.1

No you don't.

@?/rdbms/admin/utlchn1.sql
ANALYZE TABLE online_media LIST CHAINED ROWS INTO chained_rows;

for heap tables and for IOT's look up dbms_iot in Morgan's library at www.psoug.org.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Mar 23 2007 - 10:53:44 CDT