| SQL execution time based on ash history [message #350761] |
Fri, 26 September 2008 04:23 |
John Charles
Messages: 5
Registered: January 2004
|
Junior Member |
|
|
Hello all,
I'm trying to retrieve the execution time by SQL_ID from the ash tables and views.
As I saw in some examples around , most of the people are using the sum of wait_time and time_waited from v$active_session_history and them group by sql_id for a certain interval of time.
check for example http://www.databasejournal.com/features/oracle/article.php/3388861
I've tried to use this approach for an interval of 10 min and the results are confusing me.
I think is not so easy like that.
I've got this result:
AVERAGE TIME (sec) / SCHEMA / SQL_ID / EXECUTIONS / SQL_TEXT
1277.4 / KEYWARE / 2dxcvzt5phbz2 / 1 / select 'x' from dual
with the fowling query:
select round((sum(ash.wait_time + ash.time_waited) / 1000000) / decode(nvl(sqlarea.EXECUTIONS,0),0,1,sqlarea.EXECUTIONS),2) as "AVERAGE TIME (sec)",
usr.username as "SCHEMA",
ash.sql_id ,
sqlarea.EXECUTIONS,
sqlarea.sql_text
from v$active_session_history ash,
v$sqlarea sqlarea,
dba_users usr
where ash.sample_time between (SYSDATE - 10/1440) and sysdate
and ash.sql_id = sqlarea.sql_id
and ash.user_id = usr.user_id
group by sqlarea.sql_text,usr.username,ash.sql_id,sqlarea.EXECUTIONS
having round((sum(ash.wait_time + ash.time_waited) / 1000000) / decode(nvl(sqlarea.EXECUTIONS,0),0,1,sqlarea.EXECUTIONS),2) > 20
order by 1 desc;
How come a "SELECT X FROM DUAL" has around 21 min with the sum of wait_time and time_waited from the snapshots on v$active_session_history within a 10 min of interval?
Another metric that I still don't understand is the executions parameter. How can we get 0 executions per sql?
AVERAGE TIME (sec) / SCHEMA / SQL_ID / EXECUTIONS / SQL_TEXT
93.29 / GOBERHE / 3z84rgqpgjfdv / 0 / SELECT "PROP_NUMBER", "PROP_REVISION", "PART_NUMBER", "LEAR_EMAIL", "LEAR_FAX", "LEAR_FNAME", "LEAR_NAME","LEAR_PHONE", "LEAR_PHONE_2", "LEG_REG_DATE", "LEG_RE
Any ideas? suggestions? Comments?
Thank you in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
