CREATE OR REPLACE PACKAGE BODY rap
AS
/* CREATED BY RODRIGO RIGHETTI
CONTACT: rodrigo.righetti@gmail.com
blog: dbabrain.blogspot.com
*/

/* procedure capture_workload
details on package spec
*/
PROCEDURE capture_workload (
truntime NUMBER DEFAULT 30,
tfreq NUMBER DEFAULT 15,
tchange BOOLEAN DEFAULT FALSE,
tschema VARCHAR2 default 'ALL',
tflush BOOLEAN DEFAULT true
)
IS
endtime DATE; -- variable used to determine the end of the capture
BEGIN
-- preparing the tables to capture the workload
EXECUTE IMMEDIATE 'truncate table rap.sql';

EXECUTE IMMEDIATE 'truncate table rap.sqlplan';

EXECUTE IMMEDIATE 'truncate table rap.bind';

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

-- checking if tchange is true if so change the parameter "_cursor_bind_capture_interval" -- default value 900(15 minutes)
IF tchange = TRUE
THEN
EXECUTE IMMEDIATE 'alter system set "_cursor_bind_capture_interval"='
|| tfreq;
END IF;

IF tflush = TRUE
THEN
EXECUTE IMMEDIATE 'alter system flush shared_pool';
EXECUTE IMMEDIATE 'alter system flush buffer_cache';
END IF;
--------------------------------------------------------------------------------------------------------------------------
-- Start the capture process it will run based on the truntime
endtime := SYSDATE + truntime / 1440;

WHILE SYSDATE < endtime
LOOP
IF tschema ='ALL'
THEN
MERGE INTO sql a
USING SYS.v_$sqlarea b
ON (a.sql_id = b.sql_id)
WHEN MATCHED THEN
UPDATE
SET A.SHARABLE_MEM = B.SHARABLE_MEM,
A.PERSISTENT_MEM = B.PERSISTENT_MEM,
A.RUNTIME_MEM = B.RUNTIME_MEM,
A.SORTS = B.SORTS,
A.VERSION_COUNT = B.VERSION_COUNT,
A.FETCHES = B.FETCHES,
A.EXECUTIONS = B.EXECUTIONS,
A.PX_SERVERS_EXECUTIONS = B.PX_SERVERS_EXECUTIONS,
A.END_OF_FETCH_COUNT = B.END_OF_FETCH_COUNT,
A.USERS_EXECUTING = B.USERS_EXECUTING,
A.LOADS = B.LOADS,
A.INVALIDATIONS = B.INVALIDATIONS,
A.PARSE_CALLS = B.PARSE_CALLS,
A.DISK_READS = B.DISK_READS,
A.DIRECT_WRITES = B.DIRECT_WRITES,
A.BUFFER_GETS = B.BUFFER_GETS,
A.APPLICATION_WAIT_TIME = B.APPLICATION_WAIT_TIME,
A.CONCURRENCY_WAIT_TIME = B.CONCURRENCY_WAIT_TIME,
A.CLUSTER_WAIT_TIME = B.CLUSTER_WAIT_TIME,
A.USER_IO_WAIT_TIME = B.USER_IO_WAIT_TIME,
A.PLSQL_EXEC_TIME = B.PLSQL_EXEC_TIME,
A.JAVA_EXEC_TIME = B.JAVA_EXEC_TIME,
A.ROWS_PROCESSED = B.ROWS_PROCESSED,
A.COMMAND_TYPE = B.COMMAND_TYPE,
A.CPU_TIME = B.CPU_TIME,
A.ELAPSED_TIME = B.ELAPSED_TIME,
A.LAST_LOAD_TIME = B.LAST_LOAD_TIME
WHEN NOT MATCHED THEN
INSERT (
SQL_TEXT, SQL_FULLTEXT, SQL_ID,
SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM,
SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS,
USERS_OPENING, FETCHES, EXECUTIONS,
PX_SERVERS_EXECUTIONS, END_OF_FETCH_COUNT, USERS_EXECUTING,
LOADS, FIRST_LOAD_TIME, INVALIDATIONS,
PARSE_CALLS, DISK_READS, DIRECT_WRITES,
BUFFER_GETS, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,
CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME,
JAVA_EXEC_TIME, ROWS_PROCESSED, COMMAND_TYPE,
OPTIMIZER_MODE, OPTIMIZER_COST, OPTIMIZER_ENV,
OPTIMIZER_ENV_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID,
PARSING_SCHEMA_NAME, KEPT_VERSIONS, ADDRESS,
HASH_VALUE, OLD_HASH_VALUE,
PLAN_HASH_VALUE, MODULE, MODULE_HASH,
ACTION, ACTION_HASH, SERIALIZABLE_ABORTS,
OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME,
OUTLINE_SID, LAST_ACTIVE_CHILD_ADDRESS,
REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE,
LAST_LOAD_TIME, IS_OBSOLETE, CHILD_LATCH,
SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#,
EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME,
BIND_DATA)
VALUES ( B.SQL_TEXT,B.SQL_FULLTEXT,B.SQL_ID,B.SHARABLE_MEM,B.PERSISTENT_MEM,B.RUNTIME_MEM,
B.SORTS,B.VERSION_COUNT,B.LOADED_VERSIONS,B.OPEN_VERSIONS,B.USERS_OPENING,B.FETCHES,B.EXECUTIONS,
B.PX_SERVERS_EXECUTIONS,B.END_OF_FETCH_COUNT,B.USERS_EXECUTING,B.LOADS,B.FIRST_LOAD_TIME,
B.INVALIDATIONS,B.PARSE_CALLS,B.DISK_READS,B.DIRECT_WRITES,B.BUFFER_GETS,B.APPLICATION_WAIT_TIME,
B.CONCURRENCY_WAIT_TIME,B.CLUSTER_WAIT_TIME,B.USER_IO_WAIT_TIME,B.PLSQL_EXEC_TIME,
B.JAVA_EXEC_TIME,B.ROWS_PROCESSED,B.COMMAND_TYPE,B.OPTIMIZER_MODE,B.OPTIMIZER_COST,
B.OPTIMIZER_ENV,B.OPTIMIZER_ENV_HASH_VALUE,B.PARSING_USER_ID,B.PARSING_SCHEMA_ID,
B.PARSING_SCHEMA_NAME,B.KEPT_VERSIONS,B.ADDRESS,B.HASH_VALUE,B.OLD_HASH_VALUE,
B.PLAN_HASH_VALUE,B.MODULE,B.MODULE_HASH,B.ACTION,
B.ACTION_HASH,B.SERIALIZABLE_ABORTS,B.OUTLINE_CATEGORY,B.CPU_TIME,B.ELAPSED_TIME,B.OUTLINE_SID,
B.LAST_ACTIVE_CHILD_ADDRESS,B.REMOTE,B.OBJECT_STATUS,B.LITERAL_HASH_VALUE,B.LAST_LOAD_TIME,
B.IS_OBSOLETE,B.CHILD_LATCH,B.SQL_PROFILE,B.PROGRAM_ID,B.PROGRAM_LINE#,B.EXACT_MATCHING_SIGNATURE,
B.FORCE_MATCHING_SIGNATURE,B.LAST_ACTIVE_TIME,B.BIND_DATA )
WHERE b.PARSING_USER_ID NOT IN (select user_id
from dba_users
where username in ('SYS', 'DBSNMP', 'SYSMAN', 'CTXSYS','RAP',
'ROD','NAGIOS','MGMT_VIEW','EXFSYS','XDB','MGMT_VIEW','DMSYS'));
-- load the v$sql table on the base table SQL

INSERT INTO sqlplan
SELECT A.*
FROM SYS.v_$sql_plan a, sys.v_$sqlarea B
WHERE a.sql_id = B.SQL_ID
AND a.child_address = b.LAST_ACTIVE_CHILD_ADDRESS
AND b.PARSING_USER_ID NOT IN (select user_id
from dba_users
where username in ('SYS', 'DBSNMP', 'SYSMAN', 'CTXSYS','RAP',
'ROD','NAGIOS','MGMT_VIEW','EXFSYS','XDB','MGMT_VIEW','DMSYS'))
and not exists (select 'x' from sqlplan p
where a.sql_id = p.sql_id);

-- load the v$sql_plan table on the base table SQLPLAN
INSERT INTO bind
SELECT a.*
FROM SYS.v_$sql_bind_capture a, sys.v_$sqlarea B
WHERE a.sql_id = B.SQL_ID
AND a.child_address = b.LAST_ACTIVE_CHILD_ADDRESS
AND A.WAS_CAPTURED='YES'
AND b.PARSING_USER_ID NOT IN (select user_id
from dba_users
where username in ('SYS', 'DBSNMP', 'SYSMAN', 'CTXSYS','RAP',
'ROD','NAGIOS','MGMT_VIEW','EXFSYS','XDB','MGMT_VIEW','DMSYS'))
and not exists (select 'x' from bind c
where a.sql_id = c.sql_id);

--load the v$sql_bind_capture on the base table BIND


COMMIT;

