------------------------------------------------------------------------
--  Copyright (c) Visa Europe, 2009.
--
--    NAME
--      tune_report.sql
--
--    DESCRIPTION
--      
------------------------------------------------------------------------

spool tune_report.log
prompt '======================================================================='
prompt '       This script was executed by &_USER on &_DATE                    '
prompt '======================================================================='
prompt '                                                                       '
prompt ' This script analyses all sqls, executed by specified user and stored '
prompt ' in Active Session History repository.                                 '
prompt ' Note: this script should be executed under SYS or SYSTEM account.     '
prompt '                                                                       '
prompt ' The script has two options:                                           '
prompt '                                                                       '
prompt '       PLAN: this option extract execution plan for every sql          '
prompt '             statement and save it to the file in specified directory. '
prompt '             The dbms_xplan built-in Oracle package is used here.      '
prompt '             This option is DEFAULT for the script.                    '
prompt '       TUNE: this option call DBMS_SQLTUNE built-in package            ' 
prompt '             in order to retrieve Oracle recommendation for this       '
prompt '             specific sql statement.                                   '
prompt '             NOTE: this operation is very consuming on CPU activity.   '
prompt '                                                                       '
prompt ' The script requires the following parameters supplied with the        '
prompt ' sqlplus command, in the following order.                              '
prompt '                                                                       '
prompt '       1. user_schema          - The analyzed user schema.             '
prompt '                                 For Example: VMAS_APP                 '
prompt '                                                                       '
prompt '       2. report_folder        - The name of the folder on Oracle      ' 
prompt '                                 server side, where report file        '
prompt '                                 will be saved.                        '
prompt '                                 NOTE: this folder must exists and user' 
prompt '                                 should have WRITE permission on it    '
prompt '                                 For example: c:\temp\report           '
prompt '       3. module               - The name of execution module.         ' 
prompt '                                 This option allow you analyze the     ' 
prompt '                                 statement executed by specific program'
prompt '                                 or tool. You will see the module name '
prompt '                                 in a table below.                     '
prompt '       4. report_type          - The report type can be: PLAN or TUNE  '
prompt '                                                                       '

/* Define srction for testing. Please remove later */
/*
define user_schema=SYS
define report_folder='/u01/app/oracle/product/10.2.0/db_1/'
define report_type=TUNE
define module='Visa.Europe.VMAS.BS.WindowsServiceHost.A.exe'
*/
set echo off heading on underline on verify off;
set serveroutput on buffer 2560000
SET PAGESIZE 30

column username heading "User Name" new_value username format a12;
column sqls heading "Number of|Sql statements" new_value sqls format 99999;
column module heading "Execution |Module Name" new_value username format a50;

prompt
prompt Choose the user schema for investigation.
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select u.username username,
       sqlids as sqls,
       module as module
from dba_users u,
(
  select user_id, module, sum(cc) as sqlids
      from
      (
         select s.user_id as user_id, s.MODULE, count(distinct sql_id) as cc  from v$active_session_history s group by s.user_id, s.module
         UNION ALL
         select s.user_id as user_id, s.MODULE, count(distinct sql_id) as cc from dba_hist_active_sess_history s group by s.user_id, s.module
      ) allsql
  group by user_id, module
) allsql2 
where allsql2.user_id = u.user_id
order by 1 asc, 2 desc, 3 asc;

define user_schema   = &user_schema
define module		 = &module
define report_folder = &report_folder
define report_type   = &report_type


