Databases Reference
In-Depth Information
How to do it...
The following steps will show how to trace SQL activity:
1.
Connect to the database as
SYSDBA
:
CONNECT / AS SYSDBA
2.
Start tracing by issuing the following command:
ALTER SESSION SET SQL_TRACE=TRUE;
3.
Execute some work:
SELECT AMOUNT_SOLD
FROM sh.SALES S
WHERE S.CUST_ID IN (
SELECT C.CUST_ID
FROM sh.CUSTOMERS C
WHERE C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
);
4.
Stop tracing by using the following command:
ALTER SESSION SET SQL_TRACE=FALSE;
5.
Identify the session:
SELECT s.sid, s.serial#, s.process, p.spid
FROM v$session s, v$process p
WHERE s.audsid = userenv('sessionid')
AND s.paddr = p.addr;
6.
Run
TKPROF
(replacing
22801
in the example with the
SPID
value returned
from the previous query):
SQL>HOST
$ TKPROF TESTDB_ora_22801.trc 22801.txt
7. Review the generated report file (
22801.txt
in our example).
How it works...
To enable SQL tracing in our session, we can simply set the parameter
SQL_TRACE
to
TRUE
.
In step 3, we have executed a simple query, but we could perform many queries, procedures,
jobs, and so on.
