Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

How to find Top Sql through Queries

How to find Top Sql through Queries

If someone ask for top sql they from enterprise manager he can find some expensive queries. But the result may vary based on the metrics or criteria of expensive. Measurement may vary on,
a) Logical IO’s per execution?
b) Physical IO’s per execution?
c) Cpu usage?
d) Based on number of parse calls?
e) Elapsed time used?
f) Number of executions?
g) Size consumed in shared pool?
h) Number of child versions found?
i) Based on Wait time?

However from DBA_HIST_SQLSTAT we can get a complete picture of historical SQL statistics. AWR does a pretty job. Between two snapshot it displays the top sql statements in the enterprise manager. We can also see it from sql queries.

In fact Enterprise Manager displays information of SQL text from DBA_HIST_SQLTEXT view which captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view.

Along with the DBA_HIST_SQLSTAT and DBA_HIST_SQLSTAT we can use the view DBA_HIST_SNAPSHOT in order to specify the range of snapshots in between analysis will be done.

We can specify the BEGIN_INTERVAL_TIME and END_INTERVAL_TIME column of the view DBA_HIST_SNAPSHOT instead of specifying SNAP_ID if we are determined to calculate the top sql query between date range.

From the combination of the three above views here is one query while calculates the top sql from date 09/01/2008 to 09/09/2008 based on the CPU time.

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE(’09/01/2008′,’MM/DD/YYYY’)
AND END_INTERVAL_TIME<=TO_DATE(’09/09/2008′,’MM/DD/YYYY’)) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

August 18, 2008 - Posted by | oracle

1 Comment »

  1. this helps a lot! thanks!

    Comment by sguinales | December 13, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: