Re: General Question about Performance/Tuning and scheduled Tuning

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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org