time the performance enhancements of native dynamic SQL. : Utility Package « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Utility Package

time the performance enhancements of native dynamic SQL.

 
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>

 

Related examples in the same category