Definitionen

Hier beginne ich den Versuch, kurze Definitionen für Oracle-Fachbegriffe aufzuschreiben - und hoffe, dass eine solche Sammlung halbwegs übersichtlich bleiben kann ...
In der Regel denke ich mir diese Definitionen nicht selbst aus, sondern bediene mich bei den verlinkten Autoren.

Buffer Cache

Zugriff auf Buffer
"Here is a simple algorithm to access a buffer (I had to deliberately cut out so as not to deviate too much from our primary discussion):
  1. Hash the data block address (DBAs: a combination of tablespace, file_id and block_id) to find hash bucket.
  2. Get the latch protecting the hash bucket.
  3. If success, walk the hash chain, reading buffer headers to see if a specific version of the block is already in the chain. 
    1. If found, access the buffer in buffer cache, with protection of buffer pin/unpin actions. 
    2. If not found, then find a free buffer in buffer cache, unlink the buffer header for that buffer from its current chain, link that buffer header with this hash chain, release the latch and read block in to that free buffer in buffer cache with buffer header pinned.
  4. If not success, spin for spin_count times and go to step 2. If that latch was not got with spinning, then sleep (with exponentially increasing sleep time with an upper bound), wakeup, and go to step 2." - Riyaj Shamsudeen
Für den Buffer-Zugriff relevante Definitionen
  • Logical I/O (LIO)
    • Aufruf einer der in x$kcbwh verzeichneten Subroutinen - Jonathan Lewis
    • ein Blockzugriff, der mindestens ein Get für das cache buffers chains latch erfordert - Jonathan Lewis
  • db block get
    • Block-Lesezugriffe im current mode; enthält die aktuellste Version des Blocks (inklusive von Änderungen, die noch nicht commited wurden); es kann immer nur eine current copy eines Blocks im buffer cache geben; erscheinen in der Regel im Zusammenhang mit DML-Operationen (J.Lewis: "you should only change the latest version of the block"); bei FTS und IFFS wird der Segment Header im current mode gelesen (zumindest galt das für 8i, als Mark Bobak seine Erläuterung formulierte) - Mark Bobak
    • commit cleanouts werden nicht als db block gets behandelt, obwohl auch sie die aktuellste Version eines Blocks benötigen - Jonathan Lewis
    • erscheinen bei Index-Blockzugriffen, um uniqueness und referential integrity constraints zu prüfen: "behind the scenes, your session  needs to see uncommitted changes from other sessions (even though you, end-user aren’t allowed to see these changes) in case you are trying to make a change that will become illegal when the other session commits. This conflict is often the cause of mode 4 waits on TX enqueues." - Jonathan Lewis
  • consistent get
    • lesekonsistente Version eines Blocks (bezüglich einer SCN); wenn im buffer cache kein für einen benötigten Zeitpunkt konsistenter Block vorliegt, erzeugt Oracle die Version auf der Basis der Informationen in den rollback segmenten (oder liefert ora-01555) - Mark Bobak
    • auch der Zugriff auf die undo Informationen ist ein consistent get - Jonathan Lewis
    • wenn mehrere Transaktionen parallel auf den gleichen Daten arbeiten, kann das zu extremen consistent get Werten führen, da die benötigten Blocks immer wieder in einen lesekonsistenten Zustand gebracht werden müssen - Jonathan Lewis
  • pinned buffer
    • So how can it be safe to visit a buffered block without first using a latch to protect it - the answer is that you have to anticipate using it several times, so you use latching to acquire it the first time and latching to release it when you have finished with it, but pin it in the interim so that you can visit it several times without having to go through the CPU intensive process of competing for latches. - Jonathan Lewis 
    • If you want to understand the work Oracle has performed in terms of buffer visits you need to consider both, the number of logical I/Os as well as the number of buffers visited without involving logical I/O - this is represented by the "buffer is pinned count" statistics. Quite often this fact is overlooked and people only focus on the logical I/Os - which is not unreasonable - but misses the point about pinned buffers re-visited without doing logical I/O. Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the "fetchsize" or "arraysize" for bulk fetches can influence the number of logical I/Os required to process a result set. - Randolf Geist
  • consistent gets from cache (fastpath)
    • Optimierung in 11g, die erscheint, wenn ein Block immer wieder gelesen wird - und zu einer massiven Reduzierung von consistent gets und cache buffers chains latch-Zugriffen führt; betrifft möglicherweise nur Index-Blöcke - Alex Fatkulin
  • consistent get – examination
    • verwendet ein cache buffers chains latch um den Block zu finden, liest den Block und dropped das latch wieder ("probably the most efficient way of getting data from an 'unpinned' block in the buffer cache.") - Jonathan Lewis
    • nur möglich, wenn ein Block "very clean" ist (undo blocks, hash cluster table ohne hash collisions; root blocks eine Index: also Fälle, in denen beim Lesen kein Cleaning erforderlich ist) - Jonathan Lewis
  • buffer handle
    • object used to pin a buffer (technically it’s the buffer header, not a buffer itself) - Jonathan Lewis
  • buffer header
    • "Each buffer in the buffer cache has an associated element in the buffer header array, externalized as x$bh. Buffer headers keep track of various attributes and state of buffers in the buffer cache. This buffer header array is allocated in the shared pool. The buffer headers are chained together in a doubly-linked list and linked to a hash bucket. There are many hash buckets, and their number is derived and governed by the _db_block_hash_buckets parameter). Access to these hash chains (both to inspect and change) is protected by cache-buffers-chains latches." - Riyaj Shamsudeen
    • cache buffers chains latch (CBC latch)
      • "Cache buffers chains latches are taken when a process wants to walk through a cache buffer hash chain, looking if the block with required DBA (data block address) is in buffer cache. If the block happens to be in cache, then in most cases it has to be pinned first before use and unpinned after use, to make sure no-one else can perform an incompatible operation on that block at the same time. The modification of pin structures (pinning/unpinning) is also protected by CBC latches." - Tanel Poder
      • die Anzahl verfügbarer Latches wird über den Parameter _db_block_hash_latches gesteuert. "A CPU bottlenecked CBC constrained Oracle system's performance will likely be improved by increasing the number of CBC latches beyond Oracle's default values... up to a point." - Craig Shallahamer
      • Scripts zur Diagnose von CBC latches und Erläuterungen zu den üblichen Pattern ihres Auftretens liefert ein weiterer Blog-Artikel von Craig Shallahamer.
    • v$bh
      •  "displays the status and number of pings for every buffer in the SGA" - Doku
      • mit Hilfe der Spalte TCH lassen sich Hot Blocks bestimmen: "Almost every access to a buffer increments the tch value for that buffer header" - Riyaj Shamsudeen

    Storage
    • object_id
      • "Dictionary object number of the object" - Doku
    • data_object_id
      • "Dictionary object number of the segment that contains the object" - Doku
      • Clustered tables have different object ids but the same data object id. The data object_id for a given object id will change if you truncate or move a (non-clustered) table, or rebuild an index. - Jonathan Lewis
    • data block
      • "On average, the block overhead totals 84 to 107 bytes." - Doku
      • itl: "transaction entries in most operating systems require approximately 23 bytes." - Doku
      • row directory: 2 byte pro Satz
      • Datensatz: 
        • 2 byte für einen gelöschten Satz (lock byte, flag byte - z.B. D für deleted)
        • mindestens 3 byte für einen nicht gelöschten Satz (zusätzlich: column count) - Jonathan Lewis
    Latches, Mutexes, Locks
    • Latches
      • "A simple, low-level serialization mechanism to protect shared data structures in the System Global Area." Doku (11.1)
      • "Latches and mutexes are the Oracle proprietary realizations of general spinlock concept." Andrey Nikolaev
        • Der Artikel von Andrey Nikolaev erläutert die unterschiedlichen Formen der Implementierung von Spinlocks und erläutert, dass Oracles Latches eines der einfachsten Spinlocks darstellen ("Oracle latch used simple TTS with subsequent sleep. Why Oracle does not use more advanced spinlocks?")
      • zwei weitere interessante Artikel zur Latch Analyse findet man bei Frits Hoogland.
    • Mutex
      • sind Serialisierungsmechanismen, die den Zugriff auf Ressourcen steuern
      • einfacher als Latches: "mutex operatins require less CPU instructions than latch operations"
        • "the main scalability benefit comes from that there’s a mutex structure in each child cursor handle and the mutex itself acts as cursor pin structure. So if you have a cursor open (or cached in session cursor cache) you don’t need to get the library cache latch (which was previously needed for changing cursor pin status), but you can modify the cursor’s mutex refcount directly (with help of pointers in open cursor state area in sessions UGA). Therefore you have much higher scalability when pinning/unpinning cursors (no library cache/library cache pin latching needed, virtually no false contention) and no separate pin structures need to be allocated/maintained." - Tanel Poder
      • in 11g wurden fast alle library cache latches durch mutexes ersetzt - Tanel Poder
      • umfangreiche technische Details findet man bei Andrey Nikolaev
    • Library Cache Lock
      • Parse Locks: "Library cache locks aka parse locks are needed to maintain dependency mechanism between objects and their dependent objects like SQL etc. For example, if an object definition need to be modified or if parse locks are to be broken, then dependent objects objects must be invalidated. This dependency is maintained using library cache locks. For example, if a column is dropped from a table then all SQLs dependent upon the table must be invalidated and reparsed during next access to that object. Library cache locks are designed to implement this tracking mechanism. [...] Library cache pins deals with current execution of dependent objects. For example, an underlying objects should not be modified when a session is executing or accessing a dependent object (SQL). So, before parse locks on a library cache object can be broken, library cache pins must be acquired in Exclusive mode and then only library cache objects can be dropped. If a session is executing an SQL, then library cache pins will not be available and there will be waits for library cache pins. Typically, this happens for long running SQL statement." Riyaj Shamsudeen


          Bei Jonathan Lewis findet man übrigens eine Glossary-Seite, die ich hier noch mal abschreiben könnte, was aber vielleicht nicht besonders hilfreich wäre...

          Eine andere Alternative wäre die Übernahme der Definitionen, die Jonathan Lewis in Oracle Core gibt, aber auch das überzeugt mich zur Zeit nicht vollständig.