Trace SQL Query at current session in Oracle

Trace SQL Query for testing execution way

Trace will help to test the particular SQL queries how its behaving on Server with different parameters or values. you can test SQL Query at session level by changing parameter at instance level or using invisible indexing to check the performance of SQL Queries.

you can also identified network wait to execute on different server as follows:
Suppose you have to database server and application server. you application team is reporting that particular SQL queries is running slow but when you test its running good.

you can test it from database and oracle client used by application server to identified the difference of trace from both the servers.

Put query in between the trace identifier as mentioned

Trace SQL Query at session level for testing

Enable the trace in Oracle for current session:

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set tracefile_identifier = ‘_From_Database’;
alter session set events ‘10046 trace name context forever, level 12’;
alter session set events ‘10053 trace name context forever, level 1’;
—SQL Query—
alter session set events ‘10053 trace name context off’;
alter session set events ‘10046 trace name context off’;

Example:

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set tracefile_identifier = ‘_From_Application’;
alter session set events ‘10046 trace name context forever, level 12’;
alter session set events ‘10053 trace name context forever, level 1’;
SELECT * FROM Img_import a WHERE entity_no = ‘000124’ AND trunc(date_activity) between ’23-Oct-16′ AND ’22-Nov-16′ ORDER BY date_activity desc;
alter session set events ‘10053 trace name context off’;
alter session set events ‘10046 trace name context off’;

Then check the log file with tracefile_identifer at database server 11g location:

select value from v$diag_info where name =’default trace file’;%oracle_base%\diag\rdbms\orcl\orcl\trace folder with _from_application

TKPROF

you can make it in readable format by using the tkprof command:

Tkprof inputfile outputfileTkprof ic_ora_from_application.trc ic_ora_from_application.txt

Second way to enabled trace

Step 1: Identified the session to be traced

select p.PID, p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_IDor

column line format a79
set heading off
select ‘ospid: ‘ || p.spid || ‘ # ”’ ||s.sid||’,’||s.serial#||”’ ‘|| s.osuser || ‘ ‘ ||s.machine ||’ ‘||s.username ||’ ‘||s.program line
from v$session s , v$process p where p.addr = s.paddr and s.username = ‘ ‘;

Step 2: OS process id for the process has been determined then the trace can be initialized as follows:

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

For disable after trace completed

oradebug event 10046 trace name context off

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s