0

I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.

Could someone please advise how I can examine that active query/session in DB?

I'm using DB visualizer tool.

2
  • I want to look what session is running on a particular table and it's explain plan Commented Aug 25, 2017 at 8:34
  • In my company db visualizer tool is being used but I don't know how to find out using it :( Commented Aug 25, 2017 at 14:20

2 Answers 2

1

There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.

enter image description here

You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.

Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.

0

you can try this query once. This query displays SQL currently active databases

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.