Want 10053 traces? Can you query GV$ views?

If so, you’re in luck. You may have ALTER SESSION privileges in your database, but when you enable a 10053 trace and run your query, you might not have access to the trace directory on the database server or can’t find someone who does. Why wait? Use the appropriate GV$ diagnostic views available since Oracle Database 12.2.0.1.

Setting up a typical 10053 trace

You have a query that does parse and run, but sometimes performs poorly and you need more than a SQL Monitoring report to figure out why. An optimizer trace file can likely help you figure out where the cardinalities or access path choices went wrong. Here, I’m tracing a query referencing a couple of data dictionary views DBA_TABLES and DBA_TAB_COLUMNS.

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier='dd_issue';
alter session set events '10053 trace name context forever, level 1';

select *
from dba_tables
   join dba_tab_columns
      using (owner,table_name)
where table_name='FRED';

alter session set events '10053 trace name context off';

Since I set TRACEFILE_IDENTIFIER to ‘dd_issue‘ the trace file itself should be easy to find in the OS trace file directory, but since I don’t have access to any directories at the OS level, I’ll have to use GV$DIAG_TRACE_FILE to find it:

select inst_id,adr_home,trace_filename
from gv$diag_trace_file
where trace_filename like '%dd_issue%';

It’s possible that more than one trace file has the identifier ‘dd_issue‘ in it, but in this case there is only one.

Now that I know that there is only one trace file I’m looking for, I’ll retrieve the rows from the view GV$DIAG_TRACE_FILE_CONTENTS and make sure they are in the same order as the lines are in the trace file itself:

select payload
from gv$diag_trace_file
   join gv$diag_trace_file_contents
      using(inst_id,adr_home,trace_filename)
where trace_filename = 'qa21c_ora_3741366_dd_issue.trc'
order by line_number;

With the results of the query using GV$DIAG_TRACE_FILE_CONTENTS, you can browse the first few lines, save to a text file, or filter the results even further based on other columns in GV$DIAG_TRACE_FILE_CONTENTS:

For example, you might want to see only the sections of a trace file (since they can be 100,000+ lines!) that contain metrics on SQL_Costing, for example:

Conclusion

Don’t let access to the trace file directory on the database server slow you down! If you have access to the GV$ views and can run ALTER SESSION commands, you can get the trace file contents right away with the view GV$DIAG_TRACE_FILE_CONTENTS.

I haven’t forgotten about easier ways to get Exadata cell server trace files, and that will be the subject of a future blog post!