sqlmonitor

NAME
SYNOPSIS
OPTIONS
EXAMPLES
COPYRIGHT
SEE ALSO

NAME

sqlmonitor − Create sql monitor from gv$sql views

SYNOPSIS

sqlmonitor [options] -l u/p@c sqlid

For the provided sqlid, the sqlmonitor utility creates a sql monitor from the shared pool of the database using dbms_sql_monitor.report_sql_monitor. The output is written to a file named with the sqlid and a suffix of .html unless the --output-file option is provided. Use --output-directory to write the output in a different directory than the current.

The dbms_sql_monitor.report_sql_monitor has several ways to provide information about which of potentially many sql monitors to output and some of these are available as options to sqlmonitor.

OPTIONS

-l u/p

-l u/p@c

Compulsory option to provide the username, password, and potentially connect string for a database connection that provides access to a login with access to gv$sql and dba_hist_sqltext. If you omit /p, rwloadsim will prompt for the password.

--output-file=<filename>

By default, the output is written to a file named as the sqlid with a suffix of .html. Use this option to specify a different filename.

--output-suffix=<text>

If you are not using --output-file, this option will cause the generated file name to include extra text between the sqlid and the .html suffix. This can be used to distinguish the generated file names if you are this utility and/or the sqlreport utility multiple times.

--output-directory=<directory>

By default, the output is written in your current directory. Use this option to specify a different directory.

--sql-plan-hash-value=value

Provide a value for the sql_plan_hash_value argument to dbms_sql_monitor.report_sql_monitor.

--sql-exec-id=value

Provide a value for the sql_exec_id argument to dbms_sql_monitor.report_sql_monitor.

--sql-exec-start=value

Provide a value which must be a date in the exact format YYYY.MM.DDTHH24:MI:SS with a literal T to separate the date part from the time part for the sql_exec_start argument to dbms_sql_monitor.report_sql_monitor.

--con-name=value

Provide a value for the con_name argument to dbms_sql_monitor.report_sql_monitor.

--sessionid=sid
--instance=inst#
--spid=T

By default, the sqlid is taken from the command line. It can alternatively be retrived as nvl(sql_id,prev_sql_id) in the v$session dictionary view, by providing the first of these options, which will become a predicate on the sid column of v$session. If the second option is also used, gv$session will be used in stead with an additional predicate on the inst_id column. If the third option is used, there will be a join to v$process or gv$process with a predicate on its spid column.

sqlid

You must either provide a sqlid as an argument or retrieve it via the --sessionid or --spid option possibly in combination with the --instance option.

EXAMPLES

Assume you are logged in to the database server and notice the process with process id 123456 is running at 100% cpu. If you then execute

sqlmonitor -l system/{password} --spid=123456 --output-suffix=-mon

the sqlid currently being executed by that process will be retrived, and a sql monitor will be created in a file named by the sqlid with a suffix of -mon.html

If a session is logged in to instance 2 of a database and has a session id of 1234, you can create a sql monitor of the latest executed sql in that session by executing

sqlmonitor -l system/{password}@service_tp --sessionid=1234 --instance=2 --output-directory=/var/www/html

The sql monitor will be written to a file named after the sqlid with a .html suffix in the directory /var/www/html.