--
-- Setup : 
-- PATH 1) create below objects under sys schema and grant the package's execute privilege to the users you want.
-- PATH 2) grant below privileges to the user you want to install all loader objects;
-- grant create table to <user> ;
-- grant create view to <user> ;
-- grant create procedure to <user> ;
-- grant select on SYS.V_$STATNAME to <user> with grant option ;
-- grant select on SYS.V_$MYSTAT to <user> with grant option ;
-- grant select on SYS.V_$LATCH  to <user> with grant option ;
-- grant select on SYS.V_$system_parameter to <user> with grant option ;
-- grant execute on SYS.dbms_lock to <user> ;
-- grant execute on SYS.dbms_alert to <user> ;
-- grant execute on SYS.dbms_scheduler to <user> ;
--
DROP TABLE tcncrnt_loader_param PURGE;
CREATE TABLE tcncrnt_loader_param 
   ( vmain_scnrio_name VARCHAR2(64), 
     vscnrio_name VARCHAR2(64),
     vscnrio_dfntn VARCHAR2(2048),
     nenable_trace number(1) DEFAULT 0,
   	 dcreated DATE DEFAULT SYSDATE,
	 vcreator VARCHAR2(18) DEFAULT substr(user,1,18) )  ;
CREATE UNIQUE INDEX UK_tcncrnt_loader_param ON tcncrnt_loader_param (vmain_scnrio_name, vscnrio_name) ;	 
comment on table tcncrnt_loader_param is 'parameter table for the concurrent pl/sql loader';
comment on column tcncrnt_loader_param.vmain_scnrio_name is 'main scenario name';
comment on column tcncrnt_loader_param.vscnrio_name is 'sub scenario name';
comment on column tcncrnt_loader_param.nenable_trace is '10046 level 8 sql trace will be created or not - 1:create';
 
DROP TABLE tcncrnt_loader_results PURGE;
CREATE TABLE tcncrnt_loader_results
   ( vtask_name VARCHAR2(64),
 	 dtimestamp timestamp DEFAULT SYStimestamp,
     nelapsed_time number NOT NULL,
     vtop1_latch VARCHAR2(64) NOT NULL,
     vtop2_latch VARCHAR2(64) NOT NULL,
     vtop3_latch VARCHAR2(64) NOT NULL,
     vtop1_stat VARCHAR2(64) NOT NULL,
     vtop2_stat VARCHAR2(64) NOT NULL,
     vtop3_stat VARCHAR2(64) NOT NULL,
     nenable_trace number(1) DEFAULT 0 )  ;
CREATE INDEX uk_tcncrnt_loader_results ON tcncrnt_loader_results (vtask_name) ;
comment on table tcncrnt_loader_results is 'result table for the concurrent pl/sql loader';

create global temporary table gtt_runstats
( runid varchar2(15),  name varchar2(80),  value int )
on commit preserve rows;

create or replace view vw_runstats
as select 'STAT...' || a.name name, b.value
      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch ;

CREATE OR REPLACE PACKAGE pkg_cncrnt_loader IS
   --
   -- Author  : H.Tonguc YILMAZ - tonguc dot yilmaz at gmail dot com
   -- Created : 28.02.2008 06:30:46
   -- Purpose : to stop simple loop simulations and favor concurrent testing
   --
   PROCEDURE prc_set_scnrio(arg_vmain_scnrio_name IN tcncrnt_loader_param.vmain_scnrio_name%TYPE,
                            arg_vscnrio_name      IN tcncrnt_loader_param.vscnrio_name%TYPE,
                            arg_vscnrio_dfntn     IN tcncrnt_loader_param.vscnrio_dfntn%TYPE,
                            arg_nenable_trace     IN tcncrnt_loader_param.nenable_trace%TYPE);

   PROCEDURE prc_run_scnrio(arg_vmain_scnrio_name IN tcncrnt_loader_param.vmain_scnrio_name%TYPE);

   PROCEDURE prc_report_scnrio(arg_vtask_name IN tcncrnt_loader_results.vtask_name%TYPE);

END pkg_cncrnt_loader;
/

