分类 ORACLE 下的文章

转自:https://blog.csdn.net/vertual/article/details/34540555

ORACLE里锁有以下几种模式: 
0:none 
1:null 空 
2:Row-S 行共享(RS):共享表锁,sub share  
3:Row-X 行独占(RX):用于行的修改,sub exclusive  
4:Share 共享锁(S):阻止其他DML操作,share 
5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive  
6:exclusive 独占(X):独立访问使用,exclusive

1.oracle提供的所类型可以根据v$lock_type 中的type来查询,我们平时接触的最多的是两种

select * from v$lock_type where type in ('TM','TX')

查看描述,可以大概的得知两种锁的信息.TM是同步访问对象用的,TX是和事务有关的.

2.要知道的有2个概念:

(1).锁定数据的锁,也就是行级锁,只有一种:排它锁 exclusive (ROW)
(2).锁定表上的锁,即锁定元数据的锁 metadata(table),一共有5种:

  RS: row share
  RX: row exclusive
  S: share
  SRX: share row exclusive
  X: exclusive4.根据oracle联机文档的concepts的

  我们可以从这个表找出至少2个东西,.第一是每种数据库操作都对应的是什么样的锁(参考中间那一列),第二是每种锁之间,如果遇到之后是否会产生冲突,所谓冲突就是是否会使当前的数据库操作夯住.其中Y*,表示如果两个操作锁定的是同一行,那么就会有冲突,后操作的会等待前一个操作完成之后再完成,否则会一直夯在那儿;如果不为同一行,那么则不会冲突,后操作的不会等待.举一个例子来说明:假设现在A操作为:对id=1的记录进行update,而B操作为:对id=2的记录进行删除,根据表格说明,在A上操作时在TM级别的锁会是RX,TX级别只有一个是X,在B上会有一个TM级别的锁会是RX,TX级别只有一个X,而根据表格说明,当RX遇到RX的时候,如果2个操作非同一条记录,那么则不会冲突,故AB两个操作均会按照各自的先加一个TM锁,再加一个TX锁,再顺利执行各自的操作,不会夯住。如果将B操作的记录id换位1,那么两个操作记录为同一条记录,在TM锁上会表现出冲突,所以B操作就会等待A操作完成提交后(也就是A的TX锁释放了后),B再相应的生成一个TX锁和一个TM锁再完成操作,否则的话会一直夯住,等待A释放TX锁.

3.常用的动态性能视图:

select * from v$lock_type where type in ('TM','TX');

select * from v$lock;
select * from v$transaction;

重点说明一下v$lock视图:

先设置一个场景:在session A中对一个表的记录进行更新,更新完后并不提交,在session B中对改表的同一条记录进行删除



  很清晰的看到2个sid产生了锁.对于sid为140的session,产生了一个TM和一个TX锁,TM的锁模式(LMODE)=3,(3为RX: row exclusive和表格对照相符,当操作为update的时候,产生RX锁);对于sid为147的session,也产生了一个TM和一个TX锁,TM的锁模式(LMODE)=3 (3为RX: row exclusive和表格对照相符,当操作为delete的时候,产生RX锁),而TX的锁模式(LMODE)=0,代表正在等待一个锁.从v$lock_type的定义上面,我们也可以看出,type为TM的锁,ID1表示的是object_id,查询dba_objects可以很容易的得出锁定的对象是TTT这个obj. 从最后一列BLOCK(该block并不代表块,而是代表阻塞)=1也可以看出,sid=140的session在生成TX锁之后,发现之后一个的操作也是修改该条记录,所以BLOCK+1,表示阻塞其他的操作同时操作这条记录.

另外,此时,查询select * from v$transaction;视图,也可以得到相关联的信息

我们从v$lock_type对TX锁的描述,可以知道TX是和事务有关的.因此查看之前v$lock上TX锁的相关信息,可以看到ADDR的值与v$transaction的值是一样的.甚至可以根据ID1的值来计算,锁定的是哪个段:根据TX的ID1去除以和取余2的16次方,得到相关信息:



接着再在改表上创建一个索引  create index idx_ttt on ttt(object_id);

创建索引的同时,查询v$lock表

可以发现在创建索引的会生成2个TM锁,锁类别分别为4和3,我们查询这2个TM分别锁定的是什么对象:

根据查询结果发现lmode=4的object_id为55160的对象对应的是TTT这个表,LMODE=4对应的是TM的S锁

总结

