Parsing - Troubleshooting Oracle Performance - page 422

■ Note the processing shown in Figure 12-4 is later referred to as test case 3 .

With this enhancement, as shown in Figure 12-5 , the response time, compared with test case 1 and test case 2,
decreased by about 61% and 33%, respectively. Significantly, the real difference was made not by the reduction of the
CPU time spent to parse (which is already very low in test case 2) but by the reduction of the wait for SQL*Net message
from client . This means you are saving resources either in the network or in the client, or possibly both.

Figure 12-5. Comparison of the database-side resource usage profile for the three test cases.
(Components that account for less than 1% of the response time aren't displayed because they wouldn't be visible.)

In test case 2, the processing of soft parses at the database level lasted about one-tenth of a second. The question
is, where does the improvement come from? It surely doesn't come from the reduction of resource utilization at the
database level. You might intuitively think that the gain is because of fewer round-trips between the client and the
server. However, by looking at the number of waits for SQL*Net message from client and SQL*Net message to
client , you can see that there is no difference among the three test cases. In each case, there are 10,000 round-trips.
This is significant because 10,000 executions are performed, and therefore, this implies that in this particular case, all
necessary calls (among others, the parse, execute, and fetch calls) are packed into one single SQL*Net message by the
client driver. There is, however, a difference in the network layer because of the size of the messages sent between the
client and the server. You can use the following query to get information about them:

SELECT sn.name, ss.value
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name LIKE 'bytes%client'
AND ss.sid = 42

■ at the sQL statement level, it's not possible to check the amount of data sent between the client and the server.
For this reason, the previous query retrieves statistics at the session level. in this specific case, it's not a problem doing
so because i can make sure that the session i'm looking at is executing only the sQL statements of my test case. in
addition, i run the query against the dynamic performance views from another session.

Note

Figure 12-6 shows the network traffic for the three test cases. It's important to notice how switching to prepared
statements in test case 2 slightly increases the size of the messages received from the database engine. The most