NAME

mrskew - create skew profiles for Oracle SQL trace files

SYNOPSIS

  $ mrskew [options] file...
  

Options:

  --commas            print commas in numbers
  --cpuunit=float     number of seconds represented by one c unit
  --csv               print output as comma-separated values
  --dashes            print dashes in header and footer
  --debug=level       print extra diagnostic information
  --depmin=n          set minimum recursive depth for calls in the file
  --eula              print End User License Agreement and exit
  --foot              print footer row
  --format=string     set output format type
  --group=expr        group calls by expr
  --glabel=string     set label for the group column to string
  --gwidth=n          set width of column 1 to at most n characters
  --head              print header row
  --help              print a brief help message and exit
  --histogram         show call duration histogram data
  --init=string       execute code in string before processing files
  --man               print manual page and exit
  --name=pattern      select only calls with name value matching /pattern/i
  --oraver=string     set Oracle Database version for the file
  --pfact=integer     multiply percentage values by integer factor
  --pform=string      print percentage columns using given format
  --plabel=string     set label for the percentage column to string
  --precision=n       round to n digits right of the decimal point
  --rc=file           use named rc file; --norc prevents rc file usage
  --scanmax=n         scan n lines for file meta data
  --select=numexpr    report in column 2 on specified expression
  --slabel=string     set label for the select column to string
  --separator=string  use string as column separator
  --sort=string       sort rows in specified order
  --timunit=float     number of seconds represented by one tim unit
  --top=n             show only the first n rows if using --group
  --trcunit=float     override for both --cpuunit and --timunit
  --usage             print a brief help message and exit
  --verbose=n         print n-level context information
  --version           print version number and exit
  --where=expr        filter calls not matching expr
  --where1=expr       filter calls not matching expr

DESCRIPTION

Oracle extended SQL trace files contain performance data, arranged so that a single line of trace data describes a single database call ("dbcall") or a single operating system call ("syscall" or "oscall"). Dbcalls are reported upon by trace lines beginning with tokens like PARSE, EXEC, or FETCH. Syscalls are reported upon by trace lines beginning with the token WAIT. The collection of dbcalls and syscalls taken together are called Oracle calls.

mrskew prints a report revealing skew information for Oracle calls (dbcalls or oscalls) that you can specify with a --name pattern. You may group your results with a --group expression, and you may filter your results with a --where expression. The first column of mrskew output is the grouping column. The second column is, by default, the DURATION column, which reports upon the accounted-for duration of the specified calls. However, you may select a different result for the second column with a --select numeric expression.

If no input file is specified, mrskew takes its input from the standard input device. If more than one file is named on the command line, mrskew concatenates the input files. This permits you to analyze data skew across multiple trace files in a single step.

When mrskew takes its input from STDIN, it will not be able to calculate the values $timunit, $cpuunit, $depmin, and $version. To compensate for this deficiency (a speed/accuracy trade-off), you can use the options --trcunit, --timunit, --cpuunit, --depmin, and --oraver to specify the values for mrskew to use.

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 "OS DEPENDENCIES" below. Examples in this manual page use Unix quoting syntax.

--commas

Print commas in numerical output. Use --nocommas to supress commas. The default is --commas.

--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 mrskew 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 --nocommas --nodashes --format=csv --pfact=1 --pform=float plabel=PCT separator=,. 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 --dashes will do the expected thing, but listing the options in the reverse order would cause the --csv option to override the --dashes option.

--dashes

Print dashes between the header and the body, and between the body and the footer. Use --nodashes to suppress the dashes. The default is --dashes. Note that using --csv automatically sets --nodashes.

--debug=n

Print extra diagnostic information. Higher n values result in more information. The default value is --debug=0. The level is optional. If --debug is given (without a level), then the level is 1.

--depmin=n

Set the $depmin variable value to n.

--eula

Print the license information and exit.

Print a total at the end of the report. Use --nofoot to suppress the total. The default value is --foot. The parenthetical number in the footer label (e.g., "TOTAL (42)") is the total number of rows (both displayed and aggregated with "%d other") in the report.

--format=string

Set output format type defined by string. Valid values for string are tab (tabular) or csv (comma-separated values). The default value is --format=tab.

--group=expr or --g=expr

Group data by expr, which can be any valid Perl expression except for the empty (zero-length) string. The expression can refer to any variable described in "EXPRESSION VARIABLES" below, and it can use any Perl operators or functions. For example, the option --group='"$file::$line"' will direct mrskew to group output by distinct values of the concatenation of the file name, the string "::", and the line number within that file. You could accomplish the same grouping with --group='$file."::".$line' or even --group='join("::",$file,$line)', since you can use any Perl expression (including function calls) within expr.

