Here’s a convenient enhancement for tracing that came up on Twitter a few days ago – first in a tweet that I retweeted, then in a question from Christian Antognini based on this bit of the 12c Oracle documentation (opens in separate tab). The question was – does it work for you ?
The new description for max_dump_file_size says that for large enough values Oracle will split the file into multiple chunks of a few megabytes, using a suffix to identify the sequence of the chunks, keeping only the first chunk and the most recent chunks. Unfortunately this doesn’t seem to be true. However, prompted by Chris’ question I ran a quick query against the full parameter list looking for parameters with the word “trace” in their name:
rem rem Script: params.sql rem Author: Jonathan Lewis rem Dated: Sept 1998 (and tweaks) rem define m_search = '&1' set linesize 512 set pagesize 60 set trimspool on column name format a57 heading "Parameter" column ses_val format a30 word wrapped heading "Session Value" column sys_val format a30 word wrapped heading "System Value" column description format a70 heading "Description" select /*+ leading(nam val val2) full(name) full(val) use_hash(val) no_swap_join_inputs(val) full(val2) use_hash(val2) no_swap_join_inputs(val2) */ nam.ksppinm name, val.ksppstvl ses_val, val2.ksppstvl sys_val, nam.ksppdesc description, nam.indx+1 numb, nam.ksppity type, val.ksppstdf is_def, decode(bitand(nam.ksppiflg/256,1), 1,'True', 'False' ) ses_mod, decode(bitand(nam.ksppiflg/65536,3), 1,'Immediate', 2,'Deferred' , 3,'Immediate', 'False' ) sys_mod, decode(bitand(val.ksppstvf,7), 1,'Modified', 4,'System Modified', 'False' ) is_mod, decode(bitand(val.ksppstvf,2), 2,'True', 'False' ) is_adj, val.ksppstcmnt notes from x$ksppi nam, x$ksppcv val, x$ksppsv val2 where nam.indx = val.indx and val2.indx = val.indx and upper(ksppinm) like upper('%&m_search.%') escape '\' order by nam.ksppinm ;
Glancing through the result I spotted a couple of interesting parameters with the letters “uts” in their names, so re-ran my query looking for all the “uts” parameters, getting the following (edited) list:
NAME SYS_VAL DESCRIPTION ------------------------------ --------------- --------------------------------------------- _diag_uts_control 0 UTS control parameter _uts_first_segment_retain TRUE Should we retain the first trace segment _uts_first_segment_size 0 Maximum size (in bytes) of first segments _uts_trace_disk_threshold 0 Trace disk threshold parameter _uts_trace_segment_size 0 Maximum size (in bytes) of a trace segment _uts_trace_segments 5 Maximum number of trace segments
Note particularly the “first segment size” and “trace segment size” – defaulting to zero (which often means a hidden internal setting, though that doesn’t seem to be the case here, but maybe that’s what the “diag control” is for). I haven’t investigated all the effects, but after a little experimentation I found that all I needed to do to get the behaviour attributed to max_dump_file_size was to set the following two parameters – which I could do at the session level.
alter session set "_uts_first_segment_size" = 5242880; alter session set "_uts_trace_segment_size" = 5242880;
The minimum value for these parameters is the one I’ve shown above (5120 KB) and with the default value for _uts_trace_segments you will get a maximum of 5 trace files with sequential names like the following:
ls -ltr *4901*.trc -rw-r----- 1 oracle oinstall 5243099 Jan 26 08:15 orcl_ora_4901_1.trc -rw-r----- 1 oracle oinstall 5243064 Jan 26 08:15 orcl_ora_4901_12.trc -rw-r----- 1 oracle oinstall 5243058 Jan 26 08:15 orcl_ora_4901_13.trc -rw-r----- 1 oracle oinstall 5242993 Jan 26 08:15 orcl_ora_4901_14.trc -rw-r----- 1 oracle oinstall 1363680 Jan 26 08:15 orcl_ora_4901.trc
As you can see I’m currently generating my 15th trace, and Oracle has kept the first one and the previous three. It’s always working on a file with no suffix to its name but as soon as that file hits its limiting size (plus or minus a few bytes) it gets its appropriate suffix, the oldest file is deleted, and a new trace file without a suffix is started.
Apart from the usual header information the trace files start and end with lines like:
*** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4901_11.trc *** *** TRACE SEGMENT RENAMED TO /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4901_12.trc ***
There is one little trap to watch out for: if you set either of these parameters to be larger than max_dump_file_size tracing stops as soon as one of the segments hits the max_dump_file_size and that trace file ends with the usual “overflow” message – e.g, when I changed the max_dump_file_size to 4M in mid-session:
*** DUMP FILE SIZE IS LIMITED TO 4194304 BYTES ***
In my case I had started with max_dump_file_size set to 20M, so I got lucky with my choice of 5M as the segment size.
Further investigation is left as an exercise to the interested reader.
[…] As a follow-up, few days later Jonathan Lewis published a post entitled Trace file size. […]
Pingback by Trace Files Split in Multiple Parts – Striving for Optimal Performance — October 13, 2016 @ 3:56 pm BST Oct 13,2016 |
[…] As a follow-up, few days later Jonathan Lewis published a post entitled Trace file size. […]
Pingback by Trace Files Split in Multiple Parts – Cloud Data Architect — October 14, 2016 @ 7:16 am BST Oct 14,2016 |
[…] script to generate this list is available in this note about trace files, with many of the columns commented out. I’ve run the script from SQL*Plus using the […]
Pingback by 19c Trivia | Oracle Scratchpad — January 12, 2022 @ 12:55 pm GMT Jan 12,2022 |