PROMPT "CREATING RAP USER AND GRANTING PRIVILEGES"

CREATE USER rap IDENTIFIED BY rapver1
DEFAULT TABLESPACE sysaux;

ALTER USER rap QUOTA UNLIMITED ON sysaux;

GRANT CONNECT TO rap;
GRANT CREATE PROCEDURE TO rap;
GRANT CREATE DATABASE LINK TO rap;
GRANT EXECUTE ON DBMS_LOCK TO rap;
GRANT SELECT ON v_$sql TO rap;
GRANT SELECT ON v_$sqlarea TO rap;
GRANT SELECT ON v_$instance TO rap;
GRANT SELECT ON v_$sql_plan TO rap;
GRANT SELECT ON v_$sql_bind_capture TO rap;
GRANT EXECUTE ON DBMS_SYS_SQL TO rap;
GRANT ALTER SYSTEM TO rap;
GRANT CREATE TABLE TO rap;
GRANT EXECUTE ON DBMS_SYS_ERROR TO rap;
GRANT EXECUTE ON dbms_datapump TO rap;
CREATE SYNONYM rap.DBMS_SYS_SQL FOR SYS.DBMS_SYS_SQL;
GRANT SELECT ON dba_users TO rap;
GRANT CREATE SYNONYM TO rap;



CONN rap/rapver1

PROMPT "CREATING TABLES"

-- Table SQL used to capture all queries and dml on the shared_pool

CREATE TABLE sql
( sql_text VARCHAR2(1000 BYTE),
sql_fulltext CLOB,
sql_id VARCHAR2(13 BYTE),
sharable_mem NUMBER,
persistent_mem NUMBER,
runtime_mem NUMBER,
sorts NUMBER,
version_count NUMBER,
loaded_versions NUMBER,
open_versions NUMBER,
users_opening NUMBER,
fetches NUMBER,
executions NUMBER,
px_servers_executions NUMBER,
end_of_fetch_count NUMBER,
users_executing NUMBER,
loads NUMBER,
first_load_time VARCHAR2(76 BYTE),
invalidations NUMBER,
parse_calls NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
buffer_gets NUMBER,
application_wait_time NUMBER,
concurrency_wait_time NUMBER,
cluster_wait_time NUMBER,
user_io_wait_time NUMBER,
plsql_exec_time NUMBER,
java_exec_time NUMBER,
rows_processed NUMBER,
command_type NUMBER,
optimizer_mode VARCHAR2(10 BYTE),
optimizer_cost NUMBER,
optimizer_env RAW(839),
optimizer_env_hash_value NUMBER,
parsing_user_id NUMBER,
parsing_schema_id NUMBER,
parsing_schema_name VARCHAR2(30 BYTE),
kept_versions NUMBER,
address RAW(8),
hash_value NUMBER,
old_hash_value NUMBER,
plan_hash_value NUMBER,
module VARCHAR2(64 BYTE),
module_hash NUMBER,
action VARCHAR2(64 BYTE),
action_hash NUMBER,
serializable_aborts NUMBER,
outline_category VARCHAR2(64 BYTE),
cpu_time NUMBER,
elapsed_time NUMBER,
outline_sid NUMBER,
last_active_child_address RAW(8),
remote VARCHAR2(1 BYTE),
object_status VARCHAR2(19 BYTE),
literal_hash_value NUMBER,
last_load_time VARCHAR2(76 BYTE),
is_obsolete VARCHAR2(1 BYTE),
child_latch NUMBER,
sql_profile VARCHAR2(64 BYTE),
program_id NUMBER,
program_line# NUMBER,
exact_matching_signature NUMBER,
force_matching_signature NUMBER,
last_active_time DATE,
bind_data RAW(2000)
);

CREATE INDEX sql_sqlid_idx
ON SQL(sql_id);

-- Table SQLPLAN used to capture all execution plans on the shared_pool

CREATE TABLE sqlplan
( address RAW(8),
hash_value NUMBER,
sql_id VARCHAR2(13 BYTE),
plan_hash_value NUMBER,
child_address RAW(8),
child_number NUMBER,
TIMESTAMP DATE,
operation VARCHAR2(120 BYTE),
options VARCHAR2(120 BYTE),
object_node VARCHAR2(160 BYTE),
object# NUMBER,
object_owner VARCHAR2(30 BYTE),
object_name VARCHAR2(30 BYTE),
object_alias VARCHAR2(65 BYTE),
object_type VARCHAR2(80 BYTE),
optimizer VARCHAR2(80 BYTE),
ID NUMBER,
parent_id NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
search_columns NUMBER,
COST NUMBER,
cardinality NUMBER,
BYTES NUMBER,
other_tag VARCHAR2(140 BYTE),
partition_start VARCHAR2(20 BYTE),
partition_stop VARCHAR2(20 BYTE),
partition_id NUMBER,
other VARCHAR2(4000 BYTE),
distribution VARCHAR2(80 BYTE),
cpu_cost NUMBER,
io_cost NUMBER,
temp_space NUMBER,
access_predicates VARCHAR2(4000 BYTE),
filter_predicates VARCHAR2(4000 BYTE),
projection VARCHAR2(4000 BYTE),
TIME NUMBER,
qblock_name VARCHAR2(30 BYTE),
remarks VARCHAR2(4000 BYTE),
other_xml CLOB
);

