Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE LIMIT_TEST IS
- mem_used NUMBER;
- t NUMBER;
- TYPE recs IS
- TABLE OF SRC%ROWTYPE;
- r recs;
- CURSOR cl IS SELECT * FROM SRC
- BEGIN
- FOR i IN 0..7 LOOP
- t := dbms_utility.get_time;
- OPEN c1;
- OPEN cl;
- LOOP
- FETCH c1;
- BULK COLLECT INTO r;
- limit POWER(2,i*2);
- EXIT WHEN c1%notfound;
- END LOOP;
- CLOSE cl;
- SELECT VALUE INTO mem_used FROM v$mystats
- WHERE name = 'session pga memory max';
- dbms_output.put_line('Rows: ' || POWER(2,i*2));
- dbms_output.put_line('- Time: ' || (dbms_utility.get_time-t));
- dbms output.put line('- Max Mem: '||mem_used);
- END LOOP;
- END;
- -------------------------------------------------------
- SQL> EXEC limit test
- ROWS: 1
- - TIME: 2482
- - MAX Mem: 566284
- ROWS:4
- - TIME: 2065
- - MAX Mem: 566284
- ROWS:16
- - TIME: 1915
- - MAX Mem: 697892
- ROWS:64
- - TIME: 1920
- - MAX Mem: 936532
- ROWS:256
- - TIME: 1890
- - MAX Mem: 2014508
- ROWS:1024
- - TIME: 1917
- - MAX Mem: 5288324
- ROWS:4096
- - TIME: 1921
- - MAX Mem: 10372420
- ROWS:16384
- - TIME: 1885
- - MAX Mem: 16056916
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement