REM lwrpkg.sql
REM (c)2008-9 David Kurtz, Go-Faster Consultancy Ltd. www.go-faster.co.uk
REM packaged procedure to generate local write wait scenario
--------------------------------------------------------------------------------
set echo on

--------------------------------------------------------------------------------
--privileges required by package
--------------------------------------------------------------------------------
GRANT RESOURCE TO scott;
GRANT CREATE TABLE TO scott;
GRANT EXECUTE ON dbms_monitor TO scott;
GRANT EXECUTE ON dbms_lock TO scott;
GRANT ALTER SESSION TO scott;

--------------------------------------------------------------------------------
--package to administer test
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE lwr AS
PROCEDURE create_default_table --create a single test table
	(p_rows   INTEGER DEFAULT 100
	,p_rowlen INTEGER DEFAULT 100);
PROCEDURE create_tables --create sets of test tables
	(p_instances  INTEGER                    --number of pairs of tables
	,p_rows       INTEGER  DEFAULT 0         --number of rows to seed in the first table
	,p_rowlen     INTEGER  DEFAULT 100       --length of padding string
	,p_pk         BOOLEAN  DEFAULT FALSE     --if true create primary key on table
	,p_pool       VARCHAR2 DEFAULT 'DEFAULT' --name of buffer pool in which to put table
	,p_tablespace VARCHAR2 DEFAULT '');      --name of tablespace in which to put table
PROCEDURE drop_default_table; --drop a single test table
PROCEDURE drop_tables --drop sets of tables
	(p_instances  INTEGER                    --number of pairs of tables to drop
	,p_pool       VARCHAR2 DEFAULT 'DEFAULT' --name of buffer pool, which went into table name
	,p_tablespace VARCHAR2 DEFAULT '');      --name of tablespace, which went into table name
PROCEDURE create_gtts
	(p_instances  INTEGER
	,p_rowlen     INTEGER  DEFAULT 100
	,p_pk         BOOLEAN  DEFAULT FALSE
	,p_gtt_type   VARCHAR2); 
PROCEDURE test --execute test copy and truncate on a specified pair of tables
	(p_instance      INTEGER                    --run test on a specified pair of tables
	,p_iterations    INTEGER                    --number of times to copy data back and forth
        ,p_rows          INTEGER  DEFAULT 0         --if > 0 insert this number of rows
	,p_rowlen        INTEGER  DEFAULT 100       --length of padding string
	,p_pool          VARCHAR2 DEFAULT 'DEFAULT' --name of buffer pool, which went into table name
	,p_tablespace    VARCHAR2 DEFAULT ''        --name of tablespace, which went into table name
	,p_trace         BOOLEAN  DEFAULT FALSE     --if true enable session SQL trace 
        ,p_delete_type   VARCHAR2 DEFAULT 'T');     --(T)runcate, Truncate (R)euse Storage, (D)elete
PROCEDURE job_wait --wait until other test jobs have completed
	(p_seconds       INTEGER  DEFAULT 1);       --wait 10 seconds between checks on jobs)
PROCEDURE submit --submit multiple tests to run concurrently and immediately via dbms_job
	(p_instances     INTEGER  DEFAULT 10        --number of instances of process to run
	,p_iterations    INTEGER  DEFAULT 10        --number of times to copy data back and forth
	,p_rows          INTEGER  DEFAULT 0         --number of rows to seed in the first table
	,p_rowlen        INTEGER  DEFAULT 100       --length of padding string
	,p_pool          VARCHAR2 DEFAULT 'DEFAULT' --name of buffer pool, which went into table name
	,p_tablespace    VARCHAR2 DEFAULT ''        --name of tablespace, which went into table name
	,p_trace         INTEGER  DEFAULT 10        --number of instance on which to enable trace
        ,p_delete_type   VARCHAR2 DEFAULT 'T'       --(T)runcate, Truncate (R)euse Storage, (D)elete
        ,p_wait          BOOLEAN  DEFAULT FALSE);   --if true pause after submission until jobs complete
END lwr;
/

