oracleÌåϵ½á¹¹ÉîÈëѧϰBuffer Cache
oracleÌåϵ½á¹¹ÉîÈëѧϰBuffer Cache - Overview
oracle 2009-09-02 23:13:26 ÔĶÁ40 ÆÀÂÛ0 ×ֺţº´óÖÐС
ÌÎÌÎÉîÈëѧϰoracleÌåϵ½á¹¹
ÎÒ·¢¾õ£¬Ó¢ÎĵÄһЩ¼¼ÊõÎÄÕÂÔÚÃèÊöoracleÔÀí·Ç³£µÄÇ¡µ±µ½Î»£¬±í´ïµÄ·Ç³£µÄÃÀ£¬ºÇºÇ¡£Ó¢ÎIJ»ºÃ£¬Ï׳ó·Ò벢ѧϰÁË£¬ÈÏÕæ¿´ÁË£¬ÏàÐÅÄãÊÜÒæ·Ëdz¡£
oracleÌåϵ½á¹¹Ö®£ºBuffer Cache - Overview
All Oracle data is obtained by users from the buffer cache. The basic purpose of the cache is to minimize physical
disk I/O by holding (buffering) copies of requested data blocks in memory. Data that is buffered in the cache can
be served to users at memory access speed, much faster than going to disk for it. The large performance penalty of
disk I/O makes tuning the buffer cache an extremely important task for the DBA.
Oracle data is stored on disk in identically sized units called blocks. Block size is determined at database creation
by the DB_BLOCK_SIZE initialization parameter. The buffer cache is also divided into buffers of this same size, and
each buffer can hold exactly one database block. Thus, the block is the basic unit of data transfer between disk and
memory in Oracle.
ËùÓеÄoracleÊý¾Ý»ñÈ¡£¬¶¼ÊÇÓû§Í¨¹ý¸ßËÙÊý¾Ý»º´æÈ¡µÃµÄ£¬×î»ù±¾µÄÄ¿µÄ¾ÍÊÇΪÁ˼õÉÙÎïÀí´ÅÅ̵ÄI/O²Ù×÷ͨ¹ý°ÑÇëÇóµÄÊý¾Ý±£´æÔÚÄÚ´æµ±ÖС£
±»»º´æÔÚ¸ßËÙ»º³åÇøµÄÊý¾ÝÄܱ»Óû§¿ìËٵĴÓÄÚ´æÖлñÈ¡µ½£¬ÄDZȴӴÅÅÌȥȡµÃÊý¾ÝÒª¿ìºÜ¶à¡£´ÅÅ̵ÄI/OËùÏûºÄµÄ¾Þ´óµÄÐÔÄÜ´ú¼Û£¬ÕâʹµÃµ÷ÓÅ
Êý¾Ý¿â¸ßËÙ»º´æ³ÉΪdbaÒ»¸ö·Ç³£Ã÷È·µÄÖØÒªÈÎÎñ¡£
oracleÊý¾Ý±»´æ´¢ÔÚ´ÅÅÌÖеÈͬ´óСµÄµ¥ÔªÖУ¬ÕâЩµ¥Ôª±»½Ð×ö¿é£»¿é´óСÔÚÊý¾Ý¿â±»´´½¨Ê±Í¨¹ý³õʼ²ÎÊýdb_block_sizeÀ´¶¨Òå¡£Êý¾Ý¸ßËÙ»º´æ
Ò²±»»®·Ö³ÉͬÑù´óСµÄ»º´æ¿é¡£²¢ÇÒÿ¸ö»º´æ¿é³ÖÓÐÒ»¸öÊý¾Ý¿â¿é£¬Òò´Ë¿éÊÇ»ù±¾µÄÊý¾Ý´«Ë͵¥ÔªÔÚÊý¾ÝÎļþºÍÄÚ´æÖ®¼ä¡£
oracleÊý¾Ý¿â¸ßËÙ»º´æÖ®Cache Hit Ratio
The buffer cache is a shared resource, accessible by all users. When a user process requests data, Oracle firsts looks
for that data's block in the cache. If the data is buffered in the cache it is returned to the requ
Ïà¹ØÎĵµ£º
×÷Óãºmerge into ½â¾öÓÃB±í¸úÐÂA±íÊý¾Ý£¬Èç¹ûA±íÖÐûÓУ¬Ôò°ÑB±íµÄÊý¾Ý²åÈëA±í£»
Óï·¨£º
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here ......
1¡¢ÔÚ±¾»ú69ÉÏ´´½¨Êý¾Ý¿âorcl £¬global_name=orcl£¬Ê¹ÓÃÓï¾ä
alter database rename global_name to orcl.us.oracle.com ÐÞ¸ÄÊý¾Ý¿âµÄÈ«¾ÖÊý¾Ý¿âÃûΪorcl.us.oracle.com
2¡¢ÔÚÐé»ú188ÉÏ´´½¨Êý¾Ý¿âviotest£¬global_name=viotest£¬Ê¹ÓÃÓï¾ä
alter database rename global_name to viotest.us.oracle.com ÐÞ¸ÄÊý¾Ý¿âµÄÈ«¾ÖÊ ......
ÏÂÃæÊÇORACLEÐÐÁÐת»»Í¨Óô洢¹ý³Ì£º
1¡¢
Function Fun_Split_Str(p_Str Varchar2, p_Division Varchar2, p_Seq Int)
Return Varchar2 Is
v_First Int;
v_Last Int;
Begin
If p_Seq < 1 Then
&nbs ......
distinctÕâ¸ö¹Ø¼ü×ÖÀ´¹ýÂ˵ô¶àÓàµÄÖØ¸´¼Ç¼ֻ±£ÁôÒ»Ìõ£¬µ«ÍùÍùÖ»Óà ËüÀ´·µ»Ø²»Öظ´¼Ç¼µÄÌõÊý£¬¶ø²»ÊÇÓÃËüÀ´·µ»Ø²»ÖؼǼµÄËùÓÐÖµ¡£ÆäÔÒòÊÇdistinctÖ»ÓÐÓöþÖØÑ»·²éѯÀ´½â¾ö£¬¶øÕâÑù¶ÔÓÚÒ»¸öÊý¾ÝÁ¿·Ç³£´óµÄÕ¾À´Ëµ£¬ÎÞÒÉÊÇ»áÖ±½ÓÓ°Ï쵽ЧÂʵġ£
ÏÂÃæÏÈÀ´¿´¿´Àý×Ó£º
table±í
×Ö¶Î1 ×Ö¶Î2
&nbs ......
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
......