Performing Application Tracing

We can do end-to-end application tracing using oracle supplied tool and can generate and read trace files.

Tools for End-to-End Application Tracing

The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you accurately assess the efficiency of the SQL statements an application runs.
For best results, use these tools with EXPLAIN PLAN rather than using EXPLAIN PLAN alone.
After tracing information is written to files, you can consolidate this data with the TRCSESS utility, and then diagnose it with TKPROF or SQL Trace.

=> Overview of the SQL Trace Facility- SQL Trace

The SQL Trace facility provides performance information on individual SQL statements.

SQL Trace generates the following statistics for each statement:

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
User name under which each parse occurred
Each commit and rollback
Wait event data for each SQL statement, and a summary for each trace file

If the cursor for the SQL statement is closed, then SQL Trace also provides row source information that includes:
=> Row operations showing the actual execution plan of each SQL statement
=> Number of rows, number of consistent reads, number of physical reads, number of physical writes, and time elapsed for each operation on a row

Although we can enable the SQL Trace facility for a session or an instance using below commands,

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
OR
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

Oracle recommends that you use the DBMS_SESSION or DBMS_MONITOR packages instead.

=> The TRCSESS command-line 

The TRCSESS command-line utility consolidates tracing information from several trace files based on specific criteria, such as session or client ID.

=>  Overview of TKPROF

To format the contents of the trace file and place the output into a readable output file, run the TKPROF program.

TKPROF can also do the following:

=>Create a SQL script that stores the statistics in the database
=>Determine the execution plans of SQL statements

TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, 
and the number of rows which it processed.

DBMS_MONITOR

Enabling End-to-End Application Tracing using DBMS_MONITOR

To enable tracing for client identifier, service, module, action, session, instance or database, 
execute the appropriate procedures in the DBMS_MONITOR package.
With the criteria that you provide, specific trace information is captured in a set of trace files and combined into a single output trace file.

The DBMS_MONITOR package enables you to use PL/SQL for controlling additional tracing and statistics gathering.
Below are available Procedures for tracing.



A=> Tracing for Client_ID



CLIENT_ID_TRACE_ENABLE Procedure 

This procedure will enable the trace for a given client identifier globally for the database.

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
client_id    IN  VARCHAR2,
waits        IN  BOOLEAN DEFAULT TRUE,
binds        IN  BOOLEAN DEFAULT FALSE,
plan_stat    IN  VARCHAR2 DEFAULT NULL);

Parameter    Description
client_id =>     Database Session Identifier for which SQL tracing is enabled
waits   =>        If TRUE, wait information is present in the trace
binds    =>      If TRUE, bind information is present in the trace
plan_stat =>    Frequency at which we dump row source statistics. Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.

The client identifier was introduced to provide a method of uniquely identifying sessions when many connect using the same Oracle user.

The client identifier can be set for a session using the SET_IDENTIFIER procedure in the DBMS_SESSION package. For example:

EXECUTE DBMS_SESSION.SET_IDENTIFIER ('SH_TESTING');

DEMO

=>  Login to SH schema on separate session and execute below commands .

[oracle@ace2oracledb trace]$ sqlplus sh/sh

SQL> EXECUTE DBMS_SESSION.SET_IDENTIFIER ('SH_TESTING');  << this will set identifier at session level

PL/SQL procedure successfully completed.

Now login to administrative account to ENABLE Tracing as below.

=>  First let we get Identifier details as below,

SQL> select p.PID,p.SPID,s.SID,s.CLient_identifier
from v$process p,v$session s
where s.paddr = p.addr and s.username like 'SH';

       PID SPID                SID CLIENT_IDENTIFIER
---------- ------------------------ ---------- ----------------------------------------------------------------
    56 26819                95 SH_TESTING
SQL>

=> Let we enable tracing as below,

SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('SH_TESTING', TRUE,TRUE,'ALL_EXECUTIONS');

PL/SQL procedure successfully completed.

=> Let we switch to SH session and run few commands as below,