show errors

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY lwr AS
 g_lf VARCHAR2(1 CHAR) := CHR(10);
 g_prefix VARCHAR2(3 CHAR) := 'lwr';
 g_rowsource VARCHAR2(20 CHAR) := 'user_tab_columns';

 table_already_exists EXCEPTION;
 PRAGMA EXCEPTION_INIT(table_already_exists,-955);
 table_doesnt_exist EXCEPTION;
 PRAGMA EXCEPTION_INIT(table_doesnt_exist,-942);
--------------------------------------------------------------------------------
PROCEDURE exec_sql(p_sql VARCHAR2) 
IS
 l_rows INTEGER;
BEGIN
 dbms_output.put_line(p_sql);
 EXECUTE IMMEDIATE p_sql;
 l_rows := SQL%ROWCOUNT;
 IF l_rows > 0 THEN
  dbms_output.put_line(TO_CHAR(l_rows)||' rows processed'||g_lf);
 END IF;
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  RAISE;
END exec_sql;
--------------------------------------------------------------------------------
PROCEDURE create_default_table
	(p_rows   INTEGER DEFAULT 100
	,p_rowlen INTEGER DEFAULT 100
	)
IS
 l_str VARCHAR2(200 CHAR);
 l_tab VARCHAR2(10 CHAR) := g_prefix||LTRIM(TO_CHAR(0));
 l_arraysize CONSTANT PLS_INTEGER := 2500;
 l_rowsets INTEGER;
 l_counter INTEGER := 1;
 TYPE t_num IS TABLE OF INTEGER             INDEX BY PLS_INTEGER;
 TYPE t_str IS TABLE OF VARCHAR2(4000 CHAR) INDEX BY PLS_INTEGER;
 a_num t_num;
 a_str t_str;
BEGIN
 l_str := 'CREATE TABLE '||l_tab||'(a NUMBER, b VARCHAR2('||p_rowlen||' CHAR))';
 BEGIN
  exec_sql(l_str);
 EXCEPTION 
  WHEN table_already_exists THEN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_tab;
 END;

 l_rowsets := CEIL(p_rows/l_arraysize);
 l_str := 'INSERT INTO '||l_tab||' VALUES(:1, :2)';
 dbms_output.put_line(l_str);

 WHILE l_counter <= p_rows LOOP
  FOR j IN 1..l_arraysize LOOP
   a_num(j) := l_counter;
   a_str(j) := RPAD(TO_CHAR(TO_DATE(l_counter,'J'),'Jsp'),p_rowlen,'.');
   l_counter := l_counter + 1;
  END LOOP;
  FORALL j IN 1..l_arraysize EXECUTE IMMEDIATE l_str USING a_num(j), a_str(j);
  dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows processed');
 END LOOP;
 COMMIT;

END create_default_table;
--------------------------------------------------------------------------------
PROCEDURE populate_table
	(p_instance   INTEGER
	,p_rows       INTEGER  
	,p_rowlen     INTEGER  DEFAULT 100
	,p_pool       VARCHAR2 DEFAULT 'DEFAULT'
	,p_tablespace VARCHAR2 DEFAULT ''
	) 
IS
 l_str VARCHAR2(200 CHAR);
 l_tab VARCHAR2(30 CHAR);
BEGIN
 FOR j IN REVERSE 1..2 LOOP
  l_tab := LOWER(g_prefix
	||LTRIM(TO_CHAR(p_instance))
	||LTRIM(TO_CHAR(j))
	||'_'||p_pool);
  IF p_tablespace IS NOT NULL THEN
   l_tab := SUBSTR(l_tab || '_'||p_tablespace,1,30);
  END IF;
  l_str := 'TRUNCATE TABLE '||l_tab;
  exec_sql(l_str);
 END LOOP;
 l_str := 'INSERT INTO '||l_tab||' VALUES(:1, :2)';
 dbms_output.put_line(l_str);
 FOR j IN 1..p_rows LOOP
  EXECUTE IMMEDIATE l_str USING j, RPAD(TO_CHAR(TO_DATE(j,'J'),'Jsp'),p_rowlen,'.');
 END LOOP;
 dbms_output.put_line(TO_CHAR(p_rows)||' rows processed'||g_lf);
 COMMIT;
