Топ-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

Inquiries

Buffer

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

SELECT * FROM
(SELECT sql_fulltext sql,
    sql_id,
    executions,

     erows_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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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;

-----