数据操作管理,Oracle阻塞实例详解_oracle_脚本之家
分类:美高梅-数据

一、概述:阻塞是DBA经常碰到的情形,尤其是不良的应用程序设计所造成的阻塞将导致数据库性能的严重下降,直至数据库崩溃。对DBA而言,有必要知道如何定位到当前系统有哪些阻塞,到底谁是阻塞者,谁是被阻塞者。本文对此给出了描述并做了相关演示。

本篇介绍[数据知识]DAMA数据管理知识体系—数据开发篇的学习心得,供大家学习和参考。

简介

    对于数据库运维人员来说创建session或者查询时产生问题是常规情况,下面介绍一种很有效且不借助第三方工具的方式来解决类似问题。

最近开始接触运维工作,所以自己总结一些方案便于不懂数据库的同事解决一些不太紧要的数据库问题。类似方法很多理论也很多,我就不做深究,就是简单写一个方案,便于菜鸟使用的。

--更新表,注,提示符scott@CNMMBO表明用户为scott的session,用户名不同,session不同。scott@CNMMBO> update emp set sal=sal*1.1 where empno=7788;1 row updated.scott@CNMMBO> @my_env SPID SID SERIAL# USERNAME PROGRAM------------ ---------- ---------- --------------- ------------------------------------------------11205 1073 4642 robin oracle@SZDB  --另起两个session更新同样的行,这两个session都会处于等待,直到第一个session提交或回滚leshami@CNMMBO> update scott.emp set sal=sal+100 where empno=7788; goex_admin@CNMMBO> update scott.emp set sal=sal-50 where empno=7788; --下面在第一个session 查询阻塞情况scott@CNMMBO> @blocker BLOCK_MSG BLOCK-------------------------------------------------- ----------pts/5  is blocking 1067,10438 1pts/5  is blocking 1065,4464 1--上面的结果表明session 1073,4642 阻塞了后面的2个--即session 1073,4642是阻塞者,后面2个session是被阻塞者 --Author : Leshami--Blog : http://blog.csdn.net/leshami --下面查询正在阻塞的session id,SQL语句以及被阻塞的时间scott@CNMMBO> @blocking_session_detail.sql 'SID='||A.SID||'WAITCLASS='||A.WAIT_CLASS||'TIME='||A.SECONDS_IN_WAIT||CHR||'QUERY='||B.SQL_TEXT------------------------------------------------------------------------sid=1067 Wait Class=Application Time=5995 Query=update scott.emp set sal=sal+100 where empno=7788 sid=1065 Wait Class=Application Time=225 Query=update scott.emp set sal=sal-50 where empno=7788 --下面的查询阻塞时锁的持有情况 scott@CNMMBO> @request_lock_type USERNAME SID TY LMODE REQUEST ID1 ID2------------------------------ ---------- -- ----------- ----------- ---------- ----------SCOTT 1073 TX Exclusive None 524319 27412LESHAMI 1067 TX None Exclusive 524319 27412GOEX_ADMIN 1065 TX None Exclusive 524319 27412--可以看到LESHAMI,GOEX_ADMIN 2个用户都在请求524319/27412上的Exclusive锁,而此时已经被SCOTT加了Exclusive锁 --查询阻塞时锁的持有详细信息scott@CNMMBO> @request_lock_detail SID USERNAME OSUSER TERMINAL OBJECT_NAME TY Lock Mode Req_Mode---------- -------------------- --------------- ------------------------- -------------------- -- ----------- -------------------- 1065 GOEX_ADMIN robin pts/1 EMP TM Row Excl 1065 GOEX_ADMIN robin pts/1 Trans-524319 TX --Waiting-- Exclusive 1067 LESHAMI robin pts/0 EMP TM Row Excl 1067 LESHAMI robin pts/0 Trans-524319 TX --Waiting-- Exclusive 1073 SCOTT robin pts/5 EMP TM Row Excl 1073 SCOTT robin pts/5 Trans-524319 TX Exclusive 

[核心要点]

数据操作管理是数据在全生命周期内计划、控制和支持结构化数据资产的开发、维护活动,使企业数据资源达到最佳的利用价值。两个部分职能:数据库支持和数据技术管理。目标:获得资产的完整性、管理数据生命周期内的可用性、优化数据库性能。

阻塞理解

Sql Server 中当一个数据库会话中的事务正锁定一个或多个其他会话事务想要读取或修改的资源时,会产生阻塞(Blocking)。通常短时间的阻塞没有问题,且是较忙的应用程序所需要的。然而,设计糟糕的应用程序会导致长时间的阻塞,这就不必要地锁定了资源,而且阻塞了其他会话读取和更新它们。