END populate_table;
--------------------------------------------------------------------------------
PROCEDURE create_tables
	(p_instances  INTEGER
	,p_rows       INTEGER  DEFAULT 0
	,p_rowlen     INTEGER  DEFAULT 100
	,p_pk         BOOLEAN  DEFAULT FALSE
	,p_pool       VARCHAR2 DEFAULT 'DEFAULT'
	,p_tablespace VARCHAR2 DEFAULT ''
	) 
IS
 l_str VARCHAR2(200 CHAR);
 l_tab VARCHAR2(30 CHAR);
BEGIN
 FOR i IN 1..p_instances LOOP
  FOR j IN REVERSE 1..2 LOOP
   BEGIN
    l_tab := LOWER(g_prefix
 	||LTRIM(TO_CHAR(i))
	||LTRIM(TO_CHAR(j))
	||'_'||p_pool);
    IF p_tablespace IS NOT NULL THEN
     l_tab := SUBSTR(l_tab || '_'||p_tablespace,1,30);
    END IF;
    l_str := 'CREATE TABLE '||l_tab||'(a NUMBER NOT NULL';
    IF p_pk THEN
     l_str := l_str||' CONSTRAINT '||l_tab||' PRIMARY KEY';
    END IF ;
    l_str := l_str||', b VARCHAR2('||p_rowlen||' CHAR)) STORAGE (BUFFER_POOL '||p_pool||')';
    IF p_tablespace IS NOT NULL THEN
     l_str := l_str||' TABLESPACE '||p_tablespace;
    END IF;
    exec_sql(l_str);
   EXCEPTION 
    WHEN table_already_exists THEN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_tab;
   END;
  END LOOP;
--  l_str := 'INSERT INTO '||l_tab||' VALUES(:1, :2)';
--  dbms_output.put_line(l_str);
--  FOR j IN 1..p_rows LOOP
--   EXECUTE IMMEDIATE l_str USING j, RPAD(TO_CHAR(TO_DATE(j,'J'),'Jsp'),p_rowlen,'.');
--  END LOOP;
  IF p_rows > 0 THEN
   populate_table(i, p_rows, p_rowlen, p_pool, p_tablespace);
  END IF;
 END LOOP;
END create_tables;
--------------------------------------------------------------------------------
PROCEDURE drop_default_table IS
 l_str VARCHAR2(100 CHAR);
 l_tab VARCHAR2(10 CHAR) := g_prefix||LTRIM(TO_CHAR(0));
BEGIN
 l_str := 'DROP TABLE '||l_tab||' PURGE';
 exec_sql(l_str);
EXCEPTION 
 WHEN table_doesnt_exist THEN NULL;
END drop_default_table;
--------------------------------------------------------------------------------
PROCEDURE drop_tables
	(p_instances INTEGER
	,p_pool VARCHAR2 DEFAULT 'DEFAULT'
	,p_tablespace VARCHAR2 DEFAULT ''
	) 
IS
 l_str VARCHAR2(100 CHAR);
 l_tab VARCHAR2(30 CHAR);
BEGIN
 FOR i IN 1..p_instances LOOP
  FOR j IN 1..2 LOOP
   BEGIN
    l_tab := LOWER(g_prefix
 	||LTRIM(TO_CHAR(i))
	||LTRIM(TO_CHAR(j))
	||'_'||p_pool);
    IF p_tablespace IS NOT NULL THEN
     l_tab := SUBSTR(l_tab ||'_'||p_tablespace,1,30);
    END IF;
    l_str := 'DROP TABLE '
 	||l_tab
	||' PURGE';
    exec_sql(l_str);
   EXCEPTION 
    WHEN table_doesnt_exist THEN NULL;
   END;
  END LOOP;
 END LOOP;
