RE: All phasis are aborted only cleanup is completed

Posted by ErmanArslansOracleBlog on
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/All-phasis-are-aborted-only-cleanup-is-completed-tp1900p1912.html

Hi Latifa,

With this kind of an approach, you can't just be sure which sessions are exactly doing what, exactly waiting for what and the relation between their Session ids and OS process ids.

You can get that info by joining v$process and v$session.
concantrate on the wait events of these session to see if they are waiting for any db resources. (This might give you a clue)
You should write your control query accordingly.
Bytheway,
Don't you have a tool like Toad, which can assist you on finding the sessions, checking their waits and displaying the OS process id..?

On the other hand, the queries that you sent, seem normal.
It seems that online patching is trying to revise some indexes and it checks the data dictionary for that.

However,  gathering the following stats may increase the speed of those queries.. (if they are the actual causes ofcourse)

exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

ofcourse these stats gathering should have been done before the problematic phase of adop.
Anyways, I hope you are doing fine, but if you need more assistance, send me the v$ession joined v$process outputb(with waits) + patch number + adworker statuses (a screenshot) + adworker logs + AWR taken in the problematic time interval.