Re: General Question about Performance/Tuning and scheduled Tuning

On May 18, 6:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
> hpuxrac wrote:
> > 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.
>
> Postscript:
>
> You might note the default time between AWR snaps is also one hour.
> --
> 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 -

Who didn't know that already?

What does that have to do with the question from the OP?

WTF. Received on Fri May 18 2007 - 18:57:21 CDT