! Program: Explain.sqr
!
! To run explain.sqr, enter on the command line the following:
!
!   sqr explain.sqr usr/pwd input_file output_file -Oexplain.log
!
!    This program is designed to help a performance analyst optimize
! SQR code.  It reads in the input file (which is the log file from a
! SQR program run with the -S flag set on the command line) and outputs
! a file like the input file with the SQL's explain plan.  The input
! file (default = sqr.log) and the output file (default = explain.lis) 
! names are obtained from the user.  A copy of the plan_table table must 
! be accessible to the user.  The create script for this table may be   
! found in the Oracle Admin directory.  Due to the -O flag on the 
! command line, the output from explain.sqr's run will be spooled to the 
! explain.log file instead of sqr.log to avoid an overwrite of the 
! sqr.log file.
!
! Program History
!
! Personnel             Date       Change
! --------------------  ---------  -------------------------------
! Mark Johnson          04-JUN-95  Created
!

#define valid_db ORACLE
#define statement_id EXPLAIN
#define start_string 'Cursor Status:'

!-----------------------------------------------------------------

begin-setup
page-size 1 80
no-formfeed
end-setup

!-----------------------------------------------------------------

begin-program
move 0 to #cursor_num
move 'plan_table' to $plan_table
move 'all_tables' to $all_tables
do verify_valid_db
do clear_plan_table
input $input_file 'Enter input filename (default = sqr.log)'
input $output_file 'Enter output filename (default = explain.lis)'
do open_files
do read_log
end-program

!-----------------------------------------------------------------

begin-procedure read_log
while not #end-file
   read 1 into $line:80
   if $line = {start_string}
      break
   end-if
end-while
while not #end-file
   read 1 into $line:80
   print $line (1,1)
   next-listing
   if substr(ltrim($line,' '),1,6) = 'SQL = '
      let $sql_statement = substr($line, 10, 70)
      if substr(ltrim(upper($line),' SQL = '),1,7) = 'INSERT '
	 move 1 to #insert
      end-if
      while 1   ! while we read in lines of a SQL statement
	 read 1 into $line:80
	 print $line (1,1)
	 next-listing
	 if substr(ltrim($line,' '),1,11) = 'Compiles = '
	    break   ! The statement is over
	 end-if
	 if instr($line, '[$', 1)
	    move 1 to #dynamic
	 end-if
	 let $sql_statement = $sql_statement || ' ' || ltrim($line,' ')
      end-while
   add 1 to #cursor_num
   if #insert
      print 'Insert statements can not be explained' (1,4)
      next-listing
      show ' '
      show #cursor_num edit 999 '> Insert statements can not be explained'
      move 0 to #insert
      move 0 to #dynamic
   else
      if #dynamic
	 show ' '
	 show #cursor_num edit 999 '> Dynamic SQR can not be explained'
	 print 'Dynamic SQR can not be explained' (1,4)
	 next-listing
	 move 0 to #dynamic
	 move 0 to #insert
      else
	 do run_plan
	 do show_plan
      end-if
   end-if
end-if
end-while
end-procedure ! read_log

!-----------------------------------------------------------------

begin-procedure run_plan
! construct a dynamic variable holding the text of the SQL statement
! preceded by the explain plan text.  Run against the database.
let $statement_id = '{statement_id}' || to_char(#cursor_num)
let $explain_statement = 'explain plan set statement_id = '''
		      || $statement_id
		      || ''' for '
		      || $sql_statement
begin-sql ON-ERROR=explain_plan_error_proc
[$explain_statement]
end-sql
show ' '
show #cursor_num edit 999 '> Cursor explained'
end-procedure ! run_plan

!-----------------------------------------------------------------

begin-procedure show_plan
! Extract and report on the SQL statements being explained.
begin-select
lpad(' ', 2*level)  (1,2)
operation           (,)
options             (,+1)
object_name         (,+1)
decode(nvl(object_instance,100),100,' ',' ('||object_instance||')') (,+1)
   next-listing
from plan_table
connect by prior id = parent_id
       and statement_id = $statement_id
start with id = 1
  and statement_id = $statement_id
end-select
end-procedure ! show_plan

!-----------------------------------------------------------------

begin-procedure verify_valid_db
if $sqr-database != '{valid_db}'
   show ' '
   show 'Error: Must be using an {valid_db} database'
   next-listing
   print 'Error: Must be using an {valid_db} database' ()
   stop quiet
end-if
end-procedure ! verify_valid_db

!-----------------------------------------------------------------

begin-procedure clear_plan_table
! Clear the plan_table of rows that have a statement_id matching
! the statement_id being used for current explain plans.
begin-sql
delete from plan_table
where statement_id like '{statement_id}%'
end-sql
end-procedure ! clear_plan_table

!-----------------------------------------------------------------

begin-procedure open_files
!  get input_name from user and open file
if $input_file = ''
   move 'sqr.log' to $input_file
end-if
open $input_file as 1 for-reading record=80
!  get output_name from user and create file
if $output_file = ''
   move 'explain.lis' to $output_file
end-if
new-report $output_file
print 'Cursor Status and Explain Plan Report' (1) center
next-listing
print 'Generated by explain.sqr' (1) center
next-listing skiplines=2
end-procedure ! open_files

!-----------------------------------------------------------------

begin-procedure explain_plan_error_proc
show ' '
show #cursor_num edit 999 '> Unable to explain cursor'
show $sql-error
print 'Unable to explain plan' (,4)
next-listing
print $sql-error (,4)
next-listing
end-procedure ! explain_plan_error_proc

!-----------------------------------------------------------------