dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 2, 2011

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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