The default value is --group='$name', which groups calls by call names.

--glabel=string or --gl=string

Print string as the label on the group column (the first column in the output). The default value is "CALL-NAME" if --group='$name' (which is the default value of --group). Otherwise, the default value is "'expr'", where expr is the expression specified in --group=expr.

--gwidth=n

Set width of group column (the first column in the output) to no more than n characters. The default value is --gwidth=0, which means not to limit the width. mrskew will use a large enough value of n to render the complete group column header label (see --glabel and --head) and the "TOTAL (n)" label (see --foot), even if you specify a value of n that is too small to render them. It will limit your elision to at the smallest, the width of "a...", which is the smallest elided value that it makes any sense to render.

Print a header at the beginning of the report. Use --nohead to suppress the header. The default value is --head.

--help

Print a brief help message and exit.

--histogram

Print call duration histogram data with each group value. Use --nohistogram if want to see each grouping value but no performance data associated with it. The default value is --histogram.

--init=string

Execute string, which must contain syntactically correct Perl code, before beginning file processing. The default value is --init=''.

--man

Print the program's manual page and exit.

--name=pattern

Choose Oracle timed events whose names match pattern. The pattern is a Perl regular expression used to match the dbcalls and syscalls named in your trace data. The matching is done case insensitively. For example:

--name=parse

matches "PARSE" dbcall, etc.

--name=read

Matches "LOBREAD" dbcall, "db file scattered read" syscall, etc.

--name='(read|write)'

Matches "LOBREAD" dbcall, "db file scattered read" syscall, "direct path write temp" syscall, etc.

--name='db.*read'

Matches "db file scattered read" syscall, "db file sequential read" syscall, etc.

You may use the following special patterns:

all

Matches any trace file line.

call

Matches any dbcall or syscall.

dbcall

Matches any dbcall.

syscall or oscall

Matches any syscall.

If you wish to express a literal character that is a regular expression operator such as the characters in the set [()*+^$.], you must escape it by preceding it with a backslash. For example, to specify a pattern matching only the string "SQL*Net message to client", you would use --name='SQL\*Net message to client'.

Note that the only pattern that allows you to match non-call lines is the special pattern all. See "EXAMPLES" for a use case.

The default value is --name='.+', which will match any timed event name.

--oraver=string

Set the $oraver variable value to string.

--pfact=integer

Multiply proportions by integer value in percentage columns. Default value is --pfact=100. Use --pfact=1 to express proportions as raw proportions.

--pform=string

Format proportions as string in percentage columns. Default value is --pform=pct. Use --pform=float to express proportions as raw proportions.

--plabel=string or --pl=string

Print string as the label on the percentage column (the third column in the output). The default value is "%".

--precision=n or --pre=n

Round select, MEAN, MIN, and MAX values to n digits to the right of the decimal point. The default value is --precision=6.

--rc=file

Process command line options listed in file. Use --norc to prevent mrskew from opening the default rc files in your home directory or current working directory (see "ENVIRONMENT"). See "RC FILES" for more information about rc file processing.

--scanmax=n

Read n lines looking for Oracle trace file meta data information, such as the start time of the task represented within the file and the trace time units (see --cpuunit and --timunit). The default value is --scanmax=250. Using n=0 means never give up.

--select=numexpr or --s=numexpr

Select the specified numeric expression for the value of the second column of mrskew output. The default value is --select='$af', which produces a histogram of accounted-for durations (see "EXPRESSION VARIABLES") for specified calls.

--separator=string or --sep=string

Use string as the output column separator. The default value is --separator=' ' (two spaces).

--slabel=string or --sl=string

Print string as the label on the select column. The default value is "DURATION" if --select='$af' (which is the default value of --select). Otherwise, the default value is "'numexpr'", where numexpr is the expression specified in --select=numexpr.

--sort=o1,o2,...,oN

Sort by the columns denoted by strings o1,o2,...,oN. The first string, o1 defines the primary sort order, o2 defines the secondary sort order, and so on. Each oi has the following form:

A column number in the set 1, 2, ..., 7

The first character is a numeral '1', '2', ..., '7' denoting which column to order by.

A data type, either n or s

The second character is an optional 'n' (numeric) or 's' (string), specifying the sort semantics. If neither an 'n' nor 's' is specified, 'n' is used.

An order, either a or d

