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.

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!!

Code:
SELECT sql_id,
         parsing_schema_name,
         SUM (shs.executions_delta) " No of Executions",
         ROUND (
            (SUM (shs.elapsed_time_delta) / 1000000)
            / SUM (shs.executions_delta),
            1)
            "Elapsed time per execution"
    FROM dba_hist_sqlstat shs
   WHERE sql_id = '3zddfsdfsdffg'
GROUP BY shs.dbid, shs.sql_id, parsing_schema_name

OUTPUT:
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.


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';


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?