General
================================
Real Life Case Scenario.
For unclear reason, Instance was slow on performance.
after some investigation, it appears that a new trigger was created.
That Trigger was doing single row Insert.
The problem was that this Trigger was activated thousands times per hour, and it was not using bind variable.
As a result, the Library Cache was overflooded with INSERT statements, each one parsed and executed only once.
Below is a code example of not using, and then using, bind variables during INSERT , and the impact it has on performance.
Below is a same code, running in a loop 1-10000, and inserting rows to same tables.
Once without using bind variables.
Once with using bind variables.
The results are ten fold improved when using bind variables!
compare below data, 10 seconds vs 1 second.
================================
Code and Results
================================
PROCEDURE WriteAdminUtilTrace
(p_moduleName IN ADMIN_UTIL_TRACE.module%TYPE,
p_msg_level IN ADMIN_UTIL_TRACE.msg_level%TYPE,
p_msgText IN ADMIN_UTIL_TRACE.msg_text%TYPE
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ADMIN_UTIL_TRACE
(msg_seq, module, msg_level, msg_date, msg_text)
VALUES
(ADMIN_UTIL_TRACE_SEQ.NEXTVAL, p_moduleName, p_msg_level, SYSDATE, p_msgText);
COMMIT;
END WriteAdminUtilTrace;
---------------------------------------------------
PROCEDURE test_bind_variables IS
v_i NUMBER;
v_max NUMBER;
vColumnList VARCHAR2(1000);
vValueList VARCHAR2(1000);
vInsertSql VARCHAR2(1000);
v_module_name ADMIN_UTIL_TRACE.module%TYPE;
v_start_time DATE;
v_end_time DATE;
v_run_time_sec NUMBER;
BEGIN
v_module_name := 'TEST_BIND_VARIABLES';
v_max := 10000;
-------------------------------------------
-- Not Using Bind Variables
-------------------------------------------
v_start_time := SYSDATE;
WriteAdminUtilTrace(v_module_name,'I','Starting Non Bind Option with '||v_max||' INSERT rows at '||TO_CHAR(v_start_time,'YYYYMMDD hh24:mi:ss'));
FOR v_i IN 1..v_max LOOP
vColumnList := '(KEY1,IMSI,attr1)';
vValueList := '(' || ''''||'Key1_'||v_i ||''''||','||''''|| 'IMSI_'||v_i ||''''|| ',' ||''''|| 'attr1_'||v_i||''''|| ')';
vInsertSql := 'INSERT INTO SFI_CUSTOMER_PROFILE ' ||vColumnList ||' '||'VALUES '||vValueList;
EXECUTE IMMEDIATE vInsertSql;
END LOOP;
v_end_time := SYSDATE;
WriteAdminUtilTrace(v_module_name,'I','Finished at '||TO_CHAR(v_end_time,'YYYYMMDD hh24:mi:ss'));
v_run_time_sec := ROUND((v_end_time-v_start_time)*24*60*60);
WriteAdminUtilTrace(v_module_name,'I','Time to execite (sec) '||v_run_time_sec);
ROLLBACK;
-------------------------------------------
-- Using Bind Variables
-------------------------------------------
v_start_time := SYSDATE;
WriteAdminUtilTrace(v_module_name,'I','Starting Bind Option with '||v_max||' INSERT rows at '||TO_CHAR(v_start_time,'YYYYMMDD hh24:mi:ss'));
FOR v_i IN 1..v_max LOOP
vColumnList := '(KEY1,IMSI,attr1)';
vValueList := '(:b1,:b2,:b3)';
vInsertSql := 'INSERT INTO SFI_CUSTOMER_PROFILE ' ||vColumnList ||' '||'VALUES '||vValueList;
EXECUTE IMMEDIATE vInsertSql USING 'Key1_'||v_i, 'IMSI_'||v_i,'attr1_'||v_i;
END LOOP;
v_end_time := SYSDATE;
WriteAdminUtilTrace(v_module_name,'I','Finished at '||TO_CHAR(v_end_time,'YYYYMMDD hh24:mi:ss'));
v_run_time_sec := ROUND((v_end_time-v_start_time)*24*60*60);
WriteAdminUtilTrace(v_module_name,'I','Time to execite (sec) '||v_run_time_sec);
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
WriteAdminUtilTrace(v_module_name,'E','Unexpected Error. '||SQLERRM);
WriteAdminUtilTrace(v_module_name,'E','The SQL was :'||vInsertSql);
END test_bind_variables ;
END ADMIN_UTIL;
MSG_SEQ MODULE MSG_TEXT
------- -------------------- --------------------------------------
46 TEST_BIND_VARIABLES Starting Non Bind Option with 10000
INSERT rows at 20161103 13:09:41
47 TEST_BIND_VARIABLES Finished at 20161103 13:09:51
48 TEST_BIND_VARIABLES Time to execite (sec) 10
49 TEST_BIND_VARIABLES Starting Bind Option with 10000
INSERT rows at 20161103 13:09:51
50 TEST_BIND_VARIABLES Finished at 20161103 13:09:52
51 TEST_BIND_VARIABLES 03/11/2016 Time to execite (sec) 1