The third character is an optional 'a' (ascending) or 'd' (descending), specifying the sort order. If neither an 'a' nor 'd' is specified, 'd' is used.

The default value is --sort=2nd.

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

On some platforms, the Oracle kernel converts 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.000_001_024 will give you more accurate output.

WARNING: It is possible that the trace files being inspected by a single mrskew 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.

--top=n

Emit only the first n rows in the output data, and then summarize the remaining entries in a single row. The default value is --top=20. To emit all rows, use --top=0.

--trcunit=float

Set both --cpuunit and --timunit to float.

WARNING: It is possible that the trace files being inspected by a single mrskew 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.

--usage

Print the program's usage text and exit.

--verbose=level

Print more or less information about option values, examined files, and matched call names. 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. Use --verbose or --verbose=1 to emit used files, matched calls, and only key options. Use --verbose=2 to print all option values. Note that if a file you're trying to analyze doesn't show up in the examined files list, it is because either mrskew couldn't open it, or mrskew couldn't determine what Oracle version created it.

--version

Print the program's version number and exit.

--where=expr or --w=expr or --where0=expr or --w0=expr

Filter output data by expr; that is, include in the output only information from raw trace lines for which expr is true. An expr is any valid Perl expression. The expression can refer to any variable described in "EXPRESSION VARIABLES" below, and it can use any Perl operators or functions. For example, the setting --where='$p3>1 or $p1==48' will direct mrskew to include in the output only values for which the Oracle p3 field value is greater than 1 or for which the Oracle p1 field value equals 48.

The default value is --where=1. This default, coupled with the default value --where1='$dep==$depmin', sensibly matches the --name='.+' --group='$name' default values, which together create an accurate profile for the time accounted for within the input trace file(s). (Without restricting the $dep value, such a profile would double-count recursive dbcalls.) Use --where=1 --where1=1 if you want no filtering whatsoever. Note that the expression values of --where and --where1 are and-ed together to create the overall mrskew where-clause predicate. Thus, using --where=A --where1=B creates the filter ((A) and (B)) to be applied to the input.

--where1=expr or --w1=expr

Filter output data by expr; that is, include in the output only information from raw trace lines for which expr is true. An expr is any valid Perl expression. The expression can refer to any variable described in "EXPRESSION VARIABLES" below, and it can use any Perl operators or functions. See --where for examples.

The --where1 option allows you to specify --where option values without having to continually remember to include the $dep==$depmin and part of the predicate. While you could refer to $dep values in --where expressions and non-$dep values in --where1 expressions, we recommend that you use --where1 to filter with $dep-related predicates and --where to filter with all other predicates.

EXPRESSIONS

Wherever mrskew requires an expression (such as in --group and --where option arguments), you may use any valid Perl expression consisting of:

Perl operators

Operators such as or xor and not , = ?: || && | ^ < > <= >= lt gt le ge == != <=> eq ne cmp + - . * / % =~ !~ ! ~ ** ...and so on. See http://perldoc.perl.org/perlop.html for details.

Perl builtin functions

Functions such as lc uc s/// int join split sprintf substr ...and so on. See http://perldoc.perl.org/perlfunc.html for details.

File::Basename functions

The functions fileparse, basename, and dirname. See http://perldoc.perl.org/File/Basename.html for details.

mrskew expression variables

Any of the variable names described in "EXPRESSION VARIABLES".

For more information about Perl expression syntax, see http://perldoc.perl.org/perl.html#Tutorials.

EXPRESSION VARIABLES

In grouping and filter expressions, you can reference any of the following variable names:

$action_name or $action or $act

For a dbcall, remote procedure call (RPC), or syscall, $action_name is the Oracle ACTION NAME in context for the call.

For a dbcall or a syscall, $ad is the Oracle cursor address in context for the call.

For a remote procedure call (RPC), $ad is 0.

$af

For a dbcall or a remote procedure call (RPC), $af is the value of its c field, in seconds.

For a syscall, $af is the value of its ela field, in seconds.

$af is the call's accounted-for time. mrskew assigns $af this way so that specifying the following options (the defaults) will create an approximate profile:

  --name='.+' --select='$af' --group='$name'
  --where=1 --where1='$dep==$depmin'
$call or $name or $nam

See $name.

@bind, $bind[i]

For a dbcall or syscall, @bind is an array containing the list of placeholder bind values in context for the call. To refer to the "Bind#0" value (the first placeholder value in that list), use the syntax $bind[0]. To refer to the "Bind#4" value, use $bind[4]. It is convenient to use the expression join(",",@bind) to create a string showing all the values in the list, separated by commas. String data type values are presented with enclosing double quotes. Null values are presented as the empty string.

