Databases Reference
In-Depth Information
Once you have identified a
SQL_ID
for a resource-intensive SQL statement, you can view all of its
execution details via this query:
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''byzwu34haqmh4'''));
Note that the
SQL_ID
in the prior statement is enclosed by two single quotes (not double quotes).
Table 9-4. Parameter Descriptions of the SELECT_CURSOR_CACHE Function
Parameter
Description
SQL predicate to filter SQL in the cursor cache.
BASIC_FILTER
Currently not used.
OBJECT_FILTER
ORDER BY
clause for the SQL returned.
RANKING_MEASURE(n)
Filter for the top N percent queries for the ranking measure provided; invalid if
more than one ranking measure provided.
RESULT_PERCENTAGE
Top number of SQL statements filter.
RESULT_LIMIT
List of SQL attributes to return in result set.
ATTRIBUTE_LIST
Include recursive SQL.
RECURSIVE_SQL
Populating SQL Tuning Set from High-Resource SQL in AWR
High-resource SQL statement information is stored in the AWR. You can use this as input when creating
a SQL tuning set. Here are the steps:
1.
Create a SQL tuning set object.
2.
Determine begin and end AWR snapshot IDs.
3.
Populate the SQL tuning set with high resource SQL found in AWR.
These steps are detailed in the following subsections.
Step 1: Create a SQL Tuning Set Object
This next bit of code creates a tuning set object named
MY_TUNING_SET
:
BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'MY_TUNING_SET'
,description => 'STS from AWR');
END;
/