1级锁有:Select,有时会在v$locked_object出现。 
2级锁有:Select for update,Lock For Update,Lock Row Share  
select for update当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行update、delete或select for update操作。 
3级锁有:Insert, Update, Delete, Lock Row Exclusive 
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。 
4级锁有:Create Index, Lock Share 
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。 
00054, 00000, "resource busy and acquire with NOWAIT specified" 
// *Cause: Resource interested is busy. 
// *Action: Retry if necessary. 
5级锁有:Lock Share Row Exclusive  
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。 
6级锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

 

Oracle进程无法KILL处理方案:

出处:

1.oracle提供的所类型可以根据v$lock_type 中的type来查询,我们平时接触的最多的是两种

Oracle进程被KILL之后,状态被置为"KILLED",但是锁定的资源长时间不释放,会出现类似下面这样的错误提示:

ORA-00030: User session ID does not exist

或

ORA-00031: session marked for kill

以往大多都是通过重启数据库的方式来强行释放锁资源。
现提供另一种方式解决该问题,在ORACLE中KILL不掉,在OS系统中再杀,操作方式如下:

1.下面的语句用来查询哪些对象被锁:
select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程:
alter system kill session '42,21993';
3在os一级再杀死相应的进程(线程),首先执行下面的语句获得进程(线程)号:
select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=24 (24是上面的sid)
4.在OS上杀死这个进程(线程):
1)在unix上,用root身份执行命令:
#kill -9 12345(即第3步查询出的spid)
2)在windows(unix也适用)用orakill杀死线程,orakill是oracle提供的一个可执行命令,语法为:
orakill sid thread
sid:表示要杀死的进程属于的实例名
thread:是要杀掉的线程号,即第3步查询出的spid。

 

redhat linux7.3系统,安装11.2.0.4集群
(两个节点都要执行)

有问题的节点观察到的现象是
1,开机只有ohasd.bin ,init.ohasd进程,
2,系统日志显示 autorun file for ohasd is missing
3,检查ohasdrun 内容为reboot,用vi看后面多了个^@符号,看权限是root:oinstall 与正常节点对比,权限一样.

4,看了init.ohasd内容,感觉是因为判断ohasdrun文件存不存在的步骤没有正常进行,导致系统日志显示autorun file for ohasd is missing
不停循环每10s出现一次autorun file for ohasd is missing

5,kill掉init.ohasd 进程,系统自动重新运行init.ohasd进程后集群正常启动,所有资源正常.
其它操作都没有,只是kill /etc/init.d/init.ohasd run 进程之后集群就能正常启动了.
此时ohasdrun 显示restart

一、概述
由于XX数据库,各区用户数据都在一个表中,为保护数据安全,需要给不同的区运维人员不同的角色,访问同一张表的不同数据。

二、实现方式
1.给每个区建立一个只读用户
2.给每个需要访问的表建立一个视图,按用户所属区进行筛选
3.授予只读用户访问指定视图的权限

三、操作步骤
--1.建立各区查询用户 --system用户下执行
用户命名格式:区标识_readonly
以宝安区为例:ba_readonly

create user ba_readonly identified by ba_readonly;

--2.赋权限 -连接数据库--system用户下执行

grant connect to ba_readonly

--3.赋权限--建立11个自定义普通视图(system用户执行)
视图格式:OWNERNAME.TABLENAME_地区标识_vw
具体需要建立多少个视图,根据工单申请进行操作。
(如果全部表都需要建立,建议用excel进行拼接,自动生成数据库建表语句)

create view OWNERNAME.TABLENAME_地区标识_vw(name,minsal,maxsal,avgsal)
     AS SELECT e.dname,e.sal,e.sal,e.sal
        FROM    emp e
        WHERE e.deptno=“地区标识”
        GROUP BY e.dname;

授予用户访问普通视图的权限

grant select on OWNERNAME.TABLENAME_地区标识_vw to ba_readonly

--创建按区分类的临时表空间,用于排序

create temporary tablespace 地区_temp
tempfile 'C:\app\Administrator\oradata\orcl\地区_temp.dbf' 
size 500m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

进一步考虑,需求是否合理,如果大量的表需要手动建立视图,则视图的维护成本很高,是否应该分用户,分表,重新规划数据结构。

dataguard参数详解(转载)
https://developer.aliyun.com/article/52219
DB_NAME
只需注意DataGuard的主备各节点instance使用相同的db_name即可。推荐与service_name一致。