$client_id

For a dbcall, remote procedure call (RPC), or syscall, $client_id is the Oracle CLIENT ID in context for the call.

$container_id

For a dbcall or remote procedure call (RPC) or syscall, $container_id is the Oracle CONTAINER ID in context for the call.

$cpu or $c

For a dbcall or remote procedure call (RPC), $cpu is the value of the c field, in seconds. This is the CPU time consumed by a given dbcall, accurate on most operating systems to within only +/- 10,000 microseconds (us).

For a syscall, $cpu is 0.

$cpuunit

The unit in which trace file CPU durations are expressed. All the lines in a given file will have the same value.

$cr, $cu

For a dbcall, $cr and $cu are the values of the cr and cu fields, respectively. cr is the number of database buffer cache accesses in consistent mode, and cu is the number of database buffer cache accesses in current mode. See also the $lio variable.

For a syscall or remote procedure call (RPC), $cr and $cu are 0.

$cursor_id or $cid

For a dbcall or a syscall, $cursor_id is the value of the cursor id field, which is the number that follows the symbol '#' in your trace data.

For a remote procedure call (RPC), $cursor_id is -1.

$dep

For a dbcall, $dep is the value of the dep field. For a syscall, $dep is set to the value of $depmin for the file.

For a remote procedure call (RPC), $dep is 0.

$depmin

$depmin is the minimum recursive depth (dep value) found in the trace file. It is the level of top-most call stack depth in the file.

$dur

For a dbcall or remote procedure call (RPC), $dur is the value of the e field, in seconds.

For a syscall, $dur is the value of the ela field, in seconds.

$e

For a dbcall or remote procedure call (RPC), $e is the value of its e field, in seconds.

For a syscall, $e is 0.

$ela

For a dbcall or remote procedure call (RPC), $ela is 0.

For a syscall, $ela is the value of its ela field, in seconds.

$exec_id

For a dbcall or a syscall, $exec_id is the trace file line number of the most recent EXEC call that has the same $cursor_id as the current call. The $exec_id value is 0 if there is no preceding EXEC call for the current call's cursor. The $exec_id value is always 0 for a PARSE call.

$file_name or $file or $f

The name of the file from which the line of text has been obtained. This is especially useful when using mrskew to process several input files in one run, so that you can learn the identities of the files that satisfy your query.

$hv or $h

For a dbcall or a syscall, $hv is Oracle hash value that corresponds to the cursor_id field of the current line of text. If there is no preceding PARSING IN CURSOR section for the cursor id in context, then mrskew uses "#cursor_id:file" as the hash value. When cursor_id is zero, mrskew uses "#0" as the hash value.

For a remote procedure call (RPC), $hv is the first 13 digits of the MD5 checksum of the SQL text found in the most recent "RPC CALL" line.

$lio

For a dbcall, $lio is $cr + $cu.

For a syscall or remote procedure call (RPC), $lio is 0.

$line_number or $line or $l or $NR

The line number of a line within the trace data. With this variable, you can find out what lines of trace data are responsible for your greatest time consumptions for a given timed event. Grouping by an expression containing both $file_name and $line_number enables you to pinpoint the exact line within a directory of trace files that satisfies your query.

$mis

For a dbcall, $mis is the value of the mis field (on dbcall lines), which is the number of misses upon the library cache encountered for that dbcall.

For a syscall or remote procedure call (RPC), $mis is 0.

$module_name or $module or $mod

For a dbcall, remote procedure call (RPC), or a syscall, $module_name is the Oracle MODULE NAME in context for the call.

$name or $nam or $call

For a dbcall or remote procedure call (RPC), $name is the call name (e.g., "PARSE", "EXEC", or "RPC EXEC").

For a syscall, $name is the value of the nam field, which is the Oracle-given name for the syscall.

$obj

For a dbcall or remote procedure call (RPC), $obj is 0.

For a syscall, $obj is the value of the obj# field, which is the Oracle object id of an object being manipulated. For syscalls reported by Oracle Database versions prior to 10.2, $obj is 0.

$os

The name of the operating system recorded in the trace file.

$p1, $p2, $p3

For a dbcall or remote procedure call (RPC), $p1, $p2, $p3 are all 0.

For a syscall, $p1, $p2, $p3 are the values of the three parameter fields that Oracle emits for each syscall. In Oracle versions prior to 10.2, these parameters were named p1, p2, and p3, respectively. From Oracle version 10.2 onward, these three fields have more descriptive names like "file #" and "block #". However, there are still only three such attributes per WAIT line, and mrskew still uses the names $p1, $p2, and $p3, regardless of what those attributes are called in the trace file. Oracle publishes definitions of these fields in the V$EVENT_NAME view.

