Database Reference
In-Depth Information
The fourth parameter specifies which sessions are sampled. It's possible to specify a single
session ID, a list of several session ids (separated by a comma), the constant all to sample
all sessions, a query that returns the ID of the sessions to be sampled, or one of a number of
supported expressions (for example, user=chris to query data for all sessions opened by the
specified user—see the script's header for a full list of available expressions).
The first example based on Snapper shows how to gather information similar to that shown in Figure 4-17 . The
four parameters are set as follows:
The first parameter (
ash=sql_id ) specifies to query the v$session view and aggregate the
resulting data by SQL ID.
The second parameter (15) specifies to use a sampling period of 15 seconds.
The third parameter (1) specifies to take a single sample.
The fourth parameter (
all ) specifies to sample all sessions.
SQL> @snapper.sql ash=sql_id 15 1 all
-------------------------
Active% | SQL_ID
-------------------------
196% | c13sma6rkr27c
186% | 8dq0v1mjngj7t
122% | bymb3ujkr3ubk
107% | 7hk2m2702ua0g
82% | 0yas01u2p9ch4
63% | 8z3542ffmp562
62% | 0bzhqhhj9mpaa
30% | 5mddt5kt45rg3
26% |
26% | f9u2k84v884y7
Note that as shown in the previous example, the Active% column might be greater than 100%. This can happen
when the sampling is performed on several sessions. For example, in the previous output, during the sampling period,
the top SQL statement (c13sma6rkr27c) was executed, on average, by 1.96 sessions.
The second example shows how to gather information similar to that shown in Figure 4-19 . For that purpose,
compared to the preceding example, only the first parameter has to be changed. It has to specify to aggregate data
according to the session attributes module and action ( ash=module+action ):
SQL> @snapper.sql ash=module+action 15 1 all
---------------------------------------------------------------
Active% | MODULE | ACTION
---------------------------------------------------------------
97% | New Order | getProductDetailsByCatego
94% | New Order |
86% | Process Orders |
58% | Browse Products | getCustomerDetails
32% | New Order | getCustomerDetails
28% | New Customer |
22% | New Order | getProductQuantity
9% | |
Search WWH ::




Custom Search