time taken for a query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: time taken for a query

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68

    time taken for a query

    Hello:

    This is the query I use to determine top 10 sql's in my database..

    select disk_reads, executions, disk_reads/executions, sql_text
    from v$sql
    where executions != 0
    and rownum < 11
    order by 3;

    How would I conclude the time taken for each query after I run this.

    Am I missing any more columns to choose?

    ------------------

    In continuation to that, this is what I have in my statspack report as long running query result:

    CPU Elapsd
    Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
    14,813,652 296 50,046.1 30.0 1038.63 4974.33 4009553613
    SELECT T106.C1,T106.C1,C7,C13,C526870925,C536870921,C4,C53687092
    3,C536870915,C536871012,C8,C536870991,C536870992,C536871085 FROM
    T106 WHERE ((T106.C4 = 'CANAM_NY') AND (T106.C7 < 5)) ORDE
    R BY 1 ASC


    Can anyone, PLEASE decode the numbers: [not the select statement]
    I mean, what is Elapsed time, CPU Elapsed time, exec etc.,

    And from the above, how would we determine, what time it took for the query to run...


    Thanks, ST2003
    Last edited by st2003; 10-08-2003 at 06:22 PM.

  2. #2
    Join Date
    Aug 2000
    Posts
    87
    Hi,

    Why cant you enable trace for the session thatz executing the sql and generate tkprof on that trace which gives more information abt the sqls parse,elapsed,fetch,cpu time information.

    Regards,
    Vinod

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: time taken for a query

    Originally posted by st2003
    This is the query I use to determine top 10 sql's in my database..

    select disk_reads, executions, disk_reads/executions, sql_text
    from v$sql
    where executions != 0
    and rownum < 11
    order by 3;
    Top 10 based on what? This query is totaly useles, it shows you only the first 10 statements that it (randomly) finds in shared pool (not in any specific ranking order) - it certamly doesn't show you top 10 statements on any criteria.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    Thanks for the responses..

    Someone has given me this query to find top 10 sql's and i was trying to decode this.. If this is a junk query.., then can anyone give me some queries to find top 10 of resource intensiveness, time taken etc., what are the views i would use for the same?

    Thanks, ST2003

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    select diskexec, bufexec, executions, sql_text
      from
            (select dense_rank() over (order by decode(executions, 0, 0, buffer_gets/executions) desc) rank,
                    decode(executions, 0, 0, buffer_gets/executions) bufexec,
                    sql_text,
                    executions,
                    decode(executions, 0, 0, disk_reads/executions) diskexec
              from v$sql
             where executions > 10)
     where rank <= 10;
    just one of them, ordering buffer gets per executions

  6. #6
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    Thanks for the query Pando..

    Now with the result how can I determine, how long the query took to execute.. This is my output, when i make the rank < 2 from your query.

    How do i rad this to determine my result?

    Thanks, ST2003

    DISKEXEC BUFEXEC EXECUTIONS
    ---------- ---------- ----------
    SQL_TEXT
    ----------------------------------------------------------------------------------------------------
    27.6888758 30034.3768 27166
    select 'TS'||'^'||ts.tablespace_name||'^'||ltrim(to_char(100-(fssum/dfsum*100), 999)) from (s
    name from dba_tablespaces ts where tablespace_name <> 'TEMP' and status <> 'READ ONLY' and
    ace_name not in (select distinct tablespace_name from dba_rollback_segs where table
    space_name <> 'SYSTEM') ) ts, ( select tablespace_name,sum(dfsum_inner) dfsum from ( select table
    s dfsum_inner from dba_data_files where autoextensible='YES' and maxbytes >= bytes union all Selec
    me, bytes dfsum_inner from dba_data_files where autoextensible='YES' and maxbytes < bytes union al
    pace_name, bytes dfsum_inner from dba_data_files where autoextensible='NO' ) group by tablespace_n
    s.tablespace_name, (select sum(bytes) from ( Select tablespace_name, maxbytes bytes from d
    autoextensible='YES' and maxbytes >=

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there is no such view which is gonna tell you how long will the query take

    you have to take on mind as well my query is ordering buffer gets per executions, some people might want to order disk reads per executions

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can't determine execution times from anywhere in the dictionary. If you look at the EXECUTIONS column of your result you'll see that this particular query was executed 27166 times! Do you think every execution of this query has taken the same amount of time? Well, it hasn't! the first time it might taken x seconds, next time y miliseconds, 100th time z seconds, .000th time w minutes and 20.000th times maybe x hours! So which time are you looking for?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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