SQL> select * from products where rownum<10;
..
..
..

=> Close SH session and let we return to SYS session to identify *.trc file as below,

[oracle@ace2oracledb log]$ sqlplus "/as sysdba"

SQL> select * from v$diag_info;

 1 Default Trace File
/oracle_home/app/oracle/diag/rdbms/stdb/STDB/trace/STDB_ora_30409.trc
     0
 [oracle@ace2oracledb log]$ cd /oracle_home/app/oracle/diag/rdbms/stdb/STDB/trace/

Now lookout for trace file with SPID as highlighted above in yellow marks.

[oracle@ace2oracledb trace]$
[oracle@ace2oracledb trace]$ ls -lrt STDB_ora_26819.trc
-rw-r-----. 1 oracle asmadmin 7183 Feb 21 18:10 STDB_ora_26819.trc
[oracle@ace2oracledb trace]$

SO we found trace file for Client Identifier “SH_TESTING”. We will analyze this file using TKPROF later.

CLIENT_ID_TRACE_DISABLE Procedure

This procedure will disable tracing enabled by the CLIENT_ID_TRACE_ENABLE Procedure.

DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE( client_id    IN  VARCHAR2);

to disable above operation execute below,

[oracle@ace2oracledb log]$ sqlplus "/as sysdba"

SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ('SH_TESTING');

PL/SQL procedure successfully completed.

SQL>

B=> Tracing for DATABASE or INSTANCE level




DATABASE_TRACE_ENABLE Procedure

This procedure enables SQL trace for the whole database or a specific instance.

DBMS_MONITOR.DATABASE_TRACE_ENABLE(
   waits          IN BOOLEAN DEFAULT TRUE,
   binds          IN BOOLEAN DEFAULT FALSE,
   instance_name  IN VARCHAR2 DEFAULT NULL,
   plan_stat      IN VARCHAR2 DEFAULT NULL);

DEMO
SQL> EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE();

PL/SQL procedure successfully completed.

[oracle@ace2oracledb log]$ sqlplus "/as sysdba"

SQL>
SQL> SELECT COUNT(*) FROM TAB;

  COUNT(*)
----------
      8287

[oracle@ace2oracledb trace]$ cd /oracle_home/app/oracle/diag/rdbms/stdb/STDB/trace
[oracle@ace2oracledb trace]$
[oracle@ace2oracledb trace]$ ls -lrt STDB_ora_31996.trc
-rw-r-----. 1 oracle asmadmin 19307 Feb 21 18:23 STDB_ora_31996.trc
[oracle@ace2oracledb trace]$

DATABASE_TRACE_DISABLE Procedure

This procedure disables SQL trace for the whole database or a specific instance.
DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name  IN VARCHAR2 DEFAULT NULL);

SQL> EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE();

PL/SQL procedure successfully completed.


C=> Tracing on MODULE and ACTION Basis



SERV_MOD_ACT_TRACE_ENABLE Procedure

This procedure will enable SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified.
The module name and action name are stored in the SGA for each session. 
They can be set using the SET_MODULE and SET_ACTION procedures of the DBMS_APPLICATION_INFO package

Syntax
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
   service_name    IN VARCHAR2,
   module_name     IN VARCHAR2 DEFAULT ANY_MODULE,
   action_name     IN VARCHAR2 DEFAULT ANY_ACTION,
   waits           IN BOOLEAN DEFAULT TRUE,
   binds           IN BOOLEAN DEFAULT FALSE,
   instance_name   IN VARCHAR2 DEFAULT NULL,
   plan_stat       IN VARCHAR2 DEFAULT NULL);

Parameter    Description
service_name    => Name of the service for which SQL trace is enabled
module_name    => Name of the MODULE for which SQL trace is enabled. If omitted, SQL trace is enabled or all modules and actions in a given service.
action_name    => Name of the ACTION for which SQL trace is enabled. If omitted, SQL trace is enabled for all actions in a given module.
waits    => If TRUE, wait information is present in the trace
binds    => If TRUE, bind information is present in the trace
instance_name    => If set, this restricts tracing to the named instance_name
plan_stat    => Frequency at which we dump row source statistics. Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.

