No question at this time
DBA Top 10
1 M. Cadot 10800
2 A. Kavsek 9600
3 B. Vroman 4700
4 P. Wisse 4100
5 J. Schnackenberg 2900
6 J. PĂ©ran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48527
Total active users1412
Act. users last 24h6
Act. users last hour2
Registered user hits last week147
Registered user hits last month416
Go up

Difference of sql execution time in awr and dba_hist_sqlstat.
Next thread: Why would materialized views, refresh group,view logs be excluded from export dump ?
Prev thread: How to use ExtractValue function in my XML?

Message Score Author Date
Hello, For one database we encountered some perfo...... abhishek jindal Sep 04, 2015, 11:12
Abhishek, Wait events are usually measured in c...... Philip Wisse Sep 04, 2015, 11:34
Hi Philip, Thanks for replying. I have already ta...... abhishek jindal Sep 04, 2015, 13:12
If you transform centiseconds by using "/1000/1000...... Jan Schnackenberg Sep 04, 2015, 13:56
Hi Jan, my apologies, i was confused between micr...... abhishek jindal Sep 04, 2015, 14:38

Follow up by mail Click here


Subject: Difference of sql execution time in awr and dba_hist_sqlstat.
Author: abhishek jindal, India
Date: Sep 04, 2015, 11:12, 1806 days ago
Os info: AIX 7
Oracle info: 11.2.0.4
Message: Hello,
For one database we encountered some performance problem. i generated awr report and found that one sql is taking 19,361 seconds under SQL ordered by Elapsed Time section.
when i checked that same sql id with dba_hist_sqlstat view it showed me it was executed for 180 seconds only. I am confused with these outputs. Please help me in understanding what i am doing wrong or if i am right then why two time.?

col SNAP_TIME for a12
col EXECUTIONS_DELTA for 999,999,999 heading EXECUTIONS
col DISK_READS_DELTA for 999,999,999 heading DISK_READS
col BUFFER_GETS_DELTA for 999,999,999 heading BUFFER_GETS
col ROWS_PROCESSED_DELTA for 999,999,999 heading ROWS_PROCESSED
col CPU_TIME_DELTA for 999,999 heading CPU_TIME
col ELAPSED_TIME_DELTA for 999,999,999 heading ELAPSED_TIME
col AVG_GET for 999,999,999.9
col AVG_RD for 999,999,999.9
col AVG_TM for 9,999,999.9 heading AVG_TM(MS)
set pages 100
set line 150

select to_char(END_INTERVAL_TIME,'MM-DD hh24:mi') SNAP_TIME,
EXECUTIONS_DELTA,
DISK_READS_DELTA,
DISK_READS_DELTA/EXECUTIONS_DELTA AVG_RD,
BUFFER_GETS_DELTA,
BUFFER_GETS_DELTA/EXECUTIONS_DELTA AVG_GET,
ROWS_PROCESSED_DELTA,
CPU_TIME_DELTA/1000/1000 CPU_TIME_DELTA,
ELAPSED_TIME_DELTA/1000/1000 ELAPSED_TIME_DELTA,
ELAPSED_TIME_DELTA/1000 / EXECUTIONS_DELTA AVG_TM,
PLAN_HASH_VALUE PLAN
from dba_hist_sqlstat s, DBA_HIST_SNAPSHOT h
where s.snap_id >= &&start_snap
and s.snap_id <= &&end_snap
and SQL_ID= '$sql_id'
and ('$plan_hash' is null or PLAN_HASH_VALUE='$plan_hash')
and EXECUTIONS_DELTA >0
and s.snap_id = h.snap_id
order by 1


SNAP_TIME EXECUTIONS DISK_READS AVG_RD BUFFER_GETS AVG_GET ROWS_PROCESSED CPU_TIME ELAPSED_TIME AVG_TM(MS) PLAN
------------ ------------ ------------ -------------- ------------ -------------- -------------- -------- ------------ ------------ ----------
08-27 01:00 1 104,202 104,202.0 256,861 256,861.0 0 5 53 53,440.8 1136458882
08-28 02:01 1 121,537 121,537.0 309,945 309,945.0 386,868 19 184 183,715.5 3028919531

Thanks in Advane
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Difference of sql execution time in awr and dba_hist_sqlstat.
Author: Philip Wisse, Netherlands
Date: Sep 04, 2015, 11:34, 1806 days ago
Message: Abhishek,

Wait events are usually measured in centiseconds.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Difference of sql execution time in awr and dba_hist_sqlstat.
Author: abhishek jindal, India
Date: Sep 04, 2015, 13:12, 1805 days ago
Message: Hi Philip,
Thanks for replying. I have already taken account that thing and thats why i divided wait event time with /1000/1000. so it shows me output in seconds.
Still i can see difference.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Difference of sql execution time in awr and dba_hist_sqlstat.
Author: Jan Schnackenberg, Germany
Date: Sep 04, 2015, 13:56, 1805 days ago
Message: If you transform centiseconds by using "/1000/1000" you will not get seconds.

"centi" means "hundredth" -> 100 centiseconds is one second

Your SQL statement does not distinquish between centiseconds and milliseconds.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Difference of sql execution time in awr and dba_hist_sqlstat.
Author: abhishek jindal, India
Date: Sep 04, 2015, 14:38, 1805 days ago
Message: Hi Jan,
my apologies, i was confused between micro and centi. i can now see clearly your and Philip point. But i checked oracle docs and according to it, it is in microseconds

ELAPSED_TIME_DELTA NUMBER Delta value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching

Thanks
Abhishek
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here