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, 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!!
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 and IDLE_TIME is default.
Also the query is something like ...
Insert into /*+ APPEND PARALLEL 4*/.. (c1,c2,c3) .... Select SELECT /*+ PARALLEL 4*/ (c1,c2,c3)..... ;
When i execute the 'Select' part in Prod, it completes within ~5 minutes!!! So how come its showing ~14hrs in dba_hist_sqlstat?
When i query dba_hist_sqlstat for the sql_id, below is the result
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?