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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 2, 2011

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

By Jim Czuprynski

PL/SQL Objects

/*
|| Type Specification: sh.integer_return_array
|| Purpose: Implements Swingbench function return values interface
|| Author:  Dominic Giles
*/

CREATE OR REPLACE TYPE sh.integer_return_array 
    IS VARRAY(25) OF INTEGER
/

/*
|| Package Specification: sh.swingbench
|| Purpose: Specifies signatures for Swingbench functions and procedures for
||          application workload generation
|| Author:  Dominic Giles (reformatted and augmented for these demonstrations)
*/

CREATE OR REPLACE PACKAGE sh.swingbench AS
    FUNCTION storedprocedure1(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure2(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure3(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure4(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure5(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure6(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure7(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

    FUNCTION storedprocedure8(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
      RETURN integer_return_array;

END;
/

/*
|| Package Body: sh.swingbench
|| Purpose: Implements public functions and procedures for Swingbench 
||          application workload generation
|| Author:  Dominic Giles (reformatted and augmented for these demonstrations)
*/

CREATE OR REPLACE PACKAGE BODY sh.swingbench AS
    SELECT_STATEMENTS   INTEGER := 1;
    INSERT_STATEMENTS   INTEGER := 2;
    UPDATE_STATEMENTS   INTEGER := 3;
    DELETE_STATEMENTS   INTEGER := 4;
    COMMIT_STATEMENTS   INTEGER := 5;
    ROLLBACK_STATEMENTS INTEGER := 6;
    dml_array integer_return_array := integer_return_array();

    FUNCTION from_mills_to_tens(value INTEGER) 
      RETURN float 
    IS
        real_value float := 0;
    BEGIN
        real_value := value/1000;
        RETURN real_value;
    EXCEPTION 
        WHEN ZERO_DIVIDE THEN
            real_value := 0;
            RETURN real_value;
    END from_mills_to_tens;

    PROCEDURE sleep(
         min_sleep INTEGER
        ,max_sleep INTEGER) 
    IS
    BEGIN
        IF (((max_sleep - min_sleep) > 0) AND (min_sleep < max_sleep)) THEN
            DBMS_LOCK.SLEEP(DBMS_RANDOM.VALUE(from_mills_to_tens(max_sleep), from_mills_to_tens(min_sleep)));
        END IF;
    END sleep;

    PROCEDURE init_dml_array 
    IS
    BEGIN
        dml_array := integer_return_array();
        FOR i IN 1..7 
            LOOP
                dml_array.EXTEND;
                dml_array(i) := 0;
            END LOOP;
    END init_dml_array;

    PROCEDURE increment_selects(num_selects INTEGER)
    IS
    BEGIN
        dml_array(SELECT_STATEMENTS) := dml_array(SELECT_STATEMENTS) + num_selects;
    END increment_selects;

    PROCEDURE increment_inserts(num_inserts INTEGER)
    IS
    BEGIN
        dml_array(INSERT_STATEMENTS) := dml_array(INSERT_STATEMENTS) + num_inserts;
    END increment_inserts;

    PROCEDURE increment_updates(num_updates INTEGER)
    IS
    BEGIN
        dml_array(UPDATE_STATEMENTS) := dml_array(UPDATE_STATEMENTS) + num_updates;
    END increment_updates;

    PROCEDURE increment_deletes(num_deletes INTEGER)
    IS
    BEGIN
        dml_array(DELETE_STATEMENTS) := dml_array(DELETE_STATEMENTS) + num_deletes;
    END increment_deletes;

    PROCEDURE increment_commits(num_commits INTEGER) 
    IS
    BEGIN
        dml_array(COMMIT_STATEMENTS) := dml_array(COMMIT_STATEMENTS) + num_commits;
    END increment_commits;  

    PROCEDURE increment_rollbacks(num_rollbacks INTEGER) 
    IS
    BEGIN
        dml_array(ROLLBACK_STATEMENTS) := dml_array(ROLLBACK_STATEMENTS) + num_rollbacks;
    END increment_rollbacks;  

    /*
    || SSD Workloads
    */

    FUNCTION storedprocedure1(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure1
    -- Purpose:     Generates a random read workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nBegKey         NUMBER(15)    := 0;    
        nEndKey         NUMBER(15)    := 0;    
        nMinQtySold     NUMBER(15,2)  := 0;
        nMaxAmtSold     NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 250);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-SSD', action_name => 'RR');

        FOR i in 1..nIterations
            LOOP
                nBegKey := DBMS_RANDOM.VALUE(1, 120000000);
                nEndKey := nBegKey + DBMS_RANDOM.VALUE(1, 25);
                SELECT MIN(quantity_sold), MAX(amount_sold)
                  INTO nMinQtySold, nMaxAmtSold
                  FROM sh.ssd_sales_copy
                 WHERE key_id BETWEEN nBegKey AND nEndKey
                 ;
    
                sleep(min_sleep, max_sleep);
                
            END LOOP;

        RETURN dml_array;
    END storedprocedure1;

    FUNCTION storedprocedure2(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure2
    -- Purpose:     Generates a random write workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nBegKey         NUMBER(15)    := 0;    
        nEndKey         NUMBER(15)    := 0;    
        nQtyFct         NUMBER(15,2)  := 0;
        nAmtFct         NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 250);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-SSD', action_name => 'RW');

        FOR i in 1..nIterations
            LOOP
                nBegKey := DBMS_RANDOM.VALUE(1, 120000000);
                nEndKey := nBegKey + DBMS_RANDOM.VALUE(1, 25);
                nQtyFct := DBMS_RANDOM.VALUE(1, 15);
                nAmtFct := DBMS_RANDOM.VALUE(1, 15);
    
                UPDATE sh.ssd_sales_copy
    		       SET quantity_sold = quantity_sold * (1 + (nQtyFct / 100))
                      ,amount_sold = amount_sold * (1 + (nAmtFct / 100))
                 WHERE key_id BETWEEN nBegKey AND nEndKey
    			;
    			COMMIT;
                
                sleep(min_sleep, max_sleep);

            END LOOP;

        RETURN dml_array;
    END storedprocedure2;

    FUNCTION storedprocedure3(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure3
    -- Purpose:     Generates a sequential read workload (table scans) against 
    --              the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nMinQtySold     NUMBER(15,2)  := 0;
        nMaxAmtSold     NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := 1;
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-SSD', action_name => 'SR');

        FOR i in 1..nIterations
            LOOP
    			SELECT MIN(quantity_sold), MAX(amount_sold) 
          		  INTO nMinQtySold, nMaxAmtSold
    	          FROM sh.ssd_sales_copy
    	        ;

                sleep(min_sleep, max_sleep);

            END LOOP;

        RETURN dml_array;
    END storedprocedure3;

    FUNCTION storedprocedure4(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure4
    -- Purpose:     Generates a sequential write workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nQtyFct         NUMBER(15,2)  := 0;
        nAmtFct         NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 5);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-SSD', action_name => 'SW');

        FOR i in 1..nIterations
            LOOP
                nQtyFct := DBMS_RANDOM.VALUE(1, 15);
                nAmtFct := DBMS_RANDOM.VALUE(1, 15);
    
                UPDATE sh.ssd_sales_copy
    		       SET quantity_sold = quantity_sold * (1 + (nQtyFct / 100))
                      ,amount_sold = amount_sold * (1 + (nAmtFct / 100))
    			;
    			COMMIT;

                sleep(min_sleep, max_sleep);
                
            END LOOP;

        RETURN dml_array;
    END storedprocedure4;
    
    /*
    || HDD Workloads
    */

    FUNCTION storedprocedure5(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure5
    -- Purpose:     Generates a random read workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nBegKey         NUMBER(15)    := 0;    
        nEndKey         NUMBER(15)    := 0;    
        nMinQtySold     NUMBER(15,2)  := 0;
        nMaxAmtSold     NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 250);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-HDD', action_name => 'RR');

        FOR i in 1..nIterations
            LOOP
                nBegKey := DBMS_RANDOM.VALUE(1, 120000000);
                nEndKey := nBegKey + DBMS_RANDOM.VALUE(1, 25);
                SELECT MIN(quantity_sold), MAX(amount_sold)
                  INTO nMinQtySold, nMaxAmtSold
                  FROM sh.hdd_sales_copy
                 WHERE key_id BETWEEN nBegKey AND nEndKey
                 ;
    
                sleep(min_sleep, max_sleep);
                
            END LOOP;

        RETURN dml_array;
    END storedprocedure5;

    FUNCTION storedprocedure6(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure6
    -- Purpose:     Generates a random write workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nBegKey         NUMBER(15)    := 0;    
        nEndKey         NUMBER(15)    := 0;    
        nQtyFct         NUMBER(15,2)  := 0;
        nAmtFct         NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 250);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-HDD', action_name => 'RW');

        FOR i in 1..nIterations
            LOOP
                nBegKey := DBMS_RANDOM.VALUE(1, 120000000);
                nEndKey := nBegKey + DBMS_RANDOM.VALUE(1, 25);
                nQtyFct := DBMS_RANDOM.VALUE(1, 15);
                nAmtFct := DBMS_RANDOM.VALUE(1, 15);
    
                UPDATE sh.hdd_sales_copy
    		       SET quantity_sold = quantity_sold * (1 + (nQtyFct / 100))
                      ,amount_sold = amount_sold * (1 + (nAmtFct / 100))
                 WHERE key_id BETWEEN nBegKey AND nEndKey
    			;
    			COMMIT;
                
                sleep(min_sleep, max_sleep);

            END LOOP;

        RETURN dml_array;
    END storedprocedure6;

    FUNCTION storedprocedure7(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure7
    -- Purpose:     Generates a sequential read workload (table scans) against 
    --              the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nMinQtySold     NUMBER(21,2)  := 0;
        nMaxAmtSold     NUMBER(21,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := 1;
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-HDD', action_name => 'SR');

        FOR i in 1..nIterations
            LOOP
    			SELECT MIN(quantity_sold), MAX(amount_sold) 
          		  INTO nMinQtySold, nMaxAmtSold
    	          FROM sh.hdd_sales_copy
    	        ;

                sleep(min_sleep, max_sleep);

            END LOOP;

        RETURN dml_array;
    END storedprocedure7;

    FUNCTION storedprocedure8(
         min_sleep INTEGER
        ,max_sleep INTEGER
         ) RETURN INTEGER_RETURN_array 
    -----
    -- Function:    storedprocedure8
    -- Purpose:     Generates a sequential write workload against the specified table
    -- Author:      Jim Czuprynski (based on Dominic Giles's Swingbench sample template)
    -- Maintenance Log:
    -- Version	Resource	Comments
    -- -------  ----------  -------------------------------------------------------- 
    -- 1.0      JSC         2011-08-17: Initial version (built into SwingBench)
    -----
    IS
        nIterations     NUMBER(5)     := 0;
        nQtyFct         NUMBER(15,2)  := 0;
        nAmtFct         NUMBER(15,2)  := 0;
    BEGIN
        init_dml_array();
        nIterations := DBMS_RANDOM.VALUE(1, 5);
        DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'LG-HDD', action_name => 'SW');

        FOR i in 1..nIterations
            LOOP
                nQtyFct := DBMS_RANDOM.VALUE(1, 15);
                nAmtFct := DBMS_RANDOM.VALUE(1, 15);
    
                UPDATE sh.hdd_sales_copy
    		       SET quantity_sold = quantity_sold * (1 + (nQtyFct / 100))
                      ,amount_sold = amount_sold * (1 + (nAmtFct / 100))
    			;
    			COMMIT;

                sleep(min_sleep, max_sleep);
                
            END LOOP;

        RETURN dml_array;
    END storedprocedure8;
    
END;
/


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


















Thanks for your registration, follow us on our social networks to keep up-to-date