ORACLE SQLÖ´Ðв½Öè
ORACLE sql µÄ´¦Àí¹ý³Ì´óÖÂÈçÏ£º
1.ÔËÓÃHASHËã·¨£¬µÃµ½Ò»¸öHASHÖµ£¬Õâ¸öÖµ¿ÉÒÔͨ¹ýV$SQLAREA.HASH_VALUE ²é¿´
2.µ½shared pool ÖÐµÄ library cache ÖвéÕÒÊÇ·ñÓÐÏàͬµÄHASHÖµ£¬Èç¹û´æÔÚ£¬ÔòÎÞÐèÓ²½âÎö£¬½øÐÐÈí½âÎö
3.Èç¹ûshared pool²»´æÔÚ´ËHASHÖµ£¬Ôò½øÐÐÓï·¨¼ì²é£¬²é¿´ÊÇ·ñÓÐÓï·¨´íÎó
4.Èç¹ûûÓÐÓï·¨´íÎ󣬾ͽøÐÐÓïÒå¼ì²é£¬¼ì²é¸ÃSQLÒýÓõĶÔÏóÊÇ·ñ´æÔÚ£¬¸ÃÓû§ÊÇ·ñ¾ßÓзÃÎʸöÔÏóµÄȨÏÞ
5.Èç¹ûûÓÐÓïÒå´íÎ󣬶ԸÃSQL½øÐнâÎö£¬Éú³É½âÎöÊ÷£¬Ö´Ðмƻ®
6.Éú³ÉORACLEÄÜÔËÐеĶþ½øÖÆ´úÂ룬ÔËÐиôúÂë²¢ÇÒ·µ»Ø½á¹û¸øÓû§
Ó²½âÎöºÍÈí½âÎö¶¼ÔÚµÚ5²½½øÐÐ
Ó²½âÎöͨ³£ÊÇ°º¹óµÄ²Ù×÷£¬´óÔ¼Õ¼Õû¸öSQLÖ´ÐеÄ70%×óÓÒµÄʱ¼ä£¬Ó²½âÎö»áÉú³ÉÖ´ÐÐÊ÷£¬Ö´Ðмƻ®£¬µÈµÈ¡£
µ±ÔÙ´ÎÖ´ÐÐͬһÌõSQLÓï¾äµÄʱºò£¬ÓÉÓÚ·¢ÏÖlibrary cacheÖÐÓÐÏàͬµÄHASHÖµ£¬Õâ¸öʱºò²»»áÓ²½âÎö£¬¶ø»áÈí½âÎö£¬
ÄÇôÈí½âÎö¾¿¾¹ÊǸÉÁËʲôÄØ?ÆäʵÈí½âÎö¾ÍÊÇÌø¹ýÁËÉú³É½âÎöÊ÷£¬Éú³ÉÖ´Ðмƻ®Õâ¸öºÄʱÓÖºÄCPUµÄ²Ù×÷£¬Ö±½ÓÀûÓÃÉú³ÉµÄÖ´Ðмƻ®ÔËÐÐ
¸ÃSQLÓï¾ä¡£
ÏÂÃæÕª³eygleÉîÈë½âÎöORACLE ÖйØÓÚSQLÖ´Ðйý³ÌµÄÃèÊö
1.Ê×ÏÈ»ñµÃlibrary cache latch,¸ù¾ÝSQLµÄHASH_VALUEÔÚlibrary cacheÖвéÕÒÊÇ·ñ´æÔÚ´ËHASH_VALUE£¬Èç¹ûÕÒµ½Õâ¸öHASH_VALUE£¬³Æ֮ΪÈí½âÎö£¬Server»ñµÃ¸ÄSQLÖ´Ðмƻ®×ªÏòµÚ4²½£¬Èç¹ûÕÒ²»µ½¹²Ïí´úÂë¾Í½øÐÐÓ²½âÎö¡£
2.ÊÍ·Ålibrary pool cache,»ñµÃshared pool latch,²éÕÒ²¢Ëø¶¨×ÔÓÉ¿Õ¼ä(ÔÚbucket ÖвéÕÒchunk)¡£Èç¹ûÕÒ²»µ½£¬±¨ORA-04031´íÎó
3.ÊÍ·Åshared pool latch,ÖØлñµÃlibrary cache latch,½«SQLÖ´Ðмƻ®·ÅÈëlibrary cacheÖС£
4.ÊÍ·Ålibrary cache latch,±£³ÖnullģʽµÄlibrary cache pin/lock.
5.¿ªÊ¼Ö´ÐС£
Library cache latch¿ÉÒÔÀí½âΪӲ/Èí½âÎöµÄʱºò·¢ÉúµÄ£¬ÒòΪ½âÎöµÄʱºò»áËÑË÷library cache£¬ËùÒÔ»á²úÉúlibrary cache latch
Library cache pin ÊÇÔÚÖ´ÐеĽ׶η¢ÉúµÄ¡£
Ïà¹ØÎĵµ£º
ʹÓÃexp¹¤¾ß£¬ÒÔtablesµÄÀàÐ͵¼³öij¸öÓû§ÏÂËùÓеıíºÍÊý¾Ý£¬·¢ÏÖÆäÖÐsequenceûÓб»µ¼³ö¡£ÍøÉÏËÑË÷Ö®£¬·¢ÏÖtoadòËÆÓд˹¦ÄÜ£¬ÓÚÊÇ°²×°ÁË9.6.1.1°æ±¾£¬½á¹û¾ÓȻû·¢Ïִ˹¦ÄÜ¡££¨¿ÉÄÜÊÇÎÒûÕÒµ½£¬ÖÁÉÙºÍÄÇλÀÏ´óµÄ½Øͼ²»Í¬£©£¬×îºóÕÒµ½ÈçϽű¾£¬¿ÉÒÔ½«Ä³¸öÓû§µÄÈ«²¿sequence²éѯ³öÀ´£¬²¢Æ´³É´´½¨Óï¾ä¡£
´úÂëÈçÏ£ ......
create or replace procedure p //ÓоÍÌæ»»£¬Ã»Óоʹ´½¨
is
cursor c is
select * from emp for update;
begin
for v_emp in c loop
if (v_emp.deptno =10) then
&nb ......
1£¬SqlServer´æ´¢¹ý³ÌµÄÊÂÎñ´¦Àí
Ò»ÖֱȽÏͨÓõijö´í´¦ÀíµÄģʽ´ó¸ÅÈçÏ£º
Create procdure prInsertProducts
(
@intProductId int,
@chvProductName varchar(30),
@intProductCount int
)
AS
Declare @intErrorCode int
Select @intErrorCode=@@Error
Begin transaction
if @intError ......
ÔÚÎÒÃǵÄÈÕ³£±à³ÌÖУ¬Êý¾Ý¿âµÄ³ÌÐò»ù±¾É϶¼ÒªÓëSQLÓï¾ä´ò½»µÀ£¬SQLÓï¾äµÄ±àд²»¿É±ÜÃâµÄ³ÉΪһ¸öÍ·Ì۵Ť×÷¡£ÇÒÒòΪSQLÓï¾äÊÇSTRINGÀàÐÍ£¬Òò´ËÔÚ±àÒë½×¶Î²é²»³ö´í£¬Ö»Óе½ÔËÐÐʱ²ÅÄÜ·¢ÏÖ´íÎó¡£
±¾ÎĵĽâ¾ö·½°¸£¬Í¨¹ý×Ô¶¯Éú³ÉSQLÓï¾ä£¬ÔÚÒ»¶¨³Ì¶ÈÉϽµµÍ³ö´íµÄ¸ÅÂÊ£¬´Ó¶øÌá¸ß±à³ÌЧÂÊ¡£ public int ......