Databases Reference
In-Depth Information
Parameter
Description
Non-inclusive beginning snapshot ID.
BEGIN_SNAP
Inclusive ending snapshot ID.
END_SNAP
Name of AWR baseline.
BASELINE_NAME
SQL predicate to filter SQL statements from workload; if not set, then only
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
MERGE
, and
CREATE TABLE
statements are
captured.
BASIC_FILTER
Not currently used.
OBJECT_FILTER
Order by clause on selected SQL statement(s), such as
elapsed_time
,
cpu_time
,
buffer_gets
,
disk_reads
, and so on;
N
can be 1, 2, or 3.
RANKING_MEASURE(n)
Filter for choosing top N% for ranking measure.
RESULT_PERCENTAGE
Limit of the number of SQL statements returned in the result set.
RESULT_LIMIT
List of SQL statement attributes (
TYPICAL
,
BASIC
,
ALL
, and so on).
ATTRIBUTE_LIST
Include/exclude recursive SQL (
HAS_RECURSIVE_SQL
or
NO_RECURSIVE_SQL
).
RECURSIVE_SQL
Notice from the prior queries in this section that there are several ranking measures of resources
consumed by SQL statements, such as CPU time, buffer gets, and so on. The resource ranking measures
and units of measurement are detailed the following bulleted list:
cpu_time
: Number of seconds
•
elapsed_time
: Number of seconds
•
•
disk_reads
: Number of reads from disk
buffer_gets
: Number of reads from memory
•
rows_processed
: Average number of rows
•
•
optimizer_cost
: Calculated optimizer cost
executions
: Total execution count of SQL statement
These values allow you to retrieve SQL by the criteria that you're concerned with the most. They are
valid for filtering SQL in the AWR and memory.
•
Viewing Resource-Intensive SQL in Memory
Similar to querying the AWR, you can also view current high resource usage SQL in memory. The
DBMS_SQLTUNE.SELECT_CURSOR_CACHE
function is used to view current high resource-consuming SQL
statements in memory. This query selects SQL statements in memory that have required more than a
million disk reads:
SELECT
sql_id
