|elapsed_time_delta in dba_hist_sqlstat [message #610967]
||Wed, 26 March 2014 06:25
Registered: June 2013
Hi , i am using Database 11g Enterprise Edition Release 220.127.116.11.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, 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!!
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
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.
Also the query is something like ...
Insert into /*+ APPEND PARALLEL 4*/.. (c1,c2,c3) .... Select SELECT /*+ PARALLEL 4*/ (c1,c2,c3)..... ;
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?
[Updated on: Wed, 26 March 2014 14:29]
Report message to a moderator