If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Interpreting Elapsed_time_delta in Dba_hist_sqlstat

Hi , i am using Database 11g Enterprise Edition Release 11.2.0.3.0 of oracle. its a two node RAC.

I got one sql(INSERT) running for ~14 hrs(~51911.8 sec as out put of below query) from Dba_hist_sqlstat by summing the elapsed_time_delta, And i got parsing schma as schema_tran. I am using below query to capture same.

But when informed the DBA , they are saying the parsing_schema is 'schema_tran' but the executing schema is 'schema_prof' (dont know how they got that). And they have created profile(proftimeout) for user 'schema_prof' to have query execution time restricted to ~1hrs, if it exceed that ~1hr ,sql will be terminated. So the figure(elapsed_time_delta) shown by Dba_hist_sqlstat for that sql_id is not correct one!!

Also i queried the dba_users to see the profile allocated for 'SCHEMA_PROF', its showing 'proftimeout'
and checked the DBA_PROFILES to see the CONNECT_TIME parameter, its showing 60.

Shocking part is, When i execute the 'Select' part in Prod, it completes within ~5 minutes!!! So how come its showing ~14hrs in dba_hist_sqlstat? Or is there some different way i sould query the dba_hist_sqlstat?

select snap_id,instance_number,executions_delta,elapsed_time_delta/(1000000*60*60)
from dba_hist_sqlstat
where sql_id='3zddfsdfsdffg';

I can't say for certain that the elapsed time is accurate. You can run the query from
sqlplus and set timing on in your session to check the time and see if it is close to
accurate, but it sounds like you are trying to tune the query, so you might want to
run the query with set autoexplain on along with set timing on, get an explain plan
and elapsed time. You can then look at the primary keys, partitioning, indexes foreign
keys and the way in which the query is written.

Thanks. Actually when i am seeing on DB wait activities, i am finding these sqls experiencing those waits at peak period(for around ~2hrs), yesterday i see same i.e. high Concurrency(latch: shared pool)+ Other wait(IPC send completion sync). these are lifting up the total DB time. Just wanted to find out what was wrong at runtime as its populating a temp table.

i think there is some concept behind it. Actually i get the similar situation again yesterday, and what i found This query runs in parallel , so what i believe the elapsed time calculation should be different like sum (elapsed_time_delta)/sum(PX_SERVERS_EXECS_DELTA), correct? But what if all the parallel slave were not busy , it will give wrong result..

Now to get the exact time i queried v$active_session_history , and substracted sql_exec_start from max(sampletime) for the same sql for all the samples, and i got the elapsed time around ~50 minutes which is quite possible at run time as per the profile setup i believe.

Now again when i checked the PX_SERVERS_EXECS_DELTA for this query i got some big figures ~100000. But in query hint its given as 'APPEND PARALLEL 4' also i checked 'degree' from dba_tables fro that temp table, its coming as 1.