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

The California Consumer Privacy Act: What You Need to Know


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