Re: General Question about Performance/Tuning and scheduled Tuning

On May 18, 9:03 am, "Alexander Peters" <apet..._at_ap-data.de> 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;
>
> --

It is better that you specify your database software version (if you know it)
because it seems to me that from 8.1.5 exist dbms_stats (ml note 237293.1)
so from that version it is better you use that package to collect statistics.
However you give so little details that's impossible to say if it is the optimizer or
other the problem

Bye
 Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri May 18 2007 - 02:45:34 CDT