Home » RDBMS Server » Performance Tuning » elapsed_time_delta in dba_hist_sqlstat
elapsed_time_delta in dba_hist_sqlstat [message #610967] Wed, 26 March 2014 06:25 Go to next message
Messages: 91
Registered: June 2013
Hi , i am using Database 11g Enterprise Edition Release 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,
         SUM (shs.executions_delta) " No of Executions",
         ROUND (
            (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?

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

Re: elapsed_time_delta in dba_hist_sqlstat [message #611086 is a reply to message #610967] Thu, 27 March 2014 10:07 Go to previous message
Messages: 91
Registered: June 2013
Can somebody help me understanding this.

[Updated on: Thu, 27 March 2014 10:08]

Report message to a moderator

Previous Topic: Best Design for batch processing using BLOB Types
Next Topic: SQL performance
Goto Forum:

Current Time: Mon Jan 23 21:10:47 CST 2017

Total time taken to generate the page: 0.15602 seconds