oracle mutex
An introduction to Mutexes in 10gR2
By Connie Green, Kumar Rajamani
from meetings with: Kumar Rajamani, Russell Green, Saureen Shah, Cecilia Gervasio and Connie Green
Introduction
This paper is intended as an introduction to mutexes. It describes new concepts associated with mutexes, when mutexes are used, and related wait events and views.
Overview
A mutex, similarly to a latch, is a low-level serialization mechanism used to control access to a shared data structure in the SGA. Serialization is required to avoid an object being:
? deallocated while someone is accessing it
? read while someone is modifying it
? modified while someone is modifying it
? modified while someone is reading it
Mutexes can be defined and used in many different ways. For example:
? Each structure being protected by a mutex can have its own mutex (e.g. a parent cursor has its own mutex, and each child cursor has its own mutex)
? Each structure can be protected by more than one mutex, with each mutex protecting a different part of the structure
? A mutex can protect more than one structure
The mutex can either be stored within the structure it protects, or elsewhere. Mutexes are usually dynamically created at the same time as the structure they protect. If a mutex is embedded within the structure it protects, then it will be implicitly destroyed when the owning structure is freed.
Benefits of mutexes
Although mutexes and latches are both serialization mechanisms, mutexes have certain features latches do not.
Smaller and faster
Mutexes were introduced as an alternative to latches, as they are much faster to get, and smaller. A mutex get is in the region of 30-35 instructions, compared to 150-200 instructions for a latch. A mutex is around 16 bytes in size, compared to 112 bytes for a latch in 10.2 (in prior releases it was 200 bytes). Some of the reduction in latch size was achieved by obsolet
Ïà¹ØÎĵµ£º
--oracle jobsÅú´¦ÀíÃüÁî²Î¿¼
qlplus /nolog
connect sys/lee as sysdba
--ÒÔÏÂÁ½¾äÔÚsys(¼´¾ßÓÐdbaȨÏÞµÄÓû§)Óû§ÏÂÖ´ÐÐ
show parameter job_queue_processes;
alter system set job_queue_processes=10;
exit
sqlplus /nolog
connect jcy/jcy
ALTER TABLE T_OA_AFFICHE MODIFY (INPUT_TIME DATE);
ALTER TABLE ......
5.µ÷Óú¯ÊýFN_ADDONE
--------------------
SQL> SET SERVEROUTPUT ON
SQL> DECLARE CNUM NUMBER;
2 BEGIN
3 CNUM := USER1_ADB.FN_ADDONE(3);
4 DBMS_OUTPUT.PUT_LINE('CNUM = ' || CNUM);
5 END;
6&nbs ......
COMMITÊÇÒ»¸ö·Ç³£¿ìµÄ²Ù×÷£¬µ±ÎÒÃÇ·¢²¼commitÃüÁîʱ£¬ÕæÕýÀ§Äѵ͝×÷ÒѾÍê³É£¬
ÔÚÊý¾Ý¿âÖÐÒѾִÐÐÁËÊý¾Ý¸ü¸Ä£¬ËùÒÔÒѾÍê³ÉÁË99%µÄÈÎÎñ£¬ÀýÈ磺ÏÂÁвÙ×÷ÒѾ²úÉú£º
1.ÔÚSGA(Buffer Cache)ÖÐÒѾÉú³ÉÁËundo¿é£»
2.ÔÚSGA(Buffer Cache)ÖÐÒѾÉú³ ......
ʹÓÃSYSÓû§ÒÔSYSDBAÉí·ÝµÇ¼ϵͳ
²é¿´ÐÞ¸Äǰsga_max_size£¬sga_target´óС
show parameter sga_max_size;
show parameter sga_target;
Ð޸IJÎÊý
alter system set sga_max_size=1600m scope=spfile;
alter system set sga_target=1600m scope=spfile;
²é¿´Ð޸ĺósga_max_size£¬sga_target´óС
show parameter sga_ ......
ÓÐÁ½ÖÖº¬ÒåµÄ±í´óС¡£Ò»ÖÖÊÇ·ÖÅä¸øÒ»¸ö±íµÄÎïÀí¿Õ¼äÊýÁ¿£¬¶ø²»¹Ü¿Õ¼äÊÇ·ñ±»Ê¹Ó᣿ÉÒÔÕâÑù²éѯ»ñµÃ×Ö½ÚÊý£º
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
»òÕß
Select Segment_Name,Sum(bytes)/1024/1024 from User_Extents Group By Segment_Name
ÁíÒ»ÖÖ±íʵ¼ÊÊ¹Ó ......