SQLs in PLSQL

A short demonstration of how SQLs embedded in PLSQL are handled.

I define a stored procedure that
a. Opens a Cursor and fetches from it
b. Runs an INSERT, using PLSQL variables for each row from the cursor
c. Runs three other INSERTs using literals

Note how the Parsing and Execution is handled for a. and b. versus c.
Also note how the SQL is converted to Upper Case.

First the test case :

SQL> REM Demonstrate that SQLs in PLSQL becomes Binds
SQL>
SQL> connect / as sysdba
Connected.
SQL> grant select on dba_objects to hemant;

Grant succeeded.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL>
SQL> drop table log_table purge;

Table dropped.

SQL> create table log_table (entry_id number, entry_text varchar2(500));

Table created.

SQL>
SQL> create or replace procedure PLSQL_SQL_bind
2  as
3  l_entry_id  number;
4  l_entry_text varchar2(500);
5
6  cursor c1 is
7  select object_id, owner||object_name from dba_objects where object_type = 'VIEW' order by object_id;
8
9  begin
10  open c1;
11  loop
12  fetch c1 into l_entry_id,l_entry_text;
13  exit when c1%NOTFOUND;
14  insert into log_table values (l_entry_id, l_entry_text);
15  end loop;
16  insert into LOG_TABLE values (0,'A Dummy');
17  insert into LOG_TABLE values (1,'Another');
18  insert into LOG_TABLE values (2,'A Third');
19  commit;
20  end;
21  /

Procedure created.

SQL>
SQL> show errors;
No errors.
SQL>
SQL> select count(*) from dba_objects where object_type = 'VIEW';

COUNT(*)
----------
    3697

SQL> alter session set sql_trace=TRUE;

Session altered.

SQL> execute PLSQL_sql_bind;

PL/SQL procedure successfully completed.

SQL> select 'x' from dual;

'
-
x

SQL> exit 

So, the cursor fetches 3,697 rows, causing the first INSERT to be executed 3,697 times.

This is what tkprof shows :

For the Cursor c1 :

SELECT OBJECT_ID, OWNER||OBJECT_NAME
FROM
DBA_OBJECTS WHERE OBJECT_TYPE = 'VIEW' ORDER BY OBJECT_ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3698      0.23       0.22          0       5407          0        3697
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3700      0.24       0.23          0       5407          0        3697

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 

For the INSERTs of the rows from the Cursor :

INSERT INTO LOG_TABLE
VALUES
(:B2 , :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   3697      0.14       0.14          0         36       4009        3697
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3698      0.14       0.14          0         36       4009        3697

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS 

For the 3 additional INSERTs with Literals :

INSERT INTO LOG_TABLE
VALUES
(0,'A Dummy')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

INSERT INTO LOG_TABLE
VALUES
(1,'Another')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

INSERT INTO LOG_TABLE 
VALUES
(2,'A Third')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 

Thus, we can see that when using PLSQL variables, Oracle replaces them with it's own generated Bind Variables (:B1 and :B2). Also note that there is a single Parse call for the first INSERT executed 3,697 times while the INSERTs with literals are parsed separately for each execution -- no bind variable substitution has occurred.

.
.
.