Home » RDBMS Server » Performance Tuning » Long Running SQL -V$session_longops
Long Running SQL -V$session_longops [message #236221] Wed, 09 May 2007 06:23 Go to next message
Messages: 225
Registered: July 2005
Senior Member

I have tried to understand to check current status of long running query by executing below query.

Suppose one of unix shell script is calling t1.sql in it.

when t1.sql is trying to load data from temp table (nearly 100,000 records minimum),

Now this query was going on , unix sun solaris platform, oracle

sql> select * from v$session_longops where time_Remaining >0

load time
end time

and it gives me details, i cant understand what to look, what to tune to make query efficient,


[Updated on: Wed, 09 May 2007 06:24]

Report message to a moderator

Re: Long Running SQL -V$session_longops [message #236222 is a reply to message #236221] Wed, 09 May 2007 06:28 Go to previous messageGo to next message
Messages: 48
Registered: February 2007
v$session_longops will give you the estimated time for completion and is purely a management related view so you need to use tkprof and sql trace for further analysis
Re: Long Running SQL -V$session_longops [message #236395 is a reply to message #236221] Wed, 09 May 2007 16:34 Go to previous message
Messages: 118
Registered: February 2007
Senior Member
You have to look at your very query and try to understand what it does, how it does and is there any possibility to do the same thing in a better way.
V$session_longops will give you an estimated time for particular long operation and such operations generally might be from zero till n for a query.
For more info about v$session_longops you can read my paper about this view at http://www.gplivna.eu/papers/v$session_longops.htm but as previous poster said there is no much use of it for tunig a particular query.

Gints Plivna

[Updated on: Thu, 10 May 2007 02:03]

Report message to a moderator

Previous Topic: TUNING
Next Topic: Optimal size for my memory components ( development db )
Goto Forum:

Current Time: Thu Aug 17 08:53:22 CDT 2017

Total time taken to generate the page: 0.28532 seconds