CREATE INDEX sqlplan_sqlid_idx
ON sqlplan(sql_id,child_address);

-- Table BIND used to capture all binds on the shared_pool
CREATE TABLE bind
( address RAW(8),
hash_value NUMBER,
sql_id VARCHAR2(13 BYTE),
child_address RAW(8),
child_number NUMBER,
NAME VARCHAR2(120 BYTE),
POSITION NUMBER,
dup_position NUMBER,
datatype NUMBER,
datatype_string VARCHAR2(60 BYTE),
character_sid NUMBER,
PRECISION NUMBER,
scale NUMBER,
max_length NUMBER,
was_captured VARCHAR2(3 BYTE),
last_captured DATE,
value_string VARCHAR2(4000 BYTE),
value_anydata SYS.ANYDATA
);

CREATE INDEX bind_sqlid_idx
ON bind(sql_id, child_address,last_captured);

ANALYZE TABLE SQL COMPUTE STATISTICS;
ANALYZE TABLE sqlplan COMPUTE STATISTICS;
ANALYZE TABLE bind COMPUTE STATISTICS;


-- TABLE USED ON THE CHECK THE STATUS OF THE CAPTURE_AND_PLAY PROCEDURE

CREATE TABLE rap_follow
(snap_id NUMBER,
rundate DATE,
status VARCHAR2(1000));


-- TABLES USED TO STORE THE SNAP SHOTS TAKEN ON PRODUCTION BEFORE RERUN ON TEST
CREATE TABLE rap_control
(snap_id NUMBER,
execdate DATE,
prod_instance VARCHAR2(8),
test_instance VARCHAR2(8),
capture_time NUMBER,
frequence NUMBER,
change_freq_bind VARCHAR2(10),
SCHEMA VARCHAR2(30),
replaycmd VARCHAR2(10),
FLUSH VARCHAR2(10),
comments VARCHAR2(1000));

CREATE TABLE rap_logrun
(snap_id NUMBER,
sql_id VARCHAR2(13 BYTE),
phase VARCHAR2(30),
exectype NUMBER,
errornum NUMBER,
errormsg VARCHAR2(400));


CREATE TABLE sql_snap
( snap_id NUMBER,
sql_text VARCHAR2(1000 BYTE),
sql_fulltext CLOB,
sql_id VARCHAR2(13 BYTE),
sharable_mem NUMBER,
persistent_mem NUMBER,
runtime_mem NUMBER,
sorts NUMBER,
version_count NUMBER,
loaded_versions NUMBER,
open_versions NUMBER,
users_opening NUMBER,
fetches NUMBER,
executions NUMBER,
px_servers_executions NUMBER,
end_of_fetch_count NUMBER,
users_executing NUMBER,
loads NUMBER,
first_load_time VARCHAR2(76 BYTE),
invalidations NUMBER,
parse_calls NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
buffer_gets NUMBER,
application_wait_time NUMBER,
concurrency_wait_time NUMBER,
cluster_wait_time NUMBER,
user_io_wait_time NUMBER,
plsql_exec_time NUMBER,
java_exec_time NUMBER,
rows_processed NUMBER,
command_type NUMBER,
optimizer_mode VARCHAR2(10 BYTE),
optimizer_cost NUMBER,
optimizer_env RAW(839),
optimizer_env_hash_value NUMBER,
parsing_user_id NUMBER,
parsing_schema_id NUMBER,
parsing_schema_name VARCHAR2(30 BYTE),
kept_versions NUMBER,
address RAW(8),
hash_value NUMBER,
old_hash_value NUMBER,
plan_hash_value NUMBER,
module VARCHAR2(64 BYTE),
module_hash NUMBER,
action VARCHAR2(64 BYTE),
action_hash NUMBER,
serializable_aborts NUMBER,
outline_category VARCHAR2(64 BYTE),
cpu_time NUMBER,
elapsed_time NUMBER,
outline_sid NUMBER,
last_active_child_address RAW(8),
remote VARCHAR2(1 BYTE),
object_status VARCHAR2(19 BYTE),
literal_hash_value NUMBER,
last_load_time VARCHAR2(76 BYTE),
is_obsolete VARCHAR2(1 BYTE),
child_latch NUMBER,
sql_profile VARCHAR2(64 BYTE),
program_id NUMBER,
program_line# NUMBER,
exact_matching_signature NUMBER,
force_matching_signature NUMBER,
last_active_time DATE,
bind_data RAW(2000)
) PARTITION BY LIST (snap_id)
(PARTITION sql_snap_id1 VALUES (1));