ELSE
MERGE INTO sql a
USING SYS.v_$sqlarea b
ON (a.sql_id = b.sql_id)
WHEN MATCHED THEN
UPDATE
SET A.SHARABLE_MEM = B.SHARABLE_MEM,
A.PERSISTENT_MEM = B.PERSISTENT_MEM,
A.RUNTIME_MEM = B.RUNTIME_MEM,
A.SORTS = B.SORTS,
A.VERSION_COUNT = B.VERSION_COUNT,
A.FETCHES = B.FETCHES,
A.EXECUTIONS = B.EXECUTIONS,
A.PX_SERVERS_EXECUTIONS = B.PX_SERVERS_EXECUTIONS,
A.END_OF_FETCH_COUNT = B.END_OF_FETCH_COUNT,
A.USERS_EXECUTING = B.USERS_EXECUTING,
A.LOADS = B.LOADS,
A.INVALIDATIONS = B.INVALIDATIONS,
A.PARSE_CALLS = B.PARSE_CALLS,
A.DISK_READS = B.DISK_READS,
A.DIRECT_WRITES = B.DIRECT_WRITES,
A.BUFFER_GETS = B.BUFFER_GETS,
A.APPLICATION_WAIT_TIME = B.APPLICATION_WAIT_TIME,
A.CONCURRENCY_WAIT_TIME = B.CONCURRENCY_WAIT_TIME,
A.CLUSTER_WAIT_TIME = B.CLUSTER_WAIT_TIME,
A.USER_IO_WAIT_TIME = B.USER_IO_WAIT_TIME,
A.PLSQL_EXEC_TIME = B.PLSQL_EXEC_TIME,
A.JAVA_EXEC_TIME = B.JAVA_EXEC_TIME,
A.ROWS_PROCESSED = B.ROWS_PROCESSED,
A.COMMAND_TYPE = B.COMMAND_TYPE,
A.CPU_TIME = B.CPU_TIME,
A.ELAPSED_TIME = B.ELAPSED_TIME,
A.LAST_LOAD_TIME = B.LAST_LOAD_TIME
WHEN NOT MATCHED THEN
INSERT (
SQL_TEXT, SQL_FULLTEXT, SQL_ID,
SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM,
SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS,
USERS_OPENING, FETCHES, EXECUTIONS,
PX_SERVERS_EXECUTIONS, END_OF_FETCH_COUNT, USERS_EXECUTING,
LOADS, FIRST_LOAD_TIME, INVALIDATIONS,
PARSE_CALLS, DISK_READS, DIRECT_WRITES,
BUFFER_GETS, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,
CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME,
JAVA_EXEC_TIME, ROWS_PROCESSED, COMMAND_TYPE,
OPTIMIZER_MODE, OPTIMIZER_COST, OPTIMIZER_ENV,
OPTIMIZER_ENV_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID,
PARSING_SCHEMA_NAME, KEPT_VERSIONS, ADDRESS,
HASH_VALUE, OLD_HASH_VALUE,
PLAN_HASH_VALUE, MODULE, MODULE_HASH,
ACTION, ACTION_HASH, SERIALIZABLE_ABORTS,
OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME,
OUTLINE_SID, LAST_ACTIVE_CHILD_ADDRESS,
REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE,
LAST_LOAD_TIME, IS_OBSOLETE, CHILD_LATCH,
SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#,
EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME,
BIND_DATA)
VALUES ( B.SQL_TEXT,B.SQL_FULLTEXT,B.SQL_ID,B.SHARABLE_MEM,B.PERSISTENT_MEM,B.RUNTIME_MEM,
B.SORTS,B.VERSION_COUNT,B.LOADED_VERSIONS,B.OPEN_VERSIONS,B.USERS_OPENING,B.FETCHES,B.EXECUTIONS,
B.PX_SERVERS_EXECUTIONS,B.END_OF_FETCH_COUNT,B.USERS_EXECUTING,B.LOADS,B.FIRST_LOAD_TIME,
B.INVALIDATIONS,B.PARSE_CALLS,B.DISK_READS,B.DIRECT_WRITES,B.BUFFER_GETS,B.APPLICATION_WAIT_TIME,
B.CONCURRENCY_WAIT_TIME,B.CLUSTER_WAIT_TIME,B.USER_IO_WAIT_TIME,B.PLSQL_EXEC_TIME,
B.JAVA_EXEC_TIME,B.ROWS_PROCESSED,B.COMMAND_TYPE,B.OPTIMIZER_MODE,B.OPTIMIZER_COST,
B.OPTIMIZER_ENV,B.OPTIMIZER_ENV_HASH_VALUE,B.PARSING_USER_ID,B.PARSING_SCHEMA_ID,
B.PARSING_SCHEMA_NAME,B.KEPT_VERSIONS,B.ADDRESS,B.HASH_VALUE,B.OLD_HASH_VALUE,
B.PLAN_HASH_VALUE,B.MODULE,B.MODULE_HASH,B.ACTION,
B.ACTION_HASH,B.SERIALIZABLE_ABORTS,B.OUTLINE_CATEGORY,B.CPU_TIME,B.ELAPSED_TIME,B.OUTLINE_SID,
B.LAST_ACTIVE_CHILD_ADDRESS,B.REMOTE,B.OBJECT_STATUS,B.LITERAL_HASH_VALUE,B.LAST_LOAD_TIME,
B.IS_OBSOLETE,B.CHILD_LATCH,B.SQL_PROFILE,B.PROGRAM_ID,B.PROGRAM_LINE#,B.EXACT_MATCHING_SIGNATURE,
B.FORCE_MATCHING_SIGNATURE,B.LAST_ACTIVE_TIME,B.BIND_DATA )
WHERE b.PARSING_USER_ID = (select user_id from dba_users where username = UPPER (tschema));
-- load the v$sql table on the base table SQL


INSERT INTO sqlplan
SELECT A.*
FROM SYS.v_$sql_plan a, sys.v_$sqlarea B
WHERE a.sql_id = B.SQL_ID
AND a.child_address = b.LAST_ACTIVE_CHILD_ADDRESS
AND b.PARSING_USER_ID = (select user_id from dba_users where username = UPPER (tschema))
and not exists (select 'x' from sqlplan p
where a.sql_id = p.sql_id);

-- load the v$sql_plan table on the base table SQLPLAN
INSERT INTO bind
SELECT a.*
FROM SYS.v_$sql_bind_capture a, sys.v_$sqlarea B
WHERE a.sql_id = B.SQL_ID
AND a.child_address = b.LAST_ACTIVE_CHILD_ADDRESS
AND A.WAS_CAPTURED='YES'
AND b.PARSING_USER_ID = (select user_id from dba_users where username = UPPER (tschema))
and not exists (select 'x' from bind c
where a.sql_id = c.sql_id);

--load the v$sql_bind_capture on the base table BIND
COMMIT;
-- wait tfreq time till the next execution
END IF;

SYS.DBMS_LOCK.sleep (tfreq);

-- update the log table with the estimation time to finish the process
update rap_follow@raplink
set status= 'CAPTURING WORKLOAD - ESTIMATED TIME TO COMPLETE: '||to_char(round((endtime-sysdate)*1440,2))||' MINUTES'
where snap_id is null;
commit;
END LOOP;

--------------------------------------------------------------------------------------------------------------------------
-- changing the _cursor_bind_capture_interval parameter back to 900 if tchange = true
IF tchange = TRUE
THEN
EXECUTE IMMEDIATE 'alter system set "_cursor_bind_capture_interval"=900';
END IF;
END capture_workload;
------------------------------------------------------------------------------------------------------------------


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

/* procedure transfer_table
Responsible to transfer the base tables to the test site
*/

PROCEDURE transfer_table
IS
h1 NUMBER; --datapump handler
job NUMBER; --used to hold the package till the import finish
begin

-- drop base tables on the target database
EXECUTE IMMEDIATE 'truncate table rap.sql';

EXECUTE IMMEDIATE 'truncate table rap.sqlplan';

EXECUTE IMMEDIATE 'truncate table rap.bind';


-- using conventional way because datapump didn't perform better

-- loading base tables

insert into sql select * from sql@raplink;
insert into sqlplan select * from sqlplan@raplink;
insert into bind select * from bind@raplink;

commit;

END transfer_table;

------------------------------------------------------------------------------------------------------------------
/* procedure create_snap
Responsible to load the base table information on the snapshoot tables
*/


PROCEDURE create_snap(prod_inst varchar2,test_inst varchar2, Tsnap_id out number,
truntime NUMBER,tfreq NUMBER,tchange VARCHAR2,tschema VARCHAR2,
TYPECMD VARCHAR2,tflush VARCHAR2,tcomment varchar2)
IS
vsnap_id number; -- used to generate the snap_id
vpart number; -- used to check the last partition created

BEGIN

-- generating the snap_id, not using sequence to avoid gaps
select nvl(max(snap_id),0)+1
into vsnap_id
from rap_control;

-- used to pass the current snap id to the other procedures
tsnap_id:=vsnap_id;

-- inserting on rap_control table
insert into rap_control
values(vsnap_id,sysdate,prod_inst,test_inst,truntime,tfreq,tchange,tschema,TYPECMD,tflush,tcomment);

