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. They all require credentials to connect as a user with necessary privileges (typically DBA or other with access to the awr repository), and the write the output to stdout (for sqlexplain producing explain plan) or to a html file for sqlreport and sqlmonitor.

sqlexplain

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.