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.
- 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;
--
Received on Fri May 18 2007 - 02:03:06 CDT
