Mega Code Archive
Time the performance enhancements of native dynamic SQL
<![CDATA[
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PACKAGE Timing AS
2 PROCEDURE StartTiming;
3 PROCEDURE StopTiming;
4 PROCEDURE PrintElapsed(p_Message IN VARCHAR2);
5 END Timing;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Timing AS
2 v_StartTime NUMBER;
3 v_EndTime NUMBER;
4
5 PROCEDURE StartTiming IS
6 BEGIN
7 v_StartTime := DBMS_UTILITY.GET_TIME;
8 END StartTiming;
9
10 PROCEDURE StopTiming IS
11 BEGIN
12 v_EndTime := DBMS_UTILITY.GET_TIME;
13 END StopTiming;
14
15 PROCEDURE PrintElapsed(p_Message IN VARCHAR2) IS
16 v_Elapsed NUMBER := (v_EndTime - v_StartTime) / 100;
17 BEGIN
18 DBMS_OUTPUT.PUT_LINE(
19 'Elapsed Time for ' || p_Message || ' is ' ||
20 v_Elapsed || ' seconds.');
21 END PrintElapsed;
22 END Timing;
23 /
Package body created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> TRUNCATE TABLE MyTable;
Table truncated.
SQL>
SQL> DECLARE
2 v_CursorID INTEGER;
3 v_Dummy NUMBER;
4 v_SQLStatement VARCHAR2(100) := 'INSERT INTO MyTable (num_col) VALUES (:num)';
5
6 c_NumRows CONSTANT NUMBER := 5000;
7 BEGIN
8 DELETE FROM MyTable;
9 COMMIT;
10
11 Timing.StartTiming;
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13
14 FOR v_Count IN 1..c_NumRows LOOP
15 DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE);
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':num', v_Count);
17 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
18 END LOOP;
19
20 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
21
22 Timing.StopTiming;
23 Timing.PrintElapsed('DBMS_SQL');
24
25 DELETE FROM MyTable;
26 COMMIT;
27
28 Timing.StartTiming;
29 FOR v_Count IN 1..c_NumRows LOOP
30 EXECUTE IMMEDIATE v_SQLStatement USING v_Count;
31 END LOOP;
32 Timing.StopTiming;
33 Timing.PrintElapsed('Native Dynamic SQL');
34 END;
35 /
Elapsed Time for DBMS_SQL is 1.04 seconds.
Elapsed Time for Native Dynamic SQL is .52 seconds.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
]]>