Resolving Child Cursor Issues Resulting In Mutex Waits

50 %
50 %
Information about Resolving Child Cursor Issues Resulting In Mutex Waits

Published on January 29, 2016

Author: MartinKlier

Source: slideshare.net

1. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Martin Klier Senior DBA Klug GmbH integrierte Systeme San Francisco, Sept 30th, 2012 Resolving Child Cursor Issues Resulting In Mutex Waits

2. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons •Issues, Quick Fixes and Solutions •Summary, Acknowledgements, Q&A session Agenda

3. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Martin Klier (martin.klier@klug-is.de) •Senior DBA for at •Focus on Performance, Tuning and High Availability •Linux since 1997, Oracle since 2003 •Weblog: http://www.usn-it.de Speaker

4. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Klug GmbH integrierte Systeme (http://www.klug-is.de) 92552 Teunz, GERMANY •Specialist leading in complex intralogistical solutions •Planning and design of automated intralogistics systems, focus on software and system control / PLC •>300 successful major projects in Europe, America, Asia

5. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits iWACS®

6. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons •Issues, Quick Fixes and Solutions •Summary, Acknowledgements, Q&A session Agenda

7. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Parsing Optimization (Hard Parse) Execution Soft Parse Shared Pool 0 or 1 child SQL Cursor Sharing Insert cursor + single child

8. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Parsing Execution Shared Pool 1 or n children SQL Adaptive Cursor Sharing I Cardinality Feedback Soft Parse Bind Mismatch or ~62 other Reasons

9. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits 64 Reasons for not re-using an existing child cursor •Optimizer mode change (ALL_ROWS, FIRST_ROWS) •NLS- and user identity trouble, •Outline mismatch, Cardinality feedback (wanted) •Bind mismatch (many sub-reasons, most unwanted) •... Parsing

10. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Parsing Optimization (Hard Parse) Execution “Harder“ Soft Parse (choose child cursor) Shared Pool >1 children SQL Adaptive Cursor Sharing II one more child cursor one more child cursor

11. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Parsing “Hard” - “Soft” - “Harder Soft” Parent Cursor <bind aware> Child Cursor Child Cursor Child Cursor SQL Peeking New Child Cursor CBOMutex X Cursor Mutex S #

12. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons •Issues, Quick Fixes and Solutions •Summary, Acknowledgements, Q&A session Agenda

13. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Mutex Example: Simultaneously removing two nodes from a singly linked list (picture from Wikipedia)

14. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Mutex •“Mutual Exclusion” = Fine-grained serialization structure •It's just like a latch, but smaller, lighter, faster •Introduced in 10g R2 •managed by KGX (Kernel Generic Mutex Module)

15. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits LC object (Cursor) Session 1 Session 2 Sleep = Wait S2 spinning on M. M S1 holding Mutex = Mutex Contention

16. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits What are Wait Events (on Mutexes)? •Somebody requests a Mutex •Can not get it by spinning •And thus, sleeps •Sleeping is recorded as wait time •Spinning is not recorded as wait-, but as CPU time

17. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits cursor: mutex X Wants: Exclusive mode on Mutex of Parent / Child To: •Build a new Child Cursor •Capture SQL bind data (peek) •Modify cursor-related statistics

18. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits cursor: mutex S Wants: Shared mode on Mutex of Parent / Child To: •Change the reference count (“in flux”) = “new guy is interested / spinning”

19. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits cursor: pin X Wants: Exclusively pin a P/C cursor in cache To: •Create the cursor •Alter the cursor

20. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits cursor: pin S Wants: Pin a P/C cursor in shared mode To: •Use (execute) the cursor

21. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Waits cursor: pin S wait on X Wants: Pin a P/C cursor in shared mode but sb. already has it in exclusive mode To: •Use (execute) the cursor •When sb. is altering the cursor (e.g. due to DDL)

22. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons •Issues, Quick Fixes and Solutions •Summary, Acknowledgements, Q&A session Agenda

23. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Simple - cursor: pin S •Caused by massively parsing one SQL_ID => Hot Spot Object in Library Cache •Diagnosis: Oracle Wait Interface •(Half) solution: Diversify SQL_ID (not randomize!) select /* WebServer4 */ something from table; Issues

24. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Issues Provocation of a cursor: pin S wait situation •Tightly looping one SQL 1,000,000 times •in 20 threads ➔ Overcrowding the cursor's pin mutex

25. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Complex - cursor: mutex S/X •Root-caused by invalidated child cursor(s) => Too many cursor objects in Library Cache •Diagnosis: •Oracle Wait Interface •10046 Level 12 session trace (=sql_trace event) •v$sql_shared_cursor plus cursortrace [296377.1] Issues

26. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits One example reason for cursor: mutex S/X Application uses jdbc setter methods improperly on INTEGER column (=2) •setNUMBER(2) => Bind Var. is NUMBER •setNULL(2) => Bind Var. is VARCHAR2 Issues = BIND MISMATCH

27. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits 10046 Level 12 trace for cursor: mutex S/X Trace 1: Bind#2 >> oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=168 kxsbbbfp=1118e1cd8 bln=32 avl=00 flg=01 Trace 2: Bind#2 >> oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=168 kxsbbbfp=110977db8 bln=22 avl=02 flg=01 value=99 Issues in 30 columns = 2^30 times BIND MISMATCH

28. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits One Quick Fix for cursor: mutex S/X System is loaded with heavy mutex waits due to high number of cursors (=version count) => frequently flush this cursor with dbms_shared_pool.purge (look out for new parsing issues = CPU) Issues

29. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits One solution for cursor: mutex S/X Application uses jdbc setter methods now properly on INTEGER column (=2) •setNUMBER(2) => Bind Var. is NUMBER •setNULL(2, java.sql.Types.INTEGER) => Bind Var. is NUMBER Issues

30. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Issues Provocation of a cursor: mutex S/X wait situation •Generating 64 child cursors for one SQL_ID •Accessing them 20x parallel ➔ Delay to create new children (X) ➔ Delay to select good child (S)

31. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Similar Problem with CHAR binds •Bind buffers are size 32, 128, 2000 or 4000 bytes •Changing CHAR bind length invalidates •Reason BIND_LENGTH_UPGRADEABLE = 4^n cursor versions Issues

32. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Heavy - Oracle internal pitfalls I •11g “features” like MOS: “Its important to note that cursor obsoletion code was removed in version 11. That means we no longer obsolete a parent cursor when it reaches 1024 child cursors [as we did in 10g.]” •Workaround Enhancement Patch 10187168 introduces parameter “_cursor_obsolete_threshold” Issues

33. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Heavy - Oracle internal pitfalls II •DB Bugs like 10157392 + 12939876 (fixed in 12.1, backported to 11.2.0.3) Memory leak: increasing number of child cursors over time, especially if the shared pool is under load. •DB Bug 9591812 (fixed in 12.1) Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X") Official MOS workaround: Be cautious when interpreting S mode mutex / pin waits.... Issues

34. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons •Issues, Quick Fixes and Solutions •Summary, Acknowledgements, Q&A session Agenda

35. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits My suggestions for “cursor: mutex S/X” casualties •Check how the application does handle bind variables Avoid BIND MISMATCH at (nearly) any cost •Reduce the number of cursor versions below 100 More will lead to overhead •Look for matching Oracle bugs in your RDBMS release •Upgrade to 11.2.0.3 or higher 11.2.0.2 is worst version for cursor issues IMHO I suggest...

36. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits More resources on this topic •MOS Documents 1356828.1; 1377998.1; 296377.1 •Põder, Tanel Presentation: “Oracle Latch and Mutex Contention Troubleshooting” •Shallahamer, Craig Book: “Oracle Performance Firefighting” (ISBN 978-0-9841023-0-3) •Nikolaev, Andrey Blog entries: “Mutex waits. Part 1 + 2” Read on...

37. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Thank you very much for your attention! Martin Klier Senior DBA Klug GmbH integrierte Systeme San Francisco, Sept 30th, 2012

38. Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits Thank you Many people have helped with suggestions, supplying test cases or taking daily work off me during preparation and travel phase. Guys, you are top! My special thanks to: My boss and company, for endorsement Two special customers, for interested patience An ex-colleague, for The Code One guy from OR, who made it possible.

Add a comment

Related pages

Resolving Child Cursor Issues Resulting In Mutex Waits

Martin Klier – Resolving Child Cursor Issues Resulting In Mutex Waits •Introduction •Oracle Parsing, Child Cursors •Mutexes, Waits and Reasons
Read more

CURSOR ISSUES RESULTING IN MUTEX WAITS - Usn's IT Blog

Database – “Oracle Internals & Performance” Bootcamp RESOLVING CHILD CURSOR ISSUES RESULTING IN MUTEX WAITS Martin Klier, Senior DBA at Klug GmbH ...
Read more

Resolving Child Cursor Issues - performing databases

@MartinKlierDBA – Resolving Child Cursor Issues Resulting In Mutex Waits Heavy - Oracle internal pitfalls II •DB Bugs like 10157392 + 12939876
Read more

Termin: Regionaltreffen Nürnberg/Franken

Agenda: 17:30: Begrüßung / Neues von der DOAG: Daniel Saraci, DOAG : 17:45: Resolving child cursor issues resulting in mutex waits → Details
Read more

11.2.0.1 library cache: mutex X | Oracle Community

... We have been experiencing system outages due to library cache: mutex X waits ... cursor: pin S wait ... resolving-child-cursor-issues-resulting ...
Read more

Issues | LinkedIn

Resolving Child Cursor Issues Resulting In Mutex Waits. 0 Views. guestffe27a. Environmental Issues and Sustainability. 22,517 Views. davidmcg ...
Read more

Blog - Page 2 of 2 - performing databases

Blog Blog DOAG Database Conference 2015 begins #DOAGDB15. 16. June 2015 6. October 2015. ... “Resolving child cursor issues resulting in mutex waits ...
Read more

How does Oracle Mutexes work? | TwelveC

Posts about How does Oracle Mutexes work? written ... Simple Solutions to “cursor: pin s” wait ... Please not that the child_address(child cursor) ...
Read more