Efficiency Comparison Test of binding variables and not binding variables -- Study Notes of Oracle efficient design

To simulate the real environment, consider comparing multiple performance indicators in the case of concurrent use of Bound variables and not bound variables.

Statspack is used for testing. If this tool is not installed, refer to Oracle9i database performance tuning guide and reference Release 2.

 

Create Table job_parameters
(Jobid number primary key,
Iterations number,
Table_idx number );

 

The stored procedure without variable binding is as follows:

Create or replace procedure dont_bind (p_job in number)
As
Rochelle rec job_parameters % rowtype;
Begin
Select * into l_rec from job_parameters where jobid = p_job;
For I in 1 .. l_rec.iterations
Loop
Execute immediate
'Insert into T' | l_rec.table_idx |'
Values ('| I | ')';
Commit;
End loop;
Delete from job_parameters where jobid = p_job;
Commit;
End;
/

 

The stored procedure for binding variables is as follows:
Create or replace procedure BIND (p_job in number)
As
Rochelle rec job_parameters % rowtype;
Begin
Select * into l_rec from job_parameters where jobid = p_job;
For I in 1 .. l_rec.iterations
Loop
Execute immediate
'Insert into T' | l_rec.table_idx | 'values () 'using I;
Commit;
End loop;
Delete from job_parameters where jobid = p_job;
Commit;
End;
/

 

Grant the user the execution permission on dbms_lock. the user name and password must be modified as needed.

Conn sys/Oracle AS sysdba;

Grant execute on dbms_lock to system;

 

Grant the user the permission to execute the statspack package. The user name and password must be modified as needed.

Conn perfstat/perfstat;

Grant execute on statspack to system;

 

 

Simulate a stored procedure in a real environment:

Create or replace procedure simulation
(P_procedure in varchar2, p_jobs in number, p_iters in number)
Authid CURRENT_USER
As
Rochelle job number;
Rochelle CNT number;
Begin
For I in 1 .. p_jobs
Loop
Begin
Execute immediate 'drop table T' | I;
Exception
When others then NULL;
End;
Execute immediate 'create table T' | I | '(x INT )';
End loop;

For I in 1 .. p_jobs
Loop
Dbms_job.submit (l_job, p_procedure | '(job );');
Insert into job_parameters
(Jobid, iterations, table_idx)
Values (l_job, p_iters, I );
End loop;

Statspack. Snap;
Commit;
Loop
Dbms_lock.sleep (30 );
Select count (*) into l_cnt from job_parameters;
Exit when (l_cnt = 0 );
End loop;
Statspack. Snap;
End;
/

 

Test the concurrent execution of five users and insert 10 thousand rows of records:

Execute simulation ('dont _ bind );
Execute simulation ('bind );

 

Generate statspack report

@? /Rdbms/admin/spreport

 

The test results are excerpted as follows:

	Bind Variable not used	Bind Variable
Soft parse %:	1.46	93.69
% Non-Parse CPU	40.78	100.00
CPU time (s)	26	8
Hard parses (per second)	1,618.06	0.42
Executes (per second)	1,687.77	1,619.35
Transactions (per second)	1,613.42	1,613.42
Log File parallel write (waits)	8,291	4,881
Latch free (waits)	1,425	3

 

Test conclusion:

The advantage of variable binding is obvious, which can greatly reduce the number of hard resolution times and the number of latch waits for serial devices.