Re: How to analyze statistics on a table/ partition

avsrk_at_mailcity.com (Subrahmanyam Arya) wrote in message news:<25c1993e.0304081223.6ed937ae_at_posting.google.com>...
> udrigu_at_yahoo.com (Koumpilai Tsil Kara) wrote in message news:<830bf207.0304070825.4d12aac1_at_posting.google.com>...
> > avsrk_at_mailcity.com (Subrahmanyam Arya) wrote in message news:<25c1993e.0304031342.15683fbd_at_posting.google.com>...
> > > Dear Oracle Gurus,
> > >
> > > Suppose i want to know statistics on a particular table like 'how many
> > > insertions happened for the last one hour " and like that over a
> > > period of one day, how can i do that ?? I dont want to do select
> > > count(*) at time t1 and do another select count(*) at t1 + 1 hr and do
> > > a diff. Are there any data dictionaries that can be queried to get
> > > some metrics.
> > >
> > > Similarly, how can i get information like How much space a record in
> > > the table holds and total space occupied by the table..available free
> > > space or percent growth in the table size...
> > >
> > > -thx,
> > > avsrk
> >
> > Hi Subrahmanyam
> >
> > How about DBA_TABLES there is a column on this dictionary table which
> > shows the rows existing in the table, the column is called NUM_ROWS,
> > you have to run the ANALYZE TABLE command first to collect the
> > statistic for the table you are interested though.
> >
> > regards
> >
> > koumpilai
>
> Hi there,
> How should i use ANALYZE command on a table??? what are the pros/cons
> to using it ?? what's the benefit i get from that
>
> -thx,
> avsrk

Hi Subrahmanyam

If you want to count the rows of 1 or 2 tables every once in a while issue the command ANALYZE TABLE yourSchema.yourTablename COMPUTE STATISTICS; command, and then run the query to the dba_tables dictionary table like SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME LIKE 'yourTablename';

Advantages: You don't use a new SGA Shared Pool space by creating a new INSERT trigger, which will also NOT help you count rows when you delete rows from the table. Analyzed tables help the Cost Based Optimiser CBO to make good decision on executino plans, when you use it.

take care

Koumpilai Received on Thu Apr 10 2003 - 09:24:16 CEST