ITPub博客

首页 > Linux操作系统 > Linux操作系统 > cursor: pin S wait on X

cursor: pin S wait on X

原创 Linux操作系统 作者:wuhesheng 时间:2009-06-24 17:17:00 0 删除 编辑

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>execp
SQLAREA     393       3     630       0
SQLAREA     393       3     630       0
SQLAREA       0       0       0       0
 
PL/SQLproceduresuccessfullycompleted.
 
SQL>execp
SQLAREA     394       3     632       0
SQLAREA     394       3     632       0
SQLAREA       0       0       0       0
 
PL/SQLproceduresuccessfullycompleted.

_use_kks_mutex = FALSE的时候,

SQL>setserveroutputon
SQL>execp
SQLAREA     360       3   4,611   3,960
SQLAREA     360       3   5,610   4,959
SQLAREA       0       0     999     999
 
PL/SQLproceduresuccessfullycompleted.
 
SQL>EXECP
SQLAREA     363       3   5,643   4,986
SQLAREA     363       3   6,642   5,985
SQLAREA       0       0     999     999
 
PL/SQLproceduresuccessfullycompleted.



存储过程代码如下,

  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;
  21. if (iin(1,1000))
  22. then
  23. if (i=1)
  24. then
  25. l_sgets:=l_gets;l_sgethits:=l_gethits;
  26. l_spins:=l_pins;l_spinhits:=l_pinhits;
  27. end if;
  28. dbms_output.put_line
  29. ( l_ns||to_char(l_gets,'999,999')||
  30. to_char(l_gethits,'999,999')||
  31. to_char(l_pins,'999,999')||
  32. to_char(l_pinhits,'999,999') );
  33. if (i=1000)
  34. then
  35. dbms_output.put_line
  36. ( l_ns||to_char(l_gets-l_sgets,'999,999')||
  37. to_char(l_gethits-l_sgethits,'999,999')||
  38. to_char(l_pins-l_spins,'999,999')||
  39. to_char(l_pinhits-l_spinhits,'999,999') );
  40. end if;
  41. end if;
  42. end loop;
  43. end;
  44. /

从测试中可看到,使用mutex,library cache pin大幅度降低。
Oracle这个算法改进是oracle10g中的一个亮点。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/119501/viewspace-607460/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Mutexes in Oracle10g
请登录后发表评论 登录
全部评论

注册时间:2008-08-20

  • 博文量
    34
  • 访问量
    64762