Jian Zhang

• Oct 22nd, 2006

 

set timing on

paparao

• Oct 23rd, 2006

 

By test with the help of the following tools

tkprof    or using explain plan

tkprof is available to DBA Only where as explain plan can run programmer as well as DBA also.

as well as tkprof generates complexilty after sucessful execution only where as explain plan can show Oracle internal plan & other details.

Eventhough they are not alternatives for one to another. But both are designed for one purpose only.

They are two different tools they are enganed in diffent useful situations.

for that refer manual

Bernard van Niekerk

• Nov 7th, 2006

 

Explain plan will only tell if a query execution plan is bad, not WHICH query is slow ... anyway. If the user reports the application being slow I would use v$session_longops to see if I can spot the culprit after that I will enable tracing.And also TRACE is the tool (enable through alter session set sql_trace = true;) and TKRPOF is only formating the output.

VENKAT

• Jun 21st, 2007

 

Can use the following query to find running queries and how long it will run?

select sid, serial#, username, target, opname, sofar, totalwork, time_remaining/60 "Time Rem", elapsed_seconds/60 "Elap Sec"
from v$session_longops where sofar<totalwork order by username

senthilora

• Sep 24th, 2007

 

You can use STATSPACK to take SNAPs while running those queries and get the report with details of SQLs taking more time to respond.

pabroy

 Profile Answers by pabroy 

• May 5th, 2008

 

You can search Top ten sql.

Top 10 Sql. SELECT *FROM   (SELECT rownum,Substr(a.sql_text,1,200) sql_text,        Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,                a.buffer_gets,                a.disk_reads,                a.executions,                a.sorts,               a.address        FROM   v$sqlarea a        ORDER BY 3 DESC)WHERE  rownum <= 10;