Reading Trace files with SQL

Oracle 12.2 provided some new views that enable trace files to be read via SQL. Previously, it had been possible to do this by creating external tables, but the new views make it much easier. You can simply query what trace files exist with SQL, and then access them without need for server access. 

This is particularly useful on some cloud platforms such as autonomous database, where there is no server access, even for the DBA. However, this technique is applicable to all Oracle databases.  Now, not just the DBA, but developers can easily obtain trace files.

Lots of other people have blogged about this, but Chris Antognini makes the point extremely well:

• TKPROF’s Argument PDBTRACE
• SQL Trace in Oracle Database Exadata Express Cloud Service 

In a post on my PeopleSoft blog, I demonstrated enabling trace on an application server process.  I also specified that as a trace file identifier. Now I can query the trace files that exist, and restrict the query by filename or date.

set pages 99
select * from gv$diag_trace_file f
where 1=1
and f.modify_time > trunc(sysdate)-1
and f.trace_filename like 'finprod%ora%.trc'
order by modify_time desc
/

   INST_ID ADR_HOME                                                     TRACE_FILENAME                 CHANGE_TIME                          MODIFY_TIME                              CON_ID
---------- ------------------------------------------------------------ ------------------------------ ------------------------------------ ------------------------------------ ----------
         1 /u02/app/oracle/diag/rdbms/finprod/finprod1                  finprod1_ora_306641.trc        23/03/2023 21.25.41.000000000 -05:00 23/03/2023 21.25.41.000000000 -05:00          0

Then I can also query the trace file contents, and even just spool it to a local file.

clear screen 
set head off pages 0 feedback off
with x as (
select /*+LEADING(F)*/ f.trace_filename, c.line_number, c.payload
--, max(c.line_number) over (partition by c.trace_filename) max_line_number
from gv$diag_trace_file f, gv$diag_trace_File_contents c
where c.adr_home = f.adr_home
and c.trace_filename = f.trace_filename
and f.modify_time > trunc(sysdate)-1
and f.trace_filename like 'finprod%ora%306641.trc'
)
select payload from x
ORDER BY line_number
/

The contents of the spool file looks just like the trace file.  I can profile it with tkprof or another trace profiler.

Trace file /u02/app/oracle/diag/rdbms/finprod/finprod1/trace/finprod1_ora_306641.trc
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Build label:    RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701
ORACLE_HOME:    /u02/app/oracle/product/19.0.0.0/dbhome_1
System name:      Linux 
Node name:  naukp-aora101
Release:    4.14.35-2047.514.5.1.2.el7uek.x86_64 
Version:    #2 SMP Thu Jul 28 15:33:31 PDT 2022 
Machine:    x86_64 
Storage:    Exadata 
Instance name: finprod1
Redo thread mounted by this instance: 1
Oracle process number: 225
Unix process pid: 306641, image: oracle@xxxxp-aora102

*** 2023-03-23T21:46:34.632063-04:00
*** SESSION ID:(2337.13457) 2023-03-23T21:46:34.632080-04:00
*** CLIENT ID:(NVRUNCNTL) 2023-03-23T21:46:34.632086-04:00
*** SERVICE NAME:(finprod.acme.com) 2023-03-23T21:46:34.632161-04:00
*** MODULE NAME:(RPTBOOK) 2023-03-23T21:46:34.632166-04:00
*** ACTION NAME:(PI=9980346:NVGL0042:42001) 2023-03-23T21:46:34.632171-04:00
*** CLIENT DRIVER:() 2023-03-23T21:46:34.632177-04:00

IPCLW:[0.0]{-}[RDMA]:RC: [1679622394631549]Connection 0x7f83ee131550 not formed (2). Returning retry.
IPCLW:[0.1]{E}[RDMA]:PUB: [1679622394631549]RDMA lport 0x400012c62778 dst 100.107.2.7:40056 bid 0x1805ea7b58 rval 2