OracleÖ´ÐÐSQLÓï¾äµÄ¹ý³Ì
µ±ÎÒÃÇÌá½»Ò»ÌõsqlÓï¾äʱ£¬oracle»á×öÄÄЩ²Ù×÷ÄØ£¿
Oracle»áΪÿ¸öÓû§½ø³Ì·ÖÅäÒ»¸ö·þÎñÆ÷½ø³Ì£ºservice process£¨Êµ¼ÊÇé¿öÓ¦¸ÃÇø·ÖרÓ÷þÎñÆ÷ºÍ¹²Ïí·þÎñÆ÷£©£¬µ±service process½ÓÊÕµ½Óû§½ø³ÌÌá½»µÄsqlÓï¾äʱ£¬·þÎñÆ÷½ø³Ì»á¶ÔsqlÓï¾ä½øÐÐÓï·¨ºÍ´Ê·¨·ÖÎö¡£
Ãû´Ê½âÊÍ£º
Óï·¨·ÖÎö£ºÓï¾ä±¾ÉíÕýÈ·ÐÔ¡£
´Ê·¨·ÖÎö£º¶ÔÕÕÊý¾Ý×ÖµäÖмì²é±í£¬Ë÷Òý£¬ÊÓͼºÍÓû§È¨ÏÞ¡£
¼ì²éͨ¹ýºó£¬·þÎñÆ÷½ø³Ì»á½«sqlÓï¾äת±äΪasciiÂ룬²¢Í¨¹ýÒ»¸öhashº¯Êý½«asciiÂëÉú³É³öÒ»¸öhashÖµ£¬·þÎñÆ÷½ø³Ì»áµ½share poolÖвéѯ´ËhashÊÇ·ñ´æÔÚ£¬Èç¹û´æÔÚ£¬·þÎñÆ÷½ø³Ì»á´ÓsharepoolÖжÁÈ¡ÒѾ½âÎöºÃµÄÓï¾äÀ´Ö´ÐУ»Èç¹û²»´æÔÚ£¬ÔòÐèÒª×öÒÔϲ½Ö裺Éú³ÉÖ´Ðмƻ®ºÍÉú³ÉÖ´ÐбàÂ루ÇëÀí½âºÎΪִÐмƻ®£©¡£½âÎöÍê³Éºó£¬Oracle»á½«sqlÓï¾ä±¾Éí´úÂë¡¢hashÖµ¡¢±àÒë´úÂë¡¢Ö´Ðмƻ®ºÍËùÓÐÓë¸ÃÓï¾äÏà¹ØµÄͳ¼ÆÊý¾Ý´æ·Åµ½sharepoolÖС£
×¢Ò⣺
1 ¾¡Á¿Ð´ÏàͬµÄsqlÓï¾ä£¬ÒòΪ¼´Ê¹ÊÇfromÓï¾äÖÐtable˳ÐòµÄ±ä»¯¡¢²éѯ×Ö¶ÎλÖõı仯£¬ÉõÖÁÖ»ÊÇ´óСдµÄ²»Í¬£¬¶¼»á´ÙʹoracleÖØÐÂ×öÒ»´ÎÓ²½âÎö¡£
2 Ôö´óshare_pool_size¿ÉÒÔ±£Áô¸ü¶àµÄ»º´æÔÚÄÚ´æÖеÄsqlÓï¾äÖ´Ðмƻ®£¬Ò²Òâζ׏²ÏísqlµÄ¿ÉÄÜÐÔµÄÔö´ó¡£
ÔÚÉú³É±àÒë´úÂëºó£¬service process»áÊÔͼ´Ódb_bufferÖжÁÈ¡ÊÇ·ñ´æÔÚÏà¹ØµÄ»º´æÊý¾Ý¡£
ÏÂÃæÎÒÃÇ·ÖÁ½ÖÖÇé¿öÀ´ËµÃ÷£º
1 db_bufferÖв»°üº¬ÄÚ´æÊý¾Ý£ºservice process»áÊ×ÏÈÔÚ±íµÄÍ·²¿ÇëÇóһЩÐÐËø£¬ÉêÇë³É¹¦ºó£¬½«ÕâЩÐÐËùÔڵĵÚÒ»¸öblock¶ÁÈëdb_buffer¡£´ËʱÈç¹ûdb_buffer¿ÕÏпռ䲻×㣬Ôò»á´¥·¢Ð´²Ù×÷—DBWr¡£Èç¹ûdb_bufferÊ£ÓàµÄ¿Õ¼ä²»¹»´æ´¢ÐÂÊý¾Ý£¬¾Í»á´¥·¢DBWr½ø³Ì£¬½«db_bufferÖÐÔàÊý¾ÝдÈëÊý¾ÝÎļþ¡£ÌÚ³öÀ´µÄ¿Õ¼äдÈëÐÂÊý¾Ý¡£
×¢Ò⣺ db_blockÊÇoracle×îСµÄÂß¼µ¥Î»£¬¼´Ê¹ÎÒÃÇËùÒªÇóµÄÊý¾ÝÖ»ÊÇÒ»¸öblockËù°üº¬µÄÖÚ¶àÐÐÖеÄÒ»Ðлò¼¸ÐУ¬ÎÒÃÇÈÔÈ»ÐèÒª½«Õû¸öblock¶ÁÈëdb_buffer¡£db_blockµÄ´óС¿ÉÒÔÉèÖÃΪ8kµÄÕûÊý±¶£¬²¢ÇÒ¿ÉÒÔÕë¶Ô²»Í¬µÄ±í¿Õ¼äÉèÖò»Í¬µÄdb_block_sizeµÄ´óС£¬Ò»°ã½¨ÒéÔÚselect¶àµÄ±íÉϽ«db_block_sizeÉèÖôóһЩ£¬¶ødml²Ù×÷¶àµÄ±íÉÏÉèÖõÄСһЩ¡£
2 DBWrÊÇдÊý¾Ý½ø³Ì£¬´¥·¢DBWr½ø³ÌµÄʼþ³ýÁËdb_buffer¿Õ¼ä²»¹»Í⣬ckpt½ø³ÌÒ²ÊÇ´¥·¢DBWrµÄʼþ¡£
²¹³ä£º
1 ¶ÎÊÇoracle×îСµÄÍØÕ¹µ¥Î»¡£
2 ckpt½ø³Ì£º¼ì²éµã½ø³Ì¡£½«scnдÈëÈÕÖ¾Îļþ£¬¿ØÖÆÎļþ£¬Êý¾ÝÎļþÍ·£¬Êý¾Ý¿éÍ·²¿¡£´¥·¢ckpt½ø³ÌµÄʼþÓÐalter system checkpoint£¬alter tablespace offl
Ïà¹ØÎĵµ£º
SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓ㨶þ£©
×÷Õߣºfreedk
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¸ÄÉÆSQLÓï¾ä
¡¡¡¡ºÜ¶àÈ˲»ÖªµÀSQLÓï¾äÔÚSQL SERVERÖÐÊÇÈçºÎÖ´Ðеģ¬ËûÃǵ£ÐÄ×Ô¼ºËùдµÄSQLÓï¾ä»á±»SQL SERVERÎó½â¡£±ÈÈ磺
select * from table1 where name=''zhangsan'' and tID > 10000
ºÍÖ´ÐÐ:
select * from table1 where tID > ......
create function [dbo].[DeptTree](@initDeptCode varchar(10))/*¶¨Ò庯Êýc_tree£¬ÊäÈë²ÎÊýΪ³õʼ½ÚµãDeptCode*/
returns @t table(DeptCode varchar(10),UpDeptCode varchar(10),lev int)/*¶¨Òå±ítÓÃÀ´´æ·ÅÈ¡³öµÄÊý¾Ý*/
begin
declare @i int/*±êÖ¾µÝ¹é¼¶±ð*/
set @i=1
insert @t ......
DBCC
Transact-SQL ±à³ÌÓïÑÔÌṩ DBCC Óï¾ä×÷Ϊ Microsoft® SQL Server™ 2000 µÄÊý¾Ý¿â¿ØÖÆ̨ÃüÁî¡£ÕâЩÓï¾ä¶ÔÊý¾Ý¿âµÄÎïÀíºÍÂß¼Ò»ÖÂÐÔ½øÐмì²é¡£Ðí¶à DBCC Óï¾äÄܹ»¶Ô¼ì²âµ½µÄÎÊÌâ½øÐÐÐÞ¸´¡£
Êý¾Ý¿â¿ØÖÆ̨ÃüÁîÓï¾ä±»·ÖΪÒÔÏÂÀà±ð¡£
Óï¾ä·ÖÀà Ö´ÐÐ
ά»¤Óï¾ä ¶ÔÊý¾Ý¿â¡¢Ë÷Òý»òÎļþ×é½øÐÐά»¤µÄÈÎÎñ¡£ ......
ÎÞ·¨´ò¿ªÓû§Ä¬ÈÏÊý¾Ý¿â£¬µÇ¼ʧ°Ü,ÕâÊÇSQL ServerʹÓÃÕßÊìϤµÄÎÊÌâÖ®Ò»¡£ÔÚʹÓÃÆóÒµ¹ÜÀíÆ÷¡¢²éѯ·ÖÎöÆ÷¡¢¸÷À๤¾ßºÍÓ¦ÓÃÈí¼þµÄʱºò£¬Ö»Òª¹Øϵµ½Á¬½ÓSQL ServerÊý¾Ý¿âµÄʱºò£¬¶¼ÓпÉÄÜ»áÅöµ½´ËÎÊÌâ,ÒýÆð´Ë´íÎó·¢ÉúµÄÔÒò±È½Ï¶à,ÏÂÃæÎÒÃǾÍÀ´Ïêϸ·ÖÎöÒýÆð´ËÎÊÌâµÄÔÒòÒÔ¼°½â¾ö°ì·¨¡£
Ò»¡¢ÔÒò
µÇ¼ÕÊ»§µÄĬÈÏÊý¾Ý¿â±»É¾³ý¡ ......
1. Ö±½ÓÔÚPL/SQL ÖÐÐÞ¸ÄÊý¾Ý
selectÓï¾äºóÃæ¼Ó‘for updata’£¬´ò¿ª½çÃæÉϵÄСËø£¬±à¼£¬°´¹³¹³±£´æ¡£
eg: select * from xtgldxsyncdw for update; ²éѯ½á¹û´°¿ÚµÄСËø¼´¿É´ò¿ª¡£
2. µ¼È˵¼³ötables
tools -->import ......