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;
Related Documents
No comments:
Post a Comment