NAME

mrls - list attributes of Oracle extended SQL trace files

SYNOPSIS

  $ mrls [options] [file...]

Options:

  --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

DESCRIPTION

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:

R

Approximate total response time represented by the file.

SIZE

Size of the file in bytes.

ORA

An Oracle version number for the file.

START

Estimated begin time of the user experience measured by the trace data, expressed in the time zone denoted by the TZ environment variable.

FILE

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.

OPTIONS

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.

--cpuunit=float

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.

--csv

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.

--dashes

Print dashes between the header and the body. Use --dashes to display the dashes. The default is --nodashes.

--dep

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.

--dformat=format

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.

--end

Report the time at which the task ended. The default value is --noend.

--eula

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.

--help

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.

--man

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.

--none

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.

--precision=n

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).

--reverse

Sort in reverse order. The default value is --noreverse.

--rc=file

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.

--row

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.

--scanmax=n

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=s

Sort by the field denoted by s. Valid values for s are:

cpu or c

Sort by CPU consumption

dep

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)

r

Sort by task response time

row

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.

--start

Report the time at which the task started. The default value is --start; use --nostart to suppress the column.

--tformat=format

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.

--timunit=float

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 11.2.0.1 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.

--trcunit=float

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.

--tz=string

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.

--verbose=level

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.

--version

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

RC FILES

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.

EXAMPLES

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:

  mrls --details

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

TROUBLESHOOTING

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.

DIAGNOSTICS

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.

ENVIRONMENT

.mrls.rc

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:

  ~/.mrls.rc
  ./.mrls.rc

If you do not wish to execute the options in these files, then specify --norc on the command line.

MRTOOLS_RCPATH

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.

MRTOOLS_VERBOSE

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.

TZ

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

AUTHORS

Cary Millsap

SUPPORT

mrls version 3.1.0.4.

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.