*********************************************
***** (1) TOOLS TO ANALYZE TRACE FILES. *****
*********************************************
Mainly 2 tools to analyze trace files.
(A) tkprof
(B) trcsess (from oracle 10g). This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.
*****************************************************
***** (2) FIND WHERE TRACE FILE WILL BE STORED. *****
*****************************************************
If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:
SQL> set serveroutput on size 1000000 for wra
SQL> declare
2 paramname varchar2(256);
3 integerval binary_integer;
4 stringval varchar2(256);
5 paramtype binary_integer;
6 begin
7 paramtype:=dbms_utility.get_parameter_value(‘user_dump_dest’,integerval,stringval);
8 if paramtype=1 then
9 dbms_output.put_line(stringval);
10 else
11 dbms_output.put_line(integerval);
12 end if;
13 end;
14 /
C:oracleadminsansudump
PL/SQL procedure successfully completed.
SQL>
If the user you are using has access to the base views then you can do the following instead.
SQL> select name,value from v$parameter where name=’user_dump_dest’;
NAME VALUE
——————— ————————————————-
user_dump_dest C:oracleadminsansudump
*********************************************************
***** (3) MAKING TRACE FILE AVAILABLE TO ALL USERS. *****
*********************************************************
There is an undocumented parameter _trace_files_public that if set to true changes the file permissions in the user_dump_dest directory when trace files are created to allow everyone to read them. This parameter can be checked with the following SQL. Beware that this is an undocumented parameter and should not be routinely set to true as some information in trace files can be used by hackers or malicious users. You can set this parameter by adding the following line to the init.ora file:
# allow trace files to be created with public permissions
_trace_files_public=true
# disable this feature:
#_trace_files_public=true
# or =>
_trace_files_public=false
Here is the SQL to check the value of this parameter:
SQL> select x.ksppinm name,y.ksppstvl value
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where x.inst_id=userenv(‘Instance’)
4 and y.inst_id=userenv(‘Instance’)
5 and x.indx=y.indx
6 and x.ksppinm=’_trace_files_public’;
NAME
—————————————————————-
VALUE
——————————————————————————–
_trace_files_public
FALSE
SQL>
Imp : Please note that X$KSPPI contain all parameters with their index number and X$KSPPCV contain all related values based on its index in X$KSPPIf.
*******************************************************
***** (4) FIND SID AND SERIAL# FOR OTHER SESSION: *****
*******************************************************
We are using a simple example and the session we are looking for is for the user SCOTT and we are logged into this session with AS SYSDBA. We need to be logged in as SYS or AS SYSDBA so that we can access the packages DBMS_SUPPORT and DBMS_SYSTEM needed to set trace in another session or in our own session.
SQL> column sid format 9999999999
SQL> select s.sid, s.serial#, s.username, s.osuser from v$session s, v$process p where s.paddr = p.addr;
SID SERIAL# USERNAME OSUSER
—— ——- ——————– ——————–
1 1 SYSTEM
2 1 SYSTEM
3 1 SYSTEM
4 1 SYSTEM
5 1 SYSTEM
6 1 SYSTEM
7 1 SYSTEM
8 1 SYSTEM
9 253 SYSTEM ZULIApete
10 20 SCOTT ZULIApete
10 rows selected.
SQL>
For our test we will consider SID and SERIAL# as 10 and 20 respectively.
**********************************
***** (5) TRACE EVENT BASICS *****
**********************************
Event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality. Some events are used by support analysts and developers to force certain conditions to occur for testing purposes.
Trace in fact sets an event in the Oracle kernel. In our case we want to look at event number 10046 – This event tells the Oracle kernel to enable SQL statement timings. The level of trace, Oracle kernel should generate is defined as below :
Trace Level 0 = No statistics generated
Trace Level 1 = standard trace output including parsing, executes and fetches plus more.
Trace Level 2 = Same as level 1.
Trace Level 4 = Same as level 1 but includes bind information.
Trace Level 8 = Same as level 1 but includes wait’s information.
Trace Level 12 = Same as level 1 but includes binds and waits.
For a complete list of events that can be set look at the file $ORACLE_HOME/rdmbs/mesg/oraus.msg on Unix or Linux. This file is not shipped on Windows systems. Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.
**********************************************************************
***** (6) Using ALTER SESSION to set trace in your own session. *****
**********************************************************************
The alter session command can be used to set trace for the current session as follows:
SQL> alter session set sql_trace=true;
Session altered.
SQL> — execute some code that you want to trace.
SQL> alter session set sql_trace=false;
Session altered.
This method can also be used to set timing and dump file size for the current session as follows:
SQL> alter session set timed_statistics=true;
Session altered.
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
Using ALTER SESSION to set extended trace using events
One last method I want to demonstrate is the alter session syntax to set events. Again stick to 10046 (trace) and level 8 and do not attempt to set any of the other events that are available without Oracles say so in a supported system. Here is the example of setting trace to level 12, including binds and waits:
SQL> alter session set events ‘10046 trace name context forever, level 12’;
Session altered.
SQL> — execute some code that you want to trace
SQL> alter session set events ‘10046 trace name context off’;
Session altered.
***************************************************
*****(7) User Level tracing using DBMS_SYSTEM.*****
***************************************************
(7-1) Set trace in another session using DBMS_SYSTEM. The following method will only be used with event 10046.
First lets set trace in SCOTT’s session using the DBMS_SYSTEM package. Before we do let’s turn on timed statistics so that the trace files get timing info and also set the dump file size so that there is plenty of room for the trace being generated.
SQL> exec dbms_system.set_bool_param_in_session(10,20,’timed_statistics’,true);
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_int_param_in_session(10,20,’max_dump_file_size’,2147483647);
PL/SQL procedure successfully completed.
OK, here we set trace in SCOTT’s session as following:
SQL> — now use standard dbms_support interface
SQL> exec dbms_system.set_sql_trace_in_session(10, 20, true);
PL/SQL procedure successfully completed.
SQL> — execute some code that you want to trace.
…………….
…………….
SQL> exec dbms_system.set_sql_trace_in_session(10, 20, false);
PL/SQL procedure successfully completed.
SQL>
(7-2) Second Method to set trace in another session using DBMS_SYSTEM. The following method will only be used with any event. /* Important Method */.
Next we can again use the DBMS_SYSTEM interface but this time use the set event syntax. This allows us to set any event in the database. This is of course not sanctioned by Oracle support and can cause damage to your database if not done correctly. Use this interface with care and just set 10046 (trace) events. Here is how it is done:
SQL> exec dbms_system.set_ev(10,20,10046,8,”);
So it is explained as following way:
SQL> exec dbms_system.set_ev(SID, SERIAL#, EVENT_NUMBER, TRACE_LEVEL,”);
PL/SQL procedure successfully completed.
SQL> — execute some code
SQL> exec dbms_system.set_ev(10,20,10046,0,”);
PL/SQL procedure successfully completed.
*********************************************************
***** (8) Setting Trace using DBMS_SUPPORT package. *****
*********************************************************
(8-1) Installing DBMS_SUPPORT package.
Using the example above we set trace to level 8, you can of course set it to any level you wish from the list we discussed above. Next we will use the DBMS_SUPPORT package to set trace. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink. First we will install the package:
SQL> — now do the same with dbms_support
SQL> — the package has to be installed first – you should ask Oracle first though!
SQL> @%ORACLE_HOME%rdbmsadmindbmssupp.sql
Package created.
Package body created.
SQL>
(8-2) Use DBMS_SUPPORT to set trace in another users session.
Next use the interface to again set trace for SCOTT’s session that we found earlier. here it is:
SQL> exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);
PL/SQL procedure successfully completed.
SQL> — execute some code you want to trace.
SQL> exec dbms_support.stop_trace_in_session(10,20);
PL/SQL procedure successfully completed.
SQL>
(8-3) use DBMS_SUPPORT to set trace in your own session
How do we set trace in our own session. Well first we can use all of the approaches seen above and pass in the SID and SERIAL# for our own session. There are other methods for setting trace in your own session though. The first is again using the DBMS_SUPPORT package. Here it is:
SQL> exec dbms_support.start_trace(waits=>true,binds=>false);
PL/SQL procedure successfully completed.
SQL> — run some code you want to trace.
SQL> exec dbms_support.stop_trace;
PL/SQL procedure successfully completed.
SQL>
*****************************************************************
**** (9) Use DBMS_SESSION to set trace in your own session. *****
*****************************************************************
The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:
SQL> — in your own session using dbms_session
SQL> exec dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.
SQL> — execut some code you want to trace.
SQL> exec dbms_session.set_sql_trace(false);
PL/SQL procedure successfully completed.
SQL>
**************************************************************
***** (10) Using oradebug to set trace through SQL*Plus. *****
**************************************************************
oradebug is a debugging utility that is essentially undocumented and is intended for use by Oracle support analysts for various tasks one of which is that it can be used to set trace. oradebug is available from svrmgrl before Oracle 9i and from SQL*Plus after. The first step in using this tool is to find the OS PID or the Oracle PID of the process you want to analyse. You can do this as follows:
SQL> connect system/manager@sans as sysdba
Connected.
SQL> col sid for 999999
SQL> col serial# for 999999
SQL> col spid for a8
SQL> col username for a20
SQL> col osuser for a20
1 select s.sid, s.serial#, p.spid, p.pid, s.username, s.osuser
2 from v$session s,v$process p
3* where s.paddr=p.addr
SQL> /
SID SERIAL# SPID(ospid) PID USERNAME OSUSER
——- ——- ——– ———- ——————– ——————–
1 1 2528 2 SYSTEM
2 1 2536 3 SYSTEM
3 1 2540 4 SYSTEM
4 1 2544 5 SYSTEM
5 1 2552 6 SYSTEM
6 1 2604 7 SYSTEM
7 1 2612 8 SYSTEM
8 1 2652 9 SYSTEM
10 343 3740 12 SYS ZULIApete
———> 12 70 864 13 SCOTT ZULIApete
10 rows selected.
Now that we have found the Operating System PID and Oracle PID (values 864 and 13 in this case) of SCOTT’s session we can use this to set trace with the oradebug tool as follows:
SQL> — set the OS PID
SQL> oradebug setospid 864
Windows thread id: 864, image: ORACLE.EXE
SQL> — or set the Oracle pid
SQL> oradebug setorapid 13
Windows thread id: 864, image: ORACLE.EXE
SQL> — set the trace file size to unlimitd
SQL> oradebug unlimit
Statement processed.
SQL> — now turn on trace for SCOTT
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
—- Run some queries for which you want traces.
SQL> — run some queries in another session and then turn trace off
SQL> oradebug event 10046 trace name context off
Statement processed.
*******************************
*****(11) Important Note. *****
*******************************
You should be aware that some of these methods allow setting of extended trace and some do not. Those that allow extended trace are easy to spot. These methods include ways to set the trace level or include variables suitably named such as waits or binds which again enable extended trace facilities.
Some trace methods have a default level such as set sql_trace=true which sets trace to level 8. The rest set trace to normal trace levels.
One other point to note is that we have looked first at ways to set trace in another session to the one you are logged into and also now at ways of setting trace in your own session, there is a third option, which is to set trace for the whole system (i.e for all users sessions), This is not recommended unless you know what you are doing and are monitoring trace as you can quickly fill the file system.
**********************************************
***** (12) Setting Instance Level Trace. *****
**********************************************
(12-1) Using the SQL_TRACE parameter of init.ora :
Trace can be set in the database initialization file the init.ora file. If you use spfile then you can still use the init.ora file and then copy it to the spfile. Simply add the following line to the init.ora file:
sql_trace=true
You can also set timed_statistics and max_dump_file_size in the init.ora file in the same way. i.e
timed_statistics=true
max_dump_file_size=unlimited
Trace can also be disabled at the instance level by simply commenting out the same parameter or by deleting it. A commented line is shown next:
#sql_trace=true
Or you can set the same parameter to false:
sql_trace=false
(12-2) Using ALTER SYSTEM to set trace at the instance level
You can also use the alter system syntax to set trace at the system level. Here is a simple example:
SQL> alter system set sql_trace=true scope=spfile;
System altered.
SQL>
SQL> Execute your code during the lifetime of database.
SQL> alter system set sql_trace=false scope=spfile
System altered.
SQL>
(12-3) Using EVENT init.ora parameter for enabling trace.
Another method that can be used to set trace at the instance level is to add an event (or multiple events)to the initialization file, the init.ora as described above. Again if you use spfile’s then you can copy the init.ora to spfile or use ALTER SYSTEM to set the value in the spfile. Here is an example of setting the trace event 10046 to level 12 in the initialization file:
# set the event in the init.ora
event = “10046 trace name context forever, level 12”
# to turn off the event simply comment out the line as follows:
# event = “10046 trace name context forever, level 12”
(12-4) The most preferrable method for enabling database level tracing without shutting down database. :
Quite often you would like trace to be set for a session as soon as the user logs on. Also you may want to be able to set trace for a specific set of users when they log in. This can easily be done with a database logon trigger. Here is a sample trigger.
Connected to:
Personal Oracle9i Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
SQL> create or replace trigger set_trace after logon on database
2 begin
3 if user not in (‘SYS’,’SYSTEM’) then
4 execute immediate ‘alter session set timed_statistics=true’;
5 execute immediate ‘alter session set max_dump_file_size=unlimited’;
6 execute immediate ‘alter session set sql_trace=true’;
7 end if;
8 exception
9 when others then
10 null;
11 end;
12 /
Trigger created.
SQL> show err
No errors.
SQL>
OK, that was easy. You can also use the following syntax for extended tracing.
SQL> execute immediate ‘alter session set events ‘10046 trace name context forever,level 12’
If you have any trouble with your system trigger and it causes logins to fail is to always include, as I have, an exception handler that calls null; for any error condition.
If all else fails you can disable system triggers by setting the parameter _system_trig_enabled=false in the initialisation file. This undocumented/hidden parameter stops the processing of system triggers such as logon triggers.
************************************************************
*****(13) New Tracing Method in Oracle10g DBMS_MONITOR *****
************************************************************
Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC/Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.
The new functionality works in three levels. You can use the old SID/SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.
Let’s now take a look at some of the features of this package.
(13-1) Setting DBMS_MONITOR with SID / SERIAL# :
Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# – we will use SCOTT connected through SQL*Plus as our sample session:
SQL> select s.sid,s.serial#,s.username
2 from v$session s, v$process p
3 where s.paddr=p.addr
SQL> /
SID SERIAL# USERNAME
———- ———- ——————————
248 153 SCOTT
258 61 DBSNMP
251 418 SYSMAN
255 961 SYS
249 215
27 rows selected.
SQL>
OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:
SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);
PL/SQL procedure successfully completed.
SQL> — execute some sql that you want to trace.
SQL> — in the other session
SQL> — turn trace off
SQL> exec dbms_monitor.session_trace_disable(248,153);
PL/SQL procedure successfully completed.
SQL>