help.sql: Show TPT Script Purpose and Syntax

2020-01-15

If you use my TPT scripts for Oracle troubleshooting, I have some good news for you. I had created a help.sql script years ago for showing me a quick index of my scripts, with syntax examples, straight from sqlplus command line. BUT, it was just an empty template, I never got to populating help.sql to cover my most used scripts.

Recently Tomasz Sroka, who had attended my Oracle troubleshooting training, mentioned that he had taken matters into his own hands and documented about 100 of my scripts in the help command. He did most of the hard work and since the ball was rolling, I added a few more items and now we have a decent “index of TPT scripts” what people have been regularly asking for.

Pull the latest changes from my TPT repo and try help.sql out yourself. Make sure you scroll right in the text boxes below to see the full output, including script usage examples:

SQL> @help help

NAME            DESCRIPTION                       USAGE
--------------- --------------------------------- ------------------------------------
help.sql        Display TPT script help           @help <search_expression>
                                                  @help explain
                                                  @help lock|latch.*hold
                                                  @help ^ind.*sql|^tab.*sql

Help.sql also supports regular expressions, like showing anything where script name or description contains “latch” or “lock”:

SQL> @help latch|lock

NAME               DESCRIPTION                                                  USAGE
------------------ --------------------------------------------------- --------------------------------------------------------------------------------------------------------------
latchprof.sql      Profile top latch holders (V$ version)              @latchprof <grouping_columns> <sid> <latch_name> <samples>
                                                                       @latchprof name,sqlid 123 % 10000
                                                                       @latchprof sid,name,sqlid % "shared pool" 10000

latchprofx.sql     Profile top latch holders eXtended (X$ version)     @latchprofx <grouping_columns> <sid> <latch_name> <samples>
                                                                       @latchprofx sid,name 123 % 10000
                                                                       @latchprofx sid,name,timemodel,hmode,func % "shared pool" 10000

lock.sql           Display current locks                               @lock <filter_expression>
                                                                       @lock 1=1
                                                                       @lock type='TM'

lt.sql             Display lock type info                              @lt <lock_name>
                                                                       @lt TM

segcached.sql      Display number of buffered blocks of a segment      @segcached [<owner>.]<object_name>
                                                                       @segcached soe.orders
                                                                       @segcached soe.%

Show everything with “ash” in the script name or description:

SQL> @help ash

NAME                    DESCRIPTION                                                  USAGE
----------------------- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------
ash_wait_chains.sql     Display ASH wait chains (multi-session wait signature, a     @ash/ash_wait_chains <grouping_cols> <filters> <from_time> <to_time>
                        session waiting for another session etc.)                    @ash/ash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate

asqlmon.sql             Report SQL-monitoring-style drill-down into where in an      @ash/asqlmon <sql_id> <child#> <from_time> <to_time>
                        execution plan the execution time is spent (ASH based)       @ash/asqlmon 7q729nhdgtsqq 0 sysdate-1/24 sysdate
                                                                                     @ash/asqlmon 7q729nhdgtsqq % sysdate-1 sysdate

dash_wait_chains.sql    Display ASH (based on DBA_HIST) wait chains (multi-session   @ash/dash_wait_chains <grouping_cols> <filters> <from_time> <to_time>
                        wait signature, a session waiting for another session etc.)  @ash/dash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate

event_hist.sql          Display a histogram of the number of waits from ASH          @ash/event_hist.sql <event> <filter_expression> <from_time> <to_time>
                        (milliseconds)                                               @ash/event_hist.sql log.file 1=1 sysdate-1/24 sysdate
                                                                                     @ash/event_hist.sql log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdate

event_hist_micro.sql    Display a histogram of the number of waits from ASH          @ash/event_hist_micro <event> <filter_expression> <from_time> <to_time>
                        (microseconds)                                               @ash/event_hist_micro log.file 1=1 sysdate-1/24 sysdate
                                                                                     @ash/event_hist_micro log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdate

hash.sql                Display the hash value, sql_id, and child number of the last @hash
                        SQL in session

Use regular expressions to search only the file name (not description), by searching strings that end with “.sql”:

SQL> @help seg.*\.sql$

NAME                      DESCRIPTION                                                  USAGE
----------------------- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------
seg.sql                 Display segment information                                  @seg [<owner>.]<segment_name>
                                                                                     @seg soe.customers
                                                                                     @seg soe.%

segcached.sql           Display number of buffered blocks of a segment               @segcached [<owner>.]<object_name>
                                                                                     @segcached soe.orders
                                                                                     @segcached soe.%

topseg.sql              Display top space users per tablespace                       @topseg <tablespace_name>
                                                                                     @topseg soe
                                                                                     @topseg %

topsegstat.sql          Display information about top segment-level statistics       @topsegstat <statistic_name>
                                                                                     @topsegstat reads
                                                                                     @topsegstat %

And that’s it! Help.sql does not need any installation, it’s just a big UNION ALL query containing all the help text and a WHERE clause for filtering. Currently 118 TPT scripts are listed in there, but I’ll add more over time (GitHub contributions to help.sql are welcome too, if you follow its existing style).

History:

Previously the closest thing to my TPT script index was just a grep Purpose: *.sql command as explained here and my blog entries, training and YouTube troubleshooting hacking sessions in general. But now we have an actual index with syntax examples too! Big thanks to Tomasz Sroka for getting this thing going.

Future:

I plan to create web-based documentation too (let’s see which year), possibly auto-generating the HTML pages from the help.sql and adding more info about when/where to use the scripts, plus links to relevant blog entries with case studies (my own blog entries and by others too). Stay tuned, check back in 2030, should be done by then! ;-)


NB! Check out 2020 online training classes here! Advanced Oracle Troubleshooting training, Advanced Oracle SQL Tuning training, Practical Linux Performance & Application Troubleshooting training. In addition to the online classes, all attendees will receive personal downloadable video recordings too!