三、文中涉及到的相关SQL脚本完整代码如下:

概念及活动

数据操作管理提供从数据创建、获取到数据归档、清洗整个过程的支持功能。
数据库支持
数据库支持是数据管理的核心,由DBA完成(开发DBA和产品DBA)。开发DBA侧重开发活动,产品DBA侧重数据操作管理活动。当然也需要数据管理专员、数据架构师、数据分析师等参与。

  1. 实施并控制数据库环境。重点介绍数据库管理系统DBMS产品、版本、工具、参数、安全、存储等。
  1. 美高梅4858官方网站,美高梅网站是多少,获取来自外部的数据。获取外部不同格式的数据源、数据采集办法。
  2. 规划数据恢复。保证业务连续性、数据可用性的数据库恢复规划(SLA)。
  3. 备份和恢复数据。定期数据备份、事务日志、热备、冷备等方案。
  4. 设定数据库性能服务水平。数据库的两个评价:可用性和性能,可用性是前提。可用性包括:可管理性、可恢复性、可靠性、可服务性。
  5. 监控并调整数据库性能。性能监控包括:内存分配、锁和阻塞、更新数据库统计失败、SQL编码不良、索引缺失、应用程序活动、数据库数量和规模或使用上升、数据库波动等。
  6. 规划数据留存方案。数据预留规划,数据有效期内使用数据的协议。原则:维护数据的代价永远不应超过数据带给的价值。
  7. 归档,留存和清除数据。DBA应会同开发人员、操作人员、服务器管理员、存储管理员等一同批准数据预留规划执行。
  8. 支持专用数据库。特定的数据库如:CAD/CAM、OLAP、实时数据库、地理空间数据库等。数据库的整合、融合是DBA需要重点关注内容,需要妥善解决。

数据库技术管理
遵循技术管理原则和标准,参考技术管理模型ITIL(信息技术基础设施库)。

  1. 理解数据技术要求。了解技术需求的10个问题:重点是解决什么问题、需要哪些功能、支撑硬件、软件、网络、存储、人员资源是否满足。
  1. 定义数据技术架构。数据技术架构是企业整体技术架构的组成部分,重点关注:技术标准、技术适用环境、分布式环境的相关技术,考虑技术架构、技术架构组件、企业技术路线图内容。
  2. 评估数据技术。9个评估维度和10步评估过程,以及DBMS考虑的相关因素。
  3. 安装和管理数据技术。DBA的具体工作是部署新技术、产品到开发/测试、QA/验证和生产环节。
  4. 备案和跟踪数据技术的使用许可。跟踪软件许可协议和法规要求,避免财务损失和法律风险。
  5. 支持数据技术的使用和问题。协同工作、敏捷开发、结对编程。

综述

  • 指导原则:Craig Mullins 《数据库管理》10条数据操作管理指导原则;
  • 过程总结:15个活动。
  • 组织和文化问题:DBA在提供高可用的数据资源的同时,赢得利益相关者的支持,通过敏捷开发、极限编程XP、Scrum提升技术适应能力。什么是应用DBA?侧重环境开发、测试、QA和产品。什么是过程DBA?专注DBMS控制和执行的过程逻辑。

 