Primary Site
Standby Site
.DB_NAME='DB' .DB_NAME='DB’
DB_UNIQUE_NAME
Primary与Standby端数据库的唯一名字,设定后不可再更改。
注意:
如果主备db_unique_name不一样,需要与LOG_ARCHIVE_CONFIG配合使用
db_unique_name并未规定需要与数据库service_name一致,可以自定义任意名称。

Primary Site
Standby Site
.db_unique_name='Primary’ .db_unique_name='Standb
LOG_ARCHIVE_CONFIG
列出主备库上的DB_UNIQUE_NAME 参数。默认情况下,定义该参数能确保主备库数据库能够互相识别对方
Primary与Standby端的db_unique_name不一致时

Primary Site Standby Site
*.db_unique_name=Primary
*.db_unique_name=Standby
.LOG_ARCHIVE_CONFIG='DG_CONFIG=(Primary,Standby)' .LOG_ARCHIVE_CONFIG='DG_CONFIG=(Primary,Standby)'
如在主备库db_unique_name不一致的情况下未配置 LOG_ARCHIVE_CONFIG则会出现如下报错
ORA-16057: DGID from server not in Data Guard configuration
原因:主库没有设置参数log_archive_config
解决方法*.log_archive_config='dg_config=( Primary , Standby )'

alter system set log_archive_config='dg_config=( Primary , Standby )' scope=both;
Primary与Standby端的db_unique_name一致时

Primary Site Standby Site
.db_unique_name=test .db_unique_name=test
.LOG_ARCHIVE_CONFIG='' .LOG_ARCHIVE_CONFIG='
LOG_ARCHIVE_DEST_1
本地归档路径。Primary与Standby需要定义各自的online redo log的归档地址,以系统实际的存放路径为准。格式如下:
Primary Site:
*.LOG_ARCHIVE_DEST_1='LOCATION=/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) '
Standby Site:
*.LOG_ARCHIVE_DEST_1='LOCATION=/stdby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) '
注意:
在LOG_ARCHIVE_DEST_n设置DB_UNIQUE_NAME表示该参数在 DB_UNIQUE_NAME指定的数据库上生效 ,设置为本地的db_unique_name。以priamry端为例,格式如下:
*.LOG_ARCHIVE_DEST_1='LOCATION=/archivelog/ VALID_FOR=(ALL_LOGFILES,
ALL_ROLES) DB_UNIQUE_NAME=Primary'
这样配置的意义为: 在数据库 Primary 上log_archive_dest_1对主备库上的联机日志都有效,这里的 db_unique_name可以省略                    
LOG_ARCHIVE_DEST_2
该参数仅当数据库角色为primary时生效,指定primary归档redo log到该参数定义的standby database上。
log_archive_dest_2可以说是dataguard上最重要的参数之一,它定义了redo log的传输方式(sync or async)以及传输目标(即standby apply node),直接决定了dataguard的数据保护级别。
格式如下:
Primary Site:
*.LOG_ARCHIVE_DEST_2='SERVICE=DR2 lgwr async VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) '
Standby Site: (switch over后生效)
*.LOG_ARCHIVE_DEST_2='SERVICE=DR1 lgwr async VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) '
注意:
LOG_ARCHIVE_DEST_2参数里定义的service值,比如DR1,是tnsnames.ora文件里定义的Oracle Net名称。
有时会在LOG_ARCHIVE_DEST_2定义DB_UNIQUE_NAME的值,当前节点设置的均为另一端数据库的db_unique_name。以primary端为例,格式如下:
*.LOG_ARCHIVE_DEST_2='SERVICE=DR1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Standby'  
这个参数的意义为: 在数据库DR1上log_archive_dest_2对主库上的联机日志都有效        
关于valid_for参数,有如下解释:
The redo_log_type keyword identifies the destination as valid for archiving to one of the following:
ONLINE_LOGFILE:This destination is valid only when archiving online redo log files.
STANDBY_LOGFILE:This destination is valid only when archiving standby redo log files.
ALL_LOGFILES:This destination is valid when archiving either online redo log files or standby redo log files.
The database_role keyword identifies the role in which this destination is valid for archiving:
PRIMARY_ROLE:This destination is valid only when the database is running in the primary role.
STANDBY_ROLE:This destination is valid only when the database is running in the standby role.
ALL_ROLES:This destination is valid when the database is running in either the primary or the standby role.              
LOG_ARCHIVE_DEST_3
该参数仅当数据库角色为standby时生效,定义primary database的日志写到standby database的standby redo log中。
Primary Site:
*.LOG_ARCHIVE_DEST_3='LOCATION=/archivelog/standbylog/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) '
Standby Site:
*.LOG_ARCHIVE_DEST_3='LOCATION=/arch/arch3/ VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE)'
注意:
LOCATION定义的路径以本节点能读写的实际路径为准。
LOG_ARCHIVE_DEST_STATE_n
设置为ENABLE,激活log_archive_dest_n定义的属性。
FAL_SERVER and FAL_CLIENT
当Primary Database的某些日志没有成功发送到Standby Database, 这时候发生饿了归档裂缝(Archive Gap)。
FAL是Fetch Archive Log的简写,它是dataguard主备之间GAP的处理机制。
当Primary Database的某些日志没有成功发送到Standby Database, 这时候发生饿了归档裂缝(Archive Gap)。
Primary上不会有GAP,所以fal_server和fal_client也是只在standby上生效的参数,当然为了switch over的需要同样会在primary端进行预设置。
缺失的这些日志就是裂缝(Gap)。 Data Guard能够自动检测,解决归档裂缝,不需要DBA的介入。这需要配置FAL_CLIENT, FAL_SERVER 这两个参数(FAL: Fetch Archive Log)。
从FAL 这个名字可以看出,这个过程是Standby Database主动发起的“取”日志的过程,Standby Database 就是FAL_CLIENT. 它是从FAL_SERVER中取这些Gap, 10g中,这个FAL_SERVER可以是Primary Database, 也可以是其他的Standby Database。
如:FAL_SERVER='PR1,ST1,ST2';
FAL_CLIENT和FAL_SERVER两个参数都是Oracle Net Name。 FAL_CLIENT 通过网络向FAL_SERVER发送请求,FAL_SERVER通过网络向FAL_CLIENT发送缺失的日志。 但是这两个连接不一定是一个连接。 因此FAL_CLIENT向FAL_SERVER发送请求时,会携带FAL_CLIENT参数值,用来告诉FAL_SERVER应该向哪里发送缺少的日志。 这个参数值也是一个Oracle Net Name,这个Name是在FAL_SERVER上定义的,用来指向FAL_CLIENT.
FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name.

