Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 7 October 2012

Script: To Get Top SQL


While finding the top 10  most expensive SQL, you must keep all the below concept in your mind:
– Is it Logical  I/O per Execution.
– Is it Physical I/O per Execution.
– Is it CPU used.
– Is the number of child version found.
– Is it based on the parse call.
– Is it number of execution.
TOP SQL based on number of elapsed time
SELECT * FROM
(SELECT sql_text, child_number, disk_reads,
    executions, first_load_time,last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;
Based on the I/O

select * from (select disk_reads,executions,sql_text from v$sqlarea
 order by 1 desc, 2 desc) where rownum <=10;

The above query finds the TOP sql statements that currently stored in SQL cache ordered by elapsed time

SELECT   b.username username, a.disk_reads phyreads, a.executions noexec, a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions) rds_ex_ratio, a.command_type, a.sql_text sqlqry
    FROM v$sqlarea a, dba_users b
   WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 10000
   ORDER BY a.disk_reads DESC;
The above query will find top SQL execution on the basis of reads execution ration.
Average buffer gets per execution during a period of activity of the instance:

SELECT username,  buffer_gets,  disk_reads, executions, buffer_get_per_exec,
       parse_calls, sorts, rows_processed, hit_ratio,module,sql_text
       -- elapsed_time, cpu_time, user_io_wait_time, ,
  FROM (SELECT sql_text, b.username, a.disk_reads, a.buffer_gets,
               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
               a.parse_calls, a.sorts, a.executions, a.rows_processed,
               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
               module
               -- cpu_time, elapsed_time, user_io_wait_time
          FROM v$sqlarea a, dba_users b
         WHERE a.parsing_user_id = b.user_id
           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
           AND a.buffer_gets > 10000
         ORDER BY buffer_get_per_exec DESC)
 WHERE ROWNUM <= 20;

Find query execution time > 6 seconds
SELECT username, sql_text, sofar, totalwork, units
FROM v$sql,v$session_longops
WHERE sql_address = address AND sql_hash_value = hash_value
ORDER BY address, hash_value, child_number;
Note: It is always better to use Statspack and AWR report for this purpose.

0 comments:

Post a Comment