robin@SZDB:~/dba_scripts/custom/sql> more my_env.sql SELECT spid, s.sid, s.serial#, p.username, p.programFROM v$process p, v$session sWHERE p.addr = s.paddr AND s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);robin@SZDB:~/dba_scripts/custom/sql> more blocker.sql col block_msg format a50; select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid||','||d.serial# block_msg, a.block from v$lock a,v$lock b,v$session c,v$session d where a.id1=b.id1 and a.id2=b.id2 and a.block>0 and a.sid <>b.sid and a.sid=c.sid and b.sid=d.SID; robin@SZDB:~/dba_scripts/custom/sql> more blocking_session_detail.sql--To find the query for blocking session--Access Privileges: SELECT on v$session, v$sqlareaSELECT 'sid=' || a.SID || ' Wait Class=' || a.wait_class || ' Time=' || a.seconds_in_wait || CHR  || ' Query=' || b.sql_text FROM v$session a, v$sqlarea b WHERE a.blocking_session IS NOT NULL AND a.sql_address = b.addressORDER BY a.blocking_session/robin@SZDB:~/dba_scripts/custom/sql> more request_lock_type.sql--This script generates a report of users waiting for locks.--Access Privileges: SELECT on v$session, v$lockSELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', lmode, ltrim) lmode, DECODE(m.request,0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', request, ltrim(to_char request, m.id1, m.id2FROM v$session sn, v$lock mWHERE (sn.sid = m.sid AND m.request != 0) OR (sn.sid = m.sid AND m.request = 0 AND lmode != 4 AND  IN (SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )ORDER BY id1, id2, m.request; robin@SZDB:~/dba_scripts/custom/sql> more request_lock_detail.sqlset linesize 190col osuser format a15col username format a20 wrapcol object_name format a20 wrapcol terminal format a25 wrapcol Req_Mode format a20select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL, DECODE(B.ID2, 0, A.OBJECT_NAME, 'Trans-'||to_char OBJECT_NAME, B.TYPE, DECODE(B.LMODE,0,'--Waiting--', 1,'Null', 2,'Row Share', 3,'Row Excl', 4,'Share', 5,'Sha Row Exc', 6,'Exclusive', 'Other') "Lock Mode", DECODE(B.REQUEST,0,' ', 1,'Null', 2,'Row Share', 3,'Row Excl', 4,'Share', 5,'Sha Row Exc', 6,'Exclusive', 'Other') "Req_Mode" from DBA_OBJECTS A, V$LOCK B, V$SESSION Cwhere A.OBJECT_ID = B.ID1 and B.SID = C.SID and C.USERNAME is not nullorder by B.SID, B.ID2;

[观点解读]

通过对以上内容的学习,"数据小兵"对于要点补充解读如下:
书中强调数据库的操作管理主要由数据库支持和数据库技术管理两个部分组成。重点强调的是DBA数据专业角色。
数据小兵"很庆幸的跟大家推荐 宜信 数据操作管理,Oracle阻塞实例详解_oracle_脚本之家。韩峰老师 《数据库的昨天、今天与明天》,也想大家通过韩老师资深DBA的多年从业经验,对DBA有更为清晰的认识。我这里简单对韩老师提及的DBA职业发展简述如下:
根据企业的三大领域,信息、数据、技术(研发及基础设施),DBA职业发展之路可考虑三个不同层级:

  • 从事底层数据库技术研究、运维平台类开发;
  • 企业中从事数据库设计、架构、优化等;
  • 企业的数据建模、治理类工作;

数据库支持

书中从DBA数据库支持角度强调数据库的环境搭建、数据来源的采集、数据恢复规划及备份、数据库性能调优、数据留存规划等方面进行了介绍。
"数据小兵"认为这部分重点延伸了数据开发部分内容,从技术支持角度对DBA对于企业的数据库管理系统提供支持,保障数据资产的可用性和性能。这部分主要从技术角度强调DBA的工作任务和职责,相关数据库产品的也有不同正如韩峰老师对于数据库三大阵营的理解:第一大阵营是“OldSQL”;第二大阵营为“NewSQL”;第三大类为“NoSQL”。从而阐明对于数据库产品的理解应从"观念转变"的角度进行理解。"数据小兵"从自身MCDBA的角度对于数据库支持的DBA,强调保持对新技术的探索态度,本着对某数据库产品深入、学透态度,不断延伸对于数据库系统的技术能力。

数据库技术管理

书中对管理角度对数据技术提出相关要求、定义数据技术架构、评估数据技术、安装和管理数据技术、备案和跟踪数据技术的使用许可、支持数据技术的使用和问题。
"数据小兵"认为这部分内容重点可结合数据治理、数据架构、企业架构部分内容,从支撑数据的技术角度提出相关技术解决方案、整体技术架构及评估的维度、步骤等。书中提出ITIL作为信息技术基础设施库的技术管理模型,ITIL V3 的核心架构是基于服务生命周期的。服务战略是生命周期运转的轴心;服务设计,服务转换和服务运营是实施阶段;服务改进则在于对服务的定位和基于战略目标对有关的进程和项目的优化改进。向大家推荐 [荷] Jan Van B《基于ITIL的IT服务管理基础篇》。

例子

   为了更好说明,下面用一个例子来介绍。创建一个表并插入数据,然后创建不同的session,同事阻塞session。具体的代码截图如下:

1.创建表Employee

美高梅4858官方网站 1

2.插入测试数据

美高梅4858官方网站 2

 

 

现在我们有了测试表,表中有12条数据,打开另一个查询对话框在SSMS中(意味着重新创建了一个session)

