hpuxrac wrote:
> On May 18, 11:39 am, DA Morgan <damor..._at_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..._at_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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri May 18 2007 - 17:45:22 CDT