Primary Site Standby Site
.fal_server='DR2’ .fal_server=' DR1 ’
.fal_client='DR1' .fal_client=' DR2'
其中DR1、DR2分别为主备库的网络服务名
DB_FILE_NAME_CONVERT
primary与standby上diskgroup的名称或是数据文件的存放路径不一致的时候,需要定义该参数进行转换,否则standby apply后无法创建与primary一致的数据文件并报错。
db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映(如果两数据库的目录结构不一样),如果有多个对映,逐一指明对映关系。
格式:
*.db_file_name_convert=主数据库数据文件目录,备用数据库数据文件目录
例如:
Primary Site:
*.db_file_name_convert='+DATAGRP/db/datafile/','+DG1/db/datafile/'
或者*.db_file_name_convert='/u01/app/oradata/standby','/u01/app/oradata/primary'
Standby Site:
*.db_file_name_convert='+DG1/db/datafile/','+DATAGRP/db/datafile/'

或者*.db_file_name_convert='/u01/app/oradata/ primary ','/u01/app/oradata/ standby '
其中+DG1/db/datafile/是primary dastabase上存放datafile的路径,+DATAGRP/db/datafile/是standby上存放datafile的路径
多对多映射设定
*.db_file_name_convert='/opt/oracle/oraInventory/oradata/oracle',

