11G

Database Replay test schema

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Wednesday, 24 March 2010 23:17
Last Updated on Monday, 12 March 2012 13:54
Published on Wednesday, 24 March 2010 23:17
Written by Guy Lambregts
Hits: 3836

Database Replay test schema



Setup Workload Sample Schema

 

See Database Replay using the command line

 

See Database Replay using the Database Control




In this example I create tables T_PRECIOUSMETALS_PM and T_EXCHANGERATE_ER.


SQL> create tablespace PM;

Tablespace created.

SQL> grant create session,create table,create sequence to PM identified by PM;

Grant succeeded.

SQL> alter user PM default tablespace PM temporary tablespace TEMP;

User altered.

SQL> alter user PM quota unlimited on PM;

User altered.

SQL> grant select any dictionary to pm;

Grant succeeded.

SQL> connect PM/PM
Connected.

SQL> create table T_PRECIOUSMETALS_PM (PM_PK varchar2(2),PM_NAME varchar2(20),PM_IS_PGM varchar2(3) not null, primary key (PM_PK)) organization index initrans 2 tablespace PM;

Table created.

SQL> insert into T_PRECIOUSMETALS_PM values ('RU','Ruthenium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('PD','Palladium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('OS','Osmium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('IR','Iridium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('PT','Platinium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('Au','Gold','NO');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('Ag','Silver','NO');

1 row created.

SQL> commit;

Commit complete.

SQL> create table T_EXCHANGERATE_ER (ER_PK number, ER_FK_PM varchar2(2) not null, ER_RATE number not null, ER_TIME timestamp default systimestamp not null , primary key (ER_PK)) organization heap initrans 6 tablespace PM;

Table created.

SQL> alter table T_EXCHANGERATE_ER add constraint FK_ER_PM foreign key (ER_FK_PM) references T_PRECIOUSMETALS_PM (PM_PK);

Table altered.

SQL> create index IDX_FK_ER_PM on T_EXCHANGERATE_ER ( ER_FK_PM ) initrans 6 tablespace PM;

Index created.

SQL> create sequence SEQ_ER cache 1000;




Populate workload schema



-- saved in C:\Data\Support\WEBSITE\workload_capture.sql


select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_exchangerate_er) pivot (avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1;


declare
begin
for i in 1 .. 100000 loop
insert into t_exchangerate_er values (seq_er.nextval,'Au',dbms_random.value (925,935),systimestamp);
end loop;
end;
/


commit;

select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_exchangerate_er) pivot (avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1;

declare
begin
for i in 1 .. 20000 loop
insert into t_exchangerate_er values (seq_er.nextval,'Ag',dbms_random.value (12,14),systimestamp);
end loop;
end;
/


commit;

select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_exchangerate_er) pivot (avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1;


declare
begin
for i in 1 .. 5000 loop
insert into t_exchangerate_er values (seq_er.nextval,'PT',dbms_random.value (1100,1150),systimestamp);
end loop;
end;
/


commit;


select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_exchangerate_er) pivot (avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1;


declare
begin
for i in 1 .. 1000 loop
insert into t_exchangerate_er values (seq_er.nextval,'PD',dbms_random.value (240,250),systimestamp);
end loop;
end;
/


commit;

set autotrace traceonly;

select * from t_exchangerate_er;

set autotrace off;

select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_exchangerate_er) pivot (avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1;


-- saved in C:\Data\Support\WEBSITE\workload_capture.sql