conn hr/hr

set timing on
set serveroutput on

declare

procedure step_1 is
  narray_size pls_integer := 10 ;
  usermsg message_array_t := message_array_t(
                            message_t(123456790, 'TONGUC DEMO1', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO2', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO3', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO4', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO5', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO6', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO7', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO8', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO9', SYSDATE),
                            message_t(123456790, 'TONGUC DEMO0', SYSDATE) );
    msg_prop                DBMS_AQ.message_properties_t;
	msg_prop_array          DBMS_AQ.message_properties_array_t;
begin
    msg_prop_array := DBMS_AQ.message_properties_array_t(msg_prop, msg_prop,
	                        msg_prop, msg_prop,
							msg_prop, msg_prop,							
	                        msg_prop, msg_prop,
							msg_prop, msg_prop );
	for i in 1 .. 1000 loop
		pkg_queue.prc_enqueue_array(usermsg, narray_size, msg_prop_array);
		pkg_queue.prc_dequeue_array(narray_size);
	end loop;
end;

procedure step_2 is

   CURSOR s_cur IS
      SELECT * FROM tbl_message_copy;
   s_array     pkg_iot.fetch_array;
   narray_size PLS_INTEGER := 10;
BEGIN
   FOR i IN 1 .. 1000 LOOP
      FOR i IN 1 .. narray_size LOOP
         INSERT INTO tbl_message_copy
         VALUES
            (seq_queue_id.NEXTVAL, 123456790, 'TONGUC DEMO' || i, SYSDATE, 0);
      END LOOP;
      OPEN s_cur;
      FETCH s_cur BULK COLLECT
         INTO s_array LIMIT narray_size;
      pkg_iot.prc_enqueue_array(s_array,
                                narray_size);
      pkg_iot.prc_dequeue_array(narray_size);
      CLOSE s_cur;
      COMMIT;
   END LOOP;
end;

begin
runstats_pkg.rs_start;

step_1;

runstats_pkg.rs_middle;

step_2;

runstats_pkg.rs_stop(10000);
end;
/

Run1 ran in 1242 hsecs
Run2 ran in 1412 hsecs
run 1 ran in 87,96% of the time

Name                                  Run1        Run2        Diff
STAT...sorts (rows)                 12,330       2,070     -10,260
STAT...table fetch by rowid         11,721           0     -11,721
LATCH.enqueue hash chains            8,487      20,715      12,228
LATCH.sort extent pool                   0      13,658      13,658
LATCH.library cache lock            14,131          36     -14,095
LATCH.session allocation            24,284       8,048     -16,236
STAT...table scan rows gotten       27,665      10,030     -17,635
LATCH.channel operations paren          57      18,702      18,645
STAT...calls to get snapshot s      14,982      34,425      19,443
LATCH.shared pool                   24,018       4,013     -20,005
LATCH.row cache objects              7,025      30,488      23,463
STAT...consistent changes               37      30,032      29,995
LATCH.sequence cache                     0      30,100      30,100
LATCH.redo allocation               72,794      37,255     -35,539
LATCH.object queue header oper       4,627      42,033      37,406
STAT...redo entries                 65,995      26,881     -39,114
STAT...db block gets               122,496      67,830     -54,666
STAT...db block gets from cach     122,496      67,830     -54,666
STAT...db block changes            156,242      82,804     -73,438
LATCH.cache buffers chains         628,207     750,242     122,035
STAT...session uga memory max      392,880     254,380    -138,500
STAT...session logical reads       140,460     289,804     149,344
STAT...no work - consistent re      10,983     200,483     189,500
STAT...consistent gets              17,964     221,974     204,010
STAT...consistent gets from ca      17,964     221,974     204,010
STAT...session uga memory          392,880      65,464    -327,416
STAT...session pga memory max    1,376,256     262,144  -1,114,112
STAT...session pga memory        1,376,256     131,072  -1,245,184
STAT...IMU Redo allocation siz   3,141,056      64,352  -3,076,704
STAT...undo change vector size   8,664,060   3,262,660  -5,401,400
STAT...IMU undo allocation siz  14,317,820   3,946,624 -10,371,196
STAT...redo size                21,797,668   7,830,096 -13,967,572

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
977,158   1,200,023     222,865     81.43%

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.74