Shareware software
Working with a window

Top type
-
Buffer
-
Physical Reads
-
Executions
-
Parse Calls
-
Sharable memory
-
Version Count
Sequencing
-
From the drop-down list "Top type" select the type of grouping
-
In the "Number of displayed rows" field, specify the number of displayed rows
-
Press the "Show data" button

View information on request
-
Select a line in the "List query"
-
The query itself will be displayed in the "Query text" field
-
On the "Plan" tab, the query plan
-
When you switch to the "Recomendation" tab, recommendations are generated for optimizing the query using DBMS_SQLTUNE

SQL Queries
-
Buffer
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
buffer_gets,
executions,
buffer_gets/(nvl2(executions,1,1)) "Gets/Exec",
hash_value,
address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
)
WHERE rownum <= 100
-
Physical read
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
disk_reads,
executions,
disk_reads/(nvl2(executions,1,1)) "Reads/Exec",
hash_value,
address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC
)
WHERE rownum <= 100
-
Executions
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
executions,
rows_processed,
rows_processed/(nvl2(executions,1,1)) "Rows/Exec",
hash_value,
address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC
)
WHERE rownum <= 100
-
Parse calls
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
parse_calls,
executions,
hash_value,
address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC
)
WHERE rownum <= 100
-
Sharable memory
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
sharable_mem,
executions,
hash_value,
address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC
)
WHERE rownum <= 100
-
Version count
SELECT *
FROM
(SELECT sql_fulltext sql,
sql_id,
version_count,
executions,
hash_value,
address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC
)
WHERE rownum <= 100
-
Load recomendation
DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => $$SQL_ID,
task_name => $$TASK_NAME_GEN);
END;
/
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK ($$TASK_NAME_GEN);
end;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ($$TASK_NAME_GEN) "SQL" FROM dual;