Home » RDBMS Server » Performance Tuning » SQL execution time based on ash history (10g release 2)
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.
Previous Topic: taking to much time to update
Next Topic: a locking scenario
Goto Forum:
  


Current Time: Sun Dec 04 10:59:48 CST 2016

Total time taken to generate the page: 0.10011 seconds