DEMO

=> Login from ABHI_TEST and execute below statement.

CREATE or replace PROCEDURE add_emp(
  name VARCHAR2,
  salary NUMBER
  ) AS
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE( module_name => 'ad_to_emp', action_name => 'test_emp');
  INSERT INTO emp_testing (ename,sal) VALUES (name,salary);
  commit;
  DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END;

=> Login from sys user and run below.

SQL> select p.PID,p.SPID,s.SID,s.CLient_identifier,SERVICE_NAME
from v$process p,v$session s
where s.paddr = p.addr and s.username like 'ABHI_TEST';  2    3  

     PID SPID                SID  CLIENT_IDENTIFIER  SERVICE_NAME
----------------------------------------------------------------
    59  4441                    42                          SYS$USERS

SQL> EXECUTE dbms_monitor.serv_mod_act_trace_enable  (service_name=>'SYS$USERS',module_name=>'ad_to_emp',action_name=>'test_emp');

PL/SQL procedure successfully completed.

SQL> exit

=> Login from ABHI_TEST and execute below statement.

SQL> exec add_emp('abhishek',500);

PL/SQL procedure successfully completed.

SQL>

=> Login at OS prompt and look for trace file as below.

[oracle@ace2oracledb trace]$ ls -lrt  STDB_ora_4441.trc
-rw-r-----. 1 oracle asmadmin 182699 Feb 21 21:00 STDB_ora_4441.trc
[oracle@ace2oracledb trace]$

SERV_MOD_ACT_TRACE_DISABLE Procedure

This procedure will disable the trace at ALL enabled instances for a given combination of Service Name, MODULE, and ACTION name globally.

DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
   service_name    IN  VARCHAR2,
   module_name     IN  VARCHAR2,
   action_name     IN  VARCHAR2 DEFAULT ALL_ACTIONS,
   instance_name   IN  VARCHAR2 DEFAULT NULL);

=> Login from sys user and run below to Disable

SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'SYS$USERS',module_name=>'ad_to_emp',action_name=>'test_emp');

PL/SQL procedure successfully completed.

SQL>

D=> Tracing on SID Basis



SESSION_TRACE_ENABLE Procedure

This procedure enables a SQL trace for the given Session ID on the local instance
Syntax
DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id   IN  BINARY_INTEGER DEFAULT NULL,
    serial_num   IN  BINARY_INTEGER DEFAULT NULL,
    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE,
    plan_stat    IN  VARCHAR2 DEFAULT NULL);

Parameter    Description
session_id    => Client Identifier for which SQL trace is enabled. If omitted (or NULL), the user's own session is assumed.
serial_num    => Serial number for this session. If omitted (or NULL), only the session ID is used to determine a session.
waits    => If TRUE, wait information is present in the trace
binds    => If TRUE, bind information is present in the trace
plan_stat    => Frequency at which we dump row source statistics. 
               Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.

The procedure enables a trace for a given database session, and is still useful for client/server applications. 
The trace is enabled only on the instance to which the caller is connected, since database sessions do not span instances. 
This tracing is strictly local to an instance.

DEMO

=> Login using ABHI_TEST and wait for execution.

[oracle@ace2oracledb trace]$ sqlplus abhi_test/Oracle_4U

SQL>

=> Login in using SYS user and collect SID,PID and SERIAL# details as below

SQL> set lines 500
col username for a40
col service_name for a20
select s.username,p.PID,p.SPID,s.SID,s.CLient_identifier,s.SERVICE_NAME,s.SERIAL#
from v$process p,v$session s
where s.paddr = p.addr and s.username like 'ABHI_TEST';SQL> SQL> SQL>   2    3  

USERNAME     PID SPID        SID CLIENT_IDENTIFIER   SERVICE_NAME    SERIAL#
---------------------------------------- ---------- ------------------------ 
ABHI_TEST    36 11100        109                    SYS$USERS        38453

