Re: General Question about Performance/Tuning and scheduled Tuning

Cristian Cudizio wrote:

> 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

Hello Cristian Cudizio,
i use an Oracle 9.20 Server and have the some problem on a Oracle 10.1 and Oracle 10.2 Server. I dont know which details you need to know, please let me know.

Is there a way to say the Oracle Server: Optimize yourself every evening or if the Table xY grows about 10%?

bye A. Peters

--