Oracle

Posted December 2, 2011

Oracle 11gR2 I/O Performance Tuning: Using Solid State Drives - Page 2

By Jim Czuprynski

/*
|| 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.

Go to page: Prev 1 2 3 4 Next

Oracle Archives

0 Comments (click to add your comment)

Comment and Contribute

Latest Forum Threads
Oracle Forum
Topic	By	Replies	Updated
Oracle Data Mining: Classification	jan.hasller	0	July 5th, 07:19 AM
Find duplicates - Unique IDs	Lava	5	July 2nd, 08:30 AM
no matching unique or primary key	rcanter	1	April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger	Gladiator	3	February 29th, 06:01 PM

Advertiser Disclosure: Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.