Oracle Wait Interface - 911

1. Oracle Wait Interface - 911 Surender Sara Verities Solutions LLC. Surender.sara@veritiesllc.com

2. Performance Methodology Goals Always Consistent and Repeatable Results Cost Effective Corporate Wide Deployment Customizable Always Accurate and becomes a Standard

3. Performance Issues Categories Category 1 When we don�t know anything about the issue or the exact timings on when it happens and need to zero in to a narrow time window and eventually to the set of problem statements. Category 2 We know when the problem seem to occur but not the problem. Ex, �Every day 9-12 AM things are slow� Category 3 We know what specific problem we need to resolve (end user response time perspective) users know what needs to be tuned � BEST START ! , Focus based Category 4 Meeting ongoing performance SLA � Specific Things Need to be tuned to be able to service SLA�s effectively Category 5 Building Performance History ( STATSPACK like approach but more intelligent and specific ) � Ability to capture the problem details only

4. Performance Tuning Goals (1) To be able to answer questions about sudden slowness, localized or generalized when we receive a help desk call someone walks in we get a page Ability to compare hourly performance horizontally, this will enable us to answer � WHY ARE WE SLOW TODAY� type questions compared to �YESTERDAY WAS FINE� Need to build historical performance data to compare the fluctuations vertically ( today) and horizontally ( same time frame but different days )

5. Performance Tuning Goals (2) Look for signatures and patterns over time and to know what to expect in response time and when ( PROACTIVE GOAL ) Baseline for tuning efforts Translate the data into meaningful capacity planning needs Quick, few clicks and the problem is exposed

6. Best Strategy, Always On! The ideal way to collect data is �ALWAYS ON� strategy. This can be accomplished in two ways 10046 always on Init.ora parameter Database logon trigger Sampling v$session_wait every second Direct SGA attach method where we can sample the data without using database resources can be the best way to accomplish this goal ( need c, pro*c expertise)

7. Issues with Best Strategy - 10046 10046 Not possible to leave it on always, will have negative effect on your performance Will fill your space in no time on a highly active system (USER_DUMP_DEST) If desired can use it as follows Trace all sessions from 9 AM to 10 AM � Use logon Trigger to set the trace and then DROP or disable the trigger

8. Example � Database Logon Trigger with 10046 CREATE OR REPLACE TRIGGER TRC_APPS AFTER LOGON ON APPS.schema DECLARE event_stmt varchar2(100) := 'ALTER SESSION SET EVENTS '||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||'''' ; BEGIN EXECUTE IMMEDIATE event_stmt ; END; /

9. Issues with Best Strategy � Sampling v$session_wait If sampling is done per second on this table via direct attached SGA method that method will work as long as the data store is not in the same database that is being monitored

10. What to do with - 10046 ? 10046 Use this for tracing programs once they are identified as a bottleneck. Use this to trace all sessions when problematic time window is defined for ex. Put a database logon trigger at 9 am and stop at 10 am. We will get trace for all the users at that time interval Use this to trace all given user connection for already active sessions ( will start tracing inactive sessions also when they become active) using PLSQL code Have to be prudent to trace specific programs manually by eliminating the START and END empty wait times EMAIL SURENDER.SARA@veritiesllc.com for the tracing paper

11. What to do � v$session_wait ? Sample data every X seconds � 30 to 60 seconds recommended We are after big bottlenecks during the first phase This way we meet the goal of always on for all sessions but not creating problems of our own

12. Fitting It together - GOALS! Need something that will capture what each session did in terms of WAITS and RESOURCES used before they disconnect Need something to sample every session�s all the time Meets our goal �always on� but is lightweight Provide historical data Provide a quick way to catch big issues every time they occur and the details Cost effective and corporate standard