Data dictionary find time of long running query

pts.
Tags:
SQL
Howdi All, I'm trying to figure out a way to find queries running longer than X minutes in the data dictionary. It's been difficult to limit it to a specific query. Sessions are no problem. Here's the latest query I've come up with. SELECT sess.status, sess.username, sess.machine, sess.program, sess.process, s.optimizer_cost, ops.message, round(ops.elapsed_seconds/60), TRIM (s.sql_text), ops.time_remaining FROM v$sql s, v$session sess, v$session_longops ops WHERE s.address = sess.sql_address AND sess.status = 'ACTIVE' AND ops.sid = sess.sid AND sess.username not like 'SYS%' Thanks. -jim

Answer Wiki

Thanks. We'll let you know when a new response is added.

Jim:

I’m not sure what you mean by ‘limit it to a specific query’.

Do you mean search for specific query text with LIKE in v$sqltext or v$sqlarea?

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tmjimob
    I mean that v$session tells how long a specific session has been running, not how long a particular query is running. I'm trying to find all queries running longer than a certain amount of time. I don't care about sessions, because sessions that access our database in general run many queries. That's why I join with the "long ops" table. However, the results from this join don't seem very accurate. Thanks.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following