Re: General Question about Performance/Tuning and scheduled Tuning

On May 18, 12:29 pm, "Alexander Peters" <apet..._at_ap-data.de> wrote:
> 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
>
> --- Hide quoted text -
>
> - Show quoted text -

the ANALYZE commands are obsolete since 8.1.5. You need to use dbms_stats.
dbms_stats has a facility to gather only statistics for 'stale' tables, these are tables for which more than 10 percent of the data has changed.
If you run a pl/sql job which just calls dbms_stats.gather_schema_stats with the gather stale option, you have completed the objective above.
Further info on dbms_stats on http://tahiti.oracle.com

--
Sybrand Bakker
Senior Oracle DBA