access to a cursor in the Library Cache • It must acquire a shared mutex to read the cursor • Another session has an exclusive mutex that locks the cursor Cursor: Pin S Wait on X
resources • Guarantee consistency • Spinlocks: waiters "spin" on CPU • Latch: prone to false contention (one latch controls many objects) • Mutex: precise and faster; part of the object's memory structure Latches and Mutexes
more precise than latches • Often implemented directly under hardware = FAST • Dynamic—part of/internal to the object it protects • Not tracked/instrumented individually to reduce overhead Mutexes
• Oracle collects statistics on mutex activity, not individual mutexes • Sleeping is a wait event • Spinning is not a wait—it is registered as CPU time • Mutex sleeps indicate a concurrency problem... ...but not necessarily a database concurrency problem Mutexes
processing • Non-database sources of mutex sleeps/concurrency: • Oversubscribed CPU • Bad PL/SQL, deadlocking, triggers • Altering OS process priority • Bugs Mutexes
on CPU can lead to CPU death spirals • Back-off mechanisms are a safety valves • Latches sleep or get a semaphore wait • Pre-11.2.0.4, mutexes had no back-off mechanism Spinlocks and Backoff Controls
• _mutex_wait_scheme: Controls mutex backoff behavior • _mutex_spin_count: How many times the process "spins" (255) • _mutex_wait_time: How long the process sleeps (10ms) Mutex Backoff Behavior
are saved in the SGA's (shared) Library Cache • The Library Cache is organized as a hash tree • Hashing a SQL statement generates a hash value • That hash value is the cursor's address in the Library Cache What is a cursor?
permission checks • Evaluates access paths, performs cost-based optimization • Creates an executable version of the cursor (think: compiled) Parsing: Hard Parse
metadata • Bind variables are in the SQL text and produce identical SQL hashes • Saves space in the Library Cache • Reduces impact of hard parsing • Soft parsing evaluates bind values • Oracle reuses the existing execution plan if possible Cursor Sharing and Soft Parsing
• The SQL is the same, but existing cursors can't be shared, so: • The SQL hash maps to an existing address in the Library Cache • The dynamic metadata creates a new child cursor under the parent Cursor Sharing
with different execution plans • A parent cursor can be marked: • Bind-sensitive: Cursor can be re-parsed to improve the plan • Bind-aware: Child execution plans are based on bind values • Bind-aware cursors need something between a hard and soft parse • Optimizer reviews children for potential reuse based on bind values Cursor Sharing
cursor caching (if enabled) eliminates* soft parsing • Stores repeat/re-entrant session SQL in PGA w/pointer • Pointer to the child cursor in the Library Cache Session Cursor Cache * If the cursor is open; Oracle still con fi rms/validates the cursor
• It located a hash tree entry in the Library Cache • It wants to set a shared mutex on a cursor to guarantee consistency • The cursor is locked with an exclusive mutex Cursor: Pin S Wait on X
the Library Cache hash tree • This entry corresponds to a parent cursor—not an execution plan • The parent cursor must be bind-aware • Optimizer searches for, runs a compatible (cached) child cursor • Bind-aware cursors perform a semi-hard parse to evaluate binds • A match means the binds are compatible with an existing child Session Cache Miss/Library Cache Hit
cursor (heap 0) • Heap 0 points to a hash table of child cursors • The hash table is organized from newest to oldest • Assumes younger children are better matches • Each read requires a shared mutex—Pin S Finding Compatible Child Cursors
Check the child for compatibility • If compatible, execute; if not, release shared mutex • Move to the next-youngest child cursor • Repeat until fi Child Cursor Read Details
create a new child cursor • The process is called an Incomplete List Insertion • Adds a new entry at the end of the child cursor hash table • At this point, the entry is an incomplete child cursor (unparsed) • The new entry requires a hard parse No Compatible Children
Adding child cursors alters the parent cursor hash table • kkshinis sets an exclusive mutex (pin X) on the parent cursor ...and places an exclusive mutex on the incomplete child cursor Incomplete List Insertion
cursor cache, etc. • Hash matches a parent cursor in the Library Cache • The parent cursor has an exclusive mutex. • Is Session B blocked? Genesis of the Pin S Wait on X
change the parent cursor! • Begins a top-down scan of the child hash table for a compatible cursor • If found, it uses the cursor • Eventually, Session B reaches the end of the hash table, encountering the incomplete child cursor Genesis of the Pin S Wait on X
and when fi • kkshindel deletes the cursor from the incomplete list • kkshinins inserts (moves) the cursor to the top of the list • Inserting the new cursor shifts existing cursors down Incomplete List Removal kkshindel remove child from incomplete list bi=XXXXXXXXXXXX cld=YY flg=ZZ kkshhcins insert child into hash table bi=AAAAAAAAAAAA cld=BB flg=CC
fi • ...because bind values don't match • ...because CPU can't keep up with hard-parsing • ...because cursors are invalidated/moved out of cache • ...because of database parameter settings or bugs • ...because of concurrency issues inside or outside the database Finding Patterns in Randomness
sessions over blockers • Prioritizing database sessions/processes in the OS • Blocked sessions in prioritized by Oracle Resource Manager • OS applies priority decay to reschedule processes spinning on CPU CPU Issues
when sessions are short-lived • Mismatches between client and database environments (NLS, etc) • Undersized SGA • Resizing the Shared Pool/Buffer Cache, esp. as workload changes • cursor_sharing=force Database Configuration
be an OS issue • Application code causing deadlocks • Devastating when a blocked session holds an exclusive mutex! • Triggers • PL/SQL, application code, OS operations that "manage" concurrency • Poor lock implementations, incomplete cleanup, etc. Concurrency
(in the shared Library Cache) • Child cursors are keyed to their PDB—they can't be reused elsewhere • Prevents local PDB activity from blocking other PDBs • Prevents cache "poisoning" • An exclusive mutex on heap 0 on the parent cursor is global Multitenant
• who discovered it? • who/what does it affect? • how do we reproduce it? • what's been done so far? • if intermittent, how often or what is the timing? • is it related to or dependent on something else?
X' waits. • 1353015.1: How to Identify Hard Parse Failures • 1356828.1: FAQ: 'cursor: mutex/cursor: pin/library cache: mutex' Type Wait Events • 1377998.1: Troubleshooting: Waits for Mutex Type Events • 1373500.1: Top 5 Database and/or Instance Performance Issues in RAC Environment • 1377446.1: Troubleshooting Performance Issues • 296377.1: Troubleshooting: High Version Count Issues • 786507.1: How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' Resources
• 1268724.1: "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]' • 2548359.1: High Waits On Library Cache Lock or Cursor: Pin S wait on X Due To DS_SVC Queries From 12.2 Onwards • 2585072.1: Synonym with Same Name in Loop-back DB link Causing 'SQL*Net message from dblink' and 'Cursor: pin S wait on X' Resources
High Cursor: Pin S Wait On X • 2963869.1: High waits for "cursor: pin S wait on X" on a recursive query from sys.hist_head$ • 402027.1: Bug:5653007; 5485914: SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with SQL_TRACE enabled. • 742599.1: High 'cursor: pin S wait on X' and/or 'library cache lock' Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity • 9472669.8: Bug 9472669 - 'cursor: pin S wait on X' waits for invalid SQL over DB link Resources
oraclesean
masahirokawahara
hironobuiga
tomoki10
tatsutaka
happysamurai294
oikon48
nrug_member
hssh2_bin
ry
bitkey
moepy_stats
yanzm
mraible
eileencodes
techseoconnect
michaelherold
addyosmani
baasie
ammeep
chriscoyier
minecr
katiecoart
oripsolob
palkan
![www.viscosityna.com @ViscosityNA Questions [email protected] https://linktr.ee/oraclesean](https://files.speakerdeck.com/presentations/2147fd7674f04295a8dc44c7ce96eb4a/slide_74.jpg){kind=link}
