SEO

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;


--

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

Cristian Cudizio wrote:

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

--

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Alexander Peters schreef:

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

ALTER TABLE MONITOR
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGTYXfLw8L4IAs830RAjjcAKCFsMRtjs9rVdhIRTVz44 oWGusntACgiM22
q/co1JqjD9UUeofxqmZYZpo=
=xJpC
-----END PGP SIGNATURE-----

On May 18, 12:29 pm, "Alexander Peters" <apet...@ap-data.de> wrote:
> Cristian Cudizio wrote:
> > On May 18, 9:03 am, "Alexander Peters" <apet...@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

On May 18, 12:29 pm, "Alexander Peters" <apet...@ap-data.de> wrote:
> Cristian Cudizio wrote:
> > On May 18, 9:03 am, "Alexander Peters" <apet...@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

sybrandb wrote:

> On May 18, 12:29 pm, "Alexander Peters" <apet...@ap-data.de> wrote:
> > Cristian Cudizio wrote:
> > > On May 18, 9:03 am, "Alexander Peters" <apet...@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



--

Alexander Peters 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;

My instinct from reading this thread is that you are trying to treat
Oracle like SQL Server and don't understand the huge differences in
concept and architecture.

Produce a Stats Pack when the database is working properly.
Produce them every hour (at a minimum) until it is working poorly.
Determine what has changed.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On May 18, 11:39 am, DA Morgan <damor...@psoug.org> wrote:
> Alexander Peters 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;
>
> My instinct from reading this thread is that you are trying to treat
> Oracle like SQL Server and don't understand the huge differences in
> concept and architecture.
>
> Produce a Stats Pack when the database is working properly.
> Produce them every hour (at a minimum) until it is working poorly.
> Determine what has changed.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Tuning by statpack hourly reports?

Sounds like a reply for the Oracle WTF.

Try reading Cary Millsap's book Optimizing Oracle Performance.

Rinse and repeat.

hpuxrac wrote:
> On May 18, 11:39 am, DA Morgan <damor...@psoug.org> wrote:
>> Alexander Peters 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;
>> My instinct from reading this thread is that you are trying to treat
>> Oracle like SQL Server and don't understand the huge differences in
>> concept and architecture.
>>
>> Produce a Stats Pack when the database is working properly.
>> Produce them every hour (at a minimum) until it is working poorly.
>> Determine what has changed.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
>
> Tuning by statpack hourly reports?

Last time I attended a lecture by Tom Kyte he recommended snapping
every 15-20 minutes (UKOUG). I wouldn't necessarily look at all of
them. But it sure would be nice to have them available when trying
to determine what happened when.

You assumed an intent not in evidence. <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org