Oracle TOP SQL

The module allows you to determine the queries that make the maximum contribution to the utilization of certain Oracle database resources, as well as get recommendations for improving query performance

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;