SQL Trace:-
Now we will learn about SQL Trace & TKPROF :-
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 Chaitu_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.
