cursor: pin S wait on X

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. createorreplaceprocedurep
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 iin1..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 (iin(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/119501/viewspace-607460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/119501/viewspace-607460/