OraFAQ Forum: Performance Tuning » elapsed_time_delta in dba_hist

Home » RDBMS Server » Performance Tuning » elapsed_time_delta in dba_hist_sqlstat

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

elapsed_time_delta in dba_hist_sqlstat [message #610967]

Wed, 26 March 2014 06:25

VIP2013
Messages: 91
Registered: June 2013

Member

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.

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?

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?

[Updated on: Wed, 26 March 2014 14:29]

Report message to a moderator