====================================================================================
Hands-on exercise - Bind variables

Introduction.
Bind variables is one of the most often neglected by developers feature of database
programming interfaces. Their lack frequently leads to less efficient, and what is
even more important less scalable applications.
Each SQL statement needs to be parsed before execution. This process is not only CPU
intensive but also causes some kind of serialization of transactions' access to
shared memory structures like cache library. Mentioned serialization is implemented
with use of latches which are kind of memory locks. General rule is: the more latches
transaction tries to acquire, the biggest serialization and the worse scalability.
SQL Statements without bind variables require so-called hard parsing and need several
times more latches then similar statements using bind variables. Additionally 
statements with bind variables need to be hard-parsed only once and then results of
this parsing phase can be reused which requires much less CPU.

The goal of this exercise is to show:
1. Performance gain from using bind variables with DML statements.
2. Performance gain from using bind variables with SELECT statements.
3. (OPTIONAL) Impact of not using bind variables on scalability.
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

0. Prepare the environment. 

All the tables and procedures necessary to perform the exercies can be created
with use of the 'prepare_environment.sql' script. In order to execute it perform 
the following command from SQL*Plus console:

SQL> @G:\Applications\Oracle\Training\Workshop\Tuning\Bind_variables\prepare_environment.sql

Ignore any ORA-00942 errors.


------------------------------------------------------------------------------------

1. See performance gain from using bind variables with DML statements.

Very often applications insert many rows with different values into the same columns 
of the same table. Such INSERT queries are usually very good candidates for using 
bind variables. Thanks to them hard parsing of such queries has to be done only 
once at the beginning.

In order to show the difference in terms of performance between INSERT statements
using and not using bind variables we will use two following procedures:

-- Procedure inserting data without using bind variables.
CREATE OR REPLACE PROCEDURE no_bv_inserts AS
  productid NUMBER(6);
  description VARCHAR2(30);

  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  description := 'some description';

  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    productid := 300000 + i;
    EXECUTE IMMEDIATE 'INSERT INTO PRODUCT VALUES (' || productid || ', ''' || description || ''')';
  END LOOP;
 
  COMMIT;

  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;

-- Procedure inserting data using bind variables
CREATE OR REPLACE PROCEDURE bv_inserts AS
  productid NUMBER(6);
  description VARCHAR2(30);

  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  description := 'some description';

  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    productid := 300000 + i;
    EXECUTE IMMEDIATE 'INSERT INTO PRODUCT VALUES (:prodid, :describ)' USING productid, description;
  END LOOP;

  COMMIT;
 
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;


They are inserting 10000 rows into the PRODUCT table in one transaction. The total 
time of the operation is then printed on the screen.

The 'sequential_inserts_test.sql' script placed in the bind_variables directory
performs the whole test with use of procedures showed above.
 
SQL> @G:\Applications\Oracle\Training\Workshop\Tuning\Bind_variables\sequential_inserts_test.sql

Each procedure is executed twice to show that difference in performance does not
strongly depend on sequence in which they are executed. Between execution of 
the procedures clean up of the PRODUCT table is performed.

Note: Be patient - the whole test can take even a few minutes.


------------------------------------------------------------------------------------

2. See performance gain from using bind variables with SELECT statements.

Also using bind variables with SELECT statement can bring improvement in terms of
performance. Very often applications contain queries that are executed multiple times
with different values in the WHERE clause. Such statements are very good candidates
for using bind variables.

In order to show the difference in terms of performance between SELECT statements
using and not using bind variables we will use two following procedures:


- Procedure selecting data without using bind variables

CREATE OR REPLACE PROCEDURE NO_BV_SELECTS AS
  TYPE rc IS REF CURSOR;
  cur rc;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    OPEN cur FOR
      'SELECT ename FROM emp WHERE empno = ' || to_char(i);
    CLOSE cur;
  END LOOP;
  
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;


-- Procedure selecting data using bind variables

CREATE OR REPLACE PROCEDURE "BV_SELECTS" AS
  TYPE rc IS REF CURSOR;
  cur rc;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    OPEN cur FOR
      'SELECT ename FROM emp WHERE empno = :x' using i;
    CLOSE cur;
  END LOOP;
  
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;


They open 10000 cursors based on SELECT statements executed against 
the EMP table. The total time of the operation is then printed on the screen.

The 'sequential_selects_test.sql' script placed in the bind_variables directory
performs the whole test with use of procedures showed above.
 
SQL> @G:\Applications\Oracle\Training\Workshop\Tuning\Bind_variables\sequential_selects_test.sql

Each procedure is executed twice to show that difference in performance does not
strongly depend on sequence in which they are executed. 

Note: Be patient - the whole test can take even a few minutes.

------------------------------------------------------------------------------------

3. (OPTIONAL) See impact of not using bind variables on scalability.

NOTE: In principle this test should be performed in a single user environment to
exclude mutual influence of sessions of different users which might bring 
unpredictable results. For that reason this exercise has been marked as optional.

Finally have a look at influence of using bind variables on scallability of 
an application. To show this we will submit different numbers of concurrent jobs
inserting rows into different tables. Each row will be inserted in a different 
transaction (to simulate bigger concurrency). We will use the following procedures:

CREATE OR REPLACE PROCEDURE NO_BV_CONCURRENCY(job_id NUMBER, jobs_no NUMBER) AS
  jp_rec job_parameters%ROWTYPE;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  SELECT * INTO jp_rec FROM job_parameters WHERE jobid = job_id;
  
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. jp_rec.iterations
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t' || jp_rec.table_index || ' VALUES (' || i || ')';
    COMMIT;
  END LOOP;
  stop_time := DBMS_UTILITY.GET_TIME;
  
  total_time := (stop_time - start_time) * 10;
  INSERT INTO test_results VALUES (job_id, jobs_no, jp_rec.iterations, 'NO', total_time);
  DELETE FROM job_parameters WHERE jobid = job_id;
  COMMIT;
END;



CREATE OR REPLACE PROCEDURE BV_CONCURRENCY(job_id NUMBER, jobs_no NUMBER) AS
  jp_rec job_parameters%ROWTYPE;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  SELECT * INTO jp_rec FROM job_parameters WHERE jobid = job_id;
  
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. jp_rec.iterations
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t' || jp_rec.table_index || ' VALUES (:x)' USING i;
    COMMIT;
  END LOOP;
  stop_time := DBMS_UTILITY.GET_TIME;
  
  total_time := (stop_time - start_time) * 10;
  INSERT INTO test_results VALUES (job_id, jobs_no, jp_rec.iterations, 'YES', total_time);
  DELETE FROM job_parameters WHERE jobid = job_id;
  COMMIT;
END;
 
Each of these procedures does the following things: 
- takes as the first argument job identifier, 
- uses this ID to retrieve from the JOB_PARAMETERS table the number of INSERTS 
  that it is supposed to perform,
- performs inserts,
- stores time of execution and related information in the TEST_RESULTS table,
- removes the row retrieved in step 2 from the JOB_PARAMETER table.

To drive the test we will use the CONCURRENCY_SIMULATION procedure which is shown
below:

CREATE OR REPLACE PROCEDURE CONCURRENCY_SIMULATION 
  (procedure_name VARCHAR2, jobs NUMBER, iterations NUMBER) 
  AUTHID current_user
AS
  job_id NUMBER;
  counter NUMBER;
BEGIN
  FOR i IN 1 .. jobs
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE T' || i;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
    EXECUTE IMMEDIATE 'CREATE TABLE T' || i || '(X NUMBER)';
  END LOOP;
  
  FOR i IN 1 .. jobs
  LOOP
    DBMS_JOB.SUBMIT(job_id, procedure_name || '(JOB, ' || jobs || ');');
    INSERT INTO job_parameters VALUES (job_id, iterations, i);
  END LOOP;
  
  COMMIT;
  
  LOOP
    DBMS_LOCK.SLEEP(5);
    SELECT COUNT(*) INTO counter FROM job_parameters;
    EXIT WHEN (counter = 0);
  END LOOP;

  FOR i IN 1 .. jobs
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE T' || i;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;


This procedure does the following:
- creates tables into which procedures described above will insert data
- submits number of jobs passed as the second argument. Each job simply executes
  BV_CONCURRENCY or NO_BV_CONCURRENCY depending of what was the first argument.
- adds appropriate entries to the JOB_PARAMETERS table.
- checks every 5 seconds if there are still running jobs (by querying the number
  of rows left in the JOB_PARAMETERS table)
- performs clean up.

To see impact of using or not using bind variables in the application on
performance in case of strong concurrency please execute the following commands:

SQL> exec concurrency_simulation('NO_BV_CONCURRENCY', 1, 100);
SQL> exec concurrency_simulation('BV_CONCURRENCY', 1, 100);

This will cause submission of one job executing respectively NO_BV_CONCURRENCY or
BV_CONCURRENCY procedure. Each procedure will perform 100 INSERT operations.

SQL> exec concurrency_simulation('NO_BV_CONCURRENCY', 100, 100);
SQL> exec concurrency_simulation('BV_CONCURRENCY', 100, 100);

In this case everything is the same except the number of concurrent jobs. Now we
have 100 of them.
Then you can check the results querying the TEST_RESULTS table:

COLUMN BIND_VARIABLES FORMAT A15

SELECT job_no, bind_variables, avg(execution_time) AS average_time 
FROM test_results GROUP BY job_no, bind_variables;



------------------------------------------------------------------------------------

4. Clean up the environment.

Execute the 'cleanup_environment.sql' script.

SQL> @G:\Applications\Oracle\Training\Workshop\Tuning\Bind_variables\cleanup_environment.sql

====================================================================================