Building on the demo PLSQL procedure in my previous
SQLs in PLSQL post, I show how Oracle converts 3 "similar" statements to the "same" statement !
If I create 3 "similar" INSERT statements in my procedure and execute it as :
SQL> create or replace procedure PLSQL_SQL_bind_2
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 l_entry_id := l_entry_id+100000;
16 insert into LOG_TABLE VALUES
17 (l_entry_id, l_entry_text);
18 l_entry_id := l_entry_id + 200000;
19 insert into
20 Log_Table
21 Values
22 (L_Entry_ID,
23 L_Entry_TEXT);
24 end loop;
25 commit;
26 end;
27 /
Procedure created.
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_2;
PL/SQL procedure successfully completed.
SQL>
I find that tkprof of the Trace shows :
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.25 0.24 0 5407 0 3697
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3700 0.26 0.25 0 5407 0 3697
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
INSERT INTO LOG_TABLE
VALUES
(:B2 , :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 11091 0.43 0.40 0 208 12239 11091
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11094 0.43 0.40 0 208 12239 11091
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Exactly the same "INSERT" statement executed 11,091 (i.e. 3697*3) times !
But why are there 3 different PARSEs ?
This is what the raw Trace file shows :
PARSING IN CURSOR #12 len=99 dep=1 uid=64 oct=3 lid=64 tim=1241463209324952 hv=608974888 ad='8f7363d0'
SELECT OBJECT_ID, OWNER||OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'VIEW' ORDER BY OBJECT_ID
END OF STMT
PARSE #12:c=12998,e=12188,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,tim=1241463209324936
EXEC #12:c=0,e=131,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1241463209325440
FETCH #12:c=219967,e=215466,p=0,cr=5407,cu=0,mis=0,r=1,dep=1,og=1,tim=1241463209541097
PARSING IN CURSOR #10 len=41 dep=1 uid=64 oct=2 lid=64 tim=1241463209542024 hv=4147238877 ad='942837f8'
INSERT INTO LOG_TABLE VALUES (:B2 , :B1 )
END OF STMT
PARSE #10:c=0,e=441,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1241463209542012
PARSING IN CURSOR #13 len=227 dep=2 uid=0 oct=3 lid=0 tim=1241463209543228 hv=2190775527 ad='8f5fb7c8'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#
END OF STMT
PARSE #13:c=0,e=416,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1241463209543221
EXEC #13:c=3999,e=4077,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1241463209547660
FETCH #13:c=0,e=105,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=1241463209547905
EXEC #10:c=7998,e=7968,p=0,cr=2,cu=22,mis=1,r=1,dep=1,og=1,tim=1241463209550338
PARSING IN CURSOR #2 len=41 dep=1 uid=64 oct=2 lid=64 tim=1241463209550701 hv=4147238877 ad='942837f8'
INSERT INTO LOG_TABLE VALUES (:B2 , :B1 )
END OF STMT
PARSE #2:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1241463209550695
EXEC #2:c=1000,e=153,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,tim=1241463209551180
PARSING IN CURSOR #3 len=41 dep=1 uid=64 oct=2 lid=64 tim=1241463209551343 hv=4147238877 ad='942837f8'
INSERT INTO LOG_TABLE VALUES (:B2 , :B1 )
END OF STMT
PARSE #3:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1241463209551338
EXEC #3:c=0,e=113,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,tim=1241463209551868
FETCH #12:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=1241463209552009
EXEC #10:c=0,e=39,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552170
EXEC #2:c=0,e=28,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552298
EXEC #3:c=0,e=25,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552414
FETCH #12:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=1241463209552517
EXEC #10:c=0,e=31,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552631
EXEC #2:c=0,e=38,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552760
EXEC #3:c=0,e=24,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=1241463209552873
with the Cursors repeatedly being executed as :
FETCH #12
EXEC #10
EXEC #2
EXEC #3
Cursor 12 is the SQL for the PLSQL Cursor c1 in my procedure.
Cursors 10, 2 and 3 are the three INSERT statements.
Note that all three have the same HashValue (hv=4147238877) !
Oracle's PLSQL engine converted the 3 INSERT statements (which are literally dissimilar) into the exactly same SQL statement which needed a Hard Parse only once and Soft Parse twice thereafter. Then, for each of the 11,091 Executions, no further parse was needed.
Go back and look at the 3 different INSERT statements in the procedure and note the single INSERT statement (hv=4147238877) !
Thus, Oracle is smart enough to convert SQL statements in a PLSQL procedure (or block) into *reusable* SQLs which do not need to be parsed 11thousand times.
.
.
.