#!/bin/ksh
################################################################################
##
## File name:   os_explain 
## Purpose:     Script to explain the currently active branch of SQL plan 
##              execution from Oracle server process stack trace
##
## Author:      Tanel Poder
## Copyright:   (c) http://www.tanelpoder.com
##              
## Usage:       1) Take a stack trace of an Oracle process (either by using a
##                 a debugger, pstack or extract the stack from a corefile,
##                 save it to a file (pstack.txt for example)
## 	        2) run ./os_explain <stack_trace_file>
##                 For example: ./os_explain pstack.txt
##	        
## Other:       Get stack using pstack on Solaris and Linux, using procstack
##              on AIX and by using pstack on recent versions of HP-UX. 
##              Older HP-UX versions may require a debugger for getting stack
##              from OS. As an alternative (especially on Windows) you could
##              use ORADEBUG DUMP ERRORSTACK for dumping stack of a process.
##              Note that ORADEBUG's stack dump mechanism is not 100% safe
##              on active processes
##
##		Most of Oracle kernel function prefix translations are based
##              on Metalink note 175982.1
##
################################################################################

# LIFO line reverser
f_lifo() {

	#echo Entering f_lifo()

	MAX_ARRAY=4095

	i=$MAX_ARRAY

	IFS=^
	while read input ; do
	    array[i]=$input
	    ((i=i-1))
	done

	i=1

	for output in ${array[@]} ;  do

	    if test "$1" = "-n" ; then
	         printf "%4d %s\n" $i $output
	    else
	         printf "%s\n" $output
	    fi
	    ((i=i+1))
	done

}


TRANSLATION_STRING='
/-----------------  lwp# 2/,$d;
s/(.*//;
s/ [[0-9][a-f]]{16} /./;
s/qerae/AND-EQUAL: /g;
s/qerba/BITMAP INDEXAND : /g;
s/qerbc/BITMAP INDEX COMPACTION: /g;
s/qerbi/BITMAP INDEX CREATION: /g;
s/qerbm/MINUS: /g;
s/qerbo/BITMAP INDEX OR: /g;
s/qerbt/BITMAP CONVERT: /g;
s/qerbu/BITMAP INDEX UNLIMITED-OR: /g;
s/qerbx/BITMAP INDEX ACCESS: /g;
s/qercb/CONNECT BY: /g;
s/qercbi/SUPPORT FOR CONNECT BY: /g;
s/qerco/COUNT: /g;
s/qerdl/DELETE: /g;
s/qerep/EXPLOSION: /g;
s/qerff/FIFO BUFFER: /g;
s/qerfi/FIRST ROW: /g;
s/qerfl/FILTER DEFINITION: /g;
s/qerfu/UPDATE: /g;
s/qerfx/FIXED TABLE: /g;
s/qergi/GRANULE ITERATOR: /g;
s/qergr/GROUP BY ROLLUP: /g;
s/qergs/GROUP BY SORT: /g;
s/qerhc/HASH CLUSTERS: /g;
s/qerhj/HASH JOIN: /g;
s/qeril/IN-LIST: /g;
s/qerim/INDEX MAINTENANCE: /g;
s/qerix/INDEX: /g;
s/qerjot/NESTED LOOP JOIN: /g;
s/qerjo/NESTED LOOP OUTER: /g;
s/qerle/LINEAR EXECUTION  IMPLEMENTATION: /g;
s/qerli/PARALLEL CREATE INDEX: /g;
s/qerlt/LOAD TABLE: /g;
s/qerns/GROUP BY NO SORT: /g;
s/qeroc/OBJECT COLLECTION ITERATOR: /g;
s/qeroi/EXTENSIBLE INDEXING QUERY COMPONENT: /g;
s/qerpa/PARTITION: /g;
s/qerpf/QUERY EXECUTION PREFETCH: /g;
s/qerpx/PARALLELIZER: /g;
s/qerrm/REMOTE: /g;
s/qerse/SET IMPLEMENTATION: /g;
s/qerso/SORT: /g;
s/qersq/SEQUENCE NUMBER: /g;
s/qerst/QUERY EXECUTION STATISTICS: /g;
s/qertb/TABLE ACCESS: /g;
s/qertq/TABLE QUEUE: /g;
s/qerua/UNION-ALL: /g;
s/qerup/UPDATE: /g;
s/qerus/UPSERT: /g;
s/qervw/VIEW: /g;
s/qerwn/WINDOW: /g;
s/qerxt/EXTERNAL TABLE FETCH : /g;
s/opifch2/SELECT FETCH: /g
'

# main()

if [ "$1" == "-a" ] ; then
	FILTER="^\$|----------"
	INDENT=" "
	shift
else 
	FILTER="^\$|\?\?|oracle|----------"
	TRANSLATION_STRING="/opifch /,\$d; $TRANSLATION_STRING"
	INDENT=" "
fi

for i in $* ; do
sed -e "$TRANSLATION_STRING" < $i \
    | egrep -v "$FILTER" \
    | sed 's/^ *//g' \
    | cut -d" " -f2- \
    | f_lifo \
    | awk "/rwsfcd/{ option=\"*\" } !/rwsfcd/{ pref=(pref \"$INDENT\") ; print pref option \" \" \$0 \" \" ; option=\" \" }" 
done

# that's all!

