Database Reference
In-Depth Information
SID OWNER NAME TYPE HELD REQUEST
------ -------- --------------------- -------------------- ---------- --------
22 SYS DBMS_OUTPUT Body Null None
22 SYS DBMS_OUTPUT Table/Procedure/Type Null None
22 EODA EODA 18 Null None
22 SYS DBMS_APPLICATION_INFO Body Null None
22 SYS PLITBLM Table/Procedure/Type Null None
22 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
22 EODA 73 Share None
22 SYS DATABASE 18 Null None
8 rows selected.
We find that
P
is now missing from the view. Our parse lock has been broken.
This view is useful to you, as a developer, when it is found that some piece of code won't compile in the test or
development system—it hangs and eventually times out. This indicates that someone else is using it (actually running
it), and you can use this view to see who that might be. The same will happen with
GRANT
statements and other types
of DDL against the object. You cannot grant
EXECUTE
on a procedure that is running, for example. You can use the
same method to discover the potential blockers and waiters.
■
oracle 11
g
release 2 and above introduces the
feature
edition-based redefinition (eBr). With eBr, you can, in
fact, grant eXeCUte and/or recompile code in the database without interfering with users currently executing the code.
eBr allows you to have multiple versions of the same stored procedure in a schema at once. this allows you to work on a
copy of the procedure in a new edition (version) without contending with the current version of the procedure being used
by other users. We will not be covering eBr in this topic, however, just mentioning it when it changes the rules.
Note
Latches
Latches
are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects,
and files.
Latches are locks designed to be held for extremely short periods of time—for example, the time it takes to modify
an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer
cache or the library cache in the shared pool. Latches are typically requested internally in a
willing to wait
mode.
This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the
operation later. Other latches may be requested in an immediate mode, which is similar in concept to a
SELECT FOR
UPDATE NOWAIT
, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that
may be free, rather than sit and wait for this latch to become available. Since many requestors may be waiting for a
latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly,
based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There
is no line of latch waiters—just a mob of waiters constantly retrying.
Oracle uses atomic instructions like “test and set” and “compare and swap” for operating on latches. Since the
instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets to test
and set the latch even though many processes may be going for it simultaneously. Since the instruction is only one
instruction, it can be quite fast (but the overall latching algorithm itself is many CPU instructions). Latches are held
for short periods of time and provide a mechanism for cleanup in case a latch holder dies abnormally while holding it.
This cleanup process would be performed by PMON.
