Re: General Question about Performance/Tuning and scheduled Tuning

On May 18, 11:39 am, DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx
(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.

.

Relevant Pages

  • Re: Why not Access...?
    ... Next, I DID NOT SAY Oracle was considered a "best choice," only a sufficient ... It is a desktop application and the database in also on the same machine. ... I also want to know why Oracle is considered a best choice as compared to SQL Server. ... > Also, you need to answer the question regarding whether or not each client> will be using an individual database, and individual copy of a central> organization database, or making straight calls to a centralized ...
    (microsoft.public.sqlserver.server)
  • Re: Merge/Replication or Syncing with Oracle
    ... You can't directly access an Access database on the desktop from a WM 5 app, but you can sync between SQL Compact on the WM device and Access using the Access Syncronizer: ... As for the Oracle issue ... ... Where SQL Compact Edition easily does merge/replication to SQL Server using IIS ... ...
    (microsoft.public.sqlserver.ce)
  • Re: Oracle vs SQL Server as a back end for Access?
    ... post -- comparing back end database engines or replacing the front end ... say that Oracle 10g is better than SQL Server 2000 without determining ... whether or not the total cost of ownership of 10g over SQL Server ... developer than Oracle 10g, in spite of its new web interface. ...
    (comp.databases.ms-access)
  • Re: Oracle licence question
    ... And no - it does not freeze anything, the backups DO NOT affect connections ... SQL Server MVP ... freeze the database and send it to the target. ... What I really wanted is something that lists features I get in Oracle ...
    (comp.databases.oracle.server)
  • Re: General Question about Performance/Tuning and scheduled Tuning
    ... I have a general question about Performance and Tuning. ... some application on a MS SQL Server, ... which i use to optimize my database. ... Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - ...
    (comp.databases.oracle.server)