Топ-100
top of page
Волнистый абстрактный фон
CerebroSQL

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

top sql general

Working with a window

Oracle Top SQL - 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  

Oracle Top SQL - view SQL

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

Oracle Top SQL - view recommendation
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;

bottom of page