RAC环境下的阻塞(blockingblocked)
RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环
RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考:Oracle 阻塞(blocking blocked)
1、演示环境
scott@DEVDB> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production --在scott session中发布SQL语句,并未提交 scott@DEVDB> begin 2 update emp set sal=sal+100 where empno=7788; 3 update dept set dname='DBA' where deptno=10; 4 end; 5 / PL/SQL procedure successfully completed. --在leshami session中更新emp对象 leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788; --在usr1 session中更新emp对象 usr1@DEVDB> update scott.dept set dname='DEV' where deptno=10;
2、寻找阻塞
scott@DEVDB> @block_session_rac USER_STATUS SID_SERIAL CONN_INSTANCE SID PROGRAM OSUSER MACHINE LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME --------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- ------------------------- Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 DEPT Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 EMP Waiting '49,1007' devdb1 49 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMP Waiting '933,11691' devdb2 933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT --通过上述脚本我们可以看到session '20,1545' 锁住了对象DEPT以及EMP,而此时session '49,1007'与'933,11691'处于等待状态。 --下面是另外的一种方式来获取阻塞的情形 scott@DEVDB> @block_session_rac2 BLOCKING_STATUS ---------------------------------------------------------------------------------------------------------------------------- SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 ) SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 ) --Author : Leshami --Blog : http://blog.csdn.net/leshami
3、演示中用到的脚本
[oracle@Linux-01 ~]$ more block_session_rac.sql set linesize 180 col user_status format a15 col sid_serial format a15 col program format a30 wrapped col machine format a15 wrapped col osuser format a15 wrapped col conn_instance format a15 col object_name format a25 wrapped SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status, CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial, (SELECT instance_name FROM gv$instance WHERE inst_id = l.inst_id) conn_instance, s.sid, s.program, s.osuser, s.machine, DECODE (l.TYPE, 'RT', 'Redo Log Buffer', 'TD', 'Dictionary', 'TM', 'DML', 'TS', 'Temp Segments', 'TX', 'Transaction', 'UL', 'User', 'RW', 'Row Wait', l.TYPE) lock_type--,id1 --,id2 , DECODE (l.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', LTRIM (TO_CHAR (lmode, '990'))) lock_mode, ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status , object_name FROM gv$lock l JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid) JOIN gv$locked_object o ON (o.inst_id = s.inst_id AND s.sid = o.session_id) JOIN dba_objects d ON (d.object_id = o.object_id) WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0) ORDER BY id1, id2, ctime DESC; [oracle@Linux-01 ~]$ more block_session_rac2.sql SELECT DISTINCT s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' Serail#=' || s1.serial# || ' ) IS BLOCKING ' || s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' Serial#=' || s2.serial# || ' ) ' AS blocking_status FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND s1.inst_id = l1.inst_id AND s2.inst_id = l2.inst_id AND l1.block > 0 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2;
更多参考
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Windows Recovery Environment (WinRE) is an environment used to repair Windows operating system errors. After entering WinRE, you can perform system restore, factory reset, uninstall updates, etc. If you are unable to boot into WinRE, this article will guide you through fixes to resolve the issue. Unable to boot into the Windows Recovery Environment If you cannot boot into the Windows Recovery Environment, use the fixes provided below: Check the status of the Windows Recovery Environment Use other methods to enter the Windows Recovery Environment Did you accidentally delete the Windows Recovery Partition? Perform an in-place upgrade or clean installation of Windows below, we have explained all these fixes in detail. 1] Check Wi

In this article, we will learn about the differences between Python and Anaconda. What is Python? Python is an open source language that places great emphasis on making the code easy to read and understand by indenting lines and providing whitespace. Python's flexibility and ease of use make it ideal for a variety of applications, including but not limited to scientific computing, artificial intelligence, and data science, as well as creating and developing online applications. When Python is tested, it is immediately translated into machine language because it is an interpreted language. Some languages, such as C++, require compilation to be understood. Proficiency in Python is an important advantage because it is very easy to understand, develop, execute and read. This makes Python

Introduction and core concepts of OracleRAC (RealApplicationClusters) As the amount of enterprise data continues to grow and the demand for high availability and high performance becomes increasingly prominent, database cluster technology becomes more and more important. OracleRAC (RealApplicationClusters) is designed to solve this problem. OracleRAC is a high-availability, high-performance cluster database solution launched by Oracle.

PHP integrated environment packages include: 1. PhpStorm, a powerful PHP integrated environment; 2. Eclipse, an open source integrated development environment; 3. Visual Studio Code, a lightweight open source code editor; 4. Sublime Text, a A popular text editor, widely used in various programming languages; 5. NetBeans, an integrated development environment developed by the Apache Software Foundation; 6. Zend Studio, an integrated development environment designed for PHP developers.

Setting environment variables on Windows 11 can help you customize your system, run scripts, and configure applications. In this guide, we'll discuss three methods along with step-by-step instructions so you can configure your system to your liking. There are three types of environment variables System environment variables – Global variables are the lowest priority and are accessible to all users and applications on Windows and are typically used to define system-wide settings. User Environment Variables – Higher priority, these variables only apply to the current user and process running under that account, and are set by the user or application running under that account. Process environment variables – have the highest priority, they are temporary and apply to the current process and its sub-processes, providing the program

Common problems and solutions for Laravel environment configuration file .env When using the Laravel framework to develop projects, the environment configuration file .env is very important. It contains key configuration information of the project, such as database connection information, application keys, etc. However, sometimes there are some common problems when configuring the .env file. This article will introduce these problems and provide solutions, and attach specific code examples for reference. Problem 1: Unable to read the .env file when we have configured the .env file

Go language is a programming language with very powerful concurrency features. It uses the concept of goroutine to achieve concurrency, and also provides a wealth of tools and methods to deal with blocking. In the Go language, the implementation methods and advantages of blocking are important things we need to understand. This article will introduce the implementation method of blocking in Go language and its advantages, and provide specific code examples to help readers better understand. Implementation methods of blocking In the Go language, blocking can be implemented in a variety of ways, including channels, mutual

OracleRAC (RealApplicationClusters) is a scalability solution provided by Oracle Corporation that allows Oracle database instances to be run on multiple servers and multiple servers are combined together to provide high availability and scalability. The architecture and features of OracleRAC are very unique. This article will deeply explore the architecture, features and specific code examples of OracleRAC. 1. OracleRAC architecture Oracle
