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

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

Quote:

---

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.

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

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

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 >=

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

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?