WAIT is a 4-Letter Word
Canada
By Michael AbbeyOracle's back-end Co-operative Server technology supports just about all its suite of tools. Installations continue to use a wide assortment of front-end tools to work with data stored in Oracle's back-end. Reducing wait situations is an attainable goal regardless of the hodge-podge of tools one uses to get at Oracle.
This article will present "The Top 8 Reasons Why WAIT Situations Can Be Minimized" and offer explanations as to how. (Usually reasons are presented as the "TOP 10" - after reading this article, you are expected to fill in the last two which will no doubt be obvious after you see the first 8!). After each reason, I will explain why it helps reduce WAIT.
Rule #1: COMMIT your work often.
Regardless of what tool you use with Oracle, save your work often. If a user works with a PowerBuilder-based application, either commit programmatically or enliven a Save key on data entry screens. In SQL*Plus, after each insert, update, and delete, insert a COMMIT statement (or the more popular COMMIT WORK one sees in PL/SQL.When you save your work often, you release all the resources (locks, latches, redo space, etc.) required until you complete your transaction. Reducing resources frees them up for other users and reduces their wait times. Not to mention the other all-important feature - when you save often, you run less risk of losing work you have so carefully completed.
Rule #2: When using a tool that supports the set transaction use rollback segment syntax (used to direct transactions at large rollback segments), place a COMMIT just before each set transaction statement.
Many installations have worked with their database administrator to bring up one or more very large rollback segments. These large segments are designed to handle large volume transactions. Unbeknown to you, this set transaction statement often fails with an Oracle error complaining that it must be the first statement in a transcation. When this fails, the transaction you have so carefully pointed at a large rollback segment will go wherever it feels. Vying for rollback segment headers in smaller segments that can not handle the volume of data involved in a large transaction may end up forcing other users to queue behind your transaction until it complete. The DBMS_TRANSACTION package in recent releases allows passing set statements to the kernel in PL/SQL.
Rule #3: Let Oracle do its own locking.
Oracle will issue the least restrictive lock it requires to accomplish a task you have asked it to perform. Don't lock manually with the SQL lock verb. Oracle does a fine job on its own. Its lock manager does the best job when left on its own. With the emergence of tpo (the transaction processing option) in version 6, the default lock became share update. With share update locks, different transactions can update different rows in the same Oracle data block concurrently.
Rule #4: Train your user community to close down applications when they are not being used.
Terminal inactivity robs valuable resources from other users who may need them to go about their work. When users allow their terminals to sit and not do anything, they are still tying up a bottom-line amount of shared memory required to support their session even though they are not doing anything. Freeing up that memory by leaving an unattended session will reduce wait time for resources with other users.
Rule #5: Do not exploit multitasking when working with Windows-based applications.
It is tempting to open up multiple sessions in many windows when using a GUI front-end. It is not unheard of where users find themselves in a deadly embrace with themselves. That is, they have multiple windows open attempting to update the same data that sits uncommitted on another window. If you practice good housekeeping with your Windows applications, you will reduce your own wait times.
Rule #6: When working with views, if possible, access the base tables rather than the views to satisfy SQL queries.
Based on internal routines, sometimes Oracle assembles data for a view in memory before it allows any of it to be released to become part of query results. If queries access the tables underneath the views, it is possible that qualifying data will be available sooner, and there will be less wait time for the user. If you have ever examined output from the tkprof utility, you may notice excessive recusrive calls to the data dictionary to satisfy queries using views. The rule says "if possible" since some facilities delibeartely use views as part of their security mechanisms.
Rule #7: Implement profiles with the Server, and use resource governors with ORACLE and other third-party products.
By controlling the amount of resources available to user sessions, you can reduce wait times for concurrent sessions that may be on hold waiting for valuable resources. You will have to work with your DBA to enforce profile-based resource limits by setting the initialization parameter file entry resource_limit to true and restarting the instance. When installing end-user query tools, if you have primary and foreign key references defined in your database, they will pick up the join columns to ensure your users are not allowed to write an SQL statement that joins two 12,000 row tables without specifying any join conditions. At the same time, most reporting tools have a mechanism to control the amount of cumulative resources used by a class of users per session.
Rule #8: Get your DBA to use the utlbstat/utlestat utilities to assess the amount of rollback segment header contention.
Before any transaction that changes, removes, or creates data is allowed to commence, Oracel reserves space in the rollback segments to handle necessary undo information. The report file produced by these two utilities will give statistics and offer interpretations to ensure there is no contention. Contention for rollback segment headers can cause significant waits.
Wrapup
Paying attention to potential wait situations makes for faster systems; faster systems mean applications can process more data in less time; processing more data in less time contributes to two of the most important words in information technology - HAPPY and USERS. Enjoy the suggestions in this article if they are new to you.
About the Author Michael Abbey operates an independent consultancy in Ottawa Canada, specializing in Oracle database administration. He is co-author of two popular works "Tuning Oracle" and "Oracle: A Beginner's Guide." Watch for Michael at user conferences in North America and Europe. He can be reached as [email protected] in the eastern time zone (GMT -5).
This is a copy of an article published @ http://www.ioug.org/