Database Reference
In-Depth Information
SQL Statement Statistics
Information about cursors associated to SQL statements is available at the parent and child level through the
v$sqlarea
and
v$sql
views, respectively. In addition, performance statistics at the parent level are also available
through
v$sqlstats
. Even though the
v$sqlarea
and
v$sql
views provide more information (columns), there are
two good reasons for using the
v$sqlstats
view. First, it has a greater retention, and therefore even cursors that were
already aged out from the library cache may be seen through the
v$sqlstats
view. Second, accessing the
v$sqlstats
view requires fewer resources. Because these dynamic performance views have many columns (for example, the
v$sql
view has 72 columns in version 10.2.0.5 and 91 columns in version 12.1.0.1), they aren't fully covered here
(refer to the
Oracle Database Reference
manual for additional information). Here's the most performance-relevant
information you can extract from the
v$sql
view and which column it's found in:
The identification of the cursor (
address
,
hash_value
,
sql_id
and
child_number
).
The type of the SQL statement associated to the cursor (
•
command_type
) and the text of the SQL
statement (the first 1,000 characters in
sql_text
and the full text in
sql_fulltext
).
The service used to open the session that hard parsed the cursor (
•
service
), the schema used
for the hard parse (
parsing_schema_name
and
parsing_schema_id
), and the session attributes
that were in place during the hard parse (
module
and
action
).
If the SQL statement was executed from PL/SQL, the ID of the PL/SQL program and the line
•
•
number where the SQL statement is located (
program_id
and
program_line#
).
The number of hard parses that took place (
loads
), how many times the cursor was
invalidated (
invalidations
), when the first and last hard parses took place (
first_load_time
and
last_load_time
), the name of the stored outline category (
outline_category
), SQL
profile (
sql_profile
), SQL patch (
sql_patch
), SQL plan baseline (
sql_plan_baseline
)
used during the generation of the execution plan, and the hash value of the execution plan
associated to the cursor (
plan_hash_value
).
The number of parse, execution, and fetch calls (
•
parse_calls
,
executions
, and
fetches
) that
have been carried out and how many rows were processed (
rows_processed
). For queries,
how many times all rows were fetched (
end_of_fetch_count
).
The amount of DB time used for the processing (
•
elapsed_time
), how much of it has been
spent on CPU (
cpu_time
) or waiting for events belonging to the Application, Concurrency,
Cluster and User I/O wait classes (
application_wait_time
,
concurrency_wait_time
,
cluster_wait_time
, and
user_io_wait_time
), and how much processing has been done
by the PL/SQL engine and Java virtual machine (
plsql_exec_time
and
java_exec_time
).
All values are expressed in microseconds.
The number of logical reads, physical reads, direct writes, and sorts that have been carried out
•
•
(
buffer_gets
,
disk_reads
,
direct_writes
, and
sorts
).
Real-time Monitoring
Whereas the dynamic performance views described in the last section provide only cumulated statistics about
cursors, real-time monitoring provides information that describe how, over time, cursors are executed. There are
two important implementation details to be aware of. First, real-time monitoring provides information also during
the execution. In other words, you don't have to wait until an execution ends to know how it was carried out. Second,
the information related to real-time monitoring is stored independently from the cursor it's based on. Therefore, the
information might also be accessible when the cursor itself was already flushed from the library cache. In a way, the
goal of real-time monitoring is similar to that of active session history. In fact, whereas active session history provides
historical information about the status of the active sessions, real-time monitoring provides historical information
