We are using version 12.1.0.2.0 of oracle. We have blocking session alert setup done using below query. And the expectation was to see this mainly when data modification happening and some session is taking exclusive lock for a significant amount of time and another session is waiting. And in this case we want to dig into the session and see if any deign issue or anything else. But i see from history many times the query which was running comes out to be SELECT query running in PARALLEL threads. And i see the query co-ordinator session i.e. the parent session seems to be blocked by its own slaves. The query co-ordinator session is showing wait event "Px Deq Execute Reply" and the slave i.e. blocking session is showing wait event "cell single block physical read" and it(slave) appears to be reading from UNDO for long time.
My understanding was only data modification can cause blocking session , But as here i see, so if its normal scenario that SELECT/READ queries can also be posted in v$session as blocking session. And if this is bug or normal behavior? Should we concern about this alert or should modify it someway to only look into real exclusive lock?
select....
FROM gv$session
WHERE blocking_session IS NOT NULL
AND seconds_in_wait > 180;