- COMMAND LINE QUOTING
- RC FILES
- BUGS AND DEFICIENCIES
- COPYRIGHT AND LICENSE
mrls - list attributes of Oracle extended SQL trace files
$ mrls [options] [file...]
--all or -a include directories whose names begin with dot (.) --cpu or -c print total dep=0 CPU time --cpuunit=float number of seconds represented by one c unit --csv print output as comma-separated values --dashes print dashes in header --dep print recursive depth of task's top-most call --details or -d shorthand for --cpu --dep --lio --mis --pio --row --dformat=string set duration format --end print end time of the experience measured by the file --format=string set output format type --eula print End User License Agreement and exit --head print header row --help print help text and exit --human or -h shorthand for --dformat=human --tformat=human --lio or -l print total LIO count --man print manual page and exit --mis or -m print total library cache miss count --none deselect all printable columns except for R and FILE --ora or -o print Oracle version of the trace file --pio or -p print total PIO count --precision=n round to n digits post-decimal precision --rc=file use named rc file; --norc prevents rc file usage --recursive or -R plunge directories recursively --reverse sort in reverse order --row print total rows processed count --scanmax=n scan n lines for file meta data --size or -s print size of file in bytes --sort=string sort rows in specified order --start print start time of the experience measured by the file --tformat=string set time format --timunit=float number of seconds represented by one tim unit --trcunit=float override for both --cpuunit and --timunit --tim or -t show tim values in output --tz=z print all timestamps in time zone z --units or -u print tim-to-timestamp conversion info --verbose=n print n-level context information --version print version number and exit
mrls writes a report on Oracle extended SQL trace files in a style reminiscent of the Unix ls(1) command. If one or more file names is listed, mrls reports on only those files. If no file argument is given, mrls investigates each file in the current working directory. If you want to report only on files with a .trc file name extension, then use mrls *.trc.
By default, mrls renders the following columns for each file on which it reports:
Approximate total response time represented by the file.
Size of the file in bytes.
An Oracle version number for the file.
Estimated begin time of the user experience measured by the trace data, expressed in the time zone denoted by the TZ environment variable.
The file name.
mrls is designed to provide approximate timing information for a lot of trace files very quickly. The pursuit of quickness can come at the expense of potential diminishment of accuracy. See the "Troubleshooting" section for more information.
On Microsoft Windows, where the shell doesn't glob command line arguments for you, mrls uses BSD globbing upon each file provided in the argument list.
Some of the options require careful quoting on the command line, and the definition of "careful" varies by operating system. For more information, see "COMMAND LINE QUOTING" below. Examples in this manual page use Unix quoting syntax.
- --all or -a
Include entries whose names begin with a dot (.). It is common for such files to be considered hidden. The default value is --noall.
- --cpu or -c
Report total CPU consumption listed within the trace file. The value is calculated as the sum of c values for database calls executed at the minimum available recursive depth within the file. The default value is --nocpu. Note that using --cpu may slow down mrls appreciably in directories where there are large trace files.
Regard CPU duration values (c field values) in the raw trace file as being expressed in time units of float seconds. The default value is --cpuunit=0, which means to compute the timing unit automatically for each file being examined. If you prefer to override the automatic detection feature, then consider the following values:
0.01 Oracle versions prior to 9 0.000_001 Oracle versions 9 and beyond
WARNING: It is possible that the trace files being inspected by a single mrls execution represent two or more different trace time units. If this is the case, then any non-zero value for --cpuunit is guaranteed to produce incorrect results for at least one file.
Use comma-separated value output format. Using --csv is shorthand for --format=csv --separator=, --nodashes --precision=6 --dformat=excel --tformat=excel. If you wish to use different values for these options, then specify those options separately on the command line (or in a --rc file) after the --csv option. For example, --csv --dformat=iso8601 will do the expected thing, but listing the options in the reverse order would cause the --csv option to override the --dformat option.
Print dashes between the header and the body. Use --dashes to display the dashes. The default is --nodashes.
Report the recursive depth (dep) value of the task's topmost database call in the call stack. Negative values indicate situations that will be accompanied by warning messages emitted to STDERR.
- --details or -d
Shorthand for specifying --cpu --dep --lio --mis --pio --row. Note that using --details may slow down mrls appreciably in directories where there are large trace files. The default value is --nodetails.
Set duration format. Valid values are:
value example remark ------- ---------------- ------------------------- excel 206750.333 Usable by Microsoft Excel human 2d 9h25m50.333s Human-friendly iso8601 P2DT9H25M50.333S ISO 8601 duration seconds 206750.333 Seconds
The default value is --dformat=seconds.
Report the time at which the task ended. The default value is --noend.
Print the license information and exit.
Print a heading at the beginning of the report. Use --nohead to suppress the heading. The default value is --head.
Print usage information and exit.
- --human or -h
Shorthand for specifying --dformat=human --tformat=human.
- --lio or -l
Report total number of database buffer cache accesses listed within the trace file. The value is calculated as the sum of cr and cu values for database calls executed at recursive depth 0. The default value is --nolio. Note that using --lio may slow down mrls appreciably in directories where there are large trace files.
Print the mrls manual page and exit.
- --mis or -m
Report total number of database library cache misses listed within the trace file. The value is calculated as the sum of mis values for database calls executed at recursive depth 0. The default value is --nomis. Note that using --mis may slow down mrls appreciably in directories where there are large trace files.
Shorthand for --noall --nocpu --nodep --noend --nolio --nomis --noora --nopio --norow --nosize --nostart --notim --notiming --nounits. Used to reset the output column list to nothing so that you can additively declare, from scratch, the columns you desire.
- --ora or -o
Print the Oracle version number determined by mrls for each file. mrls will print a version number in x.y format if it finds an Oracle version stamp in the file being investigated (e.g., '11.1'). If it doesn't find an Oracle version stamp, then it will print an estimated version number with no decimal point (e.g., '9'). The default value is --ora; use --noora to suppress the field.
Round durations to n digits to the right of the decimal point. The default value is --precision=3.
- --recursive or -R
Plunge directories recursively. The default value is --norecursive. Note that if you use the short -R form, you must specify the uppercase letter 'R' (to disambiguate from the -r "row" option).
Sort in reverse order. The default value is --noreverse.
Process command line options listed in file. Use --norc to prevent mrls from opening the default rc files in your home directory or current working directory (see "ENVIRONMENT"). For details about how rc files are processed, see the mrskew manual page.
Report total number of rows returned listed within the trace file. The value is calculated as the sum of r values for database calls executed at recursive depth 0. The default value is --norow. Note that using --row may slow down mrls appreciably in directories where there are large trace files.
Read n lines looking for Oracle trace file identifying information before giving up and assuming that the input file is not an Oracle extended SQL trace file. The default value is --scanmax=250. Using n=0 means never give up. Note that using very large (or zero) values of n can cause mrls to run slowly, but only for files that have many lines of data prior to their first tim value.
Sort by the field denoted by s. Valid values for s are:
- cpu or c
Sort by CPU consumption
Sort by recursive depth (dep) value of the task's topmost database call in the call stack
- end or t1
Sort by task end time
- file or f
Sort by file name
- lio or l
Sort by number of database buffer cache accesses (LIO count)
- mis or m
Sort by database library cache miss count
- ora or o
Sort by Oracle version number
- pio or p
Sort by number of Oracle blocks obtained by OS read calls (PIO count)
Sort by task response time
Sort by database row return count
- size or s
Print the size of the file in bytes. Use --nosize to suppress the SIZE column. The default value is --size.
- start or t0 or t
Sort by task begin time
Naming a field in the --sort option also marks that field for inclusion in the output. To sort by a field but exclude it in the output, include the appropriate --nofield option after specifying the sort. For example, --sort=end --noend will sort by the END field but not include it in the output. The default value is --sort=r. The secondary sort field is always the file name, in ascending order.
Report the time at which the task started. The default value is --start; use --nostart to suppress the column.
Set time format. Valid values are:
value example remark ------- ----------------------------- ------------------------- excel 2011/01/31 15:55:02.130 Usable by Microsoft Excel human 2011 Jan 31 15:55:02.130+0000 Human-friendly iso8601 2011-01-31T15:55:02.130+0000 ISO 8601 date and time seconds 1296489302.130 Seconds since Unix epoch
The default value is --tformat=iso8601.
- --tim or -t
Report on the beginning and ending tim values found in the file. The default value is --notim.
Regard elapsed duration values (tim, e, and ela field values) in the raw trace file as being expressed in time units of float seconds. The default value is --timunit=0, which means to compute the timing unit automatically for each file being examined. If you prefer to override the automatic detection feature, then consider the following values:
value Oracle versions ------------- ------------------------------------------ 0.01 prior to 9 0.000_001 9 and beyond 0.000_001_024 9.0 through 184.108.40.206 for certain platforms
The Oracle kernel on some platforms convert nanoseconds (obtained from the OS) to microseconds (displayed in the trace output) by using a 10-bit right-shift operator instead of dividing by 1,000. It takes a little bit of sophisticated testing to determine whether your platform does this. However, if it does, then using --timunit=0.000001024 will give you more accurate output.
WARNING: It is possible that the trace files being inspected by a single mrls execution represent two or more different trace time units. If this is the case, then any non-zero value for --timunit is guaranteed to produce incorrect results for at least one file.
Set both --cpuunit and --timunit to float.
WARNING: It is possible that the trace files being inspected by a single mrls execution represent two or more different trace time units. If this is the case, then any non-zero value for --trcunit is guaranteed to produce incorrect results for at least one file.
Set the time zone for which all mrls timestamps will be rendered. Default value --tz=* means to print each file's timestamps in the time zone detected for the individual file. Acceptable time zone specifications include strings like cst, CST, and -0600. If you specify a nonsensical time zone string like abc, then mrls will use UTC.
- --units or -u
Print SEC/TIM, OFFSET, ZONE, ZONE-NAME, and M columns. SEC/TIM and OFFSET are the slope and intercept in the equation timestamp(ZONE) = SEC/TIM * tim + OFFSET. M is an internal code that describes the method with which SEC/TIM and OFFSET are calculated. The default value is --nounits.
Print more or less information about option values. The default value is the value of the MRTOOLS_VERBOSE environment variable if it is set, or --verbose=0 otherwise. The value of 0 suppresses everything but the tabular output. To print options values, use use --verbose or --verbose=1.
Print the mrls version number and exit.
COMMAND LINE QUOTING
For some option values, your operating system may require quotation marks around the value. For example, passing the option value $p1 into a tool on a Unix system requires the use of single quotes to prevent the command shell from interpreting p1 as a shell variable name. Different command shells have different quoting rules, but most fall into one of two categories: shells that behave like Unix, or the DOS shell. Here are some examples of how to quote options on each type of system:
Unix DOS --------------------------------- ----------------------------------- --option='$p1' --option=$p1 --option='"$p1.$p2"' --option="""$p1.$p2""" --option='join("::",$file,$line)' --option="join(\"::\",$file,$line)" --option='db.*read' --option=db.*read --option='latch free' --option="latch free"
MR TOOLS GRAPHICAL USER INTERFACE
The MR Tools Graphical User Interface (GUI) does not require command line quoting to protect shell special characters like '$'. The only quotes you should use in the GUI are those that are meant to be passed into the mrskew Perl expression parser. Here are some examples of how to quote options in the GUI for a corresponding command on a Unix system:
Unix MR Tools GUI --------------------------------- ----------------------------------- --option='$p1' --option=$p1 --option='"$p1.$p2"' --option="$p1.$p2" --option='join("::",$file,$line)' --option=join("::",$file,$line) --option='db.*read' --option=db.*read --option='latch free' --option=latch free
With .rc files, you can conveniently change the default behavior of any mrls option. For example:
$ cat ~/.mrls.rc --reverse
You can also construct .rc files that allow you to customize mrls queries without having to type a complex command line each time:
$ cat mrls-lio.rc # Display LIO counts and sort in standard order. --lio --sort=lio --noreverse
A line beginning with the '#' character (in the first column of the line) is a comment. A comment must be on a line by itself; do not put a comment on a line after an option. There is no need to use a line continuation character to specify a multi-line option.
Such a .rc file would be called into use like this:
$ mrls --rc=mrls-lio.rc
This command would first execute the options listed in ~/.mrls.rc (picking up the --reverse argument shown above), next execute the options listed in ./.mrls.rc (if the file exists), and then finally execute the options listed on the command line. Upon encountering the --rc=filename option, mrls will execute the options listed within the named file.
To report only on files whose names match the pattern *.trc and report on CPU time in addition to the standard file attributes, use this:
mrls --cpu *.trc
To show your ten worst response times in your current directory and every directory below it:
mrls --recursive --csv | sort -rn | head mrls -R --csv | sort -rn | head
To show all the gory workload details for all your trace files:
To show which trace file reported the most rows returned:
mrls --sort=row # smallest row count first mrls --sort=row --reverse # biggest row count first
To sort your mrls output other than by ascending response time, use --sort:
mrls -h # friendly form, smallest R first mrls --sort=o # oldest Oracle version number first mrls --sort=t # oldest task begin time first mrls --sort=t --reverse # newest task begin time first mrls --sort=file # ascending file name order
The --scanmax option allows you to sacrifice accuracy for the sake of speed in some cases. The --scanmax option saves a lot of time by giving up on files that don't look like Oracle trace data within a specified number of lines. However, it can cause mrls not to find a file's first tim value when it's buried deep within the file. If you see zero response times reported for files that you know contain interesting Oracle extended SQL trace data, try using a larger --scanmax option value.
If you see response times that are much larger than you expected for a file, check to ensure that the file contains trace data for exactly one end-user experience. The Oracle kernel sometimes appends to pre-existing trace files (especially on Microsoft Windows). When this happens, the total response time that mrls reports is the duration from the earliest tim value it finds near the top of the file to the latest tim value it finds near the bottom. Consider using the Oracle trcsess utility to chop such a trace file into smaller chunks, each representing a single end-user experience.
Exit status is 0 on successful completion, and >0 if an error occurs.
- estimating START and DEP in file '%s' using --scanmax=%d
mrls will print this message to STDERR if it reads %d lines from the top of your file named %s without finding a guaranteed correct task start time in the file. If you get this warning, the response time reported by mrls may not be precisely correct, but it is generally close enough. If the imprecision worries you, you can specify --scanmax=0 on the command line to get full precision at the expense of a potentially unbearably long-running mrls execution.
- no dbcalls with $dep values found in file '%s' [%d]
mrls will print this message to STDERR if it reads the entire input file without finding any lines with dep values on them.
- minimum $dep not conclusively found for file '%s' using --scanmax=%d
mrls will print this message to STDERR if it reads %d lines from the top of your file and %d lines from the bottom of your file without finding a dep=0 dbcall.
BUGS AND DEFICIENCIES
mrls does not open zip archives. One could argue that it should treat a zip archive like it treats a directory, plunging and reporting on what's inside. However, to gain a significant speed advantage for large trace files (presumably the very ones you'd be most likely to zip), mrls reads a little bit from the top of the file and a little bit from the bottom. To do this would require mrls to inflate and temporarily store each archive. This would cause such a response time problem for mrls that we don't presently believe it would be worth the time to try to implement the feature.
mrls does not return the Oracle version for a given Oracle trace file; it returns the first Oracle version it finds for a given Oracle trace file. The two answers are different in situations wherein a single trace file contains segments of trace data collected from more than one version of Oracle, such as will happen when Oracle reuses OS process id values.
By default, mrls will execute the options listed in the following files, in the following order, before the options you actually list on your command line:
If you do not wish to execute the options in these files, then specify --norc on the command line.
The MRTOOLS_RCPATH environment variable contains a list of directories that each --rc=file option will search for file. Value syntax is identical to the PATH environment value syntax (e.g., ".:a:a/b" in Unix, ".;c:a;c:a\b" in DOS). If file begins with '/', '.', or '~', then --rc looks for the file in the location you have specified. Otherwise, --rc will search each directory named in the MRTOOLS_RCPATH list for file, using only the first readable file that it finds.
The MRTOOLS_VERBOSE environment variable defines the default value of the --verbose option. If MRTOOLS_VERBOSE is unset, then the default value is --verbose=0. To debug rc file processing, set MRTOOLS_VERBOSE=2 in your shell before running mrls.
The library functions that mrtim calls take into account your current time zone setting defined in the TZ environment variable. If TZ is unset, mrtim interprets and expresses times in Coordinated Universal Time (UTC). Here are a few examples of timezone settings:
export TZ=CST6CDT export TZ=Europe/Copenhagen export TZ=UTC
mrls version 220.127.116.11.
Contact <> at Method R Corporation for support, or visit method-r.com for more information.
COPYRIGHT AND LICENSE
Copyright (c) 2008, 2014 by Method R Corporation. All rights reserved.
This is commercially licensed software. You may not redistribute copies of it. Please confirm with your software license administrator that you are licensed to use this Method R software product. Write <> for information.
There is NO WARRANTY, to the extent permitted by law.