Use the following Oracle SQL query in to view the functional Oracle definition of the data contained in the context-dependent p1, p2, and p3 fields:

  select name, parameter1, parameter2, parameter3
  from v$event_name where name='your nam value goes here'
$parse_id

For a dbcall or a syscall, $parse_id is the trace file line number of the most recent PARSE call that has the same $cursor_id as the current call. The value is 0 if there is no preceding PARSE call for the current call's cursor.

$pio or $p

For a dbcall, $pio is the value of the p field, which is the number of Oracle blocks obtained by OS read calls.

For a syscall or remote procedure call (RPC), $pio is 0.

$plh

For a dbcall, $plh is the value of the plh field, which is the execution plan hash value for the cursor being closed.

For a remote procedure call (RPC) or syscall, $plh is 0.

$rd_only

For an XCTEND dbcall, $rd_only is the value of the rd_only field, which is 1 if the transaction was read-only, or 0 if it was read-write.

For all other calls, $rd_only is 0.

$rlbk

For an XCTEND dbcall, $rlbk is the value of the rlbk field, which is 1 if the transaction was rolled back, or 0 if it was committed.

For all other calls, $rlbk is 0.

$row or $r

For a dbcall, $row is the value of the r field, which is the number of rows returned by a given dbcall.

For a remote procedure call (RPC) or syscall, $row is 0.

$serial_number or $serial

For a dbcall or remote procedure call (RPC) or syscall, $serial_number is the Oracle session serial number in context (from the SESSION ID line) for the call.

$service_name or $service or $serv

For a dbcall or remote procedure call (RPC) or syscall, $service_name is the Oracle SERVICE NAME in context for the call.

$session_id or $sid

For a dbcall or remote procedure call (RPC) or syscall, $session_id is the Oracle SESSION ID in context for the call.

$sql

For a dbcall or remote procedure call (RPC) or syscall, $sql is the SQL text in context for the call, normalized by replacing sequences of whitespace (blanks, tabs, newlines, formfeeds, etc.) by a single space character. To use only a substring of the SQL text, use substr($sql, offset, length). For example, use substr($sql,0,50) to refer to the first 50 characters of a statement's text; use substr($sql,20,50) to refer to the 21st through the 70th characters; or use substr($sql,-50) to refer to the last 50 characters.

$sqlid

For a dbcall or a syscall, $sqlid is the Oracle SQL id value that corresponds to the cursor_id field of the current line of text. If there is no sqlid field in the PARSING IN CURSOR section for the cursor id in context, then mrskew uses "hv=hv" as the SQL id value. If there is no preceding PARSING IN CURSOR section for the cursor id in context, then mrskew uses "#cursor_id:file" as the SQL id value. When cursor_id is zero, mrskew uses "#0" as the SQL id value.

For a remote procedure call (RPC), $sqlid is the first 13 digits of the MD5 checksum of the SQL text found in the most recent "RPC CALL" line.

$ssql

For a dbcall or remote procedure call (RPC) or syscall, $ssql is the SQL text in context for the call, normalized by (1) replacing sequences of whitespace (blanks, tabs, newlines, formfeeds, etc.) by a single space character, (2) replacing literal values by placeholder variables, (3) replacing each comment with an empty string, (4) replacing each IN and NOT IN list with an empty list, and (5) replacing any suffix of two or more digits on an object name with an empty string. You can think of $ssql as the shareable version of a SQL or PL/SQL statement.

$ssqlid

For a dbcall or a syscall, $ssqlid is a synthesized Oracle SQL id-like value for the $ssql text that corresponds to the cursor_id field of the current line of text. If there is no preceding PARSING IN CURSOR section for the cursor id in context, then mrskew will set $ssqlid to the same value as $sqlid. When cursor_id is zero, mrskew uses "#0" as the $ssqlid value. You can think of $ssqlid as the SQL id of the shareable version of a SQL or PL/SQL statement.

$text

The entire line of raw trace data. You can use this variable to create your own Perl expression to process each input line any way you like.

$tim or $t

For a dbcall or a syscall, $tim is the value of the tim field, in seconds. tim is the time at which the call concluded. For syscalls reported by Oracle Database versions prior to 10.2, $tim is 0.

For a remote procedure call (RPC), $tim is 0.

$tim0