END drop_tables;
--------------------------------------------------------------------------------
PROCEDURE create_gtts
	(p_instances  INTEGER
	,p_rowlen     INTEGER  DEFAULT 100
	,p_pk         BOOLEAN  DEFAULT FALSE
	,p_gtt_type   VARCHAR2) 
IS
 l_str      VARCHAR2(200 CHAR);
 l_tab      VARCHAR2(30 CHAR);
 l_gtt_type VARCHAR2(50 CHAR);
BEGIN
 IF p_gtt_type = 'P' THEN
  l_gtt_type := 'PRESERVE';
 ELSE
  l_gtt_type := 'DELETE';
 END IF;

 FOR i IN 1..p_instances LOOP
  FOR j IN REVERSE 1..2 LOOP
   BEGIN
    l_tab := LOWER(g_prefix||LTRIM(TO_CHAR(i))||LTRIM(TO_CHAR(j))||'_'||l_gtt_type);
    l_str := 'CREATE GLOBAL TEMPORARY TABLE '||l_tab||' (a NUMBER NOT NULL';
    IF p_pk THEN
     l_str := l_str||' CONSTRAINT '||l_tab||' PRIMARY KEY';
    END IF ;
    l_str := l_str||', b VARCHAR2('||p_rowlen||' CHAR)) ON COMMIT '||l_gtt_type||' ROWS';
    exec_sql(l_str);
   EXCEPTION 
    WHEN table_already_exists THEN NULL;
   END;
  END LOOP;
 END LOOP;
END create_gtts;
--------------------------------------------------------------------------------
PROCEDURE test
	(p_instance      INTEGER
	,p_iterations    INTEGER
	,p_rows          INTEGER  DEFAULT 0
	,p_rowlen        INTEGER  DEFAULT 100
	,p_pool          VARCHAR2 DEFAULT 'DEFAULT'
	,p_tablespace    VARCHAR2 DEFAULT ''
	,p_trace         BOOLEAN  DEFAULT FALSE
        ,p_delete_type   VARCHAR2 DEFAULT 'T'
	) 
IS
 l_str          VARCHAR2(200 CHAR);
 l_cmd1         VARCHAR2(100 CHAR);
 l_cmd2         VARCHAR2(100 CHAR);
 l_tab1         VARCHAR2(30 CHAR);
 l_tab2         VARCHAR2(30 CHAR);
 l_trunc_suffix VARCHAR2(20 CHAR);
BEGIN
 IF p_rows > 0 THEN
  populate_table(p_instance, p_rows, p_rowlen, p_pool, p_tablespace);
 END IF;

 --determine names of tables
 l_tab1 := LOWER(g_prefix||LTRIM(TO_CHAR(p_instance))||'1'||'_'||p_pool);
 l_tab2 := LOWER(g_prefix||LTRIM(TO_CHAR(p_instance))||'2'||'_'||p_pool);
 IF p_tablespace IS NOT NULL THEN
  l_tab1 := SUBSTR(l_tab1 ||'_'||p_tablespace,1,30);
  l_tab2 := SUBSTR(l_tab2 ||'_'||p_tablespace,1,30);
 END IF;

 --generate delete/truncate commands
 IF p_delete_type = 'D' THEN
  l_cmd1 := 'DELETE FROM ';
  l_cmd2 := 'DELETE FROM ';
 ELSE --delete_type IN ('R','T')
  l_cmd1 := 'TRUNCATE TABLE ';
  l_cmd2 := 'TRUNCATE TABLE ';
 END IF;
 l_cmd1 := l_cmd1||l_tab1;
 l_cmd2 := l_cmd2||l_tab2;
 IF p_delete_type = 'R' THEN
  l_cmd1 := l_cmd1||' REUSE STORAGE';
  l_cmd2 := l_cmd2||' REUSE STORAGE';
 END IF;

 IF p_trace THEN
  EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=2097152'; --1Gb

