DB: concurrency issue with Hash Join

In 11gR2 or 12cR1, Hash joins may be impacted with the issue described in the MOS 2189126.1).

To tentatively eliminate the row cache wait on dc_users and dc_tablespaces, trying to find the minimum extent size of the temporary tablespace of the user.

Note down the value for the tempory tablespace.

SELECT name
       ,decode(bitmapped,0,least(DFLincr,dflinit),bitmapped)-1 "Level"
FROM   ts$
/

 

Set the event and bounce the database.

ALTER SYSTEM SET event='45053 trace name context forever, level 127' SID='*' SCOPE=SPFILE
/
SHUTDOWN IMMEDIATE
/
STARTUP
/