Oracle

Posted December 2, 2011

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter

~~Watch~~→

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

Featured Database Articles

Oracle

WEBINAR:On-Demand

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

WEBINAR:
On-Demand