-- checking partitions on the snap tables
-- SQL_SNAP
select max(PARTITION_POSITION)
into vpart
from user_tab_partitions
where table_name='SQL_SNAP';

IF vpart < vsnap_id then
execute immediate 'alter table sql_snap add partition SQL_SNAP_ID'||vsnap_id||' values ('||vsnap_id||')';
END if;

-- checking partitions on the snap tables
-- SQLPLAN_SNAP
select max(PARTITION_POSITION)
into vpart
from user_tab_partitions
where table_name='SQLPLAN_SNAP';

IF vpart < vsnap_id then
execute immediate 'alter table sqlplan_snap add partition SQLPLAN_SNAP_ID'||vsnap_id||' values ('||vsnap_id||')';
END if;

-- checking partitions on the snap tables
-- BIND_SNAP
select max(PARTITION_POSITION)
into vpart
from user_tab_partitions
where table_name='BIND_SNAP';

IF vpart < vsnap_id then
execute immediate 'alter table bind_snap add partition BIND_SNAP_ID'||vsnap_id||' values ('||vsnap_id||')';
END if;

-- creating partitinons for the snap_replay tables
-- checking partitions on the snap tables
-- SQL_SNAP_REPLAY
select max(PARTITION_POSITION)
into vpart
from user_tab_partitions
where table_name='SQL_SNAP_REPLAY';

IF vpart < vsnap_id then
execute immediate 'alter table sql_snap_replay add partition SQL_SNAP_replay_ID'||vsnap_id||' values ('||vsnap_id||')';
END if;

-- checking partitions on the snap tables
-- SQLPLAN_SNAP_replay
select max(PARTITION_POSITION)
into vpart
from user_tab_partitions
where table_name='SQLPLAN_SNAP_REPLAY';

IF vpart < vsnap_id then
execute immediate 'alter table sqlplan_snap_replay add partition SQLPLAN_SNAP_replay_ID'||vsnap_id||' values ('||vsnap_id||')';
END if;

-- LOADING THE SNAP TABLES
insert into sql_snap
select vsnap_id, s.* from sql s;

insert into sqlplan_snap
select vsnap_id, s.* from sqlplan s;

insert into bind_snap
select vsnap_id, s.* from bind s;

commit;

END create_snap;

------------------------------------------------------------------------------------------------------------------
/* procedure clean_snap
Responsible to create to load the base table information on the snapshoot tables
*/

PROCEDURE clean_snap (tsnap_id VARCHAR2 DEFAULT 'ALL')
IS
vsnap_id number;
BEGIN

-- if tsnap_id = all that means clean all the snapshots
if UPPER(tsnap_id) ='ALL' then
execute immediate 'truncate table sql_snap';
execute immediate 'truncate table sqlplan_snap';
execute immediate 'truncate table bind_snap';
execute immediate 'truncate table sql_snap_replay';
execute immediate 'truncate table sqlplan_snap_replay';
execute immediate 'truncate table rap_rep1';
execute immediate 'truncate table rap_rep2';
execute immediate 'truncate table rap_rep3';
execute immediate 'truncate table rap_rep4';
execute immediate 'truncate table rap_rep5';

for i in (select snap_id from rap_control ORDER BY SNAP_ID) loop
if i.snap_id > 1 then
execute immediate 'alter table sql_snap drop partition SQL_SNAP_ID'||i.snap_id;
execute immediate 'alter table sqlplan_snap drop partition SQLplan_SNAP_ID'||i.snap_id;
execute immediate 'alter table bind_snap drop partition bind_SNAP_ID'||i.snap_id;
execute immediate 'alter table sql_snap_replay drop partition SQL_SNAP_replay_ID'||i.snap_id;
execute immediate 'alter table sqlplan_snap_replay drop partition SQLplan_SNAP_replay_ID'||i.snap_id;
end if;
end loop;

execute immediate 'truncate table rap_control';

else -- drop specific snapshots
if tsnap_id != '1' then
vsnap_id:=to_number(tsnap_id);
execute immediate 'alter table sql_snap drop partition SQL_SNAP_ID'||vsnap_id;
execute immediate 'alter table sqlplan_snap drop partition SQLplan_SNAP_ID'||vsnap_id;
execute immediate 'alter table bind_snap drop partition bind_SNAP_ID'||vsnap_id;
execute immediate 'alter table sql_snap_replay drop partition SQL_SNAP_replay_ID'||vsnap_id;
execute immediate 'alter table sqlplan_snap_replay drop partition SQLplan_SNAP_replay_ID'||vsnap_id;
delete from rap_control where snap_id=vsnap_id;
delete from rap_rep1 where snap_id=vsnap_id;
delete from rap_rep2 where snap_id=vsnap_id;
delete from rap_rep3 where snap_id=vsnap_id;
delete from rap_rep4 where snap_id=vsnap_id;
delete from rap_rep5 where snap_id=vsnap_id;
commit;
else -- since I can't drop the snapshot 1 I need to truncate the partitions instead
vsnap_id:=to_number(tsnap_id);
execute immediate 'alter table sql_snap truncate partition SQL_SNAP_ID1';
execute immediate 'alter table sqlplan_snap truncate partition SQLplan_SNAP_ID1';
execute immediate 'alter table bind_snap truncate partition bind_SNAP_ID1';
execute immediate 'alter table sql_snap_replay truncate partition SQL_SNAP_replay_ID1';
execute immediate 'alter table sqlplan_snap_replay truncate partition SQLplan_SNAP_replay_ID1';
delete from rap_control where snap_id=vsnap_id;
delete from rap_rep1 where snap_id=vsnap_id;
delete from rap_rep2 where snap_id=vsnap_id;
delete from rap_rep3 where snap_id=vsnap_id;
delete from rap_rep4 where snap_id=vsnap_id;
delete from rap_rep5 where snap_id=vsnap_id;
commit;
end if;

end if;

END clean_snap;

------------------------------------------------------------------------------------------------------------------
/* procedure run_snap_type_sql
Responsible to run the workload on the test site of type sql
type sql means queries with binds and without binds
*/

PROCEDURE run_snap_type_sql (
vsnap_id NUMBER,
cleanlog BOOLEAN DEFAULT FALSE,
flushpool BOOLEAN DEFAULT FALSE
)
IS
csr_id INTEGER;
-- cursor id
rows_fetch NUMBER; -- pointer to execute
cntn NUMBER; -- fetch counter
rownumber VARCHAR2 (100);
sql_table DBMS_SQL.varchar2a;
--we must use this type because there are some statements with mode than 32kb
userid NUMBER; -- userid to parse the statement
numcapt NUMBER; -- number of distinct bind captures
err_num NUMBER; -- error code
err_msg VARCHAR2 (400); -- error message
sqlsize NUMBER;
-- statement size to check in how many pieces it will be devided
loopvar NUMBER; -- check if loop is based on bind or not
totalcmd number; -- to check the progress of the execution
totalcnt number:=0; -- to check the progress of the execution
cntsave number:=0; -- to call the create_snap_replay every 300 executions
BEGIN
if flushpool = true then
-- flushing the shared pool to be able to collect the test data afterwards
EXECUTE IMMEDIATE 'alter system flush shared_pool';
end if;
-- clean rap_logrun table
IF cleanlog = TRUE
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE RAP.RAP_LOGRUN';
END IF;

-- used on the progress log
select count(distinct sql_id)
into totalcmd
from sql_snap
where snap_id =vsnap_id
and command_type=3;


-- Test number 1 - running command_type=3 with binds(collected)
FOR i IN (SELECT sql_id, sql_fulltext, LENGTH (sql_fulltext) sizetext,
parsing_schema_name username,
DECODE ((executions), 0, 1, (executions)) exec, ROWID row_id
FROM rap.sql_snap
WHERE snap_id = vsnap_id
AND command_type = 3
ORDER BY sql_id)
LOOP
-- rownumber := i.sql_id; -- used for debug
-- DBMS_OUTPUT.put_line ('sql_id-' || rownumber); -- used for debug

-- update the follow up table to show where the execution process is
totalcnt := totalcnt+1;
update rap_follow
set status= 'REPLAY PHASE COMMAND TYPE SQL, IT HAS EXECUTED: '||TO_CHAR(TOTALCNT)||' FROM A TOTAL OF : '||to_char(totalcmd)
where snap_id = vsnap_id;

commit;

-- call the create_snap_replay procedure to save the replay
cntsave := cntsave+1;
if cntsave=100 then
rap.create_snap_replay(vsnap_id);
-- execute immediate 'alter system flush shared_pool';
cntsave:=0;
end if;


-- GETTING THE NEW USER ID - if this test database was created using export/import then the user_id will be different that is the reason we execute this check.
begin

SELECT user_id
INTO userid
FROM dba_users
WHERE username = i.username;

EXCEPTION -- this exceptions handles executions and bind errors
WHEN no_data_found
THEN
INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'USER_CHECK', 1, 1,'NO USER');

COMMIT;


end;
-- CHECKING THE SQLSIZE
SELECT LENGTH (sql_fulltext)
INTO sqlsize
FROM sql_snap
WHERE ROWID = i.row_id;

