/*
|| Oracle 11gR2: I/O Performance Tuning Series, Listing 8
||
|| Contains:
|| -- Examples of solid-state device (SSD) IO performance benchmarking
|| -- << enumerate others >>
||
|| that demonstrate the potential of SSDs for I/O performance boosting.
||
|| Author: Jim Czuprynski
||
*/
/*
|| Listing 8.1:
|| Creating test objects for HDD and SSD storage experiments
*/
/*
|| SSD-based objects
*/
-----
-- Create new tablespace
-----
DROP TABLESPACE tsp_ssd INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE tsp_ssd
DATAFILE '/u01/app/oracle/ssdonly/tsp_ssd.dbf'
SIZE 8G;
-----
-- Create new sequence
-----
DROP SEQUENCE sh.seq_ssd_sales_copy_pkid;
CREATE SEQUENCE sh.seq_ssd_sales_copy_pkid
MINVALUE 0
MAXVALUE 999999999999
START WITH 100
CACHE 1000000
NOCYCLE;
-----
-- Create new table in TSP_SSD tablespace
-----
DROP TABLE sh.ssd_sales_copy PURGE;
CREATE TABLE sh.ssd_sales_copy (
key_id NUMBER NOT NULL
,prod_id NUMBER NOT NULL
,cust_id NUMBER NOT NULL
,time_id DATE NOT NULL
,channel_id NUMBER NOT NULL
,promo_id NUMBER NOT NULL
,quantity_sold NUMBER NOT NULL
,amount_sold NUMBER NOT NULL
)
TABLESPACE tsp_ssd
NOLOGGING;
-----
-- Priming load
-----
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.sales;
COMMIT;
-----
-- Extend SH.SSD_SALES_COPY
-----
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.ssd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_ssd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.ssd_sales_copy;
COMMIT;
-- 7 iterations = xxx,xxx,xxx rows
-----
-- Create PK index and constraint
-----
ALTER TABLE sh.ssd_sales_copy DROP CONSTRAINT ssd_sales_copy_pk;
DROP INDEX sh.ssd_sales_copy_pk_idx;
ALTER TABLE sh.ssd_sales_copy
ADD CONSTRAINT ssd_sales_copy_pk
PRIMARY KEY (key_id)
USING INDEX (
CREATE INDEX sh.ssd_sales_copy_pk_idx
ON sh.ssd_sales_copy (key_id)
TABLESPACE tsp_ssd
PARALLEL 4
NOLOGGING
);
-----
-- Gather statistics
-----
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH', tabname => 'SSD_SALES_COPY', cascade => TRUE);
END;
/
/*
|| HDD-based objects
*/
-----
-- Create new tablespace
-----
DROP TABLESPACE tsp_hdd INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE tsp_hdd
DATAFILE '/u01/app/oracle/oradata/tsp_hdd.dbf'
--DATAFILE '+DATA'
SIZE 8G;
-----
-- Create new sequence
-----
DROP SEQUENCE sh.seq_hdd_sales_copy_pkid;
CREATE SEQUENCE sh.seq_hdd_sales_copy_pkid
MINVALUE 0
MAXVALUE 999999999999
START WITH 100
CACHE 1000000
NOCYCLE;
-----
-- Create new table in TSP_HDD tablespace
-----
DROP TABLE sh.hdd_sales_copy PURGE;
CREATE TABLE sh.hdd_sales_copy (
key_id NUMBER NOT NULL
,prod_id NUMBER NOT NULL
,cust_id NUMBER NOT NULL
,time_id DATE NOT NULL
,channel_id NUMBER NOT NULL
,promo_id NUMBER NOT NULL
,quantity_sold NUMBER NOT NULL
,amount_sold NUMBER NOT NULL
)
TABLESPACE tsp_hdd
NOLOGGING;
-----
-- Priming load
-----
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.sales;
COMMIT;
-----
-- Extend SH.HDD_SALES_COPY
-----
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
INSERT /*+ APPEND NOLOGGING PARALLEL(4) */ INTO sh.hdd_sales_copy
SELECT /* +PARALLEL(4) */ sh.seq_hdd_sales_copy_pkid.NEXTVAL, prod_id, cust_id, time_id,
channel_id, promo_id, quantity_sold, amount_sold
FROM sh.hdd_sales_copy;
COMMIT;
-----
-- Create PK index and constraint
-----
ALTER TABLE sh.hdd_sales_copy DROP CONSTRAINT hdd_sales_copy_pk;
DROP INDEX sh.hdd_sales_copy_pk_idx;
ALTER TABLE sh.hdd_sales_copy
ADD CONSTRAINT hdd_sales_copy_pk
PRIMARY KEY (key_id)
USING INDEX (
CREATE INDEX sh.hdd_sales_copy_pk_idx
ON sh.hdd_sales_copy (key_id)
TABLESPACE tsp_hdd
PARALLEL 4
NOLOGGING
);
-----
-- Gather statistics
-----
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH', tabname => 'HDD_SALES_COPY', cascade => TRUE);
END;
/
/*
|| Listing 8.2:
|| Moving online redo logs to SSD storage
*/
SQL> ALTER DATABASE
2 ADD LOGFILE GROUP 7
3 ('/u01/app/oracle/ssdonly/redo7a.log', '/u01/app/oracle/ssdonly/redo7b.log')
4 SIZE 128M REUSE;
Database altered.
SQL> ALTER DATABASE
2 ADD LOGFILE GROUP 8
3 ('/u01/app/oracle/ssdonly/redo8a.log', '/u01/app/oracle/ssdonly/redo8b.log')
4 SIZE 128M;
Database altered.
SQL> ALTER DATABASE
2 ADD LOGFILE GROUP 9
3 ('/u01/app/oracle/ssdonly/redo9a.log', '/u01/app/oracle/ssdonly/redo9b.log')
4 SIZE 128M;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Database altered.
SQL> SELECT group#, member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
7 /u01/app/oracle/ssdonly/redo7a.log
7 /u01/app/oracle/ssdonly/redo7b.log
8 /u01/app/oracle/ssdonly/redo8a.log
8 /u01/app/oracle/ssdonly/redo8b.log
9 /u01/app/oracle/ssdonly/redo9a.log
9 /u01/app/oracle/ssdonly/redo9b.log
6 rows selected.
» 
Comment and Contribute