CREATE OR REPLACE PACKAGE BODY pkg_cncrnt_loader IS
   --
   -- Author  : H.Tonguc YILMAZ - tonguc dot yilmaz at gmail dot com
   -- Created : 28.02.2008 06:30:46
   -- Purpose : to stop simple loop simulations and favor concurrent testing
   --
   PROCEDURE prc_set_scnrio(arg_vmain_scnrio_name IN tcncrnt_loader_param.vmain_scnrio_name%TYPE,
                            arg_vscnrio_name      IN tcncrnt_loader_param.vscnrio_name%TYPE,
                            arg_vscnrio_dfntn     IN tcncrnt_loader_param.vscnrio_dfntn%TYPE,
                            arg_nenable_trace     IN tcncrnt_loader_param.nenable_trace%TYPE) IS
   BEGIN
      DELETE FROM tcncrnt_loader_param
       WHERE vmain_scnrio_name = arg_vmain_scnrio_name
         AND vscnrio_name = arg_vscnrio_name;
      INSERT INTO tcncrnt_loader_param
         (vmain_scnrio_name, vscnrio_name, vscnrio_dfntn, nenable_trace)
      VALUES
         (arg_vmain_scnrio_name,
          arg_vscnrio_name,
          arg_vscnrio_dfntn,
          
          arg_nenable_trace);
      COMMIT;
   END prc_set_scnrio;

   PROCEDURE prc_run_scnrio(arg_vmain_scnrio_name IN tcncrnt_loader_param.vmain_scnrio_name%TYPE) IS
      njob_value PLS_INTEGER := 0;
      vjob_str   VARCHAR2(32767);
      ncncrncy   PLS_INTEGER := 0;
   BEGIN
      SELECT a.VALUE
        INTO njob_value
        FROM v$system_parameter a
       WHERE a.NAME = 'job_queue_processes';
      SELECT COUNT(*)
        INTO ncncrncy
        FROM tcncrnt_loader_param
       WHERE vmain_scnrio_name = arg_vmain_scnrio_name;
      IF njob_value < ncncrncy THEN
         EXECUTE IMMEDIATE ('alter system set JOB_QUEUE_PROCESSES = ' ||
                           to_char(nvl(ncncrncy,
                                        0) + nvl(njob_value,
                                                 0)));
      END IF;
   
      FOR cur_param IN (SELECT *
                          FROM tcncrnt_loader_param
                         WHERE vmain_scnrio_name = arg_vmain_scnrio_name) LOOP
      
         vjob_str := 'DECLARE s1 pls_integer; s2 pls_integer; vtmp varchar2(256); ntmp number; nsid v$session.SID%type; wvtop1_stat  tcncrnt_loader_results.vtop1_stat%TYPE; wvtop2_stat  tcncrnt_loader_results.vtop2_stat%TYPE; wvtop3_stat  tcncrnt_loader_results.vtop3_stat%TYPE; wvtop1_latch tcncrnt_loader_results.vtop1_latch%TYPE; wvtop2_latch tcncrnt_loader_results.vtop2_latch%TYPE; wvtop3_latch tcncrnt_loader_results.vtop3_latch%TYPE; nserial v$session.SERIAL#%type; l_msg varchar2(30); l_status number; BEGIN dbms_alert.register(''run_scnrio''); dbms_alert.waitone(''run_scnrio'',l_msg,l_status,60);';
         vjob_str := vjob_str ||
                     ' EXECUTE IMMEDIATE (''alter session set max_dump_file_size=unlimited''); EXECUTE IMMEDIATE (''ALTER session SET timed_statistics = true''); EXECUTE IMMEDIATE (''alter session set STATISTICS_LEVEL = ALL''); ';
         vjob_str := vjob_str ||
                     ' EXECUTE IMMEDIATE (''alter session set tracefile_identifier = ' ||
                     cur_param.vscnrio_name || '''); ';
         vjob_str := vjob_str ||
                     ' if l_status = 0 then delete from gtt_runstats; insert into gtt_runstats select ''before'', vw_runstats.* from vw_runstats; select hsecs into s1 from v$timer; ';
         IF cur_param.nenable_trace = 1 THEN
            vjob_str := vjob_str ||
                        ' select a.sid, a.SERIAL# into nsid, nserial from v$session a where a.SID = (select distinct sid from v$mystat) ; ';
            vjob_str := vjob_str ||
                        ' DBMS_SYSTEM.SET_EV(nsid, nserial, 10046, 8, ''''); ';
         END IF;
         vjob_str := vjob_str || cur_param.vscnrio_dfntn;
         IF cur_param.nenable_trace = 1 THEN
            vjob_str := vjob_str ||
                        ' commit; DBMS_SYSTEM.SET_EV(nsid, nserial, 10046, 0, '''');  ';
         END IF;
         vjob_str := vjob_str ||
                     ' select hsecs into s2 from v$timer; insert into gtt_runstats select ''after'', vw_runstats.* from vw_runstats; ';
         vjob_str := vjob_str ||
                     ' FOR cur_qstat IN (SELECT * FROM (SELECT NAME || '' - '' || val qstat, row_number() over(ORDER BY val DESC) rn FROM (SELECT a.NAME, (b.VALUE - a.VALUE) val FROM gtt_runstats a, gtt_runstats b WHERE a.NAME = b.NAME AND a.NAME LIKE ''STAT%'' AND a.runid = ''before'' AND b.runid = ''after'' )) WHERE rn < 4) LOOP ';
         vjob_str := vjob_str ||
                     ' IF cur_qstat.rn = 1 THEN wvtop1_stat := cur_qstat.qstat; ELSIF cur_qstat.rn = 2 THEN wvtop2_stat := cur_qstat.qstat; ELSIF cur_qstat.rn = 3 THEN wvtop3_stat := cur_qstat.qstat; END IF; END LOOP; ';
         vjob_str := vjob_str ||
                     ' FOR cur_qlatch IN (SELECT * FROM (SELECT NAME || '' - '' || val qlatch, row_number() over(ORDER BY val DESC) rn FROM (SELECT a.NAME, (b.VALUE - a.VALUE) val FROM gtt_runstats a, gtt_runstats b WHERE a.NAME = b.NAME AND a.NAME LIKE ''LATCH%'' AND a.runid = ''before'' AND b.runid = ''after'' )) WHERE rn < 4) LOOP ';
         vjob_str := vjob_str ||
                     ' IF cur_qlatch.rn = 1 THEN wvtop1_latch := cur_qlatch.qlatch; ELSIF cur_qlatch.rn = 2 THEN wvtop2_latch := cur_qlatch.qlatch; ELSIF cur_qlatch.rn = 3 THEN wvtop3_latch := cur_qlatch.qlatch; END IF; END LOOP; ';
         vjob_str := vjob_str || ' insert into tcncrnt_loader_results values(''' ||
                     cur_param.vmain_scnrio_name || '_' || cur_param.vscnrio_name ||
                     ''', current_timestamp, s2 - s1, wvtop1_latch, wvtop2_latch, wvtop3_latch, wvtop1_stat, wvtop2_stat, wvtop3_stat,' ||
                     cur_param.nenable_trace || '); commit; end if; END; ';
      
         dbms_scheduler.create_job(job_name   => cur_param.vmain_scnrio_name || '_' ||
                                                 cur_param.vscnrio_name,
                                   job_type   => 'PLSQL_BLOCK',
                                   job_action => vjob_str,
                                   enabled    => TRUE,
                                   auto_drop  => TRUE);
      
      END LOOP;
   
      dbms_lock.sleep(5);
      dbms_alert.signal('run_scnrio',
                        'Lets Go');
      COMMIT;
   
   END prc_run_scnrio;

   PROCEDURE prc_report_scnrio(arg_vtask_name IN tcncrnt_loader_results.vtask_name%TYPE) IS
      ncnt_trc PLS_INTEGER := 0;
      vprm     VARCHAR2(128);
   BEGIN
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
      dbms_output.put_line('========== ' || ' Task Name            ' || ' Average    ' ||
                           ' Std.Dev.   ' || ' Maximum    ' || ' Minimum    ' ||
                           ' Count      ');
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
      FOR cur_report IN (SELECT substr(t.vtask_name,
                                       1,
                                       length(arg_vtask_name)) nam,
                                round(AVG(t.nelapsed_time),
                                      4) AVG,
                                round(STDDEV(t.nelapsed_time),
                                      4) STDDEV,
                                round(MAX(t.nelapsed_time),
                                      4) MAX,
                                round(MIN(t.nelapsed_time),
                                      4) MIN,
                                COUNT(t.nelapsed_time) cnt
                           FROM tcncrnt_loader_results t
                          WHERE t.nenable_trace < 1
                            AND vtask_name LIKE arg_vtask_name || '%'
                          GROUP BY substr(t.vtask_name,
                                          1,
                                          length(arg_vtask_name))) LOOP
         dbms_output.put_line('Elapsed     ' || lpad(substr(cur_report.nam,
                                                            1,
                                                            20),
                                                     20) ||
                              lpad(cur_report.AVG,
                                   12) || lpad(cur_report.STDDEV,
                                               12) || lpad(cur_report.MAX,
                                                           12) ||
                              lpad(cur_report.MIN,
                                   12) || lpad(cur_report.cnt,
                                               12));
      END LOOP;
   
      FOR cur_report IN (SELECT substr(tt.nam,
                                       1,
                                       length(arg_vtask_name)) nam,
                                round(AVG(tt.cpu_used),
                                      4) AVG,
                                round(STDDEV(tt.cpu_used),
                                      4) STDDEV,
                                round(MAX(tt.cpu_used),
                                      4) MAX,
                                round(MIN(tt.cpu_used),
                                      4) MIN,
                                COUNT(tt.cpu_used) cnt
                           FROM (SELECT substr(t.job_name,
                                               1,
                                               length(arg_vtask_name)) nam,
                                        to_number((extract(hour FROM t.cpu_used) * 60 * 60) +
                                                  (extract(minute FROM t.cpu_used) * 60) +
                                                  extract(SECOND FROM t.cpu_used)) cpu_used
                                   FROM dba_scheduler_job_run_details t
                                  WHERE job_name IN
                                        (SELECT t.vtask_name
                                           FROM tcncrnt_loader_results t
                                          WHERE t.nenable_trace < 1
                                            AND vtask_name LIKE arg_vtask_name || '%')
                                  ORDER BY log_date DESC) tt
                          WHERE nam LIKE arg_vtask_name || '%'
                          GROUP BY substr(tt.nam,
                                          1,
                                          length(arg_vtask_name))) LOOP
         dbms_output.put_line('CPU Used    ' || lpad(substr(cur_report.nam,
                                                            1,
                                                            20),
                                                     20) ||
                              lpad(cur_report.AVG,
                                   12) || lpad(cur_report.STDDEV,
                                               12) || lpad(cur_report.MAX,
                                                           12) ||
                              lpad(cur_report.MIN,
                                   12) || lpad(cur_report.cnt,
                                               12));
      END LOOP;
   
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
   
      FOR cur_report IN (SELECT t.vtask_name  nam,
                                t.vtop1_latch top1,
                                t.vtop2_latch top2,
                                t.vtop3_latch top3
                           FROM tcncrnt_loader_results t
                          WHERE t.nenable_trace < 1
                            AND vtask_name LIKE arg_vtask_name || '%'
                          ORDER BY t.vtask_name) LOOP
         dbms_output.put_line('LATCH       ' || lpad(cur_report.nam,
                                                     20) || '   1.' || cur_report.top1 ||
                              ' 2.' || cur_report.top2 || ' 3.' || cur_report.top3 || ' ');
      END LOOP;
   
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
   
      FOR cur_report IN (SELECT t.vtask_name nam,
                                t.vtop1_stat top1,
                                t.vtop2_stat top2,
                                t.vtop3_stat top3
                           FROM tcncrnt_loader_results t
                          WHERE t.nenable_trace < 1
                            AND vtask_name LIKE arg_vtask_name || '%'
                          ORDER BY t.vtask_name) LOOP
         dbms_output.put_line('STATS       ' || lpad(cur_report.nam,
                                                     20) || '   1.' || cur_report.top1 ||
                              ' 2.' || cur_report.top2 || ' 3.' || cur_report.top3 || ' ');
      END LOOP;
   
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
   
      SELECT COUNT(*)
        INTO ncnt_trc
        FROM tcncrnt_loader_results t
       WHERE t.nenable_trace >= 1
         AND vtask_name LIKE arg_vtask_name || '%';
   
      IF ncnt_trc > 0 THEN
         SELECT a.VALUE
           INTO vprm
           FROM v$system_parameter a
          WHERE a.NAME = 'background_dump_dest';
      
         dbms_output.put_line('Warning     ' || ncnt_trc ||
                              ' scenerios were configured to produce sql trace files for detailed profiling analysis');
         dbms_output.put_line('            ' ||
                              ' these threads are not included in the above results since they have their own tracing costs and they can be found under ' || vprm || ' folder since job processes are traced.');
      END IF;
   
      dbms_output.put_line('========== ' || ' ==================== ' || ' ========== ' ||
                           ' ========== ' || ' ========== ' || ' ========== ' ||
                           ' ========== ');
   END prc_report_scnrio;

END pkg_cncrnt_loader;
/
/*
-- for monitoring
select * from dba_scheduler_job_run_details  where log_date > trunc(sysdate) order by log_date desc;

select * from dba_scheduler_running_jobs ;

select * from tcncrnt_loader_results t order by t.dtimestamp ;

-- for fresh scenerio building and reporting
begin 
  delete from tcncrnt_loader_results; 
  delete from tcncrnt_loader_param; 
  commit; 
end;
/
*/