'/opt/oracle/oraInventory/oradata/standby','/home/ldai/testdb',
'/home/ldai/testdb/standby'
注意: primary上的该参数仅在主备switch over后生效,格式应保持一致,比如"*.db_file_name_convert='+DG1/db/datafile','+DATAGRP/db/datafile/' ”,路径少了一个"/”,将导致standby apply失败。这个参数仅对standby数据库有效
primary上执行create tablespace等add datafile操作时,无须自定义datafile的全路径名称,由数据库自动创建datafile即可。
LOG_FILE_NAME_CONVERT
同DB_FILE_NAME_CONVERT类似,定义主备log文件的存放路径转换。
STANDBY_FILE_MANAGEMENT
初始化参数STANDBY_FILE_MANAGEMENT作用于standby数据库 ,用来控制是否自动将Primary数据库增加表空间或数据文件的改动,传播到物理Standby数据库。该参数有两个值:
AUTO:如果该参数值设置为AUTO,则Primary数据库执行的表空间创建操作也会被传播到物理Standby数据库上执行。
MANUAL:如果设置为MANUAL或未设置任何值(默认值是MANUAL),需要手工复制新创建的数据文件到物理Standby服务器。
注意:STANDBY_FILE_MANAGEMENT参数特指Primary数据库端的表空间或数据文件创建,如果数据文件是从其他数据库复制而来(比如通过TTS传输表空间),则不管STANDBY_FILE_MANAGEMENT参数值如何设置,都必须同时手工复制到Standby数据库,并重建物理Standby数据库的控制文件。
在Primary数据库端删除表空间时,会影响到物理Standby端数据库的数据文件和表空间,初始化参数STANDBY_FILE_MANAGEMENT的属性值设置决定了该事件是否需要DBA介入。
当STANDBY_FILE_MANAGEMENT设置为AUTO。
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
在Primary数据库端执行删除表空间的操作:
SQL> DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
INCLUDING DATAFILES子句,在删除表空间时Oracle也将自动删除对应的物理文件。
将初始化参数STANDBY_FILE_MANAGMENT设置为AUTO,对于表空间和数据文件的操作无须DBA手工干预,物理Standby能很好地进行处理。
当STANDBY_FILE_MANAGEMENT参数设置为MANUAL时,即使DBA在Primary数据库端执行删除操作时加上了INCLUDING DATAFILES子句,Standby数据库仍然只会将表空间和数据文件从数据字典中删除,表空间涉及的物理文件仍需要手工删除。
对于文件系统,我们可以将初始化参数STANDBY_FILE_MANAGMENT设置为AUTO,但是对于裸设备,只能将该参数设置为MANUAL。
在Primary数据库端执行数据文件重命名操作:
如果Primary数据库重命名了一个或多个数据文件,该项修改并不会自动传播到Standby数据库。 就算设置了初始化参数STANDBY_FILE_MANAGEMENT等于AUTO也不行,要让Standby的数据文件与Primary保持一致,只能手工操作。
1、alter tablespace/datafile offline;
2、使用操作系统命令来拷贝
3、alter databaser rename datafile '....' to '...'
4、alter tablespace/datafile online;
5、确认STANDBY DB中所有归档已经apply
6、alter database recover managed standby database cancel停止redo apply
7、拷贝并重命名数据文件
8、alter database rename file '...' to '....';
9、alter databas recover managed standby database disconnect from session.
添加或删除Redologs文件
数据库调优时极有可能会涉及重置日志文件大小或增加删除日志组等操作,如果STANDBY_FILE_MANAGEMENT参数值设置为AUTO的话,这种操作也会被传播到物理Standby数据库。不过在一般情况下,你可以不管STANDBY_FILE_MANAGEMENT参数的设置,因为无论Primary端对日志组或日志文件的操作是否传播到物理Standby数据库,也不会影响到物理Standby数据库的运行,不过如果你不注意其中的关系,造成的影响可能会很深远。
通常建议当你在Primary数据库增加或删除Online Redologs时,一定记得手工同步相关物理Standby数据库中相关的设置,同时也要考虑好Standby Redologs与Online Redologs之间的关系,即保证Standby Redologs比Online Redologs要至少多一组。
注意的就是在Standby数据库端操作前务必将STANDBY_FILE_MANAGEMENT设置为MANUAL,如果物理Standby数据库的日志文件与Primary数据库路径不同的话,应该通过初始化参数LOG_FILE_NAME_CONVERT的设置,让其自动进行转换。
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数,例如
rac 2台机器 每台3组 则 standy 需8组,并且创建时要为每个实例建4组。一般来说,逻辑备库需要更多的redo组数,这是由于逻辑备库有自身的redo日志,而这些redo日志优先归档,故同等条件下逻辑备库的归档速度比物理备库的慢。
跨OPEN RESETLOGS的应用
在某些情况下,Primary数据库以RESETLOGS方式打开之后,也不会影响Data Guard的配置,Standby数据库无须人工参与,自动应用OPEN RESETLOGS的操作,继续接收并应用Primary数据库OPEN RESETLOGS之后产生的日志。
当然这是有条件的,并不是所有情况下都能这么智能。我们知道执行ALTER DATABASE OPEN RESETLOGS语句之后,数据库的INCARNATION被重置,也就是此时其Standby数据库的SEQUENCE序号也会从头开始设置。当然物理Standby数据库并不关注这一点,它只是忠实地紧跟Primary数据库的脚步,一步一步地执行Primary数据库曾经执行过的操作,因此当其接收到新的REDO数据时,就会自动应用这部分REDO数据。
图中显示了Primary数据库RESETLOGS操作对Standby的影响

