cursor: pin S wait on X

zhouwf0726發表於2019-06-21
AI

BTW, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new in 10.2 and they enable shared access to objects in somewhat similar manner to shared latche; every successful get of a particular mutex will increment its value and a release will decrement. When the count is zero, no one has the mutex and it is safe to get it in exclusive mode. However, they are more fine-grained than kgl latches and provide a better wait mechanism, as far as I understand.
So if your environment supports atomic compare and swap operation (such as CMPXCHG on Intel), you might get away without cursor_space_for_time setting for ultrahigh execution rates. Otherwise the atomic mutex operations would be achieved using the new KGX latches.
At least on my laptop this feature isn't enabled by default (from an OracleWorld paper I remember that it should become default in 10.2.0.2), but so far you can experiment with it if you set _kks_use_mutex_pin = true and bounce the instance (mutex structures will be stored in the shared pool, so you might need to increase shared pool size).

There are also X$MUTEX_SLEEP and X$MUTEX_SLEEP_HISTORY fixed tables that can show some interesting information if you generate some mutex waits into them.

Now, I don't suggest for a moment that you have to understand this response. It is at an extremely deep technical level, and the practical applications of such understanding are probably few and far between. My point, though, is that you must take the time to study and understand Oracle internal operations if you are to have success in Oracle performance optimization. There are no shortcuts to making a database application run efficiently. You must understand good SQL practice, good PL/SQL practice and good host-language practice for starters. Beyond that, you ought to know how Oracle manages concurrency, how Oracle processes SQL statements and how Oracle performs data and code (SQL and PL/SQL) caching. If you're asking the question "How do I set up an Oracle database for optimal performance?" you have a long way to go. Don't try to avoid the effort; take the time to study and learn, and you will reap the rewards.

 

 

http://www.itpub.net/thread-1003340-1-2.html
http://www.itpub.net/viewthread.php?tid=1004815&page=1&extra=

 

http://yumianfeilong.com/2007/05/23/mutexes-in-oracle10g/

摘要Mutexes的介紹從http://archive.netbsd.se/?ml=oracle-l&a=2006-04&t=1969601


To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2.
For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins.
Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism.
The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex toTRUE.

Btw, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new thing in 10.2 and they enable shared access to objects in somewhat similar manner than shared latches, that every successful get of particular mutex will increment its value and release will decrement. When the count is zero, no-one has the mutex and it is safe to get it in exclusive mode too. However they are more fine grained than kgl latches and provide better waiting mechanism as far as I understand.

So if your environment supports atomic compare and swap operation (as CMPXCHG on Intel), you might get away without cursor_space_for_time setting for ultrahigh execution rates. Otherwise the atomic mutex operations would be achieved using new KGX latches.

At least on my laptop this feature isn’t enabled by default (from and
OracleWorld’s paper I remember that it should become default in 10.2.0.2), but so far you can experiment with it if you set _kks_use_mutex_pin = true and bounce the instance (mutex structures will be stored in shared pool, so you might need to increase SP size).


Oracle10g中，對shared pool中的一些Serialization operation使用更輕量的 KGX mutexes (_use_kks_mutex) 取代library cache pin，從而降低CPU Usage.

_use_kks_mutex = TRUE的時候，

SQL> exec p
SQL AREA     393       3     630       0
SQL AREA     393       3     630       0
SQL AREA       0       0       0       0
 
PL/SQL procedure successfully completed.
 
SQL> exec p
SQL AREA     394       3     632       0
SQL AREA     394       3     632       0
SQL AREA       0       0       0       0
 
PL/SQL procedure successfully completed.

_use_kks_mutex = FALSE的時候，

SQL> set serveroutput on
SQL> exec p
SQL AREA     360       3   4,611   3,960
SQL AREA     360       3   5,610   4,959
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 
SQL> EXEC P
SQL AREA     363       3   5,643   4,986
SQL AREA     363       3   6,642   5,985
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.



儲存過程程式碼如下，

  1. create or replace procedure p
  2. authid current_user
  3. as
  4. l_ns varchar2(4000);
  5. l_gets number;
  6. l_gethits number;
  7. l_pins number;
  8. l_pinhits number;
  9. l_sgets number;
  10. l_sgethits number;
  11. l_spins number;
  12. l_spinhits number;
  13. begin
  14. for i in 1 .. 1000
  15. loop
  16. execute immediate
  17. 'select namespace, gets, gethits, pins, pinhits
  18. from v$librarycache
  19. where namespace = ''SQL AREA'' '
  20. into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
  22. if ( i in (1,1000) )
  23. then
  24. if ( i = 1 )
  25. then
  26. l_sgets := l_gets; l_sgethits := l_gethits;
  27. l_spins := l_pins; l_spinhits := l_pinhits;
  28. end if;
  29. dbms_output.put_line
  30. ( l_ns || to_char(l_gets,'999,999') ||
  31. to_char(l_gethits,'999,999') ||
  32. to_char(l_pins,'999,999') ||
  33. to_char(l_pinhits,'999,999') );
  34. if ( i = 1000 )
  35. then
  36. dbms_output.put_line
  37. ( l_ns || to_char(l_gets-l_sgets,'999,999') ||
  38. to_char(l_gethits-l_sgethits,'999,999') ||
  39. to_char(l_pins-l_spins,'999,999') ||
  40. to_char(l_pinhits-l_spinhits,'999,999') );
  41. end if;
  42. end if;
  43. end loop;
  44. end;
  45. /

從測試中可看到，使用mutex，library cache pin大幅度降低。
Oracle這個演算法改進是oracle10g中的一個亮點。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-348176/，如需轉載，請註明出處，否則將追究法律責任。

相關文章