For a dbcall or a syscall, $tim0 is the time value at which the call began, in seconds. For syscalls reported by Oracle Database versions prior to 10.2, $tim0 is -$ela.

For a remote procedure call (RPC), $tim0 is 0.

$tim1

$tim1 is an alias for $tim.

$tim1prior

For a dbcall or remote procedure call (RPC) or syscall, $tim1prior is the time value at which the prior dbcall ended, in seconds. Note that syscall lines do not change the value of $tim1prior.

$timunit

The unit in which trace file time durations are expressed. All the lines in a given file will have the same value.

$type

For a CLOSE dbcall, $type is the value of the type field.

For any other dbcall or a syscall, $type is 0.

$uafbc

For a dbcall, $uafbc is $tim0 - $tim1prior, except for the first dbcall in the file. For the first dbcall in the file, $uafbc is 0. For a syscall, $uafbc is 0.

For syscalls reported by Oracle Database versions prior to 10.2 and for remote procedure calls (RPC), $uafbc is -$ela - $tim1prior.

$uafwc

For a dbcall or remote procedure call (RPC) or syscall, $uafwc is the value of $e - ($c + $ela).

$uafwc is a dbcall's unaccounted-for within-call time. If you group by call names, the $uafwc value will be negative for each syscall ($e - ($c + $ela) = -$ela for a syscall). But if you use, for example, --group='$sqlid' or --group='"$mod/$act"', you'll get a useful summary of the duration that is unaccounted-for within your trace data.

$version or $v

The Oracle version computed for the file.

RC FILES

With .rc files, you can conveniently change the default behavior of any mrskew option. For example:

  $ cat ~/.mrskew.rc
  --top=12
  --trcunit=.000001024

You can also construct .rc files that allow you to execute highly specialized mrskew queries without having to type a complex command line each time:

  $ cat mrskew-bucket1.rc
  # Bucket calls by duration.
  --gl=BUCKET
  --group='$dur < .000010 ? " 0us <= dur < 10us"
         : $dur < .000020 ? "10us <= dur < 20us"
         : $dur < .000030 ? "20us <= dur < 30us"
         : $dur < .000040 ? "30us <= dur < 40us"
         :                  "40us <= dur       "'

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:

  $ mrskew --rc=mrskew-bucket1.rc --name='.*read.*' ora_1492.trc

This command would first execute the options listed in ~/.mrskew.rc (picking up the --top=12 argument shown above, and so on), next execute the options listed in ./.mrskew.rc (if the file exists), and then finally execute the options listed on the command line.

By default, mrskew will execute the options listed in the following files, in the following order, before the options you actually list on your command line:

  ~/.mrskew.rc
  ./.mrskew.rc

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

Upon encountering the --rc=filename option, mrskew will execute the options listed within the named file. For example, imagine that you have the following rc files on your system, with MRTOOLS_RCPATH set to include ".":

  $ cat ~/.mrskew.rc
  --gl=HOME

  $ cat ./.mrskew.rc
  --gl=CWD

  $ cat ./a
  --gl=A1
  --rc=b
  --gl=A2

  $cat ./b
  --gl=B

Then running this command:

  mrskew myfile.trc --rc=a

...would result the following sequence of command line option assignments:

  mrskew myfile.trc --gl=HOME --gl=CWD --gl=A1 --gl=B --gl=A2

The result is the setting --gl=A2. To show a list of the rc files mrskew uses, along with their contents, set MRTOOLS_VERBOSE=2 in your command shell.

See the description of the "MRTOOLS_RCPATH" environment variable for details on how --rc options search for file names.

OS DEPENDENCIES

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"

This manual page uses the Unix line continuation character '\'. On DOS systems, the line continuation character is '^'. For example:

  Unix                               DOS
  ---------------------------------  -----------------------------------
  mrskew \                           mrskew ^
  --name=exec \                      --name=exec ^
  *.trc                              *.trc

Some rc files contain multi-byte Unicode characters that do not display properly on some systems. Each rc file containing a Unicode character contains detailed instructions about how to replace the Unicode character with ASCII text having equivalent meaning.

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

EXAMPLES

This command will emit a profile of calls from both input files, grouping those calls by their call names:

  mrskew ora_1492.trc ora_1493.trc

The next command will emit a skew histogram for all db file sequential read and db file scattered read calls (and any other Oracle timed event calls matching the Perl regular expression /db.*read/i), grouped by elapsed duration per call:

  mrskew --name='db.*read' --rc=p10 ora_1492.trc

