Hi we are using version 11.2.0.4 Of Oracle. We see one of the select query which used to run in less than a milli seconds, but suddenly we saw its taking ~10minutes+ , and was experiencing concurrency wait(mainly "cursor: pin S wait on X" and few "library cache lock") . We verified there was no change in plan. But when we saw this all happened exactly during the same time when we have one change going on i.e. one ALTER TABLE statement(as below) which was running on one of the underlying table, which this SELECT was accessing with an index access path. And this ALTER ran for ~1.5hrs causing thousands of such SELECT to stuck for those amount of time. And i also validated the blocking session for the SELECT was showing the session which was doing ALTER and few times also i see the SELECT was blocking each other too.
Now i am trying to understand the reason behind the same waits. The column which was getting modified was not explicitly used in the SELECT query, so why the SELECT query was experiencing such concurrency wait?
Alter statement:-
"ALTER TABLE TAB1 MODIFY C10 CHAR(19)"