Advertisement
VoronVU

PROCEDURE LIMIT_TEST IS

Apr 3rd, 2015
2,533
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE LIMIT_TEST IS
  2.   mem_used NUMBER;
  3.   t NUMBER;
  4.   TYPE recs IS
  5.   TABLE OF SRC%ROWTYPE;
  6.   r recs;
  7.   CURSOR cl IS SELECT * FROM SRC
  8. BEGIN
  9.  FOR i IN 0..7 LOOP
  10.   t := dbms_utility.get_time;
  11.   OPEN c1;
  12. OPEN cl;
  13. LOOP
  14.   FETCH c1;
  15.   BULK COLLECT INTO r;
  16.   limit POWER(2,i*2);
  17.   EXIT WHEN c1%notfound;
  18. END LOOP;
  19.  CLOSE cl;
  20.  SELECT VALUE INTO mem_used FROM v$mystats
  21.    WHERE name = 'session pga memory max';
  22.  dbms_output.put_line('Rows: ' || POWER(2,i*2));
  23.  dbms_output.put_line('- Time: ' || (dbms_utility.get_time-t));
  24.  dbms output.put line('- Max Mem: '||mem_used);
  25. END LOOP;
  26. END;
  27. -------------------------------------------------------
  28. SQL> EXEC limit test
  29. ROWS: 1
  30. - TIME: 2482
  31. - MAX Mem: 566284
  32. ROWS:4
  33. - TIME: 2065
  34. - MAX Mem: 566284
  35. ROWS:16
  36. - TIME: 1915
  37. - MAX Mem: 697892
  38. ROWS:64
  39. - TIME: 1920
  40. - MAX Mem: 936532
  41. ROWS:256
  42. - TIME: 1890
  43. - MAX Mem: 2014508
  44. ROWS:1024
  45. - TIME: 1917
  46. - MAX Mem: 5288324
  47. ROWS:4096
  48. - TIME: 1921
  49. - MAX Mem: 10372420
  50. ROWS:16384
  51. - TIME: 1885
  52. - MAX Mem: 16056916
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement