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

On Mar 23, 8:39 am, "Andrea" <netsecur...@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?

On 23 Mar, 14:33, "hpuxrac" <johnbhur...@sbcglobal.net> wrote:
> On Mar 23, 8:39 am, "Andrea" <netsecur...@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_C NT,last_analyzed
from user_tables ...

i need this values for verify chianed rows:

(chain_cnt/num_rows) > 0.1

Andrea wrote:
> On 23 Mar, 14:33, "hpuxrac" <johnbhur...@sbcglobal.net> wrote:
>> On Mar 23, 8:39 am, "Andrea" <netsecur...@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_C NT,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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 23 Mar, 17:53, DA Morgan <damor...@psoug.org> wrote:
> Andrea wrote:
> > On 23 Mar, 14:33, "hpuxrac" <johnbhur...@sbcglobal.net> wrote:
> >> On Mar 23, 8:39 am, "Andrea" <netsecur...@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_C NT,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
> atwww.psoug.org.
> --

thanks very much.

i can't launch /rdbms/admin/utlchn1.sql because it shows me that my
db isn't compatible 8.1.0.
However, tha table is created launched /rdbms/admin/utlchain.sql ...

which differents there are for this two script ??

thanks a lot