Standby 数据库状态
Standby 数据库操作

解决方案
尚未应用RESETLOGS 之前的 REDO数据 自动应用REDO 数据 无须手工介入
应用了RESETLOGS 之后的 REDO 数据,不过 Standby 数据库启用了 FRA 可以回到应用RESETLOGS 之前的状态,不过需要 DBA 手工介入
手工FLASHBACK DATABASE 到应用 RESETLOGS 日志之前的状态

重启REDO 应用,以重新接收新的REDO 数据

应用了RESETLOGS 之后的 REDO 数据,而且没有配置 FRA 无法进行应用处理 重建物理Standby 是唯一的选择

正常情况下这个逻辑没有问题,不过遇到Primary执行过OPEN RESETLOGS之后,又通过备份恢复到OPEN RESETLOGS之前的状态,视物理Standby的具体配置(配置方式决定了物理Standby是否有可能回到OPEN RESETLOGS之前的状态)的不同,情况可能会复杂很多。

参考至:http://Java-beginner-liyun.iteye.com/blog/monthblog/2010-07?show_full=true

           http://www.linuxidc.com/Linux/2011-09/42721p3.htm
           http://www.cnblogs.com/sopost/archive/2010/01/30/2190114.html
           http://epub.itpub.net/6/5.htm

           http://wenku.baidu.com/view/deec7e29cfc789eb172dc8f5.html 

作者:czmmiao 原文地址:http://czmmiao.iteye.com/blog/1311070

buffer busy waits 等待事件的产生

当n个进程想以不兼容的模式持有内存块上的buffer pin的时候,就会产生buffer busy waits等待

什么?
内存块上有buffer pin ?
不是说内存块的锁都是靠latch实现的吗?什么时候还冒出一个buffer pin?从来没听说过!!
好,既然你这么问,那我们可以先假设没有buffer pin这东东,看看oracle怎么去访问/修改一个数据块。(下面的过程尽可能的我做了简化)
1)依据数据块的地址计算出数据块所在的bucket
2)获得保护这个bucket的cbc latch
3)在这个链表上找寻我们需要的数据块
4)读取/修改数据块的内容
5)释放cbc latch
我们知道latch的获得和释放时间一般都是极短的(cpu的原子操作),上面5个步骤里1,2,3,5的时间我们都可以认为是极快的操作。
但是步骤四消耗的时间相对于这几个就大了去了。我粗糙的画了一个图,可以参展一下

这样就导致了一个问题,在大并发环境下,由于cbc latch的持有时间过长,会导致大量的latch争用,latch的大量争用非常容易导致系统的cpu资源出现瓶颈。需要特别说明的是,即使你所有的操作都是查询非修改,也会导致大量的cbc latch争用:cbc latch的持有到cbc latch的释放这段时间过长了。
如何解决这个问题呢,说一下ORACLE的做法。
ORACLE通过让每次访问buffer block的会话获取两次cbc latch,再配合在内存块上加buffer pin来解决这个问题

看如下的步骤。
1)依据数据块的地址计算出数据块所在的bucket
2)获得保护这个bucket的cbc latch
3)在这个链表上找寻我们需要的数据块,找到后,pin这个buffer(读取s,修改x)
4)释放cbc latch
5)读取/修改数据块的内容
6)获取cbc latch
7)unpin这个buffer
8)释放cbc latch
通过这种实现方式,我们看到cbc latch的持有时间大大降低了,因为cbc latch的持有,只做了很少的事情,这样就大大降低了cbc latch的争用。
你可能会挑战说,虽然cbc latch的争用会大大减轻,可是ORACLE只不过是转移了竞争点,现在变成了buffer lock之间的竞争。
你说的对,但是也不对!!
如果你的数据库里读极多,写极少,由于各个读之间的buffer pin是兼容的,都是s模式,因此不会产生任何的争用。
如果你的数据库里写极多,读极小,就会产生buffer busy waits等待,但是这种等待的代价比cbc latch的等待代价要小的多,latch的spin机制是非常耗cpu的,而buffer pin的管理本质上类似于enq 锁的机制,没有spin机制,不需要自旋耗费大量的cpu。
如果你的数据库是读写混合的场景,那么写会阻塞读,产生buffer busy waits,但是读不会阻塞写,不会产生这个等待。这个我们后面会重点讨论

