EXAMPLE FOR SQL TRACE
--1) conn sys as sysdba
CON / AS SYSDBA
--2) CREATE A NEW USER OR YOU CAN USE THE EXISTING FOR THE EXAMPLE ONLY I CREATED NEW USER
DROP USER TUSER CASCADE --DROPING IF ALREADY EXIST
/
create user tuser identified by tuser
/
grant connect,resource to tuser
/
grant alter session to tuser
/
--3) CONNECT TO THE USER AND SET SQL_TRACE TO TRUE
conn tuser/tuser
alter session set sql_Trace=true
/
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION"
/
--4) CREATE SOME TABLE FOR THE PRACTICAL
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
--5) RUN SOME QUERY
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
/
SELECT S1.ENAME,S1.DEPTNO FROM EMP S1 WHERE SAL=(SELECT MAX(S2.SAL)
FROM EMP S2 WHERE S1.DEPTNO=S2.DEPTNO)
/
--6) CONN AS SYSDBA (OR USER OTHER SESSION FOR SYSDBA) TO FIND THE SQL ID AND SQL TEXT
conn sys as sysdba
select sql_id, sql_text from v$sql where sql_text like 'SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY
DEPTNO'
/
/* YOU WILL GET THE SQL_ID FOR THE GIVEN SQL_TEXT
SQL_ID SQL_TEXT
--------------------------------------------------------------------------------
5z1tfx0xdds3t SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
5z1tfx0xdds3t
*/
--7) CONNECT WITH THE USER
conn tuser/tuser
SET LINESIZE 100
COLUMN value FORMAT A60
SELECT value FROM v$diag_info WHERE name = 'MY_TEST_SESSION'
/
/* YOU WILL BE ABLE TO SEE THE LOCATION OF YOUR TRACE FILE WITH THE ABOVE QUERY
VALUE
------------------------------------------------------------
D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_3656_MY_TEST_SESSION.trc
*/
--8) NOW AFTER TRACING THE QUERY SET TRACING OFF
alter session set sql_Trace=true
/
--9) NOW GO TO COMMAND PROMPT TO RUN THE TKPROF UTILITY TO SEE THE RESULT. IT WILL GENERATE A TRACE
FILE WHICH
--WE HUMAN CAN NOT UNDERSTAND BUT TKPROF UTILITY FORMAT IT TO HUMAN READABLE FORMAT
EXIT
--10) IN CMD GIVE THE BELOW COMMAND TKPROF
C:\Users\pushpjeet>TKPROF 'D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_2588_MY_TEST_SESSION'
'D:\MYTRACEFILE.TXT' TABLE=TUSER.EMP SYS=NO
TKPROF: Release 11.2.0.4.0 - Development on Mon Jun 5 06:36:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
--11) AFTER IT GENERATES THE OUTPUT GO TO THE LOCATION YOU SPECIFIED AND OPEN TO READ IT. BELOW IS MY
FILE OUTPUT
/*
TKPROF: Release 11.2.0.4.0 - Development on Mon Jun 5 06:36:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_2588_MY_TEST_SESSION.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
CREATE TABLE DEPT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
********************************************************************************
declare
error boolean;
st_syn_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(st_syn_detected, -995);
BEGIN
if((sys.dbms_standard.dictionary_obj_type!='SYNONYM')or(sys.dbms_standard.dictionary_obj_owner!
='PUBLIC'))
then
return;
end if;
error :=
CASE sys.dbms_standard.dictionary_obj_name
WHEN 'ST_GEOMETRY' THEN TRUE
WHEN 'ST_SURFACE' THEN TRUE
WHEN 'ST_POLYGON' THEN TRUE
WHEN 'ST_POINT' THEN TRUE
WHEN 'ST_MULTISURFACE' THEN TRUE
WHEN 'ST_MULTIPOINT' THEN TRUE
WHEN 'ST_MULTILINESTRING' THEN TRUE
WHEN 'ST_MULTICURVE' THEN TRUE
WHEN 'ST_LINESTRING' THEN TRUE
WHEN 'ST_GEOMCOLLECTION' THEN TRUE
WHEN 'ST_CURVE' THEN TRUE
WHEN 'ST_CURVEPOLYGON' THEN TRUE
WHEN 'ST_COMPOUNDCURVE' THEN TRUE
WHEN 'ST_CIRCULARSTRING' THEN TRUE
WHEN 'ST_INTERSECTS' THEN TRUE
WHEN 'ST_RELATE' THEN TRUE
WHEN 'ST_TOUCH' THEN TRUE
WHEN 'ST_CONTAINS' THEN TRUE
WHEN 'ST_COVERS' THEN TRUE
WHEN 'ST_COVEREDBY' THEN TRUE
WHEN 'ST_INSIDE' THEN TRUE
WHEN 'ST_OVERLAP' THEN TRUE
WHEN 'ST_OVERLAPS' THEN TRUE
WHEN 'ST_EQUAL' THEN TRUE
WHEN 'ST_OVERLAPBDYDISJOINT' THEN TRUE
WHEN 'ST_OVERLAPBDYINTERSECT' THEN TRUE
WHEN 'ST_GEOMETRY_ARRAY' THEN TRUE
WHEN 'ST_POINT_ARRAY' THEN TRUE
WHEN 'ST_CURVE_ARRAY' THEN TRUE
WHEN 'ST_SURFACE_ARRAY' THEN TRUE
WHEN 'ST_LINESTRING_ARRAY' THEN TRUE
WHEN 'ST_POLYGON_ARRAY' THEN TRUE
ELSE FALSE
END;
if(error) then
raise st_syn_detected;
end if;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.01 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.00 0 0 0 4
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 1)
********************************************************************************
0 0 0 LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=3 us)
********************************************************************************
SQL ID: 3bwc66pu7gkp7 Plan Hash: 15469362
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4"), COUNT(DISTINCT C4),
NVL(SUM(CASE WHEN C4 IS NULL THEN :"SYS_B_5" ELSE :"SYS_B_6" END),
:"SYS_B_7")
FROM
(SELECT /*+ NO_PARALLEL("S1") FULL("S1") NO_PARALLEL_INDEX("S1") */
:"SYS_B_8" AS C1, :"SYS_B_9" AS C2, "S1"."DEPTNO" AS C3, "S1"."SAL" AS C4
FROM "TUSER"."EMP" "S1") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 (recursive depth: 1)
Number of plan statistics captured: 1
DOWNLOAD FROM HERE
