This is interesting. I have always thought oracle latches were kind of mysterious, for lack of a technical term. Don’t get me wrong, I understand semaphores (I have programmed with these before), and “test and set” operations on certain types of computer processors. However, oracle’s rules are so intertwined that I never have a clear answer on when which latch is taken.
However, I can definitely say that when you grant privileges on an object in a production database in the middle of a busy day, you could have problems. You probably know where this is going.
Today, an application installer was installing some packages in a new schema. He was granting select/insert/update/delete on several tables in the data schema. We started seeing a large number of “library cache lock” wait events for users in another schema that were accessing the tables on which select privileges were being granted to the new schema.
It turns out that this operation will actually stamp the LAST_DDL_TIME column of DBA_OBJECTS. It doesn’t invalidate the object in the STATUS column of DBA_OBJECTS, but there is a latch that existing sessions request as a result of this “invalidation”.
See Metalink note 444560.1 for more information.
“Be very careful with altering, granting or revoking privileges on database objects that frequently used stored PL/SQL is dependent on. In fact, resolving this issue mostly depends on application project and system maintenance practices.”
2 comments for “Library cache lock during grant/revoke”