hpuxrac wrote:
> On May 18, 6:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> 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?
>>> Sounds like a reply for the Oracle WTF.
>>> Try reading Cary Millsap's book Optimizing Oracle Performance.
>>> Rinse and repeat.
>> Postscript:
>>
>> You might note the default time between AWR snaps is also one hour.
>> --
>> 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 -
>
> Who didn't know that already?
>
> What does that have to do with the question from the OP?
>
> WTF.
It didn't have anything to do with it until you questioned, for
reasons I can not fathom, my comment about taking snapshots once
an hour. It was you, I presumed, who didn't know it.
--
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 - 19:06:20 CDT
