Comparison between oracle-bound variables and non-bound variables

Preparations before Case demonstration: -- alter system flush shared_pool;
-- Set timing on;
-- Starting from an empty sharing pool, if you operate these test statements multiple times, you need to clear the sharing pool each time,
Otherwise, the non-Bind Variable SQL will be cached, so it looks very fast to run www.2cto.com ---- case -- use non-Bind Variable
Declare
Type rc is ref cursor;
Rochelle rc;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop open l_rc
'Select object_name from all_objects where object_id = '| I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line (round (dbms_utility.get_time-l_start)/, 2) | 'Seconds ...');
End;
/---- Case -- bind a variable
Declare
Type rc is ref cursor;
Rochelle rc;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop open l_rc
'Select object_name from all_objects where object_id =: x' using I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line (round (dbms_utility.get_time-l_start)/, 2) | 'Seconds ...');
End;
/

Www.2cto.com
Non-bind variables take much longer than bind variables because each query of non-bind variables is a new query, that is, a query that has never been performed before in the database. Each query must go through analysis, restriction (name resolution), security check, optimization, and so on. When you use a variable-bound query, the value of the variable to be bound is provided during query execution. After a query is compiled, the query scheme is stored in the Shared Pool (database cache) and can be used for retrieval and reuse. Therefore, the difference between the two is huge. Therefore, in actual work, we should select based on the actual situation.