In this section, we will write an application that issues the same SQL statement over and over again. The first run of the application will not use bind variables. The second run of the application will use bind variables. We will compare results to see the use of bind variables. Before we can begin, we need a table to query from. We'll build that table with the following statement:
SQL> create table db_object as 2 select rownum as object_id,object_name from dba_objects; Table created.
We will query this table in our application. We used the ROWNUM pseudo-column in order to guarantee that we have sequential numbers without gaps for our object identifiers. This is important for our simple application.
We will now run our first application. The execution can be seen below:
SQL> set timing on SQL> alter session set sql_trace=true; Session altered. SQL> DECLARE 2 counter NUMBER; 3 val VARCHAR2(30); 4 BEGIN 5 counter := 1; 6 WHILE counter <=10000 LOOP 7 SELECT object_name INTO val FROM db_object WHERE object_id = counter; 8 counter := counter + 1; 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.02
The application above took a little over 1 second to run, to process 10,000 rows. Note that in this application, where select where the OBJECT_ID column is equal to the value in the COUNTER variable. PL/SQL applications will use bind variables automatically when dealing with variables. This application uses bind variables.
Our second application does not use bind variables. It is shown below:
SQL> alter session set sql_trace=true; Session altered. SQL> set timing on SQL> DECLARE 2 counter NUMBER; 3 stmt VARCHAR2(80); 4 BEGIN 5 counter := 1; 6 WHILE counter <=10000 LOOP 7 stmt := 'SELECT object_id FROM db_object WHERE object_id = '||counter; 8 EXECUTE IMMEDIATE stmt; 9 counter := counter + 1; 10 END LOOP; 11 END; 12 / PL/SQL procedure successfully completed. Elapsed: 00:00:05.56
This application does the same body of work, but this time, we create a dynamic SQL statement to force us to NOT use bind variables. Since this application did not use bind variables, it ran in almost approximately 5.5 seconds. The absence of bind variables made our second application run 545% longer! Keep in mind that our application is relatively simple. The difference between 1 second and 5.5 seconds is not that much. But in a more complex application, that difference of 545% could seem like a lifetime.
To complete our analysis, we will examine the trace files that we generated in each run. We run the trace files though the TKPROF utility. Then, we'll look at the end of the trace file for each application. The end of the trace file for the first application looks like the following:
call count cpu elapsed disk query current rows ------- ------ -------- -------- -------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 10000 0.38 0.31 0 0 0 0 Fetch 10000 0.27 0.30 0 30020 0 10000 ------- ------ -------- -------- -------- ---------- ---------- ---------- total 20001 0.65 0.62 0 30020 0 10000
The output shown above shows the overall totals for the work our application has done. Notice that in the output above, the application only had to parse the SQL statement once. But there were 10,000 executions and fetches. This shows that bind variables were in use. Now, we'll look at the corresponding section of the trace file for the application that does not use bind variables.
call count cpu elapsed disk query current rows ------- ------ -------- -------- -------- ---------- ---------- ---------- Parse 10000 5.29 5.01 0 0 0 0 Execute 10000 0.35 0.25 0 0 0 0 Fetch 10000 0.27 0.30 0 30020 0 10000 ------- ------ -------- -------- -------- ---------- ---------- ---------- total 20000 5.91 5.56 0 30020 0 10000
The output above shows 10,000 parses, executions, and fetches. With those statistics, we an clearly see that bind variables were not in use. Also notice that of the 5.56 elapsed seconds, 5.01 seconds were from parsing alone. The parse phase contributed 90% of the total run time.
This simple example should show the power of using bind variables. Bind variables are a must to improve application performance. It is really important to learn how your development platform implements bind variables.
Bind Variables and 10046 Trace
Many times when dealing with application tuning, it is desirable to know the SQL statements that a user or an application is executing in the database. The user can issue the following command to start a trace of their session's activity:
alter session set sql_trace=true;
A trace file will be generated in the directory specified by the user_dump_dest initialization parameter. Unfortunately, many SQL statements in the trace file may look like the following:
PARSING IN CURSOR #4 len=56 dep=0 uid=5 oct=3 lid=5 tim=1069462157446467 hv=3566364369 ad='733c4908' select object_name from dba_objects where object_id = :x END OF STMT PARSE #4:c=0,e=406,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=1069462157446456
Notice that user correctly used a bind variable (:x) in this case. Unfortunately, we have no idea what bind variable value was used in this specific SQL statement. Oracle created the 10046 event to enable tracing of different levels for your sessions. The 10046 event has the following levels:
1 – Standard SQL tracing. Same as setting sql_trace=true.
4 – Same as level 1, but also shows bind variable values in the trace file
8 – Same as level 1, but also shows wait event information in the trace file
12 – Same as levels 4 and 8 together, 4+8 = 12.
To see bind variable values in our trace file, we can use the 10046 event with level 4 (or 12). The following command will turn on event 10046 with level 4:
alter session set events '10046 trace name context forever, level 4';
This will start a 10046 trace with level 4 in the user's session. Alternatively, you can use the DBMS_SYSTEM.SET_EV procedure to set an event in another user's session. The DBMS_SYSTEM package is owned by the SYS user with no public synonym, by default. So unless you have created a snyonym on this package, sign on as SYS or use the schema-dot notation to start the event in another user's session.
execute sys.dbms_system.set_ev( sid, serial, 10046, 4, '');
You will have to make appropriate substitutions for the sid and serial number of the session you want to start the trace for. Now that you've started a 10046 trace with a level sufficient to capture bind variable information, let's take a look at an example of how this data might look:
PARSING IN CURSOR #2 len=56 dep=0 uid=5 oct=3 lid=5 tim=1069463327026380 hv=3566364369 ad='733c4908' select object_name from dba_objects where object_id = :x END OF STMT PARSE #2:c=0,e=508,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1069463327026370 BINDS #2: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0000 size=24 off set=0 bfp=b6a7c228 bln=22 avl=03 flg=05 value=101
This is the same query we saw in an earlier trace file. This time, we have bind variable information as well. This section of the trace file is for cursor #2. We can see the entry for BINDS #2, which is bind variable information for cursor #2. The bind 0: line shows us information for the first bind variable (we start counting at 0). The most important piece of information is the value=101. This is the value of the bind variable for this specific execution of this SQL statement. If there were multiple bind variables for this SQL statement, then there would have been additional bind X: lines, where X is a number starting at 0 and increasing for enough bind variables. The value for that specific bind variable would be displayed. The bind variables are numbered in the order that they appear in the SQL statement.
Start the discussion at forums.toadworld.com