=> Now enable SID level tracing as below,

SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(109,38453, TRUE, FALSE);

PL/SQL procedure successfully completed.

SQL>

=> Switch to ABHI_TEST and run few commands as below,

SQL> select * from tab where rownum<4;

TNAME  TABTYPE        CLUSTERID
------------- ----------
BIT_TESTING TABLE
DATA_EMP TABLE
DEMO_DATA TABLE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ace2oracledb trace]$

=> Now check for trace file at OS prompt as below.
[oracle@ace2oracledb trace]$ cd /oracle_home/app/oracle/diag/rdbms/stdb/STDB/trace
[oracle@ace2oracledb trace]$ ls -lrt *11100*
-rw-r-----. 1 oracle asmadmin  2285 Feb 21 21:20 STDB_ora_11100.trm
-rw-r-----. 1 oracle asmadmin 10075 Feb 21 21:20 STDB_ora_11100.trc
[oracle@ace2oracledb trace]$

SESSION_TRACE_DISABLE Procedure

This procedure will disable the trace for a given database session at the local instance.
Syntax
DBMS_MONITOR.SESSION_TRACE_DISABLE(
   session_id      IN     BINARY_INTEGER DEFAULT NULL,
   serial_num      IN     BINARY_INTEGER DEFAULT NULL);

SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE();

PL/SQL procedure successfully completed.

SQL> EXIT

Equivalent Operations

If in DBMS_MONITOR any procedure which mentioned as above uses below values then these are equivalent to trace event 10046, as mentioned.

Trace Levels 

Level 0 - No trace. Just like switching sql_trace off.
Level 2 - The equivalent of regular sql_trace.

Level 4 - The same as level 2, but with the addition of bind variable values.
EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',binds=>true);

Level 8 - The same as level 2, but with the addition of wait events.
EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',waits=>true);

Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.
EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',waits=>true,binds=>true);

Application Tracing Utilities

The Oracle tracing utilities are TKPROF and TRCSESS.

==> TRCSESS

The TRCSESS utility consolidates trace output from selected trace files based on user-specified criteria.
After TRCSESS merges the trace information into a single output file, TKPROF can process the output file.

Purpose
TRCSESS is useful for consolidating the tracing of a particular session for performance or debugging purposes.

Syntax

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

Options
TRCSESS supports a number of command-line options.
Argument    Description
output    => Specifies the file where the output is generated. If this option is not specified, 
              then the utility writes to standard output.
session    => Consolidates the trace information for the session specified. 
               The session identifier is a combination of session index and session serial number, 
                such as 21.237.You can locate these values in the V$SESSION view.
clientid    => Consolidates the trace information for the specified client ID.
service    => Consolidates the trace information for the specified service name.
action    => Consolidates the trace information for the specified action name.
module    => Consolidates the trace information for the specified module name.
trace_files    => Lists the trace file names, separated by spaces, 
                   in which TRCSESS should look for trace information.

DEMO

[oracle@ace2oracledb trace]$
[oracle@ace2oracledb trace]$ trcsess output=service_filter.txt   service='SYS$USERS'   STDB_ora_4441.trc   STDB_ora_11100.trc

[oracle@ace2oracledb trace]$ cat service_filter.txt
*** [ Unix process pid: 4441 ]
*** 2022-02-21T20:57:20.811607+05:30
*** 2022-02-21T20:57:20.811611+05:30
*** 2022-02-21T20:57:20.811616+05:30
*** CLIENT DRIVER:(SQL*PLUS) 2022-02-21T20:57:20.811620+05:30

==> TKPROF

The TKPROF program formats the contents of the trace file and places the output into a readable output file.
TKPROF can also do the following:
•    Create a SQL script that stores the statistics in the database
•    Determine the execution plans of SQL statements
Note:
If the cursor for a SQL statement is not closed, then TKPROF output does not automatically include the actual execution plan of the SQL statement.
In this situation, use the EXPLAIN option with TKPROF to generate an execution plan.
TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed.

