Interpreting Elapsed_time_delta in Dba_hist_sqlstat
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Interpreting Elapsed_time_delta in Dba_hist_sqlstat

  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Interpreting Elapsed_time_delta in Dba_hist_sqlstat

    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?

  2. #2
    Join Date
    Jul 2013
    Posts
    7
    Can somebody help me with the answer. or if i have hit some bug?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Try the following. If you are writing a query based on a synonym
    then the owner of the data might be a different schema.
    Just insert the names of the tables you are using in the where clause.


    Code:
    SELECT owner, object_name, object_type, status
       FROM all_objects
     WHERE object_name IN ('', '', etc)
     ORDERT BY 1,2;
    this space intentionally left blank

  4. #4
    Join Date
    Jul 2013
    Posts
    7
    Thanks for the response. I just rechecked(from dba_objects), ther is no synonyms existing for the table mentioned in the 'INSERT' statement.

    Anyways i was trying to get the data regarding 'executing schema' and if its different from 'parsing schema' anyways and if we are storing it in any column of data dictionary views?

    Also can you please answer, if i am getting the Elapsed_time_delta some wrong value due to some bug? Or i can rely 100% on this value?
    Last edited by IP2013; 04-01-2014 at 11:39 AM.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    I can't say for certain that the elapsed time is accurate. You can run the query from
    sqlplus and set timing on in your session to check the time and see if it is close to
    accurate, but it sounds like you are trying to tune the query, so you might want to
    run the query with set autoexplain on along with set timing on, get an explain plan
    and elapsed time. You can then look at the primary keys, partitioning, indexes foreign
    keys and the way in which the query is written.

    It seems like you just need to do basic tuning.
    this space intentionally left blank

  6. #6
    Join Date
    Jul 2013
    Posts
    7
    Thanks. Actually when i am seeing on DB wait activities, i am finding these sqls experiencing those waits at peak period(for around ~2hrs), yesterday i see same i.e. high Concurrency(latch: shared pool)+ Other wait(IPC send completion sync). these are lifting up the total DB time. Just wanted to find out what was wrong at runtime as its populating a temp table.

    i think there is some concept behind it. Actually i get the similar situation again yesterday, and what i found This query runs in parallel , so what i believe the elapsed time calculation should be different like sum (elapsed_time_delta)/sum(PX_SERVERS_EXECS_DELTA), correct? But what if all the parallel slave were not busy , it will give wrong result..

    Now to get the exact time i queried v$active_session_history , and substracted sql_exec_start from max(sampletime) for the same sql for all the samples, and i got the elapsed time around ~50 minutes which is quite possible at run time as per the profile setup i believe.

    Now again when i checked the PX_SERVERS_EXECS_DELTA for this query i got some big figures ~100000. But in query hint its given as 'APPEND PARALLEL 4' also i checked 'degree' from dba_tables fro that temp table, its coming as 1.

    So how its happening can you please help me understand it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width