IF sqlsize <= 32767
THEN -- prepare the variable that will hold the statement to be parsed
-- with this size 1 record is enough to hold the statement
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
-- in case the sql is bigger than 32kb we need to divided it in pieces of 32kb
FOR x IN 1 .. CEIL (sqlsize / 32767)
LOOP
IF x = 1
THEN
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
SELECT SUBSTR (sql_fulltext, 32768 * (x - 1), 32767)
INTO sql_table (x)
FROM sql_snap
WHERE ROWID = i.row_id;
END IF;
END LOOP;
END IF;

BEGIN -- this block was created to handle parse exceptions
csr_id := DBMS_SYS_SQL.open_cursor; -- opening the cursor
cntn := 0; -- will be used to initialize the cursor execution
-- DBMS_sys_SQL.PARSE_as_user -- parsing the statement as the user that created it
DBMS_SYS_SQL.parse_as_user (csr_id,
sql_table,
1,
CEIL (sqlsize / 32767),
FALSE,
DBMS_SQL.native,
userid
);

-- checking the number of distinct bind captures to divide by executions, that way we keep the correct number of executions
SELECT COUNT (DISTINCT last_captured)
INTO numcapt
FROM rap.bind_snap
WHERE sql_id = i.sql_id AND snap_id = vsnap_id;

-- CREATING A NEW BLOCK TO HANDLE EXECTIONS DURING THE EXECUTIONS
BEGIN
-- if to check if the execution loop will be based on binds or not
IF numcapt = 0
THEN
loopvar := 1;
ELSE
loopvar := numcapt;
END IF;

-- loop of executions
FOR e IN 1 .. TRUNC (i.exec / loopvar)
LOOP
IF numcapt != 0
THEN
-- loop to separate the distinct bind captures
FOR l IN (SELECT DISTINCT last_captured
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
ORDER BY 1)
LOOP -- distinct binds
-- cursor to get the bind variables
FOR b IN (SELECT NAME, value_string
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
AND last_captured = l.last_captured
ORDER BY POSITION)
LOOP -- LOOP BIND
DBMS_SYS_SQL.bind_variable (csr_id,
b.NAME,
b.value_string
);
-- bind the variables
END LOOP; --LOOP BIND

-- executing the command and doing the first fetch
rows_fetch := DBMS_SYS_SQL.execute_and_fetch (csr_id);
cntn := DBMS_SYS_SQL.last_row_count;

-- return the last count

-- if the last count =0 the null that is to avoid error ora-01002
IF rows_fetch = 0
THEN
NULL;
ELSE
-- loop to fetch all the rows in the cursor
LOOP -- LOOP FETCHS
EXIT WHEN DBMS_SYS_SQL.fetch_rows (csr_id) = 0;
cntn := DBMS_SYS_SQL.last_row_count;
END LOOP; -- LOOP FETCHS
END IF;
END LOOP; -- distinct captures
ELSE
-- executing the command and doing the first fetch
rows_fetch := DBMS_SYS_SQL.execute_and_fetch (csr_id);
cntn := DBMS_SYS_SQL.last_row_count;

-- return the last count

-- if the last count =0 the null that is to avoid error ora-01002
IF rows_fetch = 0
THEN
NULL;
ELSE
-- loop to fetch all the rows in the cursor
LOOP -- LOOP FETCHS
EXIT WHEN DBMS_SYS_SQL.fetch_rows (csr_id) = 0;
cntn := DBMS_SYS_SQL.last_row_count;
END LOOP; -- LOOP FETCHS
END IF;
END IF;
END LOOP; -- EXECUTIONS

DBMS_SYS_SQL.close_cursor (csr_id); -- closing the cursor
EXCEPTION -- this exceptions handles executions and bind errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'EXECUTION', 1, err_num,
err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
EXCEPTION -- this exceptions handles parse errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'PARSE', 1, err_num, err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
END LOOP; -- MAIN LOOP PARSE
END run_snap_type_sql;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
/* procedure run_snap_type_dml
Responsible to run the workload on the test site of type 3
type dml means (INSERT,UPDATE,DELETE,MERGE) with AND WITHOUT binds
*/

PROCEDURE run_snap_type_dml (vsnap_id NUMBER, cleanlog BOOLEAN,flushpool BOOLEAN DEFAULT FALSE)
IS
csr_id INTEGER; -- cursor id
rows_fetch NUMBER; -- pointer to execute
cntn NUMBER; -- fetch counter
rownumber VARCHAR2 (100);
sql_table DBMS_SQL.varchar2a;
--we must use this type because there are some statements with mode than 32kb
userid NUMBER; -- userid to parse the statement
numcapt NUMBER; -- number of distinct bind captures
err_num NUMBER; -- error code
err_msg VARCHAR2 (400); -- error message
sqlsize NUMBER;
loopvar NUMBER; -- check if loop is based on bind or not
-- statement size to check in how many pieces it will be devided
totalcmd number; -- to check the progress of the execution
totalcnt number:=0; -- to check the progress of the execution
cntsave number:=0; -- to call the create_and_replay procedure
BEGIN
if flushpool = true then
-- flushing the shared pool to be able to collect the test data afterwards
EXECUTE IMMEDIATE 'alter system flush shared_pool';
end if;
-- clean rap_logrun table
IF cleanlog = TRUE
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE RAP.RAP_LOGRUN';
END IF;

-- used on the progress log
select count(distinct sql_id)
into totalcmd
from sql_snap
where snap_id =vsnap_id
and command_type IN (6, 7, 2, 189);


-- Test number 2 - running command_type=6, 7, 2, 189 with and without binds
FOR i IN (SELECT sql_id, sql_fulltext, LENGTH (sql_fulltext) sizetext,
parsing_schema_name username,
DECODE ((executions), 0, 1, (executions)) exec, ROWID row_id
FROM rap.sql_snap
WHERE snap_id = vsnap_id AND command_type IN (6, 7, 2, 189)
ORDER BY sql_id)
LOOP
-- rownumber := i.sql_id; -- used for debug
-- DBMS_OUTPUT.put_line ('sql_id-' || rownumber); -- used for debug

-- update the follow up table to show where the execution process is
totalcnt := totalcnt+1;
update rap_follow
set status= 'REPLAY PHASE COMMAND TYPE DML, IT HAS EXECUTED: '||TO_CHAR(TOTALCNT)||' FROM A TOTAL OF : '||to_char(totalcmd)
where snap_id = vsnap_id;

commit;

-- call the create_snap_replay procedure to save the replay
cntsave := cntsave+1;
if cntsave=100 then
rap.create_snap_replay(vsnap_id);
-- execute immediate 'alter system flush shared_pool';
cntsave:=0;
end if;

-- GETTING THE NEW USER ID - if this test database was created using export/import then the user_id will be different that is the reason we execute this check.
begin

SELECT user_id
INTO userid
FROM dba_users
WHERE username = i.username;

EXCEPTION -- this exceptions handles executions and bind errors
WHEN no_data_found
THEN
INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'USER_CHECK', 1, 1,'NO USER');

COMMIT;


end;
-- CHECKING THE SQLSIZE
SELECT LENGTH (sql_fulltext)
INTO sqlsize
FROM sql_snap
WHERE ROWID = i.row_id;

IF sqlsize <= 32767
THEN -- prepare the variable that will hold the statement to be parsed
-- with this size 1 record is enough to hold the statement
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
-- in case the sql is bigger than 32kb we need to divided it in pieces of 32kb
FOR x IN 1 .. CEIL (sqlsize / 32767)
LOOP
IF x = 1
THEN
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
SELECT SUBSTR (sql_fulltext, 32768 * (x - 1), 32767)
INTO sql_table (x)
FROM sql_snap
WHERE ROWID = i.row_id;
END IF;
END LOOP;
END IF;

BEGIN -- this block was created to handle parse exceptions
csr_id := DBMS_SYS_SQL.open_cursor; -- opening the cursor
cntn := 0; -- will be used to initialize the cursor execution
-- DBMS_sys_SQL.PARSE_as_user -- parsing the statement as the user that created it
DBMS_SYS_SQL.parse_as_user (csr_id,
sql_table,
1,
CEIL (sqlsize / 32767),
FALSE,
DBMS_SQL.native,
userid
);

-- checking the number of distinct bind captures to divide by executions, that way we keep the correct number of executions
SELECT COUNT (DISTINCT last_captured)
INTO numcapt
FROM rap.bind_snap
WHERE sql_id = i.sql_id AND snap_id = vsnap_id;

-- CREATING A NEW BLOCK TO HANDLE EXECTIONS DURING THE EXECUTIONS
BEGIN
-- if to check if the execution loop will be based on binds or not
IF numcapt = 0
THEN
loopvar := 1;
ELSE
loopvar := numcapt;
END IF;

-- loop of executions
FOR e IN 1 .. TRUNC (i.exec / loopvar)
LOOP
IF numcapt != 0
THEN
-- loop to separate the distinct bind captures
FOR l IN (SELECT DISTINCT last_captured
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
ORDER BY 1)
LOOP -- distinct binds
-- cursor to get the bind variables
FOR b IN (SELECT NAME, value_string
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
AND last_captured = l.last_captured
ORDER BY POSITION)
LOOP -- LOOP BIND
DBMS_SYS_SQL.bind_variable (csr_id,
b.NAME,
b.value_string
);
-- bind the variables
END LOOP; --LOOP BIND

