Database Reference
In-Depth Information
Session Level Analysis
The previous examples based on Snapper show you how to analyze the activity of the whole system (in other words,
the activity carried out by all sessions). However, with Snapper it's also possible to target a specific session. For that,
the fourth parameter, instead of being set to
all
, has to specify a session ID. The following two examples show how to
get information similar to what is shown in Figures
4-20
and
4-21
, respectively:
SQL> @snapper.sql ash=event 15 1 172
---------------------------------------------
Active% | EVENT
---------------------------------------------
22% | db file sequential read
1% | ON CPU
1% | db file parallel read
SQL> @snapper.sql ash=sql_id+module+action 15 1 172
---------------------------------------------------------------------------------
Active% | SQL_ID | MODULE | ACTION
---------------------------------------------------------------------------------
7% | c13sma6rkr27c | New Order | getProductDetailsByCatego
3% | 8dq0v1mjngj7t | New Order | getCustomerDetails
3% | 0yas01u2p9ch4 | New Order |
1% | 7hk2m2702ua0g | Process Orders |
1% | 8dq0v1mjngj7t | Browse Products | getCustomerDetails
1% | 8dq0v1mjngj7t | Browse and Update Orders | getCustomerDetails
1% | bymb3ujkr3ubk | New Order |
1% | 8z3542ffmp562 | New Order | getProductQuantity
1% | 0bzhqhhj9mpaa | New Customer |
SQL Statement Information
When you've identified a SQL statement that's responsible for a large part of the activity, to display information
about it you can use one of the following scripts:
sqlarea.sql
,
sql.sql
, and
sqlstats.sql
. As their names imply,
they extract data provided through
v$sqlarea
,
v$sql
, and
v$sqlstats
, respectively. The three scripts require two
parameters as input:
•
The first parameter specifies the ID of the SQL statement you're interested in.
•
The second parameter specifies whether the script displays either the cumulated statistics
since the cursor was loaded in the library cache, or how much the statistics are currently
increasing. When the parameter is set to a numerical value greater than 0, the latter mode is
enabled. For that purpose, the statistics are queried twice with the wait in-between (number
of seconds) specified as a parameter. For any other value, the former are shown.
