Database Reference
In-Depth Information
•
A number of columns provide information about the execution plan associated to the SQL
statement that's in execution. Specifically, its hash value (
sql_plan_hash_value
) and the
active operation (
sql_plan_line_id
,
sql_plan_operation
and
sql_plan_options
).
From 11.1 onward, a number of flags point out what operation, according to the
•
categories defined for time model statistics, is in execution (
in_connection_mgmt
,
in_parse
,
in_hard_parse
,
in_sql_execution
,
in_plsql_execution
,
in_plsql_rpc
,
in_plsql_compilation
,
in_java_execution
,
in_bind
,
in_cursor_close
and, as of version 11.2,
in_sequence_load
).
For SQL statements executed in parallel, information about the query coordinator is given
•
(
qc_instance_id
,
qc_session_id
and, as of version 11.1
qc_session_serial#
).
With the information provided through the
v$active_session_history
view, you can perform a statistical
analysis on how the database engine spends time during the processing. Let me stress that, because the data is based
on sampling, it's a statistical analysis. Therefore, more samples lead to more accurate results. In any case, because
only one sample per second and per active session is kept, the available information isn't as accurate as a method not
based on sampling (for example, SQL trace). However, in many situations, the analysis provides sufficient information
to pinpoint the cause of a performance problem.
The typical query executed against the
v$active_session_history
view is composed of the following parts:
A restriction on
•
sample_time
to focus on a specific time period.
An aggregation based on one or several columns providing context information about the
•
processing like the ID of the session (
session_id
), the SQL statement associated to the cursor
in execution (
sql_id
), or the application that carried out the processing (
program
).
A count of the number of samples. Because every sample accounts for one second, the
•
number of samples is an approximation of DB time in seconds.
■
To estimate values like the Db time, the CPu time, or the amount of time spent waiting on events, you have
to count the number of samples. be aware that aggregating data with a simple expression like
sum(time_waited)
is the
wrong way to do it! That's because the probability of an event being sampled is proportional to the length of the event.
Caution
For example, the following query retrieves the top 10 SQL statements ordered by their DB time for a period of
5 minutes (notice, for example, that the first SQL statement spent about 1,008 seconds and is responsible for about
29.9% of the total DB time):
SQL> SELECT activity_pct,
2 db_time,
3 sql_id
4 FROM (
5 SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct,
6 count(*) AS db_time,
7 sql_id
8 FROM v$active_session_history
9 WHERE sample_time BETWEEN to_timestamp('2014-02-12 22:12:30', 'YYYY-MM-DD HH24:MI:SS')
10 AND to_timestamp('2014-02-12 22:17:30', 'YYYY-MM-DD HH24:MI:SS')
11 AND sql_id IS NOT NULL
12 GROUP BY sql_id
13 ORDER BY count(*) DESC
