I want to bulk up II

Here’s what I was talking about
Just quickly, why does this use EXECUTE IMMEDIATE to sum up the latches? Because this was done in a strictly controlled banking environment and lots of people don’t have access to the V$ views. So, if they can’t access the v$latch view, the package still works albeit just providing a crude clock speed comparison.

DROP VIEW my_test_view;
DROP TABLE my_test_table;
DROP TYPE tt_my_test_type;
DROP TYPE to_my_test_type;

CREATE OR REPLACE TYPE to_my_test_type AS OBJECT
(col1 NUMBER
,col2 NUMBER
,col3 NUMBER
,col4 NUMBER
,col5 NUMBER);
/

CREATE OR REPLACE TYPE tt_my_test_type
AS TABLE OF to_my_test_type;
/
CREATE TABLE my_test_table
(col1 NUMBER
,col2 NUMBER
,col3 NUMBER
,col4 NUMBER
,col5 NUMBER);

CREATE OR REPLACE VIEW my_test_view
AS
SELECT to_my_test_type(
col1
, col2
, col3
, col4
, col5) my_row
FROM my_test_table;

CREATE OR REPLACE PACKAGE my_test_package
AS
--
PROCEDURE my_test (
i_num_rows IN NUMBER DEFAULT 100
);
--
END;
/

CREATE OR REPLACE PACKAGE BODY my_test_package
AS
--
e_no_latch_access EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_no_latch_access,-942);
--
TYPE to_test IS RECORD
(testname VARCHAR2(100)
,timetaken NUMBER
,sumlatches NUMBER
,cnt NUMBER);
TYPE tt_test IS TABLE OF to_test INDEX BY PLS_INTEGER;
--
g_test tt_test;
g_rows NUMBER;
--
PROCEDURE p_start (
i_testname IN VARCHAR2
)
AS
v_ind NUMBER;
BEGIN
v_ind := NVL(g_test.LAST,0)+1;
g_test(v_ind).testname := i_testname;
g_test(v_ind).timetaken := DBMS_UTILITY.GET_TIME;
BEGIN
EXECUTE IMMEDIATE 'SELECT SUM(gets) FROM v$latch' INTO g_test(v_ind).sumlatches ;
EXCEPTION WHEN e_no_latch_access THEN g_test(v_ind).sumlatches := 0;
END;
END p_start;
--
PROCEDURE p_end
AS
v_latches NUMBER;
BEGIN
--
BEGIN
EXECUTE IMMEDIATE 'SELECT SUM(gets) FROM v$latch' INTO v_latches;
EXCEPTION WHEN e_no_latch_access THEN v_latches := 0;
END;
g_test(g_test.LAST).timetaken := DBMS_UTILITY.GET_TIME - g_test(g_test.LAST).timetaken;
g_test(g_test.LAST).sumlatches := v_latches - g_test(g_test.LAST).sumlatches;
SELECT count(*) INTO g_test(g_test.LAST).cnt FROM my_test_table;
END p_end;
--
PROCEDURE p_initialise
AS
BEGIN
DELETE FROM my_test_table;
g_test.DELETE;
p_start('Start');
p_end;
END p_initialise;
--
PROCEDURE p_report
AS
BEGIN
FOR i IN g_test.FIRST .. g_test.LAST
LOOP
IF g_test(i).testname != 'Start'
THEN
DBMS_OUTPUT.PUT_LINE('=======================');
DBMS_OUTPUT.PUT_LINE('Proc: '||g_test(i).testname);
DBMS_OUTPUT.PUT_LINE('Time: '||to_char(g_test(i).timetaken,'99999')||' hsecs; Latches: '||to_char(g_test(i).sumlatches,'9999999999999'));
DBMS_OUTPUT.PUT_LINE('Count: '||g_test(i).cnt);
END IF;
END LOOP;
END p_report;
--
PROCEDURE my_test_straight_sql
AS
BEGIN
--
p_start('my_test_straight_sql');
--
INSERT INTO my_test_table
SELECT ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
p_end;
--
END my_test_straight_sql;
--
PROCEDURE my_test_object_view1
AS
BEGIN
--
p_start('my_test_object_view1');
--
INSERT INTO my_test_view
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
p_end;
--
END my_test_object_view1;
--
PROCEDURE my_test_object_view2
AS
CURSOR my_cursor
IS
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
my_array tt_my_test_type;
--
BEGIN
--
p_start('my_test_object_view2');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO my_array;
--
INSERT INTO my_test_view
SELECT VALUE(t)
FROM TABLE( CAST (my_array AS tt_my_test_type)) t;
--
p_end;
--
END my_test_object_view2;
--
PROCEDURE my_test_object_view3
AS
CURSOR my_cursor
IS
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
my_array tt_my_test_type;
--
BEGIN
--
p_start('my_test_object_view3');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO my_array;
--
FORALL i IN my_array.FIRST .. my_array.LAST
INSERT INTO my_test_view
VALUES (my_array(i));
--
p_end;
--
END my_test_object_view3;
--
PROCEDURE my_test_forall
AS
--
CURSOR my_cursor
IS
SELECT ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
TYPE t_col1 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col2 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col3 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col4 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col5 IS TABLE OF my_test_table.col1%TYPE;
v_col1 t_col1;
v_col2 t_col2;
v_col3 t_col3;
v_col4 t_col4;
v_col5 t_col5;
BEGIN
--
p_start('my_test_forall');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO
v_col1,
v_col2,
v_col3,
v_col4,
v_col5;
--
FORALL i IN v_col1.FIRST .. v_col1.LAST
INSERT INTO my_test_table
VALUES (
v_col1(i),
v_col2(i),
v_col3(i),
v_col4(i),
v_col5(i)
);
--
p_end;
--
END my_test_forall;
--
PROCEDURE my_test_iterator
AS
--
-- inline view because otherwise it doesn't work in version
CURSOR my_cursor
IS
SELECT *
FROM (
SELECT ROWNUM col1
, ROWNUM+1 col2
, ROWNUM+2 col3
, ROWNUM+3 col4
, ROWNUM+4 col5
FROM DUAL
CONNECT BY ROWNUM < g_rows);
--
my_rec my_cursor%ROWTYPE;
--
BEGIN
--
p_start('my_test_iterator');
--
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec;
EXIT WHEN my_cursor%NOTFOUND;
--
INSERT INTO my_test_table
VALUES (
my_rec.col1,
my_rec.col2,
my_rec.col3,
my_rec.col4,
my_rec.col5
);
--
END LOOP;
--
p_end;
--
END my_test_iterator;
--
PROCEDURE my_test (
i_num_rows IN NUMBER DEFAULT 100
)
AS
BEGIN
--
g_rows := NVL(ABS(i_num_rows),0)+1;
--
p_initialise;
my_test_straight_sql;
my_test_object_view1;
my_test_object_view2;
my_test_object_view3;
my_test_forall;
my_test_iterator;
p_report;
--
END my_test;
--
END my_test_package;
/

set serveroutput on

BEGIN
my_test_package.my_test(10000);
END;
/

About these ads

One Response to I want to bulk up II

  1. Pingback: I want to bulk up I « OraStory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: