At some time every DBA has to deal with the dreaded “The database is slow, FIX IT!” issue. To be honest? I’m not the master of tuners, and surely I’m not dealing with these things on a day-to-day basis. Oracle is not THAT bad.
However, these moment do pop-up, and sometimes, indeed, it is something in the database. And it’s up to the DBA to do something. Even if it is NOT in the DB, it’s up to the DBA to find to cause or in the least prove the DB is not the cause of the perceived slowness, but some other external (network/application/wrong query) cause.
There is a TON of information out on the Internet to find the culprit, but I needed a quick recipe to setup an trace session, so at the least I had the information at my disposal. Hence this post..
Tools of the trade:
Create an AWR report
Setup a trace with the user session which is complaining.
Creation of the AWR report is quickly done by running the script:
sql> @?/rdbms/admin/awrrpt.sql
or
sql> @?/rdbms/admin/awrrpti.sql
The difference being:
awrrpt.sql will run on the current instance, and if you want to generate a report for another instance (RAC), the awrrpti.sql script can be used. The script will ask for the database ID and the instance ID.
The interpretation of the AWR report is something for another blog, but at least we have something to look at on the screen to show off our skills.
But what if it is a more specific issue? An user complaining? Instead of wading through a whole AWR report, it can just be a specific query acting up. Or just one user is affected. In such a case, it might be more useful to resort to session tracing.
There a a lot of situations which can occur, like: the user is still logged in, experiencing the issue, the user left for home out of frustration of the slowness, but left the session running. Or just logged out, then went home after filing a complaint the db was slow and no other information *sigh*.
It would take to much time to handle all these cases, but for a future reference, I’ll document the two cases I get to deal with most:
Case 1:
Setup a trace for future logins.
Case 2 ( Note: in a future blog when there is interest or I have more time!) :
Setup a trace for a current logged in session.
Before we dive in the good stuff: there a a multitude of tools and way of doing things, like using oradebug, SET EVENT 10046, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION,DBMS_SUPPORT.START_TRACE_IN_SESSION or DBMS_MONITOR.SESSION_TRACE_ENABLE.
It would again take to much time to give a thorough overview of the pro and cons, but in short:
oradebug
Old school style way of doing a trace. Honestly? Before my time (when Oracle 8.0 was in it’s prime time).
SET EVENT 10046
ALTER SESSION SET EVENTS ’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12′;
Nice one for tracing your own session, and you are able to “replay” the issue the user experienced with either his/her account or your own privileged account (sometimes this is the case).
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
or to be precise: DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, SERIAL#, TRUE);
Find the SID and Serial# of the session, and have a go at it..this one is known since Oracle 8i/9i..
Turn it on:
exec dbms_system.set_sql_trace_in_session(5,8888,true);
Turn it off:
exec dbms_system.set_sql_trace_in_session(5,8888,false);
DBMS_SUPPORT.START_TRACE_IN_SESSION;
Before first use: create this package first, it’s not a default available one..
To install, issue the following command:
SQL> @?/rdbms/admin/dbmssupp.sql
Now you can use this package as:
DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, WAITS, BINDS);
Turn it on:
exec dbms_support.start_trace_in_session(5,8888,true,true);
Turn it off:
exec dbms_support.stop_trace_in_session(5,8888);
DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID, SERIAL_NUM,BINDS,WAITS);
This is the way Oracle 11g is handling things..
To turn on:
exec dbms_monitor.session_trace_enable(session_id=>5,serial_num=>8888,binds=>true,waits=>true);
To turn off:
exec dbms_monitor.session_trace_disable(session_id=>5,serial_num=>8888);
All (or most) of the above will create a trace file in the trace dir..more on this later.
Client identifier
It would make things a lot easier to identify the users in the database with some more information, instead of wading through a lot of unknown connections etc..
To do this, a logon trigger needs to be created:
CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’)
||’:’||
SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’)
INTO v_user_identifier
FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/
If the need arise to clear out the CLIENT_IDENTIFIER for a session, just execute the following procedure:
DBMS_SESSION.CLEAR_IDENTIFIER
Case 1: Setup a trace for future logins.
CLIENT_ID_TRACE_ENABLE Procedure
This procedure will enable the trace for a given client identifier globally for the database.
Syntax
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
client_id IN VARCHAR2,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
To turn on:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(‘janedoe’, TRUE, FALSE);
Note on plan_stat:
Frequency at which we dump row source statistics. Value should be ‘NEVER’, ‘FIRST_EXECUTION’ (equivalent to NULL) or ‘ALL_EXECUTIONS’.
Usage Notes
* The trace will be written to multiple trace files because more than one Oracle shadow process can work on behalf of a given client identifier.
* The tracing is enabled for all instances and persistent across restarts.
Turn off:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE (‘janedoe’);
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/sqltrace.htm
GemsOfProgramming 