-
Notifications
You must be signed in to change notification settings - Fork 18
Using sqlexplain, sqlreport, sqlmonitor
There are many cases, where it is necessary to inspect sql execution plans or sql reports. This is typically done executing queries in SQL*Plus, where nicely formatting the output may be difficult. Similarly, you often need to make some experiments before getting the syntax correct. After installing rwloadsim, you have access to three utilities, sqlexplain, sqlreport and sqlmonitor, that make this quite simple. Provided credentials to connect as a user with necessary privileges (typically DBA or other with access to the awr repository and/or v$) and a sql_id, they write the output to stdout (for sqlexplain producing explain plan) or to a html file for sqlreport and sqlmonitor.
The sqlexplain utility takes a sql_id as input and writes an explain plan to stdout. Using the --help option, it shows the options:
RWP*SQL Explain Release 3.2.0.1 Production on Tue, 10 Dec 2024 14:11:34 UTC
Create a sql explain plan by calling dbms_xplan.display_cursor
-l usr/pwd@con : required option to give database logon
sqlid : if provided, use that sqlid
--child-no=child# : provide cursor_child_no, default 0
--format='format text' : provide format, default 'last, all, allstats'
--sesssionid=sid : query v$session for the sqlid
--instance=inst# : query gv$session
--awr : use display_awr in stead of display_cursor
The -l option is required and provides username, password and connect string; if the password is not supplied, sqlexplain will prompt for it. You also need to provide one sql_id, and the explain plan will be generated from the contents of various v$ views. If you provide the --awr option, the contents of the awr repository is used in stead. A sample call (with abridged output) is:
sqlexplain -l username{password}@//hostname/service b90qhmm2t4bwu
RWP*SQL Explain Release 3.2.0.1 Production on Tue, 10 Dec 2024 14:15:12 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL_ID b90qhmm2t4bwu, child number 0
-------------------------------------
select count(*) from products
Plan hash value: 2823379118
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| PRODUCTS_PK | 10000 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Rather than providing sql_id, you can provide --sessionid and possibly --instance in which case the sql_id will be taken from v$session respectively gv$session.
The sqlreport utility creates a sql report by calling dbms_sql_tune.report_sql_detail and writes the report to a html file which by default is named after the sql_id. Using the --help option, it shows the options:
RWP*SQL Report Release 3.2.0.1 Production on Tue, 10 Dec 2024 14:21:29 UTC
Create a sql report by calling dbms_sqltune.report_sql_detail
-l usr/pwd@con : required option to give database logon
sqlid : exactly one sqlid must be given
--output-file=filename : write to filename in stead of sqlid.html
--output-directory=
directory : put file in directory in stead of current
The -l option and sql_id are required and you typically need to make sure the generated output file is made available to be seen via a browser. A sample call is the following, where it is assumed that the directory /var/www/html/sqlreports exists and is exposed via a http daemon:
sqlreport -l username{password}@//hostname/service 8cwr9vd6bssf9 --output-directory=/var/www/html/sqlreports
RWP*SQL Report Release 3.2.0.1 Production on Tue, 10 Dec 2024 14:23:17 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Wrote 56KiB to /var/www/html/sqlreports/8cwr9vd6bssf9.html
The sqlmonitor utility is similar to sqlreport, except it generates html output from calling dbms_sql_monitor.report_sql_monitor. Using the --help option:
RWP*SQL Monitor Release 3.2.0.1 Production on Tue, 10 Dec 2024 14:34:34 UTC
Create a sql monitor by calling dbms_sql_monitor.report_sql_monitor
-l usr/pwd@con : required option to give database logon
sqlid : exactly one sqlid must be given
--sql-exec-id=N : provide sql_exec_id
--sql-exec-start=
YYYY.MM.DDTHH24:MI:SS : provide sql_exec_start
--sql-plan-hash-value=N : provide sql_plan_hash_value
--con-name=con-name : provide con_name
--output-file=filename : write to filename in stead of sqlid.html
--output-directory=
directory : put file in directory in stead of current
you can see that you can potentially control which of many actual sql monitor reports you want included. Otherwise, it is called like the sqlreport utility is.
The utilities are included in both the full download of rwloadsim, and as stand alone generated executables. It is recommended that you use the full installation, and the utilities do not require a repository database.
The reference manual to the utilities are available in the full download using rwlman or online at