how to determine top SQL query??

[ngwh]

hi guys,

I must apologize...i hv been trying to use the search function but w/o success as it prompted me it need at least 4 chars word for search...I hv been trying to search this forum on topic on how i can determine wat's the top SQL queries...anyone with this info on how to go abt doing that??

thanks in advance.

rgds,

[jmodic]

Read about statspack.

[ngwh]

is there a way going abt checking top query w/o install statpack ?? like in some dictionary or somethg??

thks.

[julian]

STATSPACK is best. Try these two queries:

select b.username, a.buffer_gets reads,
a.executions exec, a.buffer_gets / decode
(a.executions, 0, 1, a.executions) rds_exec_ratio,
a.command_type, a.sql_text Statement
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.buffer_gets > 100000
order by a.buffer_gets desc;

select b.username, a.disk_reads reads,
a.executions exec, a.disk_reads / decode
(a.executions, 0, 1, a.executions) rds_exec_ratio,
a.command_type, a.sql_text Statement
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;

[pando]

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,
                hash_value
          from v$sql
         where executions > 10 )
 where rank <= 20;

[Mr.Hanky]

The problem is that TOP (offending) SQL can be defined in different ways. Physical reads, sorts, CPU consumption. OEM by default goes after disk reads per execution, but your problem may be CPU.

Another thing I have noticed is that with reporting systems, "number of executions" is not always accurate since often a value is plugged in for one or two parameters, while the main SQL remains the same. I ran into that problem yesterday. My point is that usually you may disregard a TOP SQL if it appears to have been run only once, often there is more lying under the covers.

MH