In this case for column c the first entry would be 1 and the rest would be zero. When k1 = 1 then it is 1, otherwise it is zero. Note that column k1 is monolithically increasing number. The code for creating table t1 and populating it is shown below Let us try the query that I used to illustrate SQL tracing with AUTOTRACE here. This can be set via ALTER SESSION SET TRACEFILE_IDENTIFIER = “some identifer”

TRACEID is a character string of your choosing.

PID is the operating system process ID from V$PROCESS.SPID.

INSTANCE is the name of the Oracle instance.The default name for a trace files is in the format INSTANCE_PID_ora_c where: Trace output is written to the database’s trace directory that can be identified by DBA user account can trace it for other sessions for example EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 51, serial_num => 60, waits => TRUE).For example grant execute on DBMS_MONITOR to sh The DBA user account can grant execution on the DBMS_MONITOR package to other users.DBMS_MONITOR package once installed, its execution is granted to DBA user account.

For the examples here I will use DBMS_MONITOR package There are a number of methods to trace oracle sessions so I am not going to cover them here. Rather, we will focus using SQL Trace within a session. Unlike AUTOTRACE that was covered here, SQL Trace covers PL/SQL blocks and can be used for all sorts of performance investigation. The SQL Trace facility and TKPROF (ToolKit Profiler) are two comprehensive and powerful performance diagnostic and summarising tools that can be used to identify the performance issues with Oracle code. Looking at Oracle’s performance with SQL Trace and TKPROF ApPosted by Mich Talebzadeh in Oracle.

Tkprof report