CREATE INDEX sql_snap_sqlid_idx ON sql_snap (sql_id) LOCAL;

CREATE TABLE sqlplan_snap
( snap_id NUMBER,
address RAW(8),
hash_value NUMBER,
sql_id VARCHAR2(13 BYTE),
plan_hash_value NUMBER,
child_address RAW(8),
child_number NUMBER,
TIMESTAMP DATE,
operation VARCHAR2(120 BYTE),
options VARCHAR2(120 BYTE),
object_node VARCHAR2(160 BYTE),
object# NUMBER,
object_owner VARCHAR2(30 BYTE),
object_name VARCHAR2(30 BYTE),
object_alias VARCHAR2(65 BYTE),
object_type VARCHAR2(80 BYTE),
optimizer VARCHAR2(80 BYTE),
ID NUMBER,
parent_id NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
search_columns NUMBER,
COST NUMBER,
cardinality NUMBER,
BYTES NUMBER,
other_tag VARCHAR2(140 BYTE),
partition_start VARCHAR2(20 BYTE),
partition_stop VARCHAR2(20 BYTE),
partition_id NUMBER,
other VARCHAR2(4000 BYTE),
distribution VARCHAR2(80 BYTE),
cpu_cost NUMBER,
io_cost NUMBER,
temp_space NUMBER,
access_predicates VARCHAR2(4000 BYTE),
filter_predicates VARCHAR2(4000 BYTE),
projection VARCHAR2(4000 BYTE),
TIME NUMBER,
qblock_name VARCHAR2(30 BYTE),
remarks VARCHAR2(4000 BYTE),
other_xml CLOB
) PARTITION BY LIST (snap_id)
(PARTITION sqlplan_snap_id1 VALUES (1));

CREATE INDEX sqlplan_snap_sqlid_idx ON sqlplan_snap(sql_id, child_address)LOCAL;

CREATE TABLE bind_snap
( snap_id NUMBER,
address RAW(8),
hash_value NUMBER,
sql_id VARCHAR2(13 BYTE),
child_address RAW(8),
child_number NUMBER,
NAME VARCHAR2(120 BYTE),
POSITION NUMBER,
dup_position NUMBER,
datatype NUMBER,
datatype_string VARCHAR2(60 BYTE),
character_sid NUMBER,
PRECISION NUMBER,
scale NUMBER,
max_length NUMBER,
was_captured VARCHAR2(3 BYTE),
last_captured DATE,
value_string VARCHAR2(4000 BYTE),
value_anydata SYS.ANYDATA
) PARTITION BY LIST (snap_id)
(PARTITION bind_snap_id1 VALUES (1));

CREATE INDEX bindsnap_sqlid_idx ON bind_snap(sql_id, child_address, last_captured)
LOCAL;



--tables used by the replay

CREATE TABLE sql_snap_replay
(snap_id NUMBER,
sql_text VARCHAR2(1000 BYTE),
sql_fulltext CLOB,
sql_id VARCHAR2(13 BYTE),
sharable_mem NUMBER,
persistent_mem NUMBER,
runtime_mem NUMBER,
sorts NUMBER,
version_count NUMBER,
loaded_versions NUMBER,
open_versions NUMBER,
users_opening NUMBER,
fetches NUMBER,
executions NUMBER,
px_servers_executions NUMBER,
end_of_fetch_count NUMBER,
users_executing NUMBER,
loads NUMBER,
first_load_time VARCHAR2(76 BYTE),
invalidations NUMBER,
parse_calls NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
buffer_gets NUMBER,
application_wait_time NUMBER,
concurrency_wait_time NUMBER,
cluster_wait_time NUMBER,
user_io_wait_time NUMBER,
plsql_exec_time NUMBER,
java_exec_time NUMBER,
rows_processed NUMBER,
command_type NUMBER,
optimizer_mode VARCHAR2(10 BYTE),
optimizer_cost NUMBER,
optimizer_env RAW(839),
optimizer_env_hash_value NUMBER,
parsing_user_id NUMBER,
parsing_schema_id NUMBER,
parsing_schema_name VARCHAR2(30 BYTE),
kept_versions NUMBER,
address RAW(8),
hash_value NUMBER,
old_hash_value NUMBER,
plan_hash_value NUMBER,
module VARCHAR2(64 BYTE),
module_hash NUMBER,
action VARCHAR2(64 BYTE),
action_hash NUMBER,
serializable_aborts NUMBER,
outline_category VARCHAR2(64 BYTE),
cpu_time NUMBER,
elapsed_time NUMBER,
outline_sid NUMBER,
last_active_child_address RAW(8),
remote VARCHAR2(1 BYTE),
object_status VARCHAR2(19 BYTE),
literal_hash_value NUMBER,
last_load_time VARCHAR2(76 BYTE),
is_obsolete VARCHAR2(1 BYTE),
child_latch NUMBER,
sql_profile VARCHAR2(64 BYTE),
program_id NUMBER,
program_line# NUMBER,
exact_matching_signature NUMBER,
force_matching_signature NUMBER,
last_active_time DATE,
bind_data RAW(2000)
) PARTITION BY LIST (snap_id)
(PARTITION sql_snap_replay_id1 VALUES (1));

