Database News You Can UseMay 2003
A monthly newsletter for Oracle users
from Database Specialists, a consulting firm
specializing in Oracle technology
We joined a couple thousand colleagues attending the IOUG Live conference in Orlando, Florida in late April. We saw old friends, made some new ones, and attended some very valuable technical sessions. “Ask DB Specialists” – A New Q & A Column
Roger Schrag and Terry Sutton from Database Specialists led an advanced DBA session on performance tuning and a lunchtime discussion forum on the wait event interface. Over 300 people attended this presentation, and the positive feedback has been overwhelming. During the conference, we also handed out many copies of our popular new reference poster on Oracle Wait Events. It was created based on suggestions from our clients and you, our newsletter subscribers. The “Common Wait Events and Parameters” seems to be everyone’s favorite part of the poster. Thank you for your support!
Over the past several months, more and more of you have been submitting questions on how to solve an Oracle problem, optimize your queries, tune the database, and so on. Therefore, we are happy to announce the launch of our new column, “Ask DB Specialists.” Every month, a member of the Database Specialists team will answer a difficult and challenging question. Do you have an interesting problem to share with the Oracle user community? Please email your questions to firstname.lastname@example.org. Include your name, telephone number, and problem description. Feel free to try and stump the experts! Because we get a lot of requests, we may not have the time to personally respond to every question. So, stay tuned for future issues of The Specialist to see your questions answered. Sparky™ Data Collector for Oracle Performance Tuning
Question from a subscriber at a financial services company in New Jersey: I am familiar with the ALTER SESSION SET EVENTS command to enable wait event tracing in my own session. Is there a way for me as the DBA to enable wait event tracing in somebody else’s session?
Roger Schrag of the Database Specialists team responds: Sure. In fact, there are several ways. Two techniques require that you query the sid and serial# columns from v$session to identify the session you wish to trace:
· EXECUTE SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION (sid, serial#) · EXECUTE SYS.DBMS_SYSTEM.SET_EV (sid, serial#, 10046, 8, '')
A third technique requires that you query the pid column from v$process to identify the Oracle process you wish to trace:
· oradebug setorapid [Oracle PID from v$process] oradebug session_event 10046 trace name context forever, level 8
The dbms_support package is not installed by default. You can install it by running the dbmssupp.sql script found in the rdbms/admin directory. This script is missing from many releases of Oracle 8i and is available as a patch. For this reason the other two techniques might be more attractive. But please be aware that dbms_system.set_ev is an undocumented built-in, and Oracle Support does not officially endorse its use. oradebug, meanwhile, is a little-known debugging facility built into SQL*Plus and Server Manager. You must be connected as sysdba to use oradebug. Type “oradebug help” for more information.
You can turn off wait event tracing in somebody else’s session with any of the following:
· EXECUTE SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION (sid, serial#)
· EXECUTE SYS.DBMS_SYSTEM.SET_EV (sid, serial#, 10046, 0, ”)
· oradebug setorapid [Oracle PID from v$process] oradebug session_event 10046 trace name context off
· EXECUTE SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid, serial#, FALSE)
You can learn a lot more about wait event tracing, and performance tuning with wait events in general, in a white paper I recently co-authored. Download the paper at http://dbspecialists.wpengine.com/presentations.html#wait_events2.
Here is another free resource in the realm of Oracle wait events and performance management that you may want to investigate. It is from Hotsos (pronounced “hot sauce”), a company dedicated to Oracle system performance. Cary Millsap, a well-known Oracle author and database expert, is a founder of Hotsos. Rocky Mountain Oracle Resources
Sparky is what Hotsos calls, “The Performance Data Collector for Oracle®” According to the website, “Sparky resolves the problems with level-8 [wait event] trace data collection. Sparky, the data collector, is free and richly documented software. It is designed and written by professional Oracle optimization specialists to collect exactly the Oracle performance statistics that you need to diagnose any Oracle system performance problem.” For more information, check out http://www.hotsos.com/products/sparky/index.html.
In March, the Rocky Mountain Oracle Users Group held its very popular annual Training Days. The two-day event was very well attended and full of great technical presentations by many popular speakers. Go to the RMOUG website at http://www.rmoug.org and click on the “Training” tab at the top of the home page. You will find dozens of presentations available for download on topics including: