Interpreting Elapsed_time_delta in Dba_hist_sqlstat
Hi , i am using Database 11g Enterprise Edition Release 188.8.131.52.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.
Also the query is something like ...
Insert into /*+ APPEND PARALLEL 4*/.. (c1,c2,c3) .... Select SELECT /*+ PARALLEL 4*/ (c1,c2,c3)..... ;
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'
SUM (shs.executions_delta) " No of Executions",
(SUM (shs.elapsed_time_delta) / 1000000)
/ SUM (shs.executions_delta),
"Elapsed time per execution"
FROM dba_hist_sqlstat shs
WHERE sql_id = '3zddfsdfsdffg'
GROUP BY shs.dbid, shs.sql_id, parsing_schema_name
3zddfsdfsdffg SCHEMA_TRAN 1 51911.8
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?
snap_id instance_number executions_delta elapsed_time_delta/(1000000*60*60)
12352 1 0 0.366866351111111
12353 1 1 7.20428535555556
12353 2 0 5.94377669638889
12352 2 0 0.905027245555556
I need expert advice, is it true that the dictionary dba_hist_sqlstat stores wrong 'elapsed_time_delta' info sometimes?
How to get the executing schema for the query, if its different that parsing_schema?
Can somebody help me with the answer. or if i have hit some bug?
Try the following. If you are writing a query based on a synonym
then the owner of the data might be a different schema.
Just insert the names of the tables you are using in the where clause.
SELECT owner, object_name, object_type, status
WHERE object_name IN ('', '', etc)
ORDERT BY 1,2;
this space intentionally left blank
Thanks for the response. I just rechecked(from dba_objects), ther is no synonyms existing for the table mentioned in the 'INSERT' statement.
Anyways i was trying to get the data regarding 'executing schema' and if its different from 'parsing schema' anyways and if we are storing it in any column of data dictionary views?
Also can you please answer, if i am getting the Elapsed_time_delta some wrong value due to some bug? Or i can rely 100% on this value?
Last edited by IP2013; 04-01-2014 at 11:39 AM.
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.
It seems like you just need to do basic tuning.
this space intentionally left blank
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.
So how its happening can you please help me understand it?
Click Here to Expand Forum to Full Width