Re: Oracle Stats

From: <art_at_unsu.com>
Date: Mon, 18 May 2009 10:25:34 -0700 (PDT)
Message-ID: <b32e19a9-96fb-4a36-9ef9-977533ce2a15_at_v17g2000vbb.googlegroups.com>



On May 17, 5:08 pm, ddf <orat..._at_msn.com> wrote:
> On May 17, 12:51 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
>
>
>
> > On May 16, 2:30 am, "stevedhow..._at_gmail.com" <stevedhow..._at_gmail.com>
> > wrote:
>
> > > On May 15, 11:47 am, a..._at_unsu.com wrote:
>
> > > > Statistic                                       Total
> > > > -------------------------------- --------------------
> > > > BUSY_TIME                                  14,954,028
> > > > IDLE_TIME                                 121,666,971
> > > > NICE_TIME                                           0
> > > > SYS_TIME                                    6,017,299
> > > > USER_TIME                                   8,936,729
> > > > LOAD                                                3
> > > > RSRC_MGR_CPU_WAIT_TIME                              0
> > > >>>>>>>>>>>> PHYSICAL_MEMORY_BYTES                          77,192   <<<<<<<<<<
> > > > NUM_CPUS                                           16
> > > > NUM_CPU_SOCKETS                                     4
>
> > > This section is puzzling.  How much RAM do you have?
>
> > There is an issue with PHYSICAL_MEMORY_BYTES column in v$osstat view.
> > This is well explained in following link.http://phlonx.com/blog/fred/index.php/2009/03/20/vosstat-anomaly/-Hide quoted text -
>
> > - Show quoted text -
>
> One person found this problem in one installation of Oracle, on Linux
> RHEL 4 64-bit.  One person.  No mention of this on Metalink.  One
> instance of a condition doesn't constitute an 'issue'.
>
> You should really do more research before you post such things.
>
> David Fitzjarrell

Well, I started reading on DBMS_STATS also. A question is, which table 'should' you gather stats on? We runs an OLTP system, with many many tables and many many queries. I found this script which is supposed to list which objects should be analyzed:

declare
 a dbms_stats.ObjectTab;
begin
 dbms_stats.gather_schema_stats(user,
  options => 'LIST AUTO',
  objlist => a);
for i in a.first .. a.last loop
 dbms_output.put_line(a(i).ownname

      || '-' || a(i).objname );
end loop;
end;
/

It does not seem to work properly. Is there a good way to list objects which should be analyzed. As an example, our ordering table is changed hundreds of times a day and looks like it has not been analyzed since 4/06/2009. How can I list that so I know that it should be analyzed.......or just analyze everything? Received on Mon May 18 2009 - 12:25:34 CDT

Original text of this message