#################################################################
#
# SCRIPT FOR CREATING EXPLAIN STATEMENT FILES FROM SQL FILES.
# ALSO CREATES a master.sql FILE THAT WILL RUN ALL FILES IN
# DIRECTORY.
# 
# USE TO TEST CHANGES IN QUERY DURATION AND COST AFTER MAKING
# INSTANCE CONFIGURATION CHANGES.
#
# ASSUMPTIONS:
#	1. Each sql file has one and only statement in it.
#	2. All statements are selects, or if they
#	have DML statements, these DML can be run without
#       causing problems.
#       3. Each sql file ends with a ; or / to execute the file.
#
#################################################################
# Get list of sql files to process.
files=`ls *.sql`

# Overwrite master.sql in case one already exists.
echo "/*" > master.sql
echo "Master script that calls all other sql files." >> master.sql
echo "*/" >> master.sql

for i in $files
do

	# Get stub name of file
	shortname=`echo $i | awk -F. '{print $1}'`
	explainfile=${shortname}_explain.sql

	# Create explainfile and prepend and append explain statements.
	echo "spool ${explainfile}.log" > ${explainfile}
	echo "EXPLAIN PLAN" >> ${explainfile}
	echo "SET STATEMENT_ID = '"$shortname"'" >> ${explainfile}
	echo "INTO plan_table" >> ${explainfile}
	echo "FOR" >> ${explainfile}
	cat $i >> ${explainfile}
	echo "SET LINESIZE 160"  >> ${explainfile}
	echo "SET PAGESIZE 0" >> ${explainfile}
	
	echo "SELECT PLAN_TABLE_OUTPUT"  >> ${explainfile}
	echo "FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '"$shortname"','TYPICAL'));"   >> ${explainfile}
	echo "spool off"   >> ${explainfile}

	# Write out master.sql entries.
	echo "/* Statements for ${shortname} query. */" >> master.sql
	echo "@@"${explainfile} >> master.sql
	echo "spool "${shortname}".log" >> master.sql
	echo "set timing on" >> master.sql
	echo "set feedback on" >> master.sql
	echo "@@"${i} >> master.sql
	echo "set timing off" >> master.sql
	echo "spool off" >> master.sql
	echo "" >> master.sql
	
done 

echo "exit" >> master.sql

echo "Created a file with explain plan statements for every"
echo "sql file found in the directory."
echo "Also created a master.sql file that calls all sql files."