-- executing the command and doing the first fetch
rows_fetch := DBMS_SYS_SQL.EXECUTE (csr_id);
END LOOP; -- distinct captures
ELSE
rows_fetch := DBMS_SYS_SQL.EXECUTE (csr_id);
END IF;
END LOOP; -- EXECUTIONS

DBMS_SYS_SQL.close_cursor (csr_id); -- closing the cursor
EXCEPTION -- this exceptions handles executions and bind errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'EXECUTION', 2, err_num,
err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
EXCEPTION -- this exceptions handles parse errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'PARSE', 2, err_num, err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
END LOOP; -- MAIN LOOP PARSE
END run_snap_type_dml;


------------------------------------------------------------------------------
/* procedure clean_replay
Responsible to clean the replay tables before the replay is executed
*/
procedure clean_replay(vsnap_id number)
IS
Begin
execute immediate 'alter table sql_snap_replay truncate partition SQL_SNAP_replay_ID'||vsnap_id;
execute immediate 'alter table sqlplan_snap_replay truncate partition SQLplan_SNAP_replay_ID'||vsnap_id;
end;

------------------------------------------------------------------------------
/* procedure create_snap_replay
Responsible to load the base table information on the snapshoot tables after the workload were tested
*/


PROCEDURE create_snap_replay(vsnap_id number)
IS

BEGIN

MERGE INTO SQL_snap_replay a
USING SYS.v_$sqlarea b
ON (a.sql_id = b.sql_id and a.snap_id=vsnap_id)
WHEN MATCHED THEN
UPDATE
SET A.SHARABLE_MEM = B.SHARABLE_MEM,
A.PERSISTENT_MEM = B.PERSISTENT_MEM,
A.RUNTIME_MEM = B.RUNTIME_MEM,
A.SORTS = B.SORTS,
A.VERSION_COUNT = B.VERSION_COUNT,
A.FETCHES = B.FETCHES,
A.EXECUTIONS = B.EXECUTIONS,
A.PX_SERVERS_EXECUTIONS = B.PX_SERVERS_EXECUTIONS,
A.END_OF_FETCH_COUNT = B.END_OF_FETCH_COUNT,
A.USERS_EXECUTING = B.USERS_EXECUTING,
A.LOADS = B.LOADS,
A.INVALIDATIONS = B.INVALIDATIONS,
A.PARSE_CALLS = B.PARSE_CALLS,
A.DISK_READS = B.DISK_READS,
A.DIRECT_WRITES = B.DIRECT_WRITES,
A.BUFFER_GETS = B.BUFFER_GETS,
A.APPLICATION_WAIT_TIME = B.APPLICATION_WAIT_TIME,
A.CONCURRENCY_WAIT_TIME = B.CONCURRENCY_WAIT_TIME,
A.CLUSTER_WAIT_TIME = B.CLUSTER_WAIT_TIME,
A.USER_IO_WAIT_TIME = B.USER_IO_WAIT_TIME,
A.PLSQL_EXEC_TIME = B.PLSQL_EXEC_TIME,
A.JAVA_EXEC_TIME = B.JAVA_EXEC_TIME,
A.ROWS_PROCESSED = B.ROWS_PROCESSED,
A.COMMAND_TYPE = B.COMMAND_TYPE,
A.CPU_TIME = B.CPU_TIME,
A.ELAPSED_TIME = B.ELAPSED_TIME,
A.LAST_LOAD_TIME = B.LAST_LOAD_TIME
WHEN NOT MATCHED THEN
INSERT (snap_id,
SQL_TEXT, SQL_FULLTEXT, SQL_ID,
SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM,
SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS,
USERS_OPENING, FETCHES, EXECUTIONS,
PX_SERVERS_EXECUTIONS, END_OF_FETCH_COUNT, USERS_EXECUTING,
LOADS, FIRST_LOAD_TIME, INVALIDATIONS,
PARSE_CALLS, DISK_READS, DIRECT_WRITES,
BUFFER_GETS, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,
CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME,
JAVA_EXEC_TIME, ROWS_PROCESSED, COMMAND_TYPE,
OPTIMIZER_MODE, OPTIMIZER_COST, OPTIMIZER_ENV,
OPTIMIZER_ENV_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID,
PARSING_SCHEMA_NAME, KEPT_VERSIONS, ADDRESS,
HASH_VALUE, OLD_HASH_VALUE,
PLAN_HASH_VALUE, MODULE, MODULE_HASH,
ACTION, ACTION_HASH, SERIALIZABLE_ABORTS,
OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME,
OUTLINE_SID, LAST_ACTIVE_CHILD_ADDRESS,
REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE,
LAST_LOAD_TIME, IS_OBSOLETE, CHILD_LATCH,
SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#,
EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME,
BIND_DATA)
VALUES ( vsnap_id,B.SQL_TEXT,B.SQL_FULLTEXT,B.SQL_ID,B.SHARABLE_MEM,B.PERSISTENT_MEM,B.RUNTIME_MEM,
B.SORTS,B.VERSION_COUNT,B.LOADED_VERSIONS,B.OPEN_VERSIONS,B.USERS_OPENING,B.FETCHES,B.EXECUTIONS,
B.PX_SERVERS_EXECUTIONS,B.END_OF_FETCH_COUNT,B.USERS_EXECUTING,B.LOADS,B.FIRST_LOAD_TIME,
B.INVALIDATIONS,B.PARSE_CALLS,B.DISK_READS,B.DIRECT_WRITES,B.BUFFER_GETS,B.APPLICATION_WAIT_TIME,
B.CONCURRENCY_WAIT_TIME,B.CLUSTER_WAIT_TIME,B.USER_IO_WAIT_TIME,B.PLSQL_EXEC_TIME,
B.JAVA_EXEC_TIME,B.ROWS_PROCESSED,B.COMMAND_TYPE,B.OPTIMIZER_MODE,B.OPTIMIZER_COST,
B.OPTIMIZER_ENV,B.OPTIMIZER_ENV_HASH_VALUE,B.PARSING_USER_ID,B.PARSING_SCHEMA_ID,
B.PARSING_SCHEMA_NAME,B.KEPT_VERSIONS,B.ADDRESS,B.HASH_VALUE,B.OLD_HASH_VALUE,
B.PLAN_HASH_VALUE,B.MODULE,B.MODULE_HASH,B.ACTION,
B.ACTION_HASH,B.SERIALIZABLE_ABORTS,B.OUTLINE_CATEGORY,B.CPU_TIME,B.ELAPSED_TIME,B.OUTLINE_SID,
B.LAST_ACTIVE_CHILD_ADDRESS,B.REMOTE,B.OBJECT_STATUS,B.LITERAL_HASH_VALUE,B.LAST_LOAD_TIME,
B.IS_OBSOLETE,B.CHILD_LATCH,B.SQL_PROFILE,B.PROGRAM_ID,B.PROGRAM_LINE#,B.EXACT_MATCHING_SIGNATURE,
B.FORCE_MATCHING_SIGNATURE,B.LAST_ACTIVE_TIME,B.BIND_DATA )
WHERE b.PARSING_USER_ID NOT IN (select user_id
from dba_users
where username in ('SYS', 'DBSNMP', 'SYSMAN', 'CTXSYS','RAP',
'ROD','NAGIOS','MGMT_VIEW','EXFSYS','XDB','MGMT_VIEW','DMSYS'))
and b.sql_id in (SELECT DISTINCT sql_id
FROM sql_snap
WHERE snap_id = vsnap_id);


COMMIT;
-- load the v$sql_plan table on the base table SQLPLAN

INSERT INTO sqlplan_snap_replay
SELECT vsnap_id,A.*
FROM SYS.v_$sql_plan a, SQL_snap_replay B
WHERE a.sql_id = B.SQL_ID
and b.snap_id=vsnap_id
and not exists (select 'x' from sqlplan_snap_replay p
where a.sql_id = p.sql_id
and p.snap_id = vsnap_id);



commit;

END create_snap_replay;

------------------------------------------------------------------------------------------------------------------
/* procedure capture_and_play
Responsible to execute all the necessary steps automatically
*/

PROCEDURE capture_and_replay (
truntime NUMBER DEFAULT 30,
-- for how long the capture workload will run
tfreq NUMBER DEFAULT 15,
-- the frequence between capture including bind
tchange BOOLEAN DEFAULT FALSE,
-- if you want to match the frequence with the bind capture
tschema VARCHAR2 DEFAULT 'ALL',
-- if you want to capture for just one schema
cleanlog BOOLEAN DEFAULT FALSE, -- if you want to clean the log
tprodlink VARCHAR2,
-- the production tnsnames you have used to create the dblink from test to prod
ttestlink VARCHAR2, -- the test tnsnames you have used to create the dblink from prod to test
TYPECMD VARCHAR2 default 'ALL', -- define wich type of code you want to execute(ALL, SQL, DML)
tflush BOOLEAN
DEFAULT true, -- flush the production and test shared pool
vcomment varchar2 default null -- any specific comments that will be stored on the rap_control table
)

