Databases Reference
In-Depth Information
Query
DBA_SQLSET*
data dictionary views (see Table 9-6 for a description of the
applicable views).
•
Query the
DBMS_SQLTUNE.SELECT_SQLSET
function.
•
Use Enterprise Manager.
You can determine the name and number of SQL statements for SQL tuning sets in your database
via this query:
•
SELECT name, created, statement_count
FROM dba_sqlset;
Here is some sample output:
NAME CREATED STATEMENT_COUNT
------------------------------ --------- ---------------
PROD_WORKLOAD 26-APR-11 1128
TOP_SQL_1308346990753 17-JUN-11 5
$$SQLA$$_2 19-AUG-11 4485
HIGH_IO 26-APR-11 0
Recall that a SQL tuning set consists of one or more SQL statements and the corresponding
execution statistics. You can use the following query to display the SQL text and associated statistical
information for each query within the SQL tuning set:
SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;
Here is a small snippet of the output (the
SQL_TEXT
column has been truncated in order to fit the
output on the page):
SQLSET_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS SQL_TEXT
--------------- ------------ ---------- ----------- ---------- ----------------------------
test1 235285363 45310000 112777 3050 INSERT ......
test1 52220149 22700000 328035 18826 delete from....
Table 9-6. Views Containing SQL Tuning Set Information
View Name
Description
Displays information regarding SQL tuning sets.
DBA_SQLSET
Displays bind variable information associated with SQL tuning sets.
DBA_SQLSET_BINDS
Shows execution plan information for queries in a SQL tuning set.
DBA_SQLSET_PLANS
Contains SQL text and associated statistics.
DBA_SQLSET_STATEMENTS
