Monday, February 23, 2009

Find Top N resource absorbing SQL Queries


At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page. Suppose user reported slow performance and this occurred in night or in holiday. Now You need to see which sql queries were run at that specific time along with their resources usages details.



With default settings, in every hour a snapshots taken by Automatic Workload Repository (AWR) and those snapshots are available for next 7 days. Here is a SQL query that give the desired top N resource sensitive queries :



SELECT * FROM
(SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('18-feb-2009 18:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('18-feb-2009 18:40', 'dd-mon-yyyy hh24:mi'))
AND PARSING_SCHEMA_NAME='SCOTT'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
) WHERE rownum < =200;

3 comments:

Anuj Singh said...

Hi
this is giving me a error

SYS AS SYSDBA>/
ERROR:
ORA-01476: divisor is equal to zero



no rows selected

Mohammad Hasan Shaharear said...

Hello anju,

Can you please explain what you are trying to do with '/'?

what is sap ecc 6.0 said...

Its is very crucial point to improve the performance so that all the users can work easily with the database and it is the core responsibility of a DBA to regularly monitor the performance. I am convinced your idea to check the top N resource sensitive queries so that a balance can be maintained. Thanks for providing the source code.