Purpose
TKPROF can locate statements that are consuming the greatest resources.
With baselines available, you can assess whether the resources used are reasonable given the work performed.

Guidelines
The input and output files are the only required arguments.
If you invoke TKPROF without arguments, then the tool displays online help.

 Syntax

tkprof input_file output_file
  [ waits=yes|no ]
  [ sort=option ]
  [ print=n ]
  [ aggregate=yes|no ]
  [ insert=filename3 ]
  [ sys=yes|no ]
  [ table=schema.table ]
  [ explain=user/password ]
  [ record=filename4 ]
  [ width=n ]

[oracle@ace2oracledb trace]$  tkprof service_filter.txt service_output.prf PRINT = 10

TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 21 22:42:25 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
[oracle@ace2oracledb trace]$
[oracle@ace2oracledb trace]$ cat service_output.prf

TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 21 22:42:25 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: service_filter.txt
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
********************************************************************************

TKPROF DEEP DIVE

tkprof accept below parameters as mentioned.

Argument    Description
input_file      =>  Specifies the input file, a trace file containing statistics produced by the SQL Trace facility.
output_file     =>  Specifies the file to which TKPROF writes its formatted output.
WAITS           =>  Specifies whether to record summary for any wait events found in the trace file.(YES)
SORT            =>  Sorts traced SQL statements in descending order of specified sort option before listing them in the output file.
Sort options are listed as follows:
•    PRSCNT - Number of times parsed
•    PRSCPU - CPU time spent parsing
•    PRSELA - Elapsed time spent parsing
•    PRSDSK - Number of physical reads from disk during parse
•    PRSQRY - Number of consistent mode block reads during parse
•    PRSCU - Number of current mode block reads during parse
•    PRSMIS - Number of library cache misses during parse
•    EXECNT - Number of executions
•    EXECPU - CPU time spent executing
•    EXEELA - Elapsed time spent executing
•    EXEDSK - Number of physical reads from disk during execute
•    EXEQRY - Number of consistent mode block reads during execute
•    EXECU - Number of current mode block reads during execute
•    EXEROW - Number of rows processed during execute
•    EXEMIS - Number of library cache misses during execute
•    FCHCNT - Number of fetches
•    FCHCPU - CPU time spent fetching
•    FCHELA - Elapsed time spent fetching
•    FCHDSK - Number of physical reads from disk during fetch
•    FCHQRY - Number of consistent mode block reads during fetch
•    FCHCU - Number of current mode block reads during fetch
•    FCHROW - Number of rows fetched
•    USERID - ID of user that parsed the cursor

PRINT           =>  Lists only the first integer sorted SQL statements from the output file.
AGGREGATE       =>  If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple users of the same SQL text.
INSERT          =>  Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3.
SYS             =>  Enables and disables the listing of SQL statements issued by the user SYS,
TABLE           =>  Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file.
EXPLAIN         =>  Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file.
RECORD          =>  Creates a SQL script with the specified filename with all of the nonrecursive SQL in the trace file. You can use this script to replay the user events from the trace file.
WIDTH           =>  An integer that controls the output line width of some TKPROF output, such as the explain plan. This parameter is useful for post-processing of TKPROF output.

Output  Analysis
This section explains the TKPROF output.

1=> Identification of User Issuing the SQL Statement in TKPROF

TKPROF lists the user ID of the user issuing each SQL statement.
If the SQL Trace input file contained statistics from multiple users, and if the statement was issued by multiple users,
then TKPROF lists the ID of the last user to parse the statement. The user ID of all database users appears in the data dictionary in the column ALL_USERS.USER_ID.




2=> Tabular Statistics in TKPROF

TKPROF lists the statistics for a SQL statement returned by the SQL Trace facility in rows and columns.
Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL column.

CALL Value    Meaning
PARSE         => Translates the SQL statement into an execution plan.
EXECUTE     => For INSERT, UPDATE, DELETE, and MERGE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH         => Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

SQL Trace Statistic    Meaning
COUNT      => Number of times a statement was parsed, executed, or fetched.
CPU           => Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not enabled.
ELAPSED   => Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not enabled.
DISK          => Total number of data blocks physically read from the data files on disk for all parse, execute, or fetch calls.
QUERY       => Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT   => Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

Statistics about the processed rows appear in the ROWS column. The column shows the number of rows processed by the SQL statement.
For SELECT statements, the number of rows returned appears for the fetch step. For UPDATE, DELETE, and INSERT statements, the number of rows processed appears for the execute step.



3=> Library Cache Misses in TKPROF
TKPROF also lists the number of library cache misses resulting from parse and execute steps for each SQL statement.
These statistics appear on separate lines following the tabular statistics. If the statement resulted in no library cache misses, then TKPROF does not list the statistic.



4=> Row Source Operations in TKPROF 

In the TKPROF output, row source operations show the number of rows processed for each operation executed on the rows, and additional row source information,
such as physical reads and writes.

Row Source Operation    Meaning
cr                                   => Consistent reads performed by the row source.
r                                     => Physical reads performed by the row source
w                                    => Physical writes performed by the row source
time                                => Time in microseconds





5=>Wait Event Information in TKPROF 

If wait event information exists, then the TKPROF output includes a section on wait events.
Output looks similar to the following:


To ensure that wait events information is written to the trace file for the session, run the following SQL statement:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

DEMO OF TKPROF

=> Print MOST expensive TOP 10 where invoker NOT LIKE SYS.

[oracle@ace2oracledb trace]$ tkprof STDB_ora_11100.trc most_intensive_op.prf SORT =PRSDSK, EXEDSK, FCHDSK PRINT = 10 SYS=NO

=> Generating a SQL Script

[oracle@ace2oracledb trace]$ tkprof STDB_ora_22916.trc with_insert_query.txt EXPLAIN=ABHI_TEST TABLE=ABHI_TEST.temp_a INSERT=test.sql SYS=NO SORT=EXECPU,FCHCPU
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 26 00:01:25 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

password =
[oracle@ace2oracledb trace]$
[oracle@ace2oracledb trace]$ more with_insert_query.txt
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 26 00:01:25 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: STDB_ora_22916.trc
Sort options: execpu  fchcpu  
********************************************************************************
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
********************************************************************************
SQL ID: 8qp9fwuhq09y0 Plan Hash: 1391721160
select * from products where rownum <100

[oracle@ace2oracledb trace]$ cat test.sql
REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
 date_of_insert                       DATE   
,cursor_num                           NUMBER 
,depth                                NUMBER 
,user_id                              NUMBER 

Views for Application Tracing

View     Description
DBA_ENABLED_AGGREGATIONS     Accumulated global statistics for the currently enabled statistics
V$CLIENT_STATS     Accumulated statistics for a specified client identifier
V$SERVICE_STATS     Accumulated statistics for a specified service
V$SERV_MOD_ACT_STATS     Accumulated statistics for a combination of specified service, module, and action
V$SERVICEMETRIC     Accumulated statistics for elapsed time of database calls and for CPU use
V$DIAG_TRACE_FILE     Information about all trace files in ADR for the current container
V$DIAG_APP_TRACE_FILE     Information about all trace files that contain application trace data  
V$DIAG_TRACE_FILE_CONTENTS     Trace data in the trace files in ADR
V$DIAG_SQL_TRACE_RECORDS     SQL_TRACE data in the trace files in ADR
V$DIAG_OPT_TRACE_RECORDS     Optimizer trace event data in the trace files in ADR
V$DIAG_SESS_SQL_TRACE_RECORDS     SQL_TRACE data in the trace files in ADR for the current user session
V$DIAG_SESS_OPT_TRACE_RECORDS     Optimizer trace event data in the trace files in ADR for the current user session
V$DIAG_ALERT_EXT     Contents of the XML-based alert log in ADR for the current container
DBA_ENABLED_TRACES   you can determine detailed information about how a trace was enabled, including the trace type.