13 April, 2010

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.

.
.
.

7 comments:

moleboy said...

Then would you suggest this version
create or replace procedure testbind is
l_lit1 vharchar2(100);
l_lit2 vharchar2(100);
l_entry_id number;
l_entry_text varchar2(500);
cursor cl is
select 'myliteral1' as lit1,'myliteral2' as lit2,object_id, owner||object_name from dba_objects where object_type = 'VIEW' order by object_id;
BEGIN
open cl;
loop
fetch cl into l_lit1, l_lit2, l_entry_id, l_entry_text;
exit when cl%notfound;
insert into log_table values (l_lit1, l_lit2, l_entry_id, l_entry_text);
end loop;
END;


rather than this version:
create or replace procedure testbind is
l_entry_id number;
l_entry_text varchar2(500);
cursor cl is
select object_id, owner||object_name from dba_objects where object_type = 'VIEW' order by object_id;
BEGIN
open cl;
loop
fetch cl into l_entry_id, l_entry_text;
exit when cl%notfound;
insert into log_table values ('myliteral1', 'myliteral2', l_entry_id, l_entry_text);
end loop;
END;

Roberto said...

Why did you run "flush shared_pool" twice?

Hemant K Chitale said...

Roberto,
To be "safe".
Hemant

Roberto said...

Hemant,
for the same reason, do you happen also to launch twice the INSERTs?
;-)
(... without primary key of course)

Hemant K Chitale said...

Roberto,

I am demonstrating multiple executions of an INSERT -- in this case each insert *is* different (I don't repeat values). Yet Oracle doesn't require a reparse for statements which are converted to using binds when not using literals.

I am demonstrating the advantage of PLSQL and variables -- we avoid additional parse calls.

Hemant

Roberto said...

I was joking about your "to be safe"; you did not see my emoticons?
I know the argument.
Thank you anyway

Hemant K Chitale said...

Roberto,

Sorry, I missed that.

Hemant