We help IT Professionals succeed at work.

v$session_longops doesnot populate for long running SQL's

vkchaitu82
vkchaitu82 asked
on
Medium Priority
347 Views
Last Modified: 2013-12-19
Hi Experts,

When we are running long running SQl's which are about 30sec this particular table does not populate entrees to monitor.But i see entrees from username 'SYS'.
Some parameters at  the system level needs to be set??
We are using Oracle9i
Thanks in advance
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
longops isn't for your sql statements it's for operations that the database runs.

you can populate it yourself with dbms_application_info package but not at the sql level.

Author

Commented:
But i see from different articles that from Oracle 8i it has been enhanced to caputre the information about long running SQL's
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Yes, it "can" do that, but it's not the sql itself it's operations behind it.

For instance, if your sql has a big table or index scan in it,  the scan might be considered a longop, the sql itself is not.  If your sql has lots and lots of small operations you very likely won't see it in the longops view because no one operation is considered "long"

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.