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;