The next command will print a skew histogram for the same calls, except this time they'll be grouped by the Oracle p3 parameter for the calls, which in this case is the number of blocks obtained in each read call (see the Oracle v$event_name fixed view for more information). Note that on Unix systems, the single quotes in '$p3' are necessary for the reasons mentioned previously. If you forget this, you'll get a warning like "Option group requires an argument".

  mrskew --name='db.*read' --group='$p3' ora_1492.trc

The next command will produce the same output as the one before, except that the output will have a nicer column header for the group-by column:

  mrskew --name='db.*read' --group='$p3' --gl='ORACLE BLOCKS PER CALL' ora_1492.trc

This command will print a skew histogram for the same calls, but this time they'll be grouped by file id and block id. Note that by default, mrskew shows only the first 20 lines of the histogram, with a single additional line summarizing all the additional lines. If you want to see all the lines, you can specify --top=0, but be warned: doing that can produce a LOT of output. Also note that grouping by a high-cardinality expression like '$p1.$p2' increases the likelihood that mrskew will use enormous amounts of memory on your computer.

  mrskew --name='db.*read' --group='"$p1.$p2"' ora_1492.trc

This command will show Oracle /db.*read/i calls with elapsed durations between .01 seconds (inclusive) and .1 seconds (exclusive), grouped by the Oracle p3 parameter value (the number of Oracle blocks obtained in each read call) at the minimum recursive depth in the file:

  mrskew --name='db.*read' --where='.01<=$ela and $ela<.1' --group='$p3' \
    ora_1492.trc

To show the file name and line number and the call name for each of the five longest Oracle call durations in all the .trc files within your current working directory:

  mrskew --group='"$file:$line $name"' --gl='FILE:LINE# CALL-NAME'
    --where1=1 --top=5 *.trc

To show which trace files contain evidence of Oracle declarative parallel execution operations:

  mrskew --name='PX.*' --group='$file' --gl='FILE' --where1=1 *.trc

To show which files have the most time spent waiting for Oracle timed events with the string "latch" in their names:

  mrskew --name='.*latch.*' --group='$file' --gl='FILE' *.trc

To show a response time profile for all the timed events found in the input file, the following two commands are equivalent:

  mrskew --name='.+' --group='$name' --gl='SUBROUTINE CALL' ora_1492.trc
  mrskew --gl='SUBROUTINE CALL' ora_1492.trc

To show the hash values of the most time-consuming SQL statements in all the .trc files within your current working directory:

  mrskew --group='$hv' --where1=1 *.trc

To show the same thing with SQL ids instead of hash values:

  mrskew --group='$sqlid' --where1=1 *.trc

To show the same thing with SQL text (the first 50 characters) instead of SQL ids:

  mrskew --group='substr($sql,0,50)' --where1=1 *.trc

To show the SQL id, but only in files that are Oracle version 11 or above:

  mrskew --group='$sqlid' --where='$version>=11' *.trc

To show which files in the current working directory have the most time contributed by the SQL text with hash value 2343063137:

  mrskew --group='$file' --where='$hv eq 2343063137' --where1=1 *.trc

To show time consumption for all operating system calls executed by Oracle for the given file(s):

  mrskew --name=oscall ora_1492.trc

To show the names of all files in the current working directory that have references to cursor #0:

  mrskew --group='$file' --where='$cursor_id == 0' *.trc

To show the service, module, and action names that consumed the most time:

  mrskew --group='"$serv.$mod.$act"' ora_1492.trc

To show the line number and the Oracle session id and serial number of the most time-consuming calls:

  mrskew --where1=1 --group='"$line:$sid.$serial"' ora_1492.trc

To show how the module and action values track line-by-line through a trace file:

  mrskew --name=all --where1=1 --top=0 --nohistogram --sort=1a \
    --group='sprintf("%8d %4d %-35.35s %-.45s", \
      $line, $sid, $mod.":".$act, $text)' \
    ora_1492.trc

To show how hash values track line-by-line through a trace file:

  mrskew --name=all --where1=1 --top=0 --nohistogram --sort=1a \
    --group='sprintf("%8d %10s %-.85s", $line, $hv, $text)' \
    ora_1492.trc 

To show how much unaccounted-for time there is within calls at recursive depth 0 for a given SQL id:

  mrskew *.trc --select='$uafwc' --where='$sqlid eq "53saa2zkr6wc3"'

To show how much time was consumed by calls associated with each parse call in the trace file:

  mrskew --group='$parse_id' --gl="PARSE-ID" ora_1492.trc

To show how much time was consumed by calls associated with each execute call in the trace file:

  mrskew --group='$exec_id' --gl="EXEC-ID" ora_1492.trc

