TKPROF

The TKPROF program converts Oracle trace files into a more readable form.

TKPROF also determine the execution plans of SQL statement and creates a SQL script that stores the statistics in the database.

Steps and Commands to trace a session and run TKPROF :-

1.       Login to putty and connect to Oracle .

2.       To get the name of the trace file that will be generated run the following sql in the same session.

select rtrim(c.value,'/')|| '/'||d.instance_name|| '_ora_'|| ltrim(to_char(a.spid))||'.trc'

from v$process a, v$session b, v$parameter c, v$instance d

where a.addr=b.paddr

and   b.audsid=sys_context('userenv','sessionid')

and   c.name='user_dump_dest';

The result of the above query gives the path and name of the trace file that will be generated for the session.

3.      Following parameter have to be set before tracing a session.

SET TIMING ON

4.      Following queries have to be fired to trace the session

alter session set timed_statistics=true;

TIMED_STATISTICS is set to TRUE to get timing information in our trace files.

alter session set max_dump_file_size = unlimited;

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file.

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

Above command enables tracing for our specific user session.

5.      Execute all the SQLs or PL/SQLs for which we want to trace the session.

6.      Once we are done with tracing, we either exit our session to stop tracing or we can use the alter session command to stop it

ALTER SESSION SET EVENTS '10046 trace name context OFF';

7.      We can also set trace outside an applications session, i.e. if we don’t have access to the source code, we can remotely enable an extended trace for another session.

The command for the same is

EXEC SYS.DBMS_SYSTEM.SET_EV(&SID, &SERIAL_NUMBER, 10046, &TRACELEVEL, ' ');

The SID and SERIAL# can be fetched from V$SESSION.

Oracle will create the trace file in the server’s udump directory.

To stop the trace in the other session we can use the command as explained in step 6 or we use the following command: -

EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID, SERIAL_NUMBER);

8.      Connect to a duplicate session in Putty and go to the path where the trace file is generated (we get the path from the step 2). We can open the .trc file and check the content of it but the content is not in a readable format. Thus, we go for TKPROF.

9.      Go to the tkprof directory in the BIN directory of the Oracle and run the following command:-

TKPROF /u01/app/ORACLE/PRODUCT/12.2.0/ADMIN/TEST\UDUMP\TEST_ORA_4936.TRC  /u01/temp/OUTPUT.TXT

EXPLAIN=I102/ORACLE66@TEST TABLE=SYS.PLAN_TABLE SYS=NO WAITS=YES SORT=FCHELA,EXEELA,PRSELA

The output file name should be given with proper path where we want to create the .prf file.

NOTE: - We should have the 777 permission(permission to read, write, and execute) on all directories to run the TKPROF.

How to run SQL Tuning Adviser manually??

Step 1: Replace SQL_ID and TASK_NAME (Unique):

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'XXXXX',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 1800,

                          task_name   => 'XXXXX_A',

                          description => 'Tuning task for statement 8p9qh5xwgc3fx_B in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Step 2: Validate if SQL Tuning Task is created or not (Status should be INITIAL):

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 3: Run the Advisor

EXEC DBMS_SQLTUNE.execute_tuning_task('XXXXX_A');

Step 4: Check the status in other session using command below: (It should show EXECUTING)

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 5: Once the Advisor task is completed. Generate report using command below:

SELECT DBMS_SQLTUNE.report_tuning_task(‘XXXXX_A') AS recommendations FROM dual;

Step 6: Analyze the report and implement the solution if necessary.

Source: Thanks Arbind!!!