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
>
> --

10.1 automatically gathers statistics every night (22:00) with package dbms_stats. If your version is 9.2 then i'm sure you have to use dbms_stats and not "ANALYZE".
We have our application migrated from 9.2 to 10.1 and then to 10.2. With 10.2 we have had some problems, with 10.1 no. 10g has a bit of self tuning, you have to use em. With 9.2 you have to analyze your system by your self. An approach maybe using STATSPACK. In my opinion if the application is complex (but if it run on both oracle and mssql may be not) it is very difficult to hava a self tuning database. A lot of experts of performance tuning say that for the most part performance problems are caused by a bad application design.

about
> Is there a way to say the Oracle Server: Optimize yourself every
> evening or if the Table xY grows about 10%?
I think that is not a correct answer. Oracle is optimized from the beginning, if data growth cause performance problems it is very probable that application is not scalable and there is no optimization oracle can do for you.

> 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.

now that we have a version put an example of query, with description of table, and how much data is involved, execution plans etc.

 Bye
  Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri May 18 2007 - 05:58:59 CDT