下面我们模拟下怎么产生busy buffer waits等待事件:

SCOTT@orcl>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fn, dbms_rowid.rowid_block_number(rowid) bl, rowid from emp ;

    FN         BL ROWID

---------- ---------- ------------------

     4        151 AAAR3xAAEAAAACXAAA
     4        151 AAAR3xAAEAAAACXAAB
     4        151 AAAR3xAAEAAAACXAAC
     4        151 AAAR3xAAEAAAACXAAD
     4        151 AAAR3xAAEAAAACXAAE
     4        151 AAAR3xAAEAAAACXAAF
     4        151 AAAR3xAAEAAAACXAAG
     4        151 AAAR3xAAEAAAACXAAH
     4        151 AAAR3xAAEAAAACXAAI
     4        151 AAAR3xAAEAAAACXAAJ
     4        151 AAAR3xAAEAAAACXAAK

    FN         BL ROWID

---------- ---------- ------------------

     4        151 AAAR3xAAEAAAACXAAL
     4        151 AAAR3xAAEAAAACXAAM
     4        151 AAAR3xAAEAAAACXAAN

可以看到,emp表上的所有行都是在file 4,block 151上面的

现在我们来模拟三种情况:

情况一:两个session并发的进行查询

SCOTT@orcl>select distinct sid from v$mystat;

   SID

   164

SCOTT@orcl>declare
2 c number;
3 begin
4 for i in 1 ..6000000 loop
5 select count(*) into c from emp where empno=7788;
6 end loop;
7 end;
8 /

SCOTT@orcl>select distinct sid from v$mystat;

   SID

   422

SCOTT@orcl>declare
2 c number;
3 begin
4 for i in 1 ..6000000 loop
5 select count(*) into c from emp where empno=7900;
6 end loop;
7 end;
8 /

这个时候查询当前的非空等待事件:

SYS@orcl>select distinct event,s.wait_class from v$session_wait s,v$event_name e where event=name and e.wait_class not like 'Idle';

EVENT WAIT_CLASS
---------------------------------------- ----------------------------------------
SQL*Net message to client Network

可以看到没有buffer busy waits的等待事件

情况二:一个session进行查询,另一个session进行更新操作:

SCOTT@orcl>select distinct sid from v$mystat;

   SID

   422

SCOTT@orcl>
SCOTT@orcl>begin
2 for i in 1 ..4000000 loop
3 UPDATE emp SET sal=2000 where empno=7788;
4 commit;
5 end loop;
6 end;
7 /

SCOTT@orcl>select distinct sid from v$mystat;

   SID

    85

SCOTT@orcl>declare
2 c number;
3 begin
4 for i in 1 ..6000000 loop
5 select count(*) into c from emp where empno=7900;
6 end loop;
7 end;
8 /

在这样的情况下系统的等待事件:

SYS@orcl>select distinct event,s.wait_class from v$session_wait s,v$event_name e where event=name and e.wait_class not like 'Idle';

EVENT WAIT_CLASS
------------------------------ ------------------------------
latch free Other
log file parallel write System I/O
control file parallel write System I/O
SQL*Net message to client Network

情况三:两个session同时并发的进行更新操作

session 1:

SCOTT@orcl>begin
2 for i in 1 ..4000000 loop
3 UPDATE emp SET sal=2000 where empno=7788;
4 commit;
5 end loop;
6 end;
7 /

session 2:

SCOTT@orcl>begin
2 for i in 1 ..4000000 loop
3 UPDATE emp SET sal=2000 where empno=7900;
4 commit;
5 end loop;
6 end;
7 /
查询系统的当前等待事件:

EVENT WAIT_CLASS
------------------------------ ------------------------------
log file switch (checkpoint in Configuration
complete)

control file parallel write System I/O
buffer busy waits Concurrency
SQL*Net message to client Network

看到了buffer busy waits的等待事件