To show which set of bind values accounts for the most time spent executing and fetching for a given SQL id:

  mrskew --group='join(",",@bind)' \
    --where='$sqlid eq "53saa2zkr6wc3" and ($call =~ /EXEC|FETCH/)' \
    ora_1492.trc

To show which value of the "three"-th placecholder variable (that is, "Bind#3") accounts for the most response time spent executing and fetching for a given SQL id:

  mrskew -group='$bind[3]' \
    --where='$sqlid eq "53saa2zkr6wc3" and ($call =~ /EXEC|FETCH/)' \
    ora_1492.trc 

mrskew is a tool that lets you stretch your imagination. This mrskew command identifies every EXEC dbcall in the current working directory, at the file's minimum recursive depth, that has one or more library cache misses:

  mrskew \
    --name=exec \
    --group='sprintf "%s:%d %d", $f, $l, $mis' \
    --where='$mis > 0' \
    --gl='FILE:LINE MISSES' \
    --nohistogram \
    *.trc

And this mrskew command shows every FETCH dbcall in the current working directory, at the file's minimum recursive depth, that has a buffer cache hit ratio value (if you're interested in such things) of less than 40%:

  mrskew \
    --name=fetch \
    --group='sprintf "%30s:%7d %9d %9d %7.3f", \
      $file, $line, $lio, $pio, ($lio-$pio)/$lio' \
    --where='$lio > 0 and ($lio-$pio)/$lio < .4' \
    --gl='FILE:LINE LIO PIO HIT_RATIO' \
    --nohistogram \
    *.trc 

SECURITY PROVISO

mrskew executes Perl code that the mrskew user types into a command-line argument. It is thus possible for a user to vandalize a system by running mrskew. This is not generally a problem, because a mrskew user typically has access to other operating system commands, like rm(1) or perl(1), that could just as much damage with far less effort. The probability of harming a system by accident with mrskew is vanishingly small; using mrskew as an implement of vandalism would require directed effort. But if your system is one of those in which certain users with command-line access are prohibited from executing specific operating system commands (like rm(1) or perl(1)), then those users should be prohibited from executing mrskew as well.

DIAGNOSTICS

Exit status is 0 on successful completion, and >0 if an error occurs.

mrskew will exit with a message in the following format if you refer to an expression variable that mrskew does not recognize:

  mrskew: can't evaluate '%s' expression '%s':
    Global symbol "%s" requires explicit package name at (eval %d) line %d.

ENVIRONMENT

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

Prepackaged RC Files

mrskew comes with the following prepackaged rc files, any of which can be used with mrskew with the --rc=filename command line option:

all

A shorthand for --name=all --where1=1 --top=0 --sort=1na --nohistogram. We commonly use these options in conjunction with a --group expression that includes $line to show mrskew variable states in a line-by-line narrative. For example, the following command shows the value of $parse_id and $exec_id for each line of the input trace file:

  mrskew ora_1492.trc \
    --group='sprintf("%5d %8d %8d %s", \
      $line, $parse_id, $exec_id, $text)' \
    --rc=all --gl="LINE# PARSE_ID  EXEC_ID TEXT"
disk

Group calls by duration into buckets suitable for latency analysis of traditional spinning disk I/O devices.

mrnl

Show trace files one line at a time, prefixing each trace line with a line number, the call begin time, end time, duration, $uafbc value, and $uafwc value. Note that using mrnl can be extremely expensive with regard to real memory consumption. We recommend for you to use --rc=mrnl only on small trace files, or with an input line filter like --where='10000 <= $line and $line <= 10100'.

p10

Group calls by duration into buckets with power-of-ten partitions 1us, 10us, 100us, 1000us, etc.

ssd

Group calls by duration into buckets suitable for latency analysis of solid state disk I/O devices.

txnz

Group calls by an "experience id" that is reset upon every occurrence of a long-latency SQL*Net message from client call. This rc file creates a function called eid that returns this experience id for each trace line. By default, this rc file will group response time by this eid; however, you may specify your own --group expression that uses this eid function any way you like. For example, you could use --rc=txnz --group='sprintf("%5d %5d %s", $line, eid, $text)' --rc=all to see which eid value is assigned to each line of your input trace file.

Some of these rc files contain multi-byte Unicode characters that will not display properly on Microsoft Windows command shells. Each file contains instructions for how to replace those Unicode characters with ASCII strings that will render properly (albeit less beautifully).

AUTHORS

Cary Millsap

SUPPORT

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