Significant performance improvement with the use of bind variables

We had a user who needed to run an adhoc report for 24k account numbers. The structure of the account table was as follows:

COL_A NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_B NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_C NUMBER(4,0) DEFAULT ON NULL 0 NOT NULL ENABLE
About 100 other columns

The table had 60 million rows and occupied about 34 gigabytes of space.

There was an index on COL_A, COL_B, COL_C plus other trailing columns.

The list of account numbers was provided as
0020510043
1024100289
And so on

A new list of account numbers may be provided every day or multiple times a day and hence the process needed to be repeatable. The first attempt was to loop through this list and execute SQL statement such as:

  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = 002 
    and     col_b = 051
    and     col_c = 0043
  ;
  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = 102
    and     col_b = 410
    and     col_c = 0289
  ;

and so on 24k times. The run time for this execution was about 300 minutes.

I changed the code to use bind variables as shown below and the execution time dropped to 22 minutes.

#!/bin/ksh
PATH=$PATH:/oracle_home/12.1.0.2
export ORACLE_SID=PROD4

function run_the_sql
{
  #echo 'run_the_sql - start' `date`
  
  /oracle_home/12.1.0.2/bin/sqlplus -s / as sysdba << EOF >> report.txt
  set linesize 150
  set heading off
  set pagesize 50000; 
  set echo off; 
  set feedback off; 
  
  var    col_a_bind number;
  var    col_b_bind number;
  var    col_c_bind number;
  
  exec :col_a_bind :=${col_a_os};
  exec :col_b_bind :=${col_b_os};
  exec :col_c_bind :=${col_c_os};
  
  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = :col_a_bind 
    and     col_b = :col_b_bind
    and     col_c = :col_c_bind
  ;
  exit
EOF
  
  #echo 'run_the_sql - end  ' `date`
}


#
## Main processing
#
clear

echo 'start : ' `date`
`rm report.txt`

temp_ctr=0

while IFS=',' read -r -u3 acct_num
do  
   col_a_os=`expr substr $acct_num 1 3`
   col_b_os=`expr substr $acct_num 4 3`
   col_c_os=`expr substr $acct_num 7 4`
   run_the_sql
   temp_ctr=temp_ctr+1
   if  [temp_ctr -gt 1000 ]; then
       print '1000 done' `date`
       temp_ctr=0
   fi
done 3< list_of_tf.txt
echo 'end   : ' `date`

Author: Dean Capps

Database consultant at Amazon Web Services. View all posts by Dean Capps