Sample Chapter: Efficient PL/SQL, Page 8
Nevertheless, the CPU cost is not the main problem here. Let's look at what happens when we run four versions of the literals procedure in four concurrent sessions on a machine with four CPUs. Even though we've seen that the parsing is expensive in terms of CPU, with four CPUs, we should be able to run the four programs in approximately the same elapsed time as the single case on a single CPU.
The literals procedure was slightly altered to include a unique tag for each SQL, so that each procedure in the four concurrent sessions will be executing unique SQL.
SQL> create or replace 2 procedure literals(tag number)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 t'||tag||'from people '|| 12 'where pid ='||i,dbms_sql.native); 13 x :=dbms_sql.execute(c); 14 x1 :=dbms_sql.fetch_rows(c); 15 dbms_sql.close_cursor(c); 16 end loop; 17 end; 18 /
We then ran "literals(1)" in the first session, "literals(2)" in the second, and so on. The elapsed times were as follows (remember these were run concurrently, not one after the other):
SQL> exec literals(1); Elapsed: 00:00:31.70 SQL> exec literals(2); Elapsed: 00:00:32.05 SQL> exec literals(3); Elapsed: 00:00:31.43 SQL> exec literals(4); Elapsed: 00:00:32.21
What happened here? Remember that when we ran just the single literals procedure, it took 29.68 seconds. Because we have 4 CPUs, theoretically, each CPU should have run flat out and returned control to the user after just under 30 seconds. Somewhere we lost a second or so. To see where time is lost for a session, we need to consult the V$SESSION_EVENT view to see the wait statistics.
Note: For a full description of the benefit of wait statistics, refer to the revolutionary Yet Another Performance Profiling Method by Anjo Kolk, Shari Yamaguchi, and Jim Viscusi at oraperf.veritas.com.
Whereas we would like either the CPU or disks to be active processing our requests, the wait statistics indicate the time where work could not be done. In each session we ran
SQL> select sid,event,time_waited 2 from v$session_event 3 where sid =... 4 and event ='latch free';
Where the SID was the unique session ID for each of the 4 sessions that ran the test. When the results were tabled, the following was observed:
SID EVENT TIME_WAITED ------- ----------- ----------- 43 latch free 79 44 latch free 72 45 latch free 69 46 latch free 87
Oracle has to protect access to the important memory structures to ensure that while one session is parsing, no other session can modify any of those memory structures on which parsing is dependent. We can see that approximately 0.8 seconds per session (3 percent) got wasted on the "latch free" event; that is, the sessions spent time waiting for their turn to access the common resources required to parse a SQL statement.
NOTE If you are using Oracle 9.2, there is a bug with session-level events—the SID value is incorrect by 1. Thus the wait event statistics for a session with (for example) SID=42 as viewed from V$SESSION will be found as SID=41 in V$SESSION_EVENT. This problem is fixed in version 10 and 184.108.40.206
Parsing doesn't just cost you vital CPU time, it stops other sessions from getting access to resources that they need, namely, the library and dictionary cache. You cannot solve this with more hardware—parsing stops you from getting the return you should be getting on the hardware you already have.