General Question about Performance/Tuning and scheduled Tuning
This is a discussion on General Question about Performance/Tuning and scheduled Tuning within the Oracle Database forums, part of the Database Server Software category; --> hpuxrac wrote: > On May 18, 11:39 am, DA Morgan <damor...@psoug.org> wrote: >> Alexander Peters wrote: >>> Hello! >>> ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-26-2008, 05:50 AM
| ||||
| ||||
 Re: General Question about Performance/Tuning and scheduled Tuning 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? > > 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 damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org      |
|
02-26-2008, 05:50 AM
| |||
| |||
| Re: General Question about Performance/Tuning and scheduled Tuning On May 18, 6:46 pm, DA Morgan <damor...@psoug.org> wrote: > 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? > > > 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...@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. |
|
02-26-2008, 05:50 AM
| ||||
| ||||
| Re: General Question about Performance/Tuning and scheduled Tuning hpuxrac wrote: > On May 18, 6:46 pm, DA Morgan <damor...@psoug.org> wrote: >> 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? >>> 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...@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@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
Linear Mode
