-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy path10g_optimize_cfl.sql
More file actions
89 lines (69 loc) · 2.23 KB
/
Copy path10g_optimize_cfl.sql
File metadata and controls
89 lines (69 loc) · 2.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
CONNECT SYS/SYS AS SYSDBA
grant select on source$ to SCOTT
/
CONNECT SCOTT/TIGER
SET SERVEROUTPUT ON
--------------------------------------------------------------------------------
-- Put the code in place.
-- source$ is a convenient table with lots of rows.
create or replace procedure putline ( approach in varchar2 , ol pls_integer) is
t0 integer; t1 integer;
cursor cur is select * from sys.source$;
one_row cur%rowtype;
type t is table of cur%rowtype index by pls_integer; many_rows t;
begin
t0 := Dbms_Utility.Get_Cpu_Time();
case approach
when 'implicit for loop' then
for j in cur loop
null;
end loop;
when 'explicit open, fetch, close' then
open cur;
loop
fetch cur into one_row;
exit when cur%NotFound;
end loop;
close cur;
when 'bulk fetch' then
open cur;
loop
fetch cur bulk collect into many_rows limit 100;
exit when many_rows.Count() < 1;
end loop;
close cur;
end case;
t1 := Dbms_Utility.Get_Cpu_Time();
Dbms_Output.Put_Line ('Timing for ' || approach ||
' with opt level ' || TO_CHAR (ol) || ' = ' || TO_CHAR (t1-t0 ));
end putline;
/
SET FEEDBACK OFF
--------------------------------------------------------------------------------
-- Time it at optimize levels 1 and 2
--
-- Level 1
alter procedure putline compile plsql_optimize_level=1
/
call putline( 'implicit for loop' , 1) -- 2073
/
call putline( 'explicit open, fetch, close' , 1) -- 2063
/
call putline( 'bulk fetch' , 1) -- 252
/
--------------------------------------------------------------------------------
-- Level 2
alter procedure putline compile plsql_optimize_level=2
/
call putline( 'implicit for loop' , 2) -- 263 <<== NOTE THE CHANGE !
/
call putline( 'explicit open, fetch, close' , 2) -- as for level 1
/
call putline( 'bulk fetch' , 2) -- as for level 1
/
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/