A lot of people have asked me whether there’s some sort of index or “table of contents” of my TPT scripts (there’s over 500 scripts in the tpt_public.zip file – http://tech.e2sn.com/oracle-scripts-and-tools )
I have planned to create such index for years, but never got to it. I probably never will :) So a good way to extract the descriptions of some scripts is this (run the command in the directory where you extracted my scripts to):
Note: the single and double-quotes may get messed up when the browser tries to be smart and replace them with nicer looking characters (which Unix doesn’t recognize then). When copying & pasting this command, make sure that the single & double-quotes are the regular ones Unix shell can accept):
$ grep -i Purpose: *.sql | awk -F: ‘{ printf(“%20s %-50s\n”, $1, $3) }’
bhla.sql Report which blocks are in buffer cache, protected by a cache
bufprof.sql Display buffer gets done by a session and their reason
calc.sql Basic calculator and dec/hex converter
channels.sql Report KSR channel message counts by channel endpoints
curheaps.sql Show main cursor data block heap sizes and their contents
dba.sql Convert Data Block Address (a 6 byte hex number) to file#, block#
ddl.sql Extracts DDL statements for specified objects
df.sql Show Oracle tablespace free space in Unix df style
diag_sid.sql Display current Session Wait info
diag_sid.sql An easy to use Oracle session-level performance snapshot utility
disco.sql Generates commands for disconnecting selected sessions
getplusparm.sql get sqlplus parameter value (such linesize, pagesize, sqlcode,
grpn.sql Quick group by query for aggregating Numeric columns
hash.sql Show the hash value, SQL_ID and child number of previously
i2h.sql Advanced Oracle Troubleshooting Seminar demo script
im.sql Display In-Memory Undo (IMU) buffer usage
init.sql Initializes sqlplus variables for 156 character terminal
kglbroken.sql
kglbroken.sql Report broken kgl locks for an object this can be used for
kill.sql Generates commands for killing selected sessions
la.sql Show which latch occupies a given memory address and its stats
lastchanged.sql Detect when a datablock in table was last changed
latchprof.sql Perform high-frequency sampling on V$LATCHHOLDER
latchprof_old.sql Perform high-frequency sampling on V$LATCHHOLDER
latchprofx.sql Perform high-frequency sampling on V$LATCHHOLDER
lh.sql Show latch holding SIDs and latch details from V$LATCHHOLDER
lhp.sql Perform high-frequency sampling on V$LATCHHOLDER
lhpx.sql Perform high-frequency sampling on V$LATCHHOLDER
lotshparses.sql Generate Lots of hard parses and shared pool activity
lotshparses2.sql Generate Lots of hard parses and shared pool activity
lotslios.sql Generate Lots of Logical IOs for testing purposes
lotspios.sql Generate Lots of Physical IOs for testing purposes
lotssparses.sql Generate Lots of soft parses and library cache/mutex activity
lotssparses2.sql Generate Lots of soft parses and library cache/mutex activity
mutexprof.sql Display KGX mutex sleep history from v$mutex_sleep_history
nonshared.sql Print reasons for non-shared child cursors from v$sql_shared_cursor
nonshared2.sql Show the reasons why more child cursors were created instead of
ostackprof.sql Take target process stack samples and show an execution profile
pmem.sql Show process memory usage breakdown – lookup by process SPID
pmem_detail.sql Show process memory usage breakdown details – lookup by process SPID
prefetch.sql Show KCB layer prefetch
pvalid.sql Show valid parameter values from V$PARAMETER_VALID_VALUES
rowcache.sql Show parent rowcache entries mathcing an object name
rs.sql Display available Redo Strands
s.sql Display current Session Wait and SQL_ID info (10g+)
sample.sql Sample any V$ view or X$ table and display aggregated results
sampleaddr.sql High-frequency sampling of contents of a SGA memory address
ses.sql Display Session statistics for given sessions, filter by
ses2.sql Display Session statistics for given sessions, filter by
sgastatx.sql Show shared pool stats by sub-pool from X$KSMSS
smem.sql Show process memory usage breakdown – lookup by session ID
smem_detail.sql Show process memory usage breakdown details – lookup by session ID
snapper.sql An easy to use Oracle session-level performance snapshot utility
snapper3.15.sql An easy to use Oracle session-level performance snapshot utility
snapper_v1.sql An easy to use Oracle session-level performance snapshot utility
snapper_v2.sql An easy to use Oracle session-level performance snapshot utility
stat.sql Execute SQL statement in script argument and report basic
sw.sql Display current Session Wait info
sw2.sql Display current Session Wait info
swg.sql Display given Session Wait info grouped by state and event
swo.sql Display current Session Wait info
topsql.sql Show TOP SQL ordered by user-provided criteria
usql.sql Show another session’s SQL directly from library cache
usqlx.sql Show another session’s SQL directly from library cache
waitprof.sql Sample V$SESSION_WAIT at high frequency and show resulting
xb.sql Explain a SQL statements execution plan with execution
xde2.sql Describe X$ tables, column offsets and report indexed fixed table
xm.sql Explain a SQL statements execution plan directly from library cache
xma.sql Explain a SQL statements execution plan directly from library cache
xmai.sql Explain a SQL statements execution plan with execution
xms.sql Explain your last SQL statements execution plan with execution
xmsh.sql Explain a SQL statements execution plan with execution
xmsi.sql Explain a SQL statements execution plan with execution
That’s a perfect index !
and hence indextype grep-and-awk is sometimes superior than indextype CTXSYS.CONTEXT
a great work
Great job Tanel,
i’ve downloaded your scripts, i’m a great fan for ASH :-) and in your script asqlmon.sql there are sql_plan_line_id, sql_plan_operation, sql_plan_options columns but i think are only for 11G rel because i’ve a 10G and this script doesn’t work because in the v$active_session_history these columns don’t exist :-(…
@Alberto
Yes, this always-enabled plan line level monitoring in ASH is available since 11g only. The server processes need to “notify” the SGA about in which exec plan step the execution is, so that the ASH – MMNL process could read it. It can’t read other processes memory directly, so this info must be in SGA. And this SGA “notification” feature was introduced in 11g.
In 10g (and 9.2) the alternative is to selectively enable statistics_level = all for monitored sessions or add gather_plan_statistics hint to some queries. However this makes the query considerably slower (and consume more CPU due to extra instrumentation and timestamp syscalls) so you only should enable this for duration of your troubleshooting and to as few sessions as possible.
@Tanel Poder
Great explanation Tanel,
thanks a lot.
@Tanel Poder
Hi Tanel,
for check my sessions in 10G in real time like as ASH I’ve written this statement:
select V$SQL_PLAN.child_number,
V$SQL_PLAN.OPERATION,OBJECT_NAME,OPTIONS,
CARDINALITY as ROWS_,(bytes)/1024 as KByte,
COST,(V$SQL_PLAN.TIME) as TIME_SEC,
CPU_COST,io_cost,V$SQL_PLAN.PLAN_HASH_VALUE,
sid,serial#,status,osuser,machine, terminal, program,sql_text,executions,first_load_time,
COMMAND_TYPE,state,OPTIMIZER_MODE,OPTIMIZER_COST, event,buffer_gets,
logon_time, blocking_session_status
from v$session,v$sql,V$SQL_PLAN
where v$sql.sql_id = V$session.sql_id
and v$sql.CHILD_NUMBER=v$session.sql_child_number
and V$SQL_PLAN.SQL_ID = V$SQL.SQL_ID
order by SID;
Hi Tanel,
A great explanation panel at all. Like a reference book .