Re: General Question about Performance/Tuning and scheduled Tuning

sybrandb wrote:

> 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

Ok, i tested the following with a positive (faster) effect.

I run in SQLPlus the command (hope that is what the answers mean)

DBMS_STATS.GATHER_SCHEMA_STATS ( ownname => 'TestDB_Slow', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', cascade => true, options => 'GATHER'); It takes 15 minutes, but this is ok. I think when i get this now in a pl/sql job all is ok. Or is someone other mind?

Greetings
A. Peters

--