--event to trace lock modes
--EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10704 trace name context forever, level 10''';

  sys.dbms_monitor.session_trace_enable(waits=>TRUE);
  EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER ='''
	||g_prefix||LTRIM(TO_CHAR(p_instance))||'_'
	          ||LTRIM(TO_CHAR(p_iterations))||'_'
                  ||p_pool||'''';
 END IF;

 FOR i IN 1..p_iterations LOOP
  --copy 1 to 2
  l_str := 'INSERT INTO '||l_tab2||' SELECT * FROM '||l_tab1;
  exec_sql(l_str);
  --truncate 1
  exec_sql(l_cmd1);
  IF p_delete_type = 'D' THEN
   COMMIT;
  END IF;

  --copy 2 to 1
  l_str := 'INSERT INTO '||l_tab1||' SELECT * FROM '||l_tab2;
  exec_sql(l_str);
  --truncate 2
  exec_sql(l_cmd2);
  IF p_delete_type = 'D' THEN
   COMMIT;
  END IF;
 END LOOP;

 IF p_trace THEN
  sys.dbms_monitor.session_trace_disable;

--this doesn't work on background processes
  EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER =''''';

 END IF;

END test;
--------------------------------------------------------------------------------
PROCEDURE job_wait --wait until other test jobs have completed
	(p_seconds INTEGER DEFAULT 1)
IS
 l_jobs INTEGER;
 l_started DATE := SYSDATE;
 l_finished DATE;
BEGIN
 dbms_output.put_line('Start : '||TO_CHAR(l_started,'HH24:MI:SS DD.MM.YYYY'));
 LOOP
  l_jobs := 0; --count number of jobs

  SELECT COUNT(*)
  INTO   l_jobs
  FROM	 user_jobs
  WHERE  next_date <= SYSDATE
  AND    LOWER(what) like 'lwr.test(%'
  AND    ROWNUM <= 1;

  EXIT WHEN l_jobs = 0;

  dbms_lock.sleep(p_seconds);
 END LOOP;

 l_finished := SYSDATE;
 dbms_output.put_line('End   : '||TO_CHAR(l_finished,'HH24:MI:SS DD.MM.YYYY'));
 dbms_output.put_line('Waited: '||TO_CHAR(ROUND((l_finished-l_started)*86400))||' secs');

END job_wait;
--------------------------------------------------------------------------------
PROCEDURE submit
	(p_instances     INTEGER  DEFAULT 10
	,p_iterations    INTEGER  DEFAULT 10
	,p_rows          INTEGER  DEFAULT 0
	,p_rowlen        INTEGER  DEFAULT 100
	,p_pool          VARCHAR2 DEFAULT 'DEFAULT'
	,p_tablespace    VARCHAR2 DEFAULT ''
	,p_trace         INTEGER  DEFAULT 10
        ,p_delete_type   VARCHAR2 DEFAULT 'T'
        ,p_wait          BOOLEAN DEFAULT FALSE
        ) 
IS
 l_job INTEGER;
 l_what VARCHAR2(200 CHAR);
BEGIN
 FOR i in 1..p_instances LOOP
  l_what := 'lwr.test(p_instance=>'||i
	||',p_iterations=>'||p_iterations
	||CASE WHEN p_rows>0 THEN ',p_rows=>'||TO_CHAR(p_rows)||',p_rowlen=>'||TO_CHAR(p_rowlen) END
	||',p_pool=>'''||p_pool||''''
	||CASE WHEN p_tablespace IS NOT NULL THEN ',p_tablespace=>'''||p_tablespace||'''' END
	||CASE p_trace WHEN i THEN ',p_trace=>TRUE' END
	||',p_delete_type=>'''||p_delete_type||''''
	||');';
--dbms_output.put_line(l_what);
  dbms_job.submit(job=>l_job,what=>l_what);
  dbms_output.put_line('Job '||l_job||':'||l_what);
 END LOOP;

 COMMIT; --to make the job submit

 IF p_wait THEN 
  job_wait;
 END IF;
END submit;
--------------------------------------------------------------------------------
END lwr;
/
--------------------------------------------------------------------------------
GRANT EXECUTE ON lwr TO PUBLIC;
--------------------------------------------------------------------------------
show errors