Oracle TOP SQL
[EN]
The module allows you to identify queries that make the maximum contribution to the utilization of certain Oracle database resources, as well as receive recommendations for improving query performance
Working with a window
Top type
Buffer
Physical Reads
Executions
Parse Calls
Sharable Memory
Version Count
Sequencing
From the "Top type" drop-down list, select the grouping type
In the "Number of displayed rows" field, specify the number of displayed rows
Click the "Show data" button
View information on request
Select a line in the list "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 "Recommendation" tab, recommendations for query optimization are generated using DBMS_SQLTUNE
[RU]
Модуль позволяет определить запросы вносящие максимальный вклад в утилизацию тех или иных ресурсов базы данных Oracle, а так же получить рекомендации по улучшению производительности запросов
Работа с окном
Top type
Buffer
Physical Reads
Executions
Parse Calls
Sharable Memory
Version Count
Последовательность действий
Из выпадающего списка "Top type" выбрать тип группировки
В поле "Number of displayed rows" указать количество отображаемых строк
Нажать кнопку "Show data"
Просмотр информации по запросу
Выделить строку в списке "List query"
В поле "Query text" отобразится сам запрос
На вкладке "Plan" план запроса
При переключении на вкладку "Recomendation" формируются рекомендации по оптимизации запроса с помощью DBMS_SQLTUNE
[QUERY]
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);BEGINmy_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;