Obtaining Trace Files without Access to the Database Server

Why Trace? 

For many years, I used database SQL Trace to investigate SQL performance problems. I would trace a process, obtain the trace file, profile it (with Oracle's TKPROF or another profiling tool such as the Method R profiler, TVD$XTAT, or OraSRP), and analyse the profile. 

Active Session History (ASH) was introduced in Oracle 10g.  Today, it is usually where I start to investigate performance problems. It has the advantage that it is always on, and I can just query ASH data from the Automatic Workload Repository (AWR). However, ASH is only available on Enterprise Edition and requires the Diagnostics Pack licence. 

Sometimes, even if available, ASH isn't enough. ASH is based on sampling database activity, while trace is a record of all the SQL activity in a session. Some short-lived behaviour, that doesn't generate many samples, is difficult to investigate with ASH. Sometimes, it is necessary to dig deeper and use SQL trace. 

On occasion, you might want to generate other forms trace.  For example, an optimizer trace (event 10053) in order to understand how an execution plan was arrived at.

Where is my Trace File? 

A trend that I have observed over the years is that is is becoming ever more difficult to get hold of the trace files. If you are not the production DBA, you are unlikely to get access to the database server. Frequently, I find that pre-production performance test databases, which are often clones of the production database, are treated as production systems. After all, they contain production data. The move to the cloud has accelerated that trend. On some cloud services, you have no access to the database server at all! 

In the past, I have blogged about using an external table from which the trace file can be queried, a variation of a theme others had also written about. It required certain privileges, a new external table was required for each trace file, and you had to know the name of the trace file, and on which RAC instance it was located. 

However, in version 12.2, it is much easier.  Oracle has provided some new views that report what trace files are available and then query their contents. 

Where Is This Session Writing Its Trace File?

The Automatic Diagnostic Repository (ADR) was first documented in 11g. The view V$DIAG_INFO was introduced in 12c, from which you can query the state of the ADR. This includes the various directory paths to which files are written and the name of the current trace file.

select dbid, con_dbid, name from v$database;
column inst_id format 99 heading 'Inst|ID'
column con_id format 99 heading 'Con|ID'
column name format a22
column value format a95
select * from v$diag_info;

Inst                                                                                                                        Con
  ID NAME                   VALUE                                                                                            ID
---- ---------------------- ----------------------------------------------------------------------------------------------- ---
   1 Diag Enabled           TRUE                                                                                              0
   1 ADR Base               /opt/oracle/psft/db/oracle-server                                                                 0
   1 ADR Home               /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM                                        0
   1 Diag Trace             /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/trace                                  0
   1 Diag Alert             /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/alert                                  0
   1 Diag Incident          /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/incident                               0
   1 Diag Cdump             /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/cdump                                  0
   1 Health Monitor         /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/hm                                     0
   1 Default Trace File     /opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM/trace/CDBHCM_ora_27009_unnest.trc      0
   1 Active Problem Count   0                                                                                                 0
   1 Active Incident Count  0                                                                                                 0
   1 ORACLE_HOME            /opt/oracle/psft/db/oracle-server/19.3.0.0                                                        0

What files have been written? 

The available files are reported by V$DIAG_TRACE_FILE

column adr_home format a60
column trace_filename format a40
column change_time format a32
column modify_time format a32
column con_id format 999
select *
from v$DIAG_TRACE_FILE
where adr_home = '&adr_Home'
order by modify_time
/

ADR_HOME                                                     TRACE_FILENAME                           CHANGE_TIME                      MODIFY_TIME                      CON_ID
------------------------------------------------------------ ---------------------------------------- -------------------------------- -------------------------------- ------
…
/opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM   CDBHCM_ora_27674_no_unnest.trc           13-SEP-22 02.06.10.000 PM +00:00 13-SEP-22 02.06.10.000 PM +00:00      3
/opt/oracle/psft/db/oracle-server/diag/rdbms/cdbhcm/CDBHCM   CDBHCM_ora_27674_unnest.trc              13-SEP-22 02.06.11.000 PM +00:00 13-SEP-22 02.06.11.000 PM +00:00      3

What is in the file? 

I can then extract the contents of the file from V$DIAG_TRACE_FILE_CONTENTS. Each line of the trace is returned in a different row. 

This script spools the contents of the current trace file from SQL Plus locally to a file of the same name. It stores the name of the ADR home and its file path and the trace file name to SQL*Plus variables and then uses these to query the trace file contents. 

I can generate a trace and then run this script to extract it locally.

REM spooltrc.sql

clear screen
set heading on pages 99 lines 180 verify off echo off trimspool on termout on feedback off
column value format a95
column value new_value adr_home heading 'ADR Home' 
select value from v$diag_info where name = 'ADR Home';
column value new_value diag_trace heading 'Diag Trace'
select value from v$diag_info where name = 'Diag Trace';
column value new_value trace_filename heading 'Trace File'
select SUBSTR(value,2+LENGTH('&diag_trace')) value from v$diag_info where name = 'Default Trace File'
/
column adr_home format a60
column trace_filename format a40
column change_time format a32
column modify_time format a32
column con_id format 999
select *
from v$DIAG_TRACE_FILE
where adr_home = '&adr_home'
and trace_filename = '&trace_filename'
/
set head off pages 0 lines 5000 verify off echo off timi off termout off feedback off long 5000
spool &trace_filename
select payload
from v$diag_trace_file_contents
where adr_home = '&adr_home'
and trace_filename = '&trace_filename'
order by line_number
/
spool off
set head on pages 99 lines 180 verify on echo on termout on feedback on

The spooltrc.sql script is available on Github.  In a subsequent blog, I will demonstrate how to use it.

The payload is a VARCHAR2 column, so it is easy to search one or several trace files for specific text. This is useful if you are having trouble identifying the trace file of interest. 

See also:

• Franck Pachot's Blog: Exadata Express Cloud Service: SQL and Optimizer trace.