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;
/
» 
Comment and Contribute