CREATE INDEX sql_snap_replay_sqlid_idx ON sql_snap_replay (sql_id) LOCAL;

CREATE TABLE sqlplan_snap_replay
( snap_id NUMBER,
address RAW(8),
hash_value NUMBER,
sql_id VARCHAR2(13 BYTE),
plan_hash_value NUMBER,
child_address RAW(8),
child_number NUMBER,
TIMESTAMP DATE,
operation VARCHAR2(120 BYTE),
options VARCHAR2(120 BYTE),
object_node VARCHAR2(160 BYTE),
object# NUMBER,
object_owner VARCHAR2(30 BYTE),
object_name VARCHAR2(30 BYTE),
object_alias VARCHAR2(65 BYTE),
object_type VARCHAR2(80 BYTE),
optimizer VARCHAR2(80 BYTE),
ID NUMBER,
parent_id NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
search_columns NUMBER,
COST NUMBER,
cardinality NUMBER,
BYTES NUMBER,
other_tag VARCHAR2(140 BYTE),
partition_start VARCHAR2(20 BYTE),
partition_stop VARCHAR2(20 BYTE),
partition_id NUMBER,
other VARCHAR2(4000 BYTE),
distribution VARCHAR2(80 BYTE),
cpu_cost NUMBER,
io_cost NUMBER,
temp_space NUMBER,
access_predicates VARCHAR2(4000 BYTE),
filter_predicates VARCHAR2(4000 BYTE),
projection VARCHAR2(4000 BYTE),
TIME NUMBER,
qblock_name VARCHAR2(30 BYTE),
remarks VARCHAR2(4000 BYTE),
other_xml CLOB
) PARTITION BY LIST (snap_id)
(PARTITION sqlplan_snap_replay_id1 VALUES (1));

CREATE INDEX sqlplan_snap_replay__sqlid_idx ON sqlplan_snap_replay(sql_id, child_address)LOCAL;

--REPORT TABLES

-- rep1 show summary statistics

CREATE TABLE rap_rep1(
snap_id NUMBER,
stat_name VARCHAR2(50),
measure_workload VARCHAR2(50),
measure_replay VARCHAR2(50),
percentage_diff NUMBER);

CREATE INDEX rap_rep1_idx
ON rap_rep1(snap_id);

-- rep2 show total number of queries with different execution plan

CREATE TABLE rap_rep2(
snap_id NUMBER,
total_checked NUMBER,
total_dif_plan NUMBER,
percentage_diff_plan NUMBER,
total_cap_work NUMBER,
total_cap_replay NUMBER,
total_not_compared NUMBER);

CREATE INDEX rap_rep2_idx
ON rap_rep2(snap_id);

-- rep3 show queries with different execution plans and statistics difference

CREATE TABLE rap_rep3(
snap_id NUMBER,
sql_id VARCHAR2(13 BYTE),
percent_disk NUMBER,
percent_buffer NUMBER,
percent_cpu_time NUMBER,
percent_elapsed NUMBER,
percent_rows_processed NUMBER);

CREATE INDEX rap_rep3_idx
ON rap_rep3(snap_id);

-- rep4 show queries where the cpu_time and elapsed_time had an increment in more than 10%

CREATE TABLE rap_rep4(
snap_id NUMBER,
sql_id VARCHAR2(13 BYTE),
percent_disk NUMBER,
percent_buffer NUMBER,
percent_cpu_time NUMBER,
percent_elapsed NUMBER,
percent_rows_processed NUMBER);

CREATE INDEX rap_rep4_idx
ON rap_rep4(snap_id);

-- rep5 This report will show all the sql_ids for the queries where the replay failed

CREATE TABLE rap_rep5
(snap_id NUMBER,
sql_id VARCHAR2(13 BYTE),
phase VARCHAR2(30),
exectype NUMBER,
errornum NUMBER,
errormsg VARCHAR2(400));

CREATE INDEX rap_rep5_idx
ON rap_rep5(snap_id);


PROMPT "DON'T FORGET TO CREATE THE DBLINKS"