Getting trace reports of PL/SQL programs and SQL statements (tkprof)

As sys user

alter system set timed_statistics=true; running the previous script

1	alter system set timed_statistics=true; running the previous script

Create a user

CREATE USER OMER
IDENTIFIED BY VALUES omer
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

CREATE USER OMER

IDENTIFIED BY VALUES omer

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

2 Roles for OMER

GRANT CONNECT TO OMER;
GRANT RESOURCE TO OMER;
ALTER USER OMER DEFAULT ROLE CONNECT;

GRANT CONNECT TO OMER;

GRANT RESOURCE TO OMER;

ALTER USER OMER DEFAULT ROLE CONNECT;

6 System Privileges for OMER

GRANT EXECUTE ANY PROGRAM TO OMER;
GRANT UNLIMITED TABLESPACE TO OMER;
GRANT ALTER SESSION TO OMER;
GRANT CREATE SESSION TO OMER;
GRANT CREATE PROCEDURE TO OMER;
GRANT CREATE TABLE TO OMER;

GRANT EXECUTE ANY PROGRAM TO OMER;

GRANT UNLIMITED TABLESPACE TO OMER;

GRANT ALTER SESSION TO OMER;

GRANT CREATE SESSION TO OMER;

GRANT CREATE PROCEDURE TO OMER;

GRANT CREATE TABLE TO OMER;

Tablespace Quota for OMER

ALTER USER OMER QUOTA UNLIMITED ON USERS;

1	ALTER USER OMER QUOTA UNLIMITED ON USERS;

Connecting as user Omer

sqlplus omer/omer

1	sqlplus omer/omer

Creating a table

CREATE TABLE OMER.emp
(
employee_id NUMBER(10),
last_name VARCHAR2(50),
First_name VARCHAR2(50),
department_id NUMBER(20),
salary NUMBER(20)
)

CREATE TABLE OMER.emp

(

employee_id NUMBER(10),

last_name VARCHAR2(50),

First_name VARCHAR2(50),

department_id NUMBER(20),

salary NUMBER(20)

)

Running the script whose trace report will be taken

declare
begin
execute immediate 'alter session set max_dump_file_size="2000M"';
execute immediate 'alter session set tracefile_identifier=''SAMPLE''';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
for aa in 1..10000 LOOP
insert into emp (employee_id, firs_name, last_name, department_id, salary)
values (192100 + aa, 'mehmet', 'Derviş', 1, 112600 + aa);
commit;
end loop ;
end;

declare

begin

execute immediate 'alter session set max_dump_file_size="2000M"';

execute immediate 'alter session set tracefile_identifier=''SAMPLE''';

execute immediate 'alter session set events ''10046 trace name context forever, level 12''';

for aa in 1..10000 LOOP

insert into emp (employee_id, firs_name, last_name, department_id, salary)

values (192100 + aa, 'mehmet', 'Derviş', 1, 112600 + aa);

commit;

end loop ;

end;

The orcl_ora_14912_EXAMPLE.trc trace file is created in /u01/app/oracle/admin/orcl/udump.
To make the trace file readable

tkprof orcl_ora_14912_EXAMPLE.trc Report_name

1	tkprof orcl_ora_14912_EXAMPLE.trc Report_name

The report can be reviewed using: more Report_name

1	The report can be reviewed using: more Report_name

Creating the trace report by creating a procedure

create or replace PROCEDURE StartTrace (pRepName VARCHAR2)
IS
vTraceString VARCHAR2 (100);
BEGIN
vTraceString := 'ALTER SESSION SET TRACEFILE_IDENTIFIER=';
vTraceString := vTraceString || CHR (39);
vTraceString := vTraceString || pRepName;
vTraceString := vTraceString || CHR (39);
EXECUTE IMMEDIATE vTraceString;
EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE="2000M"';
vTraceString := 'ALTER SESSION SET EVENTS ';
vTraceString := vTraceString || CHR (39);
vTraceString := vTraceString || '10046 trace name context forever, level 12';
vTraceString := vTraceString || CHR (39);
EXECUTE IMMEDIATE vTraceString;
END;

