• Arun Kumar

Top resource intensive SQL

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then give it to application team to optimize them.


Find top 5 resource intensive SQL

col Rank for a4
SELECT *
FROM (SELECT RANK () OVER 
(PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time, 'MM/DD/YY' ) "Snap Day",
SUM (shs.executions_delta) "Execs",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.disk_reads_delta) "Disk Reads",
ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) "Gets/Exec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "CPU/Exec(S)",
ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "IO/Exec(S)",
shs.sql_id "Sql id",
REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'), 
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) )
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 5 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');


Recent Posts

See All

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose

Wait events vs wait classes

Did you know that all the wait events inside Oracle database are categorized into wait classes? Every wait event always belongs to a wait class. Even before you learn more about each and every wait ev

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback