Oracle-L: RE: Tuning instance for batch processing

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 13 Feb 2003 07:44:08 -0800
Message-ID: <F001.0054BF02.20030213074408@fatcity.com>

Stefan - Batch jobs can vary quite a bit. There are report batch jobs that just do queries. There are update batch jobs that query and write. It is hard to have any rules of thumb that apply just to "batch jobs".

Actually, batch jobs tend to be easier to tune because it is easier to run repeated tests, varying Oracle parameters.

In gross terms, turn tracing on and run STATSPACK snapshots while you execute your batch job. Try to figure what the waits are and try to reduce them, tuning your instance or application. Trace will show you the worst SQL statements and try to improve them.

Another thing to consider is the usage cycle of your site. Don Burleson advocates running STATSPACK at regular intervals then plot the results over the 24-hour or weekly cycle so you can spot the critical points. I found one of my servers' critical time was between midnight and 6 am. They can't start some critical reports until after midnight. If the reports can complete before 6 am when the interactive users fire up, everything is great. If the reports run late, all heck breaks loose. Don's book is Oracle9i High-Performance Tuning with STATSPACK.

The best tuning methodology is the HOTSOS seminar. I had the privilege to attend one of Cary Milsap's seminars and it was awe-inspiring. Try to attend a seminar if you can, you won't regret it. I think Cary mentioned he will have a book out later this year. I can't wait!

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, February 13, 2003 8:49 AM To: Multiple recipients of list ORACLE-L

Hi list

Does anyone know of any good white papers online (or maybe a Metalink Note ID, I couldn't really find the right thing) about tuning an Oracle instance for batch processing ?

Thanks,

Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: stefan.jahnke_at_nospam.bov.de
Please remove nospam to contact me via email.

visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp

Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:andrea.palluck_at_bov.de.

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stefan Jahnke
INET: Stefan.Jahnke_at_bov.de

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------