Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: General Question about Performance/Tuning and scheduled Tuning

Re: General Question about Performance/Tuning and scheduled Tuning

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 18 May 2007 15:24:15 -0700
Message-ID: <1179527055.316288.247610@n59g2000hsh.googlegroups.com>


On May 18, 11:39 am, DA Morgan <damor..._at_psoug.org> wrote:
> Alexander Peters wrote:
> > Hello!
> > I have a general question about Performance and Tuning. All few weeks
> > the database works very slow. Selects which have a duration about 5 -
> > 10 sec. need about 40 - 60 sec. And some INSERT Statements need the
> > some time. Actually, i recompute the statistics of all Tables to solve
> > the problem, but i think thats not the real solution. Is there a way,
> > that the server optimize himself? Timed or anything else? We have the
> > some application on a MS SQL Server, and there isn't this problem. I
> > think that must be possible with a oracle server too.
>
> > A. Peters
>
> > Here my procedure, which i use to optimize my database. After this, the
> > Database is so fast as before.
> > CREATE OR REPLACE procedure compute_table ( as_tablename in varchar2 )
> > AS
> > PRAGMA AUTONOMOUS_TRANSACTION;
> > ls_sql varchar2 (255);
> > BEGIN
> > ls_sql := 'ANALYZE TABLE ' || as_tablename || ' DELETE STATISTICS';
> > EXECUTE IMMEDIATE ls_sql;
> > ls_sql := 'ANALYZE TABLE ' || as_tablename || ' ESTIMATE
> > STATISTICS';
> > EXECUTE IMMEDIATE ls_sql;
> > ls_sql := 'ANALYZE TABLE ' || as_tablename || ' COMPUTE STATISTICS';
> > EXECUTE IMMEDIATE ls_sql;
> > END;
>
> My instinct from reading this thread is that you are trying to treat
> Oracle like SQL Server and don't understand the huge differences in
> concept and architecture.
>
> Produce a Stats Pack when the database is working properly.
> Produce them every hour (at a minimum) until it is working poorly.
> Determine what has changed.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Tuning by statpack hourly reports?

Sounds like a reply for the Oracle WTF.

Try reading Cary Millsap's book Optimizing Oracle Performance.

Rinse and repeat. Received on Fri May 18 2007 - 17:24:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US