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
mymot
Messages: 225
Registered: July 2005
Senior Member
Hi,



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  9.2.0.7i

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

sofar,
blocks,
load time
end time
buffered


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

Thanks
sam

[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
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
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
gintsp
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
http://www.gplivna.eu

[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 Dec 12 07:48:03 CST 2024