create or replace PROCEDURE StartTrace (pRepName VARCHAR2)

vTraceString VARCHAR2 (100);

BEGIN

vTraceString := 'ALTER SESSION SET TRACEFILE_IDENTIFIER=';

vTraceString := vTraceString || CHR (39);

vTraceString := vTraceString || pRepName;

vTraceString := vTraceString || CHR (39);

EXECUTE IMMEDIATE vTraceString;

EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE="2000M"';

vTraceString := 'ALTER SESSION SET EVENTS ';

vTraceString := vTraceString || CHR (39);

vTraceString := vTraceString || '10046 trace name context forever, level 12';

vTraceString := vTraceString || CHR (39);

EXECUTE IMMEDIATE vTraceString;

END;

Running the procedure

DECLARE
PREPNAME VARCHAR2(200);
BEGIN
PREPNAME := NULL;
OMER StartTrace (PREPNAME);
COMMIT;
END;

DECLARE

PREPNAME VARCHAR2(200);

BEGIN

PREPNAME := NULL;

OMER StartTrace (PREPNAME);

COMMIT;

END;

Getting a Trace report on a Session basis

alter system set timed_statistics=true;
alter session set max_dump_file_size=2000M;
show parameter user_dump

alter system set timed_statistics=true;

alter session set max_dump_file_size=2000M;

show parameter user_dump

alter session set SQL_TRACE true;

1	alter session set SQL_TRACE true;

Running a query

alter session set SQL_TRACE false;

1	alter session set SQL_TRACE false;

For a different session

alter system set timed_statistics=true;
alter session set max_dump_file_size=20000;
select SID, SERIAL#, username from v$session where username=’MEHMET’;

SID SERIAL#
----------
163 110

alter system set timed_statistics=true;

alter session set max_dump_file_size=20000;

select SID, SERIAL#, username from v$session where username=’MEHMET’;

SID SERIAL#

----------

163 110

Start the trace using the SID and SERIAL# values as shown in the following lines

execute dbms_system.set_sql_trace_in_session('163','110',true); -- use false to end the session
show parameter user_dump
alter system set timed_statistics=true;

execute dbms_system.set_sql_trace_in_session('163','110',true); -- use false to end the session

show parameter user_dump

alter system set timed_statistics=true;

DISPLAY_CURSOR shows the execution plans within memory.
DISPLAY_AWR shows the execution plans even if they aren’t in memory (by default shows the last 8 days, can be changed in + or - increments of days)
trcsess can be used to separate the session that is searched for within the trace file. Use tkprof to change the file into a readable state.

The service name can be filtered according to the module (program) name.

Note: session = sid.serial#

EXECUTE dbms_monitor.session_trace_enable (226, 219);

1	EXECUTE dbms_monitor.session_trace_enable (226, 219);

cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
trcsess output=hr_session.txt session=226.219 *.trc

1 2	cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace trcsess output=hr_session.txt session=226.219 *.trc

EXPLAIN PLAN

SET STATEMENT_ID = 'testabc' FOR
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SET STATEMENT_ID = 'testabc' FOR

SELECT e.last_name, e.department_id, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

The following 2 lines can be used as an alternative to the SELECT statement above

SELECT plan_table_output FROM
TABLE(dbms_xplan.display('plan_table','testabc','serial'));

1 2	SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table','testabc','serial'));

SELECT PLAN_TABLE_OUTPUT FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b62q7nc33gzwx')); --sql_id

1 2	SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b62q7nc33gzwx')); --sql_id

SELECT PLAN_TABLE_OUTPUT FROM TABLE
(DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w'));

1 2	SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w'));

SELECT plan_table_output FROM
TABLE(dbms_xplan.display('plan_table','demo01','serial'));

1 2	SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table','demo01','serial'));