3.在新的查询窗口中首先要开启事务,然后写一个插入语句

美高梅4858官方网站 3

 

在这个地方,我们能看到开启了一个事务。但是没有end tran 来终止事务,因此事务状态为“open”,现在运行脚本来看一下当前看起的运行处于“open”状态的session。

美高梅4858官方网站 4

 

    现在能够看到如上图展示一样,运行的查询正在open状态的session。我们执行了这个命令但是没有完结它,DBA会联系这个session的创建者来完成事务,或者回滚事务。

现在让我们创建另一个session,更新一条记录并且不提交,即让查询session的状态为“open”。因此在新的查询窗口中 写一个语句来执行如下:

 

美高梅4858官方网站 5

 

这里会看到系统正在运行后没有完成语句的状态(因为上一个事务没有关闭导致表锁,这个不能插入),现在可以在另外的窗口查询一下阻塞的情况,如下检查阻塞的session。

 

美高梅4858官方网站 6

 

如上所示,阻塞的session ID是58,由于我们更新查询导致阻塞了54的执行,54就是我们插入数据未提交的批处理。

现在我们能搞清楚阻塞的原因,也就可以从容解决阻塞了。

[经验体会]

通过本章的学习,"数据小兵"结合自身工作经验,谈一谈理解:
越来越多的DBA意识到"数据的价值",他们凭借深厚的技术功底逐步探求对于企业业务的理解。他们相信脱离业务的DBA永远没有生命力,技术的创新源于业务的理解。"数据"已经成为当下最炽热的焦点,跻身于DBA中的大师们在错综复杂的数据时空中,应增强数据思维能力的理解,深入业务的理解和企业的数据资产价值的开发、利用,相信DBA的未来会是支撑企业"数据资产"的不可替代的核心力量。

以上,观点为"数据小兵"的学习心得体会,不代表官方观点,欢迎小伙伴们提出宝贵的建议,"数据小兵"将非常感激!


打造"数据思维、数据知识、数据实践"的学习和分享环境,期待大家的参与!我们共同学习和进步!
数据小兵 http://www.fuduo.wang

解决

方案1

在了解业务的情况下,可以直接使用kill session ID的语句来终止某个阻塞的session。

方案2

在执行的事务的起始加入“set lock_timeout 1000” 语句,这表示如果阻塞超过1000毫秒,这个请求将被终止。

方案3

回滚或者提交事务。这个就不细说了。

下面是所有语句的代码:

 

/****Creating dummy table Employee ****/ 
CREATE TABLE Employee ( Empid int NOT NULL, Name nchar(10) NULL, City nchar(10) NULL ) ON [PRIMARY] GO 
/**** Insert dummy data in Employee table *****/ 
Insert into Employee Values(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'), (1175,'Pete','Topeka'), (875,'Petron','Vienna'), 
(2311,'Kohli','Mumbai'), (1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'), (957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), 
(2954,'Ganeshan','Mcclean')
 /***** Insert query in new session ****/ 
BEGIN TRAN Insert into Employee Values(1245,'George','Jax') 
/**** Query to check currently running sessions ****/ 
SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes FROM sys.dm_exec_sessions 
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id 
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id 
WHERE resource_type <> 'DATABASE' --AND name ='specific db name' 

ORDER BY name

 /**** update query in new session ****/ 
update Employee set name = 'SHERAZ' where empid = 1245 
/**** Query to check blocking queries with session id ****/ 
SELECT session_id, blocking_session_id, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle); 
/*** Command if you want to kill blocking session ****/ kill (54)

 

 

总结

     自己也使用过多种不同的语句来查询定位阻塞甚至死锁,然后解决,这里也是介绍一种临时解决方式。万变不离其宗,归根结底还是因为代码甚至数据库设计上存在很多问题才导致的阻塞,比如缺失索引、事务中的查询性能和逻辑顺序存在问题、T-SQL语句性能引起的等等不一而足。对于一些常年解决类似问题的DBA人员来说没啥价值,但是对于不太理解数据库的人来说还是能暂时解决一些紧急问题,当然最后还是要把理论基础打好才能尽可能的杜绝类似情况。

本文由美高梅网站是多少发布于美高梅-数据,转载请注明出处:数据操作管理,Oracle阻塞实例详解_oracle_脚本之家

上一篇:再次学习oracle的listener,ora配置文件详解 下一篇:的安装与使用,Oracle监听器相关知识
猜你喜欢
热门排行
精彩图文