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

/* procedure capture_workload
Responsible to capture all the workload based on the specified time
There are 2 parameters
Truntime -- this parameter controls in minutes for how long the work load will be captured.
Tfreq -- this parameter controls in seconds the frequency of every capture.
Tchangebind -- this parameter will change the time the system will capture the bind variables,
if set true it will use the Tfreq time to change it, but this is a hide parameter, with
my tests it didn't affect the performance and neither caused any problems, but the decision
to change it is yours, no oracle support on that.
Be careful when setting the frequence parameter, the idea is to capture the workload and not cause more.
It will depend on your environment the best value to use.
If you don't set Tchangebind to true the default oracle bind capture occurs every 15 minutes.
You can also specify a schema name instead of collecting the whole system, except system queries.
*/
PROCEDURE capture_workload (
truntime NUMBER DEFAULT 30,
tfreq NUMBER DEFAULT 15,
tchange BOOLEAN DEFAULT FALSE,
tschema VARCHAR2 DEFAULT 'ALL',
tflush BOOLEAN DEFAULT TRUE
);

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

/* procedure clean_table
Responsible to clean up the tables and keep only useful data
*/
-- PROCEDURE clean_table;

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

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

-----------------------------------------------------------------------------------------------------------------
/* procedure create_snap
Responsible to create 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
);

-----------------------------------------------------------------------------------------------------------------
/* procedure clean_snap
Responsible to clean the snap tables
*/
PROCEDURE clean_snap (tsnap_id VARCHAR2 DEFAULT 'ALL');

------------------------------------------------------------------------------------------------------------------
/* 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
);

------------------------------------------------------------------------------
/* 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 DEFAULT FALSE,
flushpool BOOLEAN DEFAULT FALSE
);

------------------------------------------------------------------------------
/* procedure capture_replay
Responsible to capture the replay based on the snapshot workload
*/
-- PROCEDURE capture_replay (vsnap_id NUMBER);

------------------------------------------------------------------------------------------------------------------
/* 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);

------------------------------------------------------------------------------------------------------------------
/* procedure capture_and_replay
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 shared pool to get a complete clean test
vcomment VARCHAR2
DEFAULT NULL
-- any specific comments that will be stored on the rap_control table
);

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
------------------------------ 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);

------------------------------------------------------------------------------
/* 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);

------------------------------------------------------------------------------
/* 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);

------------------------------------------------------------------------------
/* 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);

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 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
);
END rap;
/