Database Reference
In-Depth Information
The following example shows the output generated by the active_sessions.sql script when gathering the top
10 sessions during a single sample of 15 seconds (notice that the data is similar to Figure 4-18 ):
SQL> @active_sessions.sql 15 1 10
Time #Sessions #Logins SessionId Username Program Activity%
-------- --------- ------- --------------- -------------------- ---------------- ---------
19:14:49 117 0 195 SOE JDBC Thin Client 1.8
224 SOE JDBC Thin Client 1.5
225 SOE JDBC Thin Client 1.5
232 SOE JDBC Thin Client 1.5
7 SOE JDBC Thin Client 1.5
227 SOE JDBC Thin Client 1.4
74 SOE JDBC Thin Client 1.4
16 SOE JDBC Thin Client 1.4
171 SOE JDBC Thin Client 1.4
68 SOE JDBC Thin Client 1.4
Top-10 Total 14.9
Notice that the preceding output also shows, for every interval, the number of open sessions and logins. This
information is essential because the script can't detect the work performed by sessions that terminate during the
sample interval. So, watch out if you see a decreasing number of sessions or a high number of logins without a
proportional increment in the number of sessions.
If, according to the output of the script, few sessions are responsible for a large part of the activity (for example,
the activity of a single session is at least a double-digit percentage), you have identified sessions that you might want
to further analyze. If, as in the example above, no particular session stands out, it obviously means that the activity
is due to many sessions. Hence, it may be necessary to look at performance statistics by aggregating them according
to a dimension which is different from the session ID. To perform this task, I advise you to use a script developed by
Tanel Põder. Its name is Snapper 1 ( snapper.sql ). Its key functionality is to sample the v$session view at a frequency
that is inversely proportional to the sampling period. During the sampling, Snapper checks the status of the specified
sessions and, for active sessions, it gathers information about their activity (for example, which SQL statement is in
execution). Because Snapper is a very flexible and powerful script that accepts many parameters, I don't describe it
fully here. I limit myself here to describing the basics and showing you a few examples. For additional information,
read the header of the script.
Snapper requires four parameters:
The first parameter specifies which dynamic performance views to be sampled. When the
constant ash is specified, the sampling is performed against v$session . The idea is to gather
data similar to what is provided by active session history. When this parameter is specified,
it's also possible to define which columns of the v$session view the data is to be aggregated
according to. For example, ash=username+sql_id means that data is aggregated according
to the username and sql_id columns of the v$session view (any column of the view can
be specified). When the constant stats is specified, the sampling is performed against
v$sesstat , v$sess_time_model , and v$session_event .
The second parameter specifies the sampling period in seconds.
The third parameter specifies the number of samples to be taken.
 
 
Search WWH ::




Custom Search