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