Note: last updated in June 2015. Download links: http://canali.web.cern.ch/canali/resources.htm and https://github.com/lucacanali
Command-line is still very useful for Oracle database administration, troubleshooting and performance monitoring. One of the first thins that I do when I want to work with a given Oracle database is to connect as a DBA user with Sql*plus and get information on the active sessions with a script. I will run the script a few times to get an idea of what's happening 'right now' and get a possible starting point for more systematic tuning/troubleshooting if needed.
I liken this approach to examining a few pictures from a remote webcam and trying to find out what's happening out there!.. The method can be powerful, although with some limitations. One of its advantages is that it is easy to implement in Oracle! One of the main source of information for this type of monitoring is GV$SESSION, which is available in Oracle since ages. More recently GV$ACTIVE_SESSION_HISTORY has proven very useful and I quite like the additional improvements introduced there in11gR2.
Examples: One of my favorites is a script I have called top.sql (not very much an original name I know :) which queries gv$session (RAC-aware) and prints out what I currently find the most interesting details (for my environment) on active sessions that I can fit in roughly 200 characters. Another script that does a similar job taking data from v$active_session_history is ash.sql. the scripts ash2.sql, ash3.sql and ash4.sql are the RAC versions for 2, 3 and 4 node RACs respectively.
One of the typical pain points when writing CLI monitoring scripts is deciding which information to display and how to pack it in a relatively short line. I have also found that with each major Oracle versions I want to refresh my scripts with a few changes.
Scripts details: I have packaged some of the scripts that I have written and that I find useful in a zip file which is available at this link (unzip locally and run using sqlplus). Note that the scripts are tested on 11gR2, many of them will also work on 10g and 12c.
Monitoring Class (Active Session Monitoring)
| Script Name | Description |
|---|---|
| top.sql | List with selected details of the active sessions, from gv$session (RAC aware) |
| top_10g.sql | Active sessions details, from gv$session, 10g version (RAC aware) |
| ash.sql | List with selected details of the active sessions, from v$active_session_history (one instance only) |
| ash_h.sql | Usage @ash_h <n#_sec>. Active sessions details for the last n#_sec seconds, from v$active_session_history (current instance only) |
| ash2.sql, ash3.sql ash4.sql | Active sessions details, from gv$active_session_history (for RAC 2, 3 and 4 nodes). |
SQL Monitoring
| Script Name | Description |
|---|---|
| monitor.sql | Display information form gv$sql_monitor for running statements statements. See also monitor_details.sql |
| monitor_plan.sql | Usage: @monitor_plan <key>. A drill-down script from monitor.sql. Prints details of the execution plan from gv$sql_plan_monitor |
| monitor_report_sid.sql, monitor_report_sid_active.sql | Usage: @monitor_report_sid_active <sid>. Generates an active/html sql monior report for the given sid. See also @monitor_report_sid <sid> for the simpler html version. |
| monitor_report_sqli_d.sql, monitor_report_sql_id_active.sql | Usage: @monitor_report_sql_id_active <sql_id>. Generates an active/html sql monior report for the given sql_id. See also @monitor_report_sql_id <sql)id> for the simpler html version. |
Drill-down Class
| Script Name | Description |
|---|---|
| ash_sess.sql | Usage: @ash_sess <inst_id> <sid> <n#_sec>. Prints details from gv$active_session_history for a given session (RAC aware) |
| explain.sql | Usage: @explain <sql_id>. Prints detailed explain plan info for given sql_id from library cache (current instance only) |
| explain_awr.sql | Usage: @explain_awr sql_id. Prints detailed explain plan info for given sql_id from AWR (rac aware) |
| obj.sql | Usage: @obj <object_id>. Find details for a given object id, or data_object_id |
| sessinfo.sql | Usage example: @sessinfo sid=<num>. Prints info on sessions that match the search pattern (RAC aware) |
| sql.sql | Usage: @sql <sql_id>. Prints sql text for given sql_id (RAC aware) |
Workload Characterization Class
| Script Name | Description |
|---|---|
| sysmetric.sql, sysmetric_details.sql | Display values of selected system metrics (RAC aware) |
| iometric.sql, iometric_details.sql | IO utilization metrics and details (RAC aware) |
| ehm.sql, ehm_local.sql ehm_micro, ehm_micro_local | Usage: @ehm 10 "db file sequential read". Event history metric data, helps finding skew in wait times, for example to study random reads latency skew (RAC aware). ehm_micro -> 12c version, latency drill down at microsecond. |
| eventmetric.sql | Wait event metric details for top wait events (RAC aware) |
| filemetric.sql | File metric details for top-usage files (RAC aware) |
| ash_top.sql | Usage @ash_top <n#_sec>. Aggregates ash data over n#_sec |
| jobs_running.sql | List running jobs details (RAC aware) |
| load.sql | Displays OS-load values (RAC aware) |
| longops.sql, longops_10g.sql, longops_details.sql | Info on long running operations from gv$sessoin_longops (RAC aware) |
| transactions.sql | List of open transactions and crash recovery (RAC aware) |
ASH and AWR Reports
| Script Name | Description |
|---|---|
| ash_report.sql | Usage example: @ash_report sysdate-1/24 sysdate Generates an ASH report for the current instance and displays it in a browser. |
| awr_report.sql | Usage: @awr_report <snap_begin> <snap_end>. Generates an AWR report for the current instance and displays it in a browser. |
| awr_global_report.sql | Usage: @awr_global_report <snap_begin> <snap_end>. Generates a global AWR report for the current database and displays it in a browser. |
| awr_snapshots.sql | Lists AWR snapshots for the last 24 hours. |
Miscellaneous
| Script Name | Description |
|---|---|
| kill.sql | Usage @kill <sid> <serial#> kill the given session (current instance only) |
| kill_select.sql | Usage example: @kill_select "sql_id='xxxx'". Helps in preparing scripts for killing large number of sessions (current instance only) |
| locks.sql, locks_10g.sql, locks_details.sql | Scripts to help investigating locks and blocking enqueues issues (RACaware) |
| binds.sql | Usage: @binds <sql_id>. Helps in finding bind values for a given sql_id (RAC aware) |
| login.sql | Sql*plus startup/config script for my environment (windows client version 11.2.0.3) |
Conclusions
Command-line scripts are still of great help for monitoring and performance troubleshooting of Oracle databases. Of particular interest are queries to print the details of active sessions, the SQL they execute and their wait events details. This makes for a quick and easy entry point to troubleshooting and to get an idea of what's happening on the DB server. More systematic troubleshooting may be needed beyond that. Command-line scripts are easy to modify and customize, many DBAs indeed have their own tool-set. A selection of my own DBA scripts can be downloaded from this link.
A few links to end this post: First of all the excellent session snapper and the rest of the collection of scripts by Tanel Poder. In addition a mention to a few cool tools that are a sort of bridge between command-line and graphical-interface approaches: MOATS by Tanel Poder and Adrian Billington , SQL Dashboard by Jagjeet Singh and topaas by Marcin Przepiorowski.