我们看到发生写的会话session 1,没有任何的buffer busy waits等待,而发生读的会话session 2,产生了大量的buffer busy waits等待。
网上对这一块的争论是比较激烈的。
道理其实非常简单
1)当读取的进程发现内存块正在被修改的时候(如果有x模式的buffer pin,就说明正在被修改),它只能等待,它不能clone块,因为这个时候内存块正在变化过程中ing,这个时候clone是不安全的。很多人说,oracle里读写是互相不阻塞的,oracle可以clone内存块,把读写的竞争分开。其实要看情况,在读的时候发现内存块正在被写,是不能够clone的,因为是不安全的。这个时候读的进程只能等待buffer busy waits。
2)当写的进程发现内存块正在被读,这个时候,读是不阻塞写的,因为ORACLE可以很容易的clone出一个xcur的数据块,然后在clone的块上进行写,这个时候clone是安全的,因为读内存块的进程不会去修改数据块,保证了clone的安全性。

说到这里,基本上可以来一个简单的总结了,但是总结前,还是有必要给大家简单介绍一下,buffer header上的两个列表

每个buffer header上都有2个列表:users list和waiter list。
users list用来记录,当前有哪些会话获得了此buffer block上的buffer pin,并记录下buffer pin的模式。
waiter list用来记录,当前有哪些会话在等待buffer block 上的buffer pin,并记录下申请buffer pin的模式。
看到这两个列表,是不是觉得似曾相识?对了,enq锁的管理也是类似的这个方式,不过多了一个列表,锁转换列表。
给大家举个例子,会更清晰一些:
session 1(sid 123):修改数据块block 1
此block的buffer headler上的users list如下:
sid hold mode
123 x

session 2(sid 134):也想修改数据块block 1,但是由于于session 1的锁模式不兼容,只能等待buffer busy waits,此时的user list不变,waiter list如下:
sid req mode
134 x

session 3(sid 156):也想修改数据块block 1,但是由于于session 1的锁模式不兼容,只能等待buffer busy waits,如果这个时候我们去观察后台的等待,会发现2个会话在等待buffer busy waits了(134,156)。此时的user list不变,waiter list如下:
sid req mode
134 x
156 x

如果这个时候sid为123的会话修改完成,那么会通知sid为134的会话获得buffer pin,此时的user list,waiter list 如下:
user list
sid hold mode
134 x

waiter list
sid req mode
156 x

可要看到,buffer lock的这种机制非常类似于enq锁的机制,先进先出,然后通过n个列表来记录锁的拥有者和等待着。等待buffer busy waits的进程在进入队列后,会设置一个1秒(_buffer_busy_wait_timeout)的超时时间,等待超时后,会“出队”检查锁有没有释放,然后重新入队。

最后我们可以来一个总结了:
1)buffer busy waits是产生在buffer block上的等待,由于n个进程想以不兼容的模式获得buffer block的buffer pin,进而引起buffer busy waits等待。
2)buffer lock的管理模式非常类似enq锁的管理模式,先进先出,有队列去记录锁的拥有者和等待着。
3)写写,读写都会产生buffer busy wiats等待。写写的两个会话,都会产生buffer busy wiaits等待,而读写的两个会话,只有读的session会产生,因为它不能去简单的clone一个内存块,正在发生写的内存块发生克隆是不安全的
4)oracle为了解决cbc latch持有时间过长的问题,以每次访问buffer block的会话获取两次cbc latch,再配合在内存块上加buffer pin来解决这个问题。

说明:oracle并不是针对所有的内存块都采取两次获取cbc latch的机制,比如针对索引root,索引branch,唯一索引的叶子节点,都是采取的一次获取机制

buffer busy waits的处理步骤:

1、定位当前buffer busy waits的热块:

select event,p1,p1text,p2,p2text,p3,p3text from v$session_wait where wait_class not like 'Idle';
EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------------------------------------- ---------- -------------------- ---------- -------------------- ---------- --------------------
latch: cache buffers chains 2.5253E+10 address 150 number 0 tries
direct path read 7 file number 1581054 first dba 1 block cnt
direct path read 7 file number 1609013 first dba 4 block cnt
log file parallel write 1 files 18 blocks 1 requests
direct path read 7 file number 1607515 first dba 1 block cnt
SQL*Net message to client 1650815232 driver id 1 #bytes 0
buffer busy waits 4 file# 151 block# 1 class#
Disk file operations I/O 3 FileOperation 0 fileno 4 filetype

我们可以看到,造成buffer busy waits热块的是file 4 block 151这个数据块

下一步我们再来查找这个数据块是属于哪个段的

Select owner, segment_name, segment_type, partition_name,tablespace_name from dba_extents
where relative_fno='&filename' and '&blocknum' between block_id and (block_id+blocks-1)
OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SCOTT EMP TABLE
————————————————
版权声明:本文为CSDN博主「bigclouder」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/kiwi_kid/article/details/25799419