IS
vprodlink VARCHAR2 (20);
-- used to test the if the specified link is correct
vtestlink VARCHAR2 (20);
-- used to test the if the specified link is correct
vsnap_id NUMBER; -- snap id returned from create_snap procedure
vschema varchar2(30); -- used to check if the specified schema exist
cchange varchar2(10); -- to use on the create_snap procedure
cflush varchar2(10); -- to use on the create_snap procedure
BEGIN

-- checking if the variables are correct before start the process

if tschema != 'ALL' THEN
begin
SELECT USERNAME
INTO VSCHEMA
FROM DBA_USERS
WHERE USERNAME=upper(tschema);

exception when no_data_found then
raise_application_error(-20003, 'YOU MUST SPECIFY A VALID SCHEMA NAME OR ALL TO CAPTURE EVERYTHING');
end;

end if;

IF TYPECMD NOT IN ('ALL','SQL','DML') THEN
raise_application_error(-20002, 'YOU MUST SPECIFY THE COMMAND TYPE YOU WANT TO REPLAY (ALL,SQL,DML)');
END IF;


-- clean follow up table from unsuccefull jobs
DELETE FROM rap_follow
WHERE snap_id IS NULL;

COMMIT;

-- checking if the specified links are ok and you are not running the process from the wrong server

-- prod link
SELECT UPPER (HOST)
INTO vprodlink
FROM user_db_links;

-- test link
SELECT UPPER (HOST)
INTO vtestlink
FROM user_db_links@raplink;

-- if the link don't match it finish the process here
IF UPPER (tprodlink) != vprodlink OR UPPER (ttestlink) != vtestlink
THEN
INSERT INTO rap_follow
(rundate,
status
)
VALUES (SYSDATE,
'THE SPECIFIED HOSTS ARE NOT MATCHING THE DBLINKS CHECK BEFORE RUNNING IT AGAIN'
);

COMMIT;
raise_application_error(-20001, 'THE SPECIFIED HOSTS ARE NOT MATCHING THE DBLINKS CHECK BEFORE RUNNING IT AGAIN');
ELSE
-- starting the capture workload process
INSERT INTO rap_follow
(rundate,
status
)
VALUES (SYSDATE,
'STARTING THE CAPTURE WORKLOAD PROCESS THE ESTIMATED TIME IS: '
|| TO_CHAR (truntime)
|| ' MINUTES'
);

COMMIT;
rap.capture_workload@raplink (truntime, tfreq, tchange, tschema,tflush);

-- starting the transfer data process
UPDATE rap_follow
SET status =
'TRANSFERING THE WORKLOAD DATA TO THE TEST ENVIROMENT, IT SHOULD BE FAST'
WHERE snap_id IS NULL;

COMMIT;
rap.transfer_table ();

-- creating snapshoot based on the workload
UPDATE rap_follow
SET status = 'CREATING THE SNAPSHOOT, IT SHOULD BE FAST'
WHERE snap_id IS NULL;

COMMIT;

if tchange = true then
cchange:='TRUE';
ELSE
cchange:='FALSE';
END IF;

if tflush = true then
cflush:='TRUE';
ELSE
cflush:='FALSE';
END IF;

rap.create_snap (prod_inst=>vprodlink,test_inst=> vtestlink, tsnap_id=>vsnap_id,truntime=>truntime,tfreq=>tfreq,tchange=>cchange,tschema=>tschema,typecmd=>TYPECMD,tflush=>cflush,tcomment=>vcomment);

-- start the replay phase on the test environment
UPDATE rap_follow
SET status = 'STARTING THE REPLAY PHASE, IT WILL TAKE A WHILE',
snap_id = vsnap_id
WHERE snap_id IS NULL;

COMMIT;

-- to clean the replay snapshot
rap.clean_replay(vsnap_id);

IF UPPER(TYPECMD) ='ALL' THEN
rap.run_snap_type_sql (vsnap_id, cleanlog, tflush);
rap.run_snap_type_sql (vsnap_id, cleanlog, false);
rap.create_snap_replay (vsnap_id);
rap.run_snap_type_dml (vsnap_id, FALSE, false);
rap.run_snap_type_dml (vsnap_id, FALSE, false);
rap.create_snap_replay (vsnap_id);
ELSIF UPPER(TYPECMD)='SQL' THEN
rap.run_snap_type_sql (vsnap_id, cleanlog, tflush);
rap.run_snap_type_sql (vsnap_id, cleanlog, false);
rap.create_snap_replay (vsnap_id);
ELSIF UPPER(TYPECMD)='DML' THEN
rap.run_snap_type_dml (vsnap_id, cleanlog, tflush);
rap.run_snap_type_dml (vsnap_id, cleanlog, false);
rap.create_snap_replay (vsnap_id);
END IF;

-- GENERATING reports
UPDATE rap_follow
SET status = 'GENERATING REPORTS'
WHERE snap_id = vsnap_id;
commit;

rap.run_rap_report1(vsnap_id);
rap.run_rap_report2_3(vsnap_id);
rap.run_rap_report4(vsnap_id);
rap.run_rap_report5(vsnap_id);

-- PROCESS COMPLETED
UPDATE rap_follow
SET status = 'PROCESS COMPLETED'
WHERE snap_id = vsnap_id;

COMMIT;
END IF;
END capture_and_replay;

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
------------------------------ REPORTS PROCEDURES -----------------------------------
-------------------------------------------------------------------------------------

------------------------------------------------------------------------------
/* procedure run_rap_report1
This report will show a orerall image of the replay over the capture workload
*/

PROCEDURE run_rap_report1 (vsnap_id NUMBER)
IS
BEGIN
DELETE FROM rap_rep1
WHERE snap_id = vsnap_id;

COMMIT;

FOR i IN (SELECT a.disk_reads, b.disk_reads_1,
ROUND (b.disk_reads_1 / a.disk_reads,
2
)
* 100 percent_disk_reads,
a.buffer_gets, b.buffer_gets_1,
ROUND (b.buffer_gets_1 / a.buffer_gets,
2
)
* 100 percent_buffer_gets,
a.cpu_time, b.cpu_time_1,
ROUND (b.cpu_time_1 / a.cpu_time, 2)
* 100 percent_cpu_time,
a.elapsed_time, b.elapsed_time_1,
ROUND (b.elapsed_time_1 / a.elapsed_time,
2
)
* 100 percent_elapsed_time
FROM (SELECT SUM (disk_reads)/sum(executions) disk_reads,
SUM (buffer_gets)/sum(executions) buffer_gets,
SUM (cpu_time)/sum(executions) cpu_time,
SUM (elapsed_time)/sum(executions) elapsed_time
FROM sql_snap
WHERE snap_id = vsnap_id
AND sql_id IN (SELECT sql_id
FROM sql_snap_replay
WHERE snap_id = vsnap_id)) a,
(SELECT SUM (disk_reads)/sum(executions) disk_reads_1,
SUM (buffer_gets)/sum(executions) buffer_gets_1,
SUM (cpu_time)/sum(executions) cpu_time_1,
SUM (elapsed_time)/sum(executions) elapsed_time_1
FROM sql_snap_replay
WHERE snap_id = vsnap_id) b)
LOOP
-- disk reads
INSERT INTO rap_rep1
(snap_id, stat_name, measure_workload, measure_replay,
percentage_diff
)
VALUES (vsnap_id, 'DISK_READS', round(i.disk_reads,3), round(i.disk_reads_1,3),
i.percent_disk_reads
);

-- buffer gets
INSERT INTO rap_rep1
(snap_id, stat_name, measure_workload, measure_replay,
percentage_diff
)
VALUES (vsnap_id, 'BUFFER_GETS', round(i.buffer_gets,3), round(i.buffer_gets_1,3),
i.percent_buffer_gets
);

-- cpu time
INSERT INTO rap_rep1
(snap_id, stat_name, measure_workload, measure_replay,
percentage_diff
)
VALUES (vsnap_id, 'CPU_TIME', round(i.cpu_time,3), round(i.cpu_time_1,3),
i.percent_cpu_time
);

-- elapsed time
INSERT INTO rap_rep1
(snap_id, stat_name, measure_workload,
measure_replay, percentage_diff
)
VALUES (vsnap_id, 'ELAPSED_TIME', round(i.elapsed_time,3),
round(i.elapsed_time_1,3), i.percent_elapsed_time
);

COMMIT;
END LOOP;
END run_rap_report1;

------------------------------------------------------------------------------
/* procedure run_rep_report2_3
This report will show all sql_id with different execution plan on rep2 and detail on rep3
*/

PROCEDURE run_rap_report2_3 (vsnap_id NUMBER)
IS
TYPE t_name IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;

vhint1 t_name;
vhint2 t_name;
count1 NUMBER := 0;
count2 NUMBER := 0;
totalcheck NUMBER := 0;
totaldiff NUMBER := 0;
vtotcapwork NUMBER := 0;
vtotcapreplay NUMBER := 0;
vtotnotcomp NUMBER := 0;
BEGIN
-- clean report table 2
DELETE FROM rap_rep2
WHERE snap_id = vsnap_id;

