top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

What is SQL Trace and what are the steps for generating Trace file?

0 votes
42 views
What is SQL Trace and what are the steps for generating Trace file?
posted Oct 7, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+1 vote
 
Best answer

SQL Trace: SQL Trace gives a wide range of information & statistics that used to tune a
group of SQL operations. We do the Sequel Trace at three levels.
1. SQL
2. Reports
3. Forms
Trace the Sequel Statements. How much time it was taking, how many rows it was
fetching, all the information was given from SQL Trace.
Steps for generating Trace file:
Enable the Trace.
Run the DML statements.
Disable the Trace.
Get the Trace file.
Convert the Trace File to Readable Format.
The Trace file was generated with he extension .TRC. Oracle has give specified
directory for trace files. To get the path use the query below.
EX: SQL> Select value from
V$PARAMETER
Where name = ‘USER_DUMP_DEST’;
To get the name of the Trace file also we have to use the Query.
SQL> Select c.value || ‘\ORA’ || TO_CHAR(a.spid, ‘FM00000’) || ‘.TRC’
From
V$PROCESS a,
V$SESSION b,
V$PARAMETER c
Where
a.addr = b.paddr
and b.ausid = userenv(‘sessionid’)
and c.name = ‘USER_DUMP_DEST’;
TKPROF: Copy the trace file, which was generated earlier, and paste it in your custom
directory.
Syntax: CMD> TKPROF d:\siri_0016.trc siri_0016.txt
The Hariha_0016.txt was created in the same drive where the Trace file was located.
Ex: --Query to Print the Cumulative Salary of the Employee table order by DEPTNO
SQL> Select deptno, ename, sal, sum(sal) over(partition by
deptno order by deptno, ename) “CUM_SAL”
from
scott.emp;

answer Oct 8, 2015 by Arun Gowda
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...