Shareware software
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;