-- clean report table 3
DELETE FROM rap_rep3
WHERE snap_id = vsnap_id;

COMMIT;

-- main loop that will define which plan we are going to compare
FOR i IN (SELECT DISTINCT sql_id
FROM sqlplan_snap
WHERE snap_id = vsnap_id
AND sql_id IN (SELECT DISTINCT sql_id
FROM sqlplan_snap_replay
WHERE snap_id = vsnap_id))
LOOP
totalcheck := totalcheck + 1; -- used by the report 2
count1 := 0;
count2 := 0;
vhint1.DELETE;
vhint2.DELETE;

-- getting all the execution plan from the workload
FOR j IN (SELECT operation || '-' || options || '-' || object_name
hint
FROM (SELECT DISTINCT operation, options, object_name, ID
FROM rap.sqlplan_snap
WHERE snap_id = vsnap_id
AND sql_id = i.sql_id
ORDER BY ID))
LOOP
vhint1 (count1) := j.hint;
count1 := count1 + 1;
END LOOP;

-- getting all the execution plan from the replay
FOR m IN (SELECT operation || '-' || options || '-' || object_name hint
FROM (SELECT DISTINCT operation, options, object_name, ID
FROM rap.sqlplan_snap_replay
WHERE snap_id = vsnap_id
AND sql_id = i.sql_id
ORDER BY ID))
LOOP
vhint2 (count2) := m.hint;
count2 := count2 + 1;
END LOOP;

-- checking the differences
IF count1 != count2
THEN

totaldiff := totaldiff + 1; -- used by the report 2

-- report 3
INSERT INTO rap_rep3
SELECT vsnap_id, i.sql_id,
ROUND (b.disk_reads_1 / a.disk_reads,
2
)
* 100 percent_disk_reads,
ROUND (b.buffer_gets_1 / a.buffer_gets,
2
)
* 100 percent_buffer_gets,
ROUND (b.cpu_time_1 / a.cpu_time, 2)
* 100 percent_cpu_time,
ROUND (b.elapsed_time_1 / a.elapsed_time,
2
)
* 100 percent_elapsed_time,
ROUND (b.rows_processed_1 / a.rows_processed,
2
)
* 100 percent_rows_processed
FROM (SELECT DECODE (SUM (disk_reads),
0, 1,
SUM (disk_reads)
)/decode(sum(executions),0,1,sum(executions)) disk_reads,
DECODE (SUM (buffer_gets),
0, 1,
SUM (buffer_gets)
)/decode(sum(executions),0,1,sum(executions)) buffer_gets,
DECODE (SUM (cpu_time),
0, 1,
SUM (cpu_time)
)/decode(sum(executions),0,1,sum(executions)) cpu_time,
DECODE (SUM (elapsed_time),
0, 1,
SUM (elapsed_time)
)/decode(sum(executions),0,1,sum(executions)) elapsed_time,
DECODE (SUM (rows_processed),
0, 1,
SUM (rows_processed)
)/decode(sum(executions),0,1,sum(executions)) rows_processed
FROM rap.sql_snap
WHERE snap_id = vsnap_id AND sql_id = i.sql_id) a,
(SELECT SUM (disk_reads)/decode(sum(executions),0,1,sum(executions)) disk_reads_1,
SUM (buffer_gets)/decode(sum(executions),0,1,sum(executions)) buffer_gets_1,
SUM (cpu_time)/decode(sum(executions),0,1,sum(executions)) cpu_time_1,
SUM (elapsed_time)/decode(sum(executions),0,1,sum(executions)) elapsed_time_1,
SUM (rows_processed)/decode(sum(executions),0,1,sum(executions)) rows_processed_1
FROM rap.sql_snap_replay
WHERE snap_id = vsnap_id AND sql_id = i.sql_id) b;

COMMIT;
ELSE

FOR h IN vhint1.FIRST .. vhint1.LAST
LOOP
IF vhint1 (h) != vhint2 (h)
THEN

totaldiff := totaldiff + 1; -- used on report 2

-- used on report 3
-- report 3
INSERT INTO rap_rep3
SELECT vsnap_id, i.sql_id,
ROUND (b.disk_reads_1 / a.disk_reads,
2
)
* 100 percent_disk_reads,
ROUND (b.buffer_gets_1 / a.buffer_gets,
2
)
* 100 percent_buffer_gets,
ROUND (b.cpu_time_1 / a.cpu_time,
2
)
* 100 percent_cpu_time,
ROUND (b.elapsed_time_1 / a.elapsed_time,
2
)
* 100 percent_elapsed_time,
ROUND
(b.rows_processed_1 / a.rows_processed,
2
)
* 100 percent_rows_processed
FROM (SELECT DECODE (SUM (disk_reads),
0, 1,
SUM (disk_reads)
)/decode(sum(executions),0,1,sum(executions)) disk_reads,
DECODE (SUM (buffer_gets),
0, 1,
SUM (buffer_gets)
)/decode(sum(executions),0,1,sum(executions)) buffer_gets,
DECODE (SUM (cpu_time),
0, 1,
SUM (cpu_time)
)/decode(sum(executions),0,1,sum(executions)) cpu_time,
DECODE (SUM (elapsed_time),
0, 1,
SUM (elapsed_time)
)/decode(sum(executions),0,1,sum(executions)) elapsed_time,
DECODE (SUM (rows_processed),
0, 1,
SUM (rows_processed)
)/decode(sum(executions),0,1,sum(executions)) rows_processed
FROM rap.sql_snap
WHERE snap_id = vsnap_id AND sql_id = i.sql_id) a,
(SELECT SUM (disk_reads)/decode(sum(executions),0,1,sum(executions)) disk_reads_1,
SUM (buffer_gets)/decode(sum(executions),0,1,sum(executions)) buffer_gets_1,
SUM (cpu_time)/decode(sum(executions),0,1,sum(executions)) cpu_time_1,
SUM (elapsed_time)/decode(sum(executions),0,1,sum(executions)) elapsed_time_1,
SUM (rows_processed)/decode(sum(executions),0,1,sum(executions)) rows_processed_1
FROM rap.sql_snap_replay
WHERE snap_id = vsnap_id AND sql_id = i.sql_id) b;

COMMIT;
vhint1.DELETE;
vhint2.DELETE;
EXIT;
END IF;
END LOOP;
END IF;
END LOOP; -- main loop

-- total capture workload
SELECT count(DISTINCT sql_id)
into vtotcapwork
FROM rap.sqlplan_snap
WHERE snap_id = vsnap_id;

-- total capture replay
SELECT count(DISTINCT sql_id)
into vtotcapreplay
FROM rap.sqlplan_snap_replay
WHERE snap_id = vsnap_id;

-- not compared, the reason may be execution problem, or not captured on the workload or on the replay.

vtotnotcomp := (vtotcapwork - totalcheck) + (vtotcapreplay - totalcheck);

-- generate the report 2
INSERT INTO rap_rep2
(snap_id, total_checked, total_dif_plan,
percentage_diff_plan,Total_cap_work,Total_cap_replay,Total_not_compared
)
VALUES (vsnap_id, totalcheck, totaldiff,
ROUND (totaldiff / totalcheck, 2) * 100,vtotcapwork,vtotcapreplay,vtotnotcomp
);

COMMIT;
END run_rap_report2_3;

------------------------------------------------------------------------------
/* procedure run_rap_report4
This report will show all the sql_ids with when the replay performance were 10% more expensive in cpu_time and elapsed_time
*/

PROCEDURE run_rap_report4 (vsnap_id NUMBER)
IS
BEGIN
-- clean report table 2
DELETE FROM rap_rep4
WHERE snap_id = vsnap_id;

COMMIT;

INSERT INTO rap_rep4
(snap_id, sql_id, percent_disk, percent_buffer,
percent_cpu_time, percent_elapsed, percent_rows_processed)
SELECT vsnap_id, a.*
FROM (SELECT sql_id,
ROUND (b.disk_reads_1 / a.disk_reads,
2
)
* 100 percent_disk_reads,
ROUND (b.buffer_gets_1 / a.buffer_gets,
2
)
* 100 percent_buffer_gets,
ROUND (b.cpu_time_1 / a.cpu_time, 2)
* 100 percent_cpu_time,
ROUND (b.elapsed_time_1 / a.elapsed_time,
2
)
* 100 percent_elapsed_time,
ROUND (b.rows_processed_1 / a.rows_processed,
2
)
* 100 percent_rows_processed
FROM (SELECT sql_id,
DECODE (SUM (disk_reads),
0, 1,
SUM (disk_reads)
)/decode(sum(executions),0,1,sum(executions)) disk_reads,
DECODE (SUM (buffer_gets),
0, 1,
SUM (buffer_gets)
)/decode(sum(executions),0,1,sum(executions)) buffer_gets,
DECODE (SUM (cpu_time),
0, 1,
SUM (cpu_time)
)/decode(sum(executions),0,1,sum(executions)) cpu_time,
DECODE (SUM (elapsed_time),
0, 1,
SUM (elapsed_time)
)/decode(sum(executions),0,1,sum(executions)) elapsed_time,
DECODE (SUM (rows_processed),
0, 1,
SUM (rows_processed)
)/decode(sum(executions),0,1,sum(executions)) rows_processed
FROM rap.sql_snap
WHERE snap_id = vsnap_id
GROUP BY sql_id) a,
(SELECT sql_id sql_id_1, SUM (disk_reads)/decode(sum(executions),0,1,sum(executions)) disk_reads_1,
SUM (buffer_gets)/decode(sum(executions),0,1,sum(executions)) buffer_gets_1,
SUM (cpu_time)/decode(sum(executions),0,1,sum(executions)) cpu_time_1,
SUM (elapsed_time)/decode(sum(executions),0,1,sum(executions)) elapsed_time_1,
SUM (rows_processed)/decode(sum(executions),0,1,sum(executions)) rows_processed_1
FROM rap.sql_snap_replay
WHERE snap_id = vsnap_id
GROUP BY sql_id) b
WHERE a.sql_id = b.sql_id_1
AND a.elapsed_time > 200000
AND b.elapsed_time_1 > 200000) a
WHERE percent_cpu_time >= 110 AND percent_elapsed_time >= 100;