declare
	/* Common variables */
	folder_name    varchar2(30) := 'TUNE_REPORT_FOLDER';
	file_name      varchar2(40);
	i_rep_type     varchar2(30);
	out_file       utl_file.file_type;  
	user_id		 number;
	is_error       number(1) :=0;

	/* Plan report variable */
	varchar_table  dbms_sql.varchar2_table;

	/* Sql tune task variables */  
	sqlid_table    dbms_sql.varchar2_table;  
	l_sql_tune_task_id  varchar2(100);  
	sqlid           dba_hist_sqlstat.sql_id%TYPE;
	task_name       varchar2(100);
	task_report     clob;  

	/* Dynamic sql variables  */  
	t_cursor   NUMBER;
	t_ret_val  NUMBER;    

	/* Procedure log prints custom message to console */
	procedure log(msg VARCHAR2) is 
	begin
		dbms_output.put_line(msg);
	end;

	/* Procedure log_error prints exception stack */
	/* together with SQL error code and Message */  
	procedure log_error(msg VARCHAR2) is 
	  begin
		log('** Error: '||msg);
		log('** SQLCODE: '||SQLCODE||' ERRMESSAGE: '||SQLERRM (SQLCODE));
		log(DBMS_UTILITY.format_error_backtrace);
	  end;

	/* Procedure execsql execute custom sql. */
	/* All execption are catched inside this procedure. */
	procedure execsql(sqlstr varchar2) is
	begin
		t_cursor := dbms_sql.open_cursor;
		dbms_sql.parse(t_cursor, sqlstr, dbms_sql.NATIVE);
		t_ret_val := dbms_sql.execute(t_cursor);
		dbms_sql.close_cursor(t_cursor);
	exception
		when OTHERS then
		log_error('Processing sql: '||sqlstr);
	end;

	/* The do_nothing procedure is empty wrapper */
	procedure do_nothing is 
	begin
		is_error := 1;
	end;

	/* The init produre executes first and prepares */
	/* resources for the report. */
	procedure init is
	begin
		/* Enable logging to console */
		dbms_output.enable(2560000);

		select max(user_id) into user_id from dba_users where username = '&user_schema';
		  
		/* Create SQL reference to folder on the disk */    
		execsql('create directory '||folder_name||' AS ''&report_folder'' ');
		  
		if upper('&report_type') = 'PLAN'
		then
			i_rep_type := 'PLAN';
			file_name  := '&user_schema'||'_PLAN_'||to_char(sysdate,'DDMON_HH24MI')||'.txt';
		else  
			i_rep_type := 'TUNE';
			file_name  := '&user_schema'||'_TUNE_'||to_char(sysdate,'DDMON_HH24MI')||'.txt';
		end if;

		/* Open pointer to the output report file */
		out_file := utl_file.fopen(folder_name, file_name, 'W');
	  
	end; -- End of init procedure

	/* Procedure to_file prints custom message to output file on the disk */
	/* The out_file must be opened before calling this procedure, otherwise  */  
	/* exception will be raised. */    
	procedure to_file(msg varchar2) is
	begin
		utl_file.put_line(out_file, msg);
	end;

	/* Procedure to_file prints custom message - clob format, to output file on the disk */
	/* The out_file must be opened before calling this procedure, otherwise  */  
	/* exception will be raised. */    
	procedure to_file(msg clob) is
	begin
		utl_file.put_line(out_file, msg);
	end;
  
	/* Procedure drop_task remove tune task, defined in the script from database. */
	/* All exception are catched inside. */  
	procedure drop_task(task_id varchar2) is
	begin
		DBMS_SQLTUNE.DROP_TUNING_TASK(task_id);
		exception
	when OTHERS THEN 
		do_nothing;
	end;

	procedure create_task(sql_id varchar2, task_id varchar2) is
	begin
		l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
								sql_id      => sql_id,
								scope       => DBMS_SQLTUNE.scope_comprehensive,
								time_limit  => 60,
								task_name   => task_id,
								description => 'Tuning task for statement '||sql_id);
		DBMS_SQLTUNE.execute_tuning_task(task_name=>task_id);							
	exception
		when OTHERS THEN 
		do_nothing;
	end;
  
	/* The procedure process_plan retrives execution plan for user sql */
	procedure process_plan is 
	begin

		select   tf.*
		  bulk collect into varchar_table  
		  FROM 
			( 
				select distinct sqlid, childn 
				from
				(
					select distinct sql_id as sqlid, sql_child_number as childn from v$active_session_history s 
						where s.user_id = user_id 
						and s.module='&module'											
					UNION 
					select distinct sql_id as sqlid, sql_child_number as childn from Dba_Hist_Active_Sess_History s 
						where s.user_id = user_id 
						and s.module='&module'											
				) 
			) ht,
		table (DBMS_XPLAN.display_cursor(ht.sqlid, ht.childn, 'SERIAL' )) tf;     
	  
		for i in 1..varchar_table.last 
		loop
			to_file(varchar_table(i));
		end loop;
	end;

	/* The procedure process_sql_tune execute functions from DBMS_SQLTUNE package */
	/* in order to ger Oracle recomendation  for ecery user sql statement */  
	procedure process_sql_tune is 
	begin

		select distinct sqlid bulk collect into sqlid_table 
		from
		(
			select distinct sqlid, childn 
			from
			(
				select distinct sql_id as sqlid, sql_child_number as childn from v$active_session_history s 
					where s.user_id = user_id 
					and s.module='&module'					
				UNION 
				select distinct sql_id as sqlid, sql_child_number as childn from Dba_Hist_Active_Sess_History s 
					where s.user_id = user_id 
					and s.module='&module'
			) 
		) allsql;
						
		log('Define tune tasks ...');                        
		/* first create and execute tasks */
		for i in 1..sqlid_table.last 
		loop
			begin
				sqlid := sqlid_table(i);
				task_name := sqlid || '_tunning_task';
				create_task(sqlid, task_name);
			exception
				when OTHERS THEN
					do_nothing;
			end;
		end loop;

		/* Wait 30 seconds for all task finished executions */
		DBMS_LOCK.SLEEP(30);
		log('Retrive task report ...');                        
		
		/* Then retrive task recomendation and remove task */
		for i in 1..sqlid_table.last 
		loop
			begin
				sqlid := sqlid_table(i);
				task_name := sqlid || '_tunning_task';
				task_report := dbms_sqltune.report_tuning_task(task_name);
				to_file(' ');
				to_file('************************* TUNNING SQL ****************');      
				to_file(task_report);
				utl_file.FFLUSH(out_file);

				drop_task(task_name);
			exception
				when OTHERS THEN 
					drop_task(task_name);
			end;
		end loop;
	end; 

	/* The dispose procedure release all open resources*/
	procedure dispose is 
	begin
		/* Drop created folder*/
		execsql('drop directory '||folder_name);    

		/* Close file */
		if utl_file.is_open(out_file) 
		then
			utl_file.fclose(out_file);
		end if;    
	end;

begin /* MAIN FLOW*/
	init;
	to_file('Report for User:'||'&user_schema'||' UserId:'||user_id||' Application Module:'||'&module');
	log('Start '||'&report_type'||' on '|| to_char(sysdate,'dd-MON-yyyy hh24:MI'));

	if i_rep_type='PLAN' 
	then
		process_plan;
	else
		process_sql_tune;
	end if;


	log('Finish Report on '|| to_char(sysdate,'dd-MON-yyyy hh24:MI'));
	log('Report File '||file_name);  
	dispose;

exception
  when OTHERS then
	log_error('General exception while main flow.');
    dispose;

end; /* END OF MAIN FLOW */
/
undefine user_schema;
undefine module;
undefine report_folder; 
undefine report_type;