Sample Chapter: Efficient PL/SQL, Page 9
Bind Variables to the Rescue
Let's return to our example scenario from earlier in this section. We want to maximize the chances of an SQL statement being found in our log of previously processed SQL statements. In order to achieve that, we need to use bind variables. With a small change to the literals procedure, we can create a new procedure, binding, which takes advantage of bind variables.
SQL> create or replace 2 procedure binding is 3 c number; 4 p number; 5 x number; 6 x1 number; 7 begin 8 for i in 1 ..10000 loop 9 c :=dbms_sql.open_cursor; 10 dbms_sql.parse(c, 11 'select pid from people '|| 12 'where pid =:b1 ',dbms_sql.native);
Notice that the SQL statement that we parse never actually changes. It is only after we have parsed the statement that we assign a value to the bind variable. This is the key difference here. From the point of view of hard parsing, in this procedure we are running 10,000 identical SQL statements. We'll see shortly how this makes a large difference to how Oracle will process the statement.
13 dbms_sql.bind_variable(c,':b1',i); 14 x :=dbms_sql.execute(c); 15 x1 :=dbms_sql.fetch_rows(c); 16 dbms_sql.close_cursor(c); 17 end loop; 18 end; 19 / Procedure created.
We then run and trace the execution as per the previous test
SQL> alter session set sql_trace =true; Session altered. SQL> exec binding PL/SQL procedure successfully completed. SQL> alter session set sql_trace =false; Session altered.
A look at the trace file seems to show that we are still parsing (the parse count is still 10,000), but somehow the performance is dramatically improved.
select pid from people where pid =:b1 call count cpu elapsed disk query current rows ------- ------ ------ ------- ----- ------ ------- ----- Parse 10000 0.99 1.02 0 0 0 0 Execute 10000 1.17 1.26 0 0 0 0 Fetch 10000 0.77 0.56 0 20000 0 9999 ------- ------ ------ ------- ----- ------ ------- ----- total 30000 2.93 2.85 0 20000 0 9999
So the routine is still performing lots of parses—after all, we called the dbms_sql.parse routine 10,000 times, so the figures make sense. To explain where the performance benefit was obtained, we need to look at the session-level statistics. To do so, first we create a view that makes it easy to obtain session-level statistics.
SQL> create or replace 2 view V$MYSTATS as 3 select s.name,m.value 4 from v$mystat m,v$statname s 5 where s.statistic#=m.statistic#; View created. SQL> grant select on V$MYSTATS to public; Grant succeeded. SQL> create or replace public synonym V$MYSTATS for V$MYSTATS; Synonym created.
Now we can look at the statistics that correspond to parsing on the system.
SQL> select *from v$mystats 2 where name like 'parse%'; NAME VALUE ------------------------------ ---------- parse time cpu 107 parse time elapsed 137 parse count (total) 10019 parse count (hard) 2 parse count (failures) 0
The key statistic is "parse count (hard)." Although in our binding procedure we asked Oracle to parse a SQL statement 10,000 times, Oracle performed this very expensive task only twice—once for the execution of the binding procedure, and once for the first SQL statement parsed. The remaining 9,999 calls to parse did not require a full parse because the SQL statement is unchanged between executions. We reused the parsing information. This is soft parsing—an explicit parse call was issued but the SQL code found in the shared pool could be reused. That is the beauty of bind variables—the likelihood of an individual SQL statement being reused is far greater. We can do even better than that—as we saw in the binding procedure, the SQL statement we parsed did not change for each iteration of the loop. So if the SQL does not change between executions, we do not need to parse it again at all, and we can take the parse call out of the loop altogether, yielding the following revised solution.