Systems Engineering and RDBMS

Archive for March 13th, 2007

Tracing and TKProf

Posted by decipherinfosys on March 13, 2007

In one of our earlier blog post, we had covered a stored procedure to enable session level tracing in Oracle.

TKProf is used for getting trace information from Oracle for a certain process. When tracing is turned on a certain process, Oracle starts writing trace information in trace files located in user dump directories. As these trace files contain oracle specific information, we use TKProf to decipher the trace information into a regular ascii file.

Pre-requisites:

) SELECT privilege on V$SESSION, V$PROCESS views.
2) EXECUTE privilege on DBMS_SYSTEM package.
3) Write permission to a directory where you want to store the trace output file.

Here are the steps to turn on the tracing and getting output using TKProf.

Step 1
Get the sid, serial# and spid values of the process you want to start the trace on.

Before you execute the following SQL, make sure you have the SELECT privilege on v$session and v$process views.

Select s.sid, s.serial#, s.process, p.spid
From v$session s, v$process p
Where s.process like ‘%client_procesId%’
and s.paddr = p.addr;
where client_processId is the pid value of the OS process

Or

In case of capturing the trace for a xyz process:

Select s.sid, s.serial#, s.process, p.spid
From v$session s, v$process p
Where Upper(s.program) like ‘%xyz%’
and s.paddr = p.addr;

Note down the sid, serial# and spid values for your process from the result of the above query.

Step 2
Run the dbms_system.set_sql_trace_in_session procedure for the sid and serial# to turn the tracing on.

Before you execute the following procedure, make sure you have EXECUTE privilege to run the package DBMS_SYSTEM. If not ask the DBA to provide the permission.

exec sys.dbms_system.set_sql_trace_in_session(sid, serial#, true);
Substitute sid and serial# values from Step 1.

Once you execute this procedure, Oracle will start writing the trace information to a file in the directory specified under user_dump_dest parameter name in the V$PARAMETER view

To find out the directory:

Select value
From v$parameter
Where name = ‘user_dump_dest’;

And the name of the file will be like OracleSid_ora_spid.trc or ora_spid_OracleSid.trc (spid value from Step 1)
Step 3
Get the output from the trace file

At the command prompt run the following command:

tkprof <trace_filename> <output_filename> explain=dbuserid/dbpassword sys=no sort=prsela,exeela,fchela

The output of the trace will be stored in the <output_filename>.

Make sure you have write privileges to the directory where you want this file to be created. Otherwise specify the complete path and filename where it needs to be created. This is a regular ascii file. You can view it with any editor

Tracing using Events

There are atleast three ways to generate raw SQL trace files using EVENT 10046.

1) Using SQL*Plus.
2) Within SQL*Plus, you can set the event levels before executing your SQL/PLSQL blocks or statements.
3) Using the stored procedure that we had posted in a previous blog post and using it at a session level or in an application progoram.

For example:
To set the event level to collect bind value information, execute the following at the SQL prompt:

alter session set events ‘10046 trace name context forever, level 4’;

To set the event levels to collect wait times:

alter session set events ‘10046 trace name context forever, level 8’;

To set the event levels to collect both wait and bind value information:

alter session set events ‘10046 trace name context forever, level 12’;

Using SYS.DBMS_SYSTEM Oracle Package

Using the SYS.DBMS_SYSTEM Oracle Package’s SET_EV store procedure, you can also set the event for a Session.

Once your server program or any stand alone SQL/PLSQL script’s session is started, open up another SQL*Plus session, you can execute the following to set the event. Prior to executing the stored procedure, you need to know the SID, SERIAL# pair values of the session under which you need to set the event.

To find out the SID, SERIAL# values for your program/session:

SQL to find out SID/SERIAL# pair and SPID values for your program

SQL> Select s.sid, s.serial#, s.process, p.spid
From v$session s, v$process p
Where s.process like ‘%client_procesId%’
and s.paddr = p.addr;

where client_processId is the pid value of the OS process

Or

In case of capturing the trace for a Wave process:

SQL> Select s.sid, s.serial#, s.process, p.spid
From v$session s, v$process p
Where Upper(s.program) like ‘%PKSHIPWAVE%’
and s.paddr = p.addr;

In the above query, make sure you provide the process/program name in Uppercase.

SQL to find out SID/SERIAL# pair and SPID values for your session

SQL> Select s.sid, s.serial#, p.spid
From v$session s, v$process p
Where s.audsid = userenv(‘SESSIONID’)
And s.paddr = p.addr;

Note down the SID, SERIAL# and SPID values for your process from the result of the above query to pass them to the SET_EV stored procedure.
Setting Event for Trace
Now, use the following to set the event to start tracing for SID/SERIAL# pair values.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(<sid>, <serial#>, 10046, <4|8|12>,‘’);
Note: The last parameter is ‘’ (two single quotes without space between them)

This will set the session to start collecting trace information for the SQL/PLSQL statements or process that are being executed from this point onwards.

Please make a note of your SID, SERIAL# pair values and as well as your SPID information for you session, to figure out the name of the trace file being created. The trace file will be created under your USER_DUMP_DEST directory. Typically the naming convention used for the trace files is <oraclesid>_ora_<spid>.trc

To find out the actual directory, under SQL*Plus run:

SQL> show parameter USER_DUMP_DEST

Posted in Oracle | 2 Comments »

Fixing Windows Search

Posted by decipherinfosys on March 13, 2007

Just a little tid-bit:

On one of the client windows boxes, they had uploaded a lot of files and some of them had extensions that Windows did not recognize.  That was fixed later but an immediate need was to search those files for code that the client had written and there were more than 10k files that were uploaded.  These were not checked into source control unfortunately at that time and windows indexing service was not picking up searches for text in these files.  Here is how you can enable indexing service to search for files with unknown extensions:

1) Right click on my computer
2) Choose manage
3) Navigate to “Services and Applications”
4) Right Click on “Indexing Service”
5) Choose properties
6) Under the Generation tab check “Index files with unknown extensions.

Posted in Windows | Leave a Comment »