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! ;-)