COMMIT;
END run_rap_report4;


------------------------------------------------------------------------------
/* procedure run_rap_report5
This report will show all the sql_ids for the queries where the replay failed
*/


PROCEDURE run_rap_report5 (vsnap_id NUMBER)
IS
BEGIN
-- clean report table 2
DELETE FROM rap_rep5
WHERE snap_id = vsnap_id;

COMMIT;

-- getting the sql_ids that got an error when executing
INSERT INTO rap_rep5
SELECT *
FROM rap_logrun
WHERE snap_id = vsnap_id;

COMMIT;

-- getting the sql_ids that were not executed at all
INSERT INTO rap_rep5
SELECT snap_id, sql_id, 'not processed', 0, 0,
'check the command type'
FROM rap.sql_snap
WHERE snap_id = vsnap_id
AND sql_id NOT IN (SELECT sql_id
FROM rap.rap_logrun
WHERE snap_id = vsnap_id)
AND sql_id NOT IN (SELECT sql_id
FROM rap.sql_snap_replay
WHERE snap_id = vsnap_id);

COMMIT;
END run_rap_report5;

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- MISCELLANEOUS
------------------------------------------------------------------------------
/* procedure run_sql_id
This procedure can run a specific sql_id and you need to check on the V$sql or V$sqlarea
*/



PROCEDURE run_sql_id (
vsnap_id NUMBER,
cleanlog BOOLEAN DEFAULT FALSE,
flushpool BOOLEAN DEFAULT FALSE,
vsql_id varchar2
)
IS
csr_id INTEGER;
-- cursor id
rows_fetch NUMBER; -- pointer to execute
cntn NUMBER; -- fetch counter
rownumber VARCHAR2 (100);
sql_table DBMS_SQL.varchar2a;
--we must use this type because there are some statements with mode than 32kb
userid NUMBER; -- userid to parse the statement
numcapt NUMBER; -- number of distinct bind captures
err_num NUMBER; -- error code
err_msg VARCHAR2 (400); -- error message
sqlsize NUMBER;
-- statement size to check in how many pieces it will be devided
loopvar NUMBER; -- check if loop is based on bind or not
totalcmd number; -- to check the progress of the execution
totalcnt number:=0; -- to check the progress of the execution
cntsave number:=0; -- to call the create_snap_replay every 300 executions
BEGIN
if flushpool = true then
-- flushing the shared pool to be able to collect the test data afterwards
EXECUTE IMMEDIATE 'alter system flush shared_pool';
end if;
-- clean rap_logrun table
IF cleanlog = TRUE
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE RAP.RAP_LOGRUN';
END IF;

-- used on the progress log
select count(distinct sql_id)
into totalcmd
from sql_snap
where snap_id =vsnap_id
and command_type=3;


-- Test number 1 - running command_type=3 with binds(collected)
FOR i IN (SELECT sql_id, sql_fulltext, LENGTH (sql_fulltext) sizetext,
parsing_schema_name username,
DECODE ((executions), 0, 1, (executions)) exec, ROWID row_id
FROM rap.sql_snap
WHERE snap_id = vsnap_id
AND sql_id = vsql_id)
LOOP
-- rownumber := i.sql_id; -- used for debug
-- DBMS_OUTPUT.put_line ('sql_id-' || rownumber); -- used for debug

-- GETTING THE NEW USER ID - if this test database was created using export/import then the user_id will be different that is the reason we execute this check.
SELECT user_id
INTO userid
FROM dba_users
WHERE username = i.username;

-- CHECKING THE SQLSIZE
SELECT LENGTH (sql_fulltext)
INTO sqlsize
FROM sql_snap
WHERE ROWID = i.row_id;

IF sqlsize <= 32767
THEN -- prepare the variable that will hold the statement to be parsed
-- with this size 1 record is enough to hold the statement
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
-- in case the sql is bigger than 32kb we need to divided it in pieces of 32kb
FOR x IN 1 .. CEIL (sqlsize / 32767)
LOOP
IF x = 1
THEN
SELECT SUBSTR (sql_fulltext, 1, 32767)
INTO sql_table (1)
FROM sql_snap
WHERE ROWID = i.row_id;
ELSE
SELECT SUBSTR (sql_fulltext, 32768 * (x - 1), 32767)
INTO sql_table (x)
FROM sql_snap
WHERE ROWID = i.row_id;
END IF;
END LOOP;
END IF;

BEGIN -- this block was created to handle parse exceptions
csr_id := DBMS_SYS_SQL.open_cursor; -- opening the cursor
cntn := 0; -- will be used to initialize the cursor execution
-- DBMS_sys_SQL.PARSE_as_user -- parsing the statement as the user that created it
DBMS_SYS_SQL.parse_as_user (csr_id,
sql_table,
1,
CEIL (sqlsize / 32767),
FALSE,
DBMS_SQL.native,
userid
);

-- checking the number of distinct bind captures to divide by executions, that way we keep the correct number of executions
SELECT COUNT (DISTINCT last_captured)
INTO numcapt
FROM rap.bind_snap
WHERE sql_id = i.sql_id AND snap_id = vsnap_id;

-- CREATING A NEW BLOCK TO HANDLE EXECTIONS DURING THE EXECUTIONS
BEGIN
-- if to check if the execution loop will be based on binds or not
IF numcapt = 0
THEN
loopvar := 1;
ELSE
loopvar := numcapt;
END IF;

-- loop of executions
FOR e IN 1 .. TRUNC (i.exec / loopvar)
LOOP
IF numcapt != 0
THEN
-- loop to separate the distinct bind captures
FOR l IN (SELECT DISTINCT last_captured
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
ORDER BY 1)
LOOP -- distinct binds
-- cursor to get the bind variables
FOR b IN (SELECT NAME, value_string
FROM bind_snap
WHERE sql_id = i.sql_id
AND snap_id = vsnap_id
AND last_captured = l.last_captured
ORDER BY POSITION)
LOOP -- LOOP BIND
DBMS_SYS_SQL.bind_variable (csr_id,
b.NAME,
b.value_string
);
-- bind the variables
END LOOP; --LOOP BIND

-- executing the command and doing the first fetch
rows_fetch := DBMS_SYS_SQL.execute_and_fetch (csr_id);
cntn := DBMS_SYS_SQL.last_row_count;

-- return the last count

-- if the last count =0 the null that is to avoid error ora-01002
IF rows_fetch = 0
THEN
NULL;
ELSE
-- loop to fetch all the rows in the cursor
LOOP -- LOOP FETCHS
EXIT WHEN DBMS_SYS_SQL.fetch_rows (csr_id) = 0;
cntn := DBMS_SYS_SQL.last_row_count;
END LOOP; -- LOOP FETCHS
END IF;
END LOOP; -- distinct captures
ELSE
-- executing the command and doing the first fetch
rows_fetch := DBMS_SYS_SQL.execute_and_fetch (csr_id);
cntn := DBMS_SYS_SQL.last_row_count;

-- return the last count

-- if the last count =0 the null that is to avoid error ora-01002
IF rows_fetch = 0
THEN
NULL;
ELSE
-- loop to fetch all the rows in the cursor
LOOP -- LOOP FETCHS
EXIT WHEN DBMS_SYS_SQL.fetch_rows (csr_id) = 0;
cntn := DBMS_SYS_SQL.last_row_count;
END LOOP; -- LOOP FETCHS
END IF;
END IF;
END LOOP; -- EXECUTIONS

DBMS_SYS_SQL.close_cursor (csr_id); -- closing the cursor
EXCEPTION -- this exceptions handles executions and bind errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'EXECUTION', 1, err_num,
err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
EXCEPTION -- this exceptions handles parse errors
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);

INSERT INTO rap_logrun
VALUES (vsnap_id, i.sql_id, 'PARSE', 1, err_num, err_msg);

COMMIT;
DBMS_SYS_SQL.close_cursor (csr_id);
END; -- CLOSING THE BLOCK RESPONSIBLE BY EXECUTIONS
END LOOP; -- MAIN LOOP PARSE
END run_sql_id;




END rap;
/