Топ-100
 
CerebroSQL

Oracle TOP SQL

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

Oracle Top SQL - window.jpg

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.jpg

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.jpg
SQL Queries

  • Buffer

Panel

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

Panel

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

Panel

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

Panel

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

Panel

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

Panel

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

Panel

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;