转://云和恩墨的两道Oracle面试题
真题1、 对于一个NUMBER(1)的列，如果查询中的WHERE条件分别是大于3和大于等于4，那么这二者是否等价？
答案：首先对于查询结果而言，二者没有任何区别。从这一点上讲无论是指定大于3还是指定大于等于4，二者结果都是一样的。但是，结果集一样并不代表二者等价，主要表现为以下几点：
① 在CHECK约束下，如果表属于非SYS用户，那么大于3会执行全表扫描；而大于等于4在经过CHECK约束的检查后，通过FILTER结束查询，能够更高效地返回结果，不用扫描全表。如果表属于SYS用户，那么这二者的执行计划是相同的。因为，若表属于非SYS用户，则最终经过查询转换后的执行SQL为：“SELECT "T_NUM1_LHR"."ID" "ID" FROM "SYS"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 AND 4>4”，而若表属于SYS用户，则最终经过查询转换后的执行SQL为：“SELECT "T_NUM1_LHR"."ID" "ID" FROM "LHR"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4”，所以，在非SYS用户下，最终的执行计划中会有“filter(NULL IS NOT NULL)”的谓词条件。
② 在使用索引的时候，由于Oracle索引结构的特点，两者扫描的节点都是从4开始，在执行计划、逻辑读和执行时间等各方面都不存在性能差异。
③ 在使用物化视图的过程中，大于3会同时扫描物化视图和原表，效率较低；而大于等于4会直接扫描物化视图，效率较高。
由此可见，在返回结果集相同的情况下，使用大于等于代替大于在某些特殊情况下可以带来SQL语句性能上的提升。总结一下，如下图所示：
对于这几种情况分别实验如下：
SYS@orclasm > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
（一）在CHECK约束下，二者的执行计划是不一样的。
DROP TABLE T_NUM1_LHR;
CREATE TABLE T_NUM1_LHR(ID NUMBER(1));
ALTER TABLE T_NUM1_LHR ADD CHECK(ID <4);
SET AUTOT ON
SELECT * FROM T_NUM1_LHR WHERE ID>3;
SELECT * FROM T_NUM1_LHR WHERE ID>=4;
LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>3;
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2700622406
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">3)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>=4;
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3764107410
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("ID">=4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
如果表中恰好有上面的CHECK约束，那么可以发现，对于大于3和大于等于4这两个SQL的执行计划是不一致的。对于后者，由于查询的条件违反了CHECK约束，因此Oracle在执行计划前面增加了一个FILTER，使得整个查询不需要在执行，因此这个查询不管表中数据有多少，都会在瞬间结束。
而对于大于3这种情况，虽然根据CHECK的约束和列定义，可以推断出这条查询不会返回任何记录，但是Oracle的优化器并没有聪明到根据列的精度来进行分析，因此这个查询会执行全表扫描。也就是说，虽然这两个查询的最终结果一样，但是执行计划并不相同，而且对于大表而言，这种情况下性能也有较大的差别。
当然这种CHECK约束是特例的情况，一般情况下不会出现。原则上到底是选择大于3还是大于等于4，应该根据具体的业务来决定，而不要尝试利用Oracle的数据精度来设置查询条件。如果以后一旦字段的结构发生了修改，比如这个例子中字段的允许出现小数，那么这两个SQL的WHERE条件就不再等价了。
若表属于SYS用户，则这二者的执行计划是相同的。
下面通过10053事件查看具体原因：
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT * FROM T_NUM1_LHR WHERE ID >= 4;
ALTER SESSION SET EVENTS '10053 trace name context off';
SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';
SYS用户：
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T_NUM1_LHR"."ID">=4
apadrv-start sqlid=4141557682765762850
:
call(in-use=1400, alloc=16344), compile(in-use=54632, alloc=55568), execution(in-use=2480, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T_NUM1_LHR"."ID" "ID" FROM "SYS"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4
kkoqbc: optimizing query block SEL$1 (#0)
普通用户：
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
constraint: "T_NUM1_LHR"."ID"<4
finally: "T_NUM1_LHR"."ID">=4 AND 4>4
FPD: transitive predicates are generated in query block SEL$1 (#0)
"T_NUM1_LHR"."ID">=4 AND 4>4
apadrv-start sqlid=11964066854041036881
:
call(in-use=1696, alloc=16344), compile(in-use=55176, alloc=58488), execution(in-use=2744, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T_NUM1_LHR"."ID" "ID" FROM "LHR"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 AND 4>4
kkoqbc: optimizing query block SEL$1 (#0)
（二）在有索引的情况下，二者的性能是否有差异
DROP TABLE T_NUM2_LHR;
CREATE TABLE T_NUM2_LHR(ID NUMBER,NAME VARCHAR2(30));
CREATE INDEX IND_TNUM2_ID ON T_NUM2_LHR(ID);
INSERT INTO T_NUM2_LHR SELECT 3，OBJECT_NAME FROM DBA_OBJECTS;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
COMMIT;
INSERT INTO T_NUM2_LHR VALUES(4,'test');
COMMIT;
SET TIMING ON
SET AUTOT ON
SELECT * FROM T_NUM2_LHR WHERE ID>3;
SELECT * FROM T_NUM2_LHR WHERE ID>=4;
LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>3;
ID NAME
---------- ------------------------------
4 test
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4021107501
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">3)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>=4;
ID NAME
---------- ------------------------------
4 test
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4021107501
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，无论是执行时间，还是逻辑读，两个SQL没有任何的差别。根据Oracle索引结构的特点，无论是大于3还是大于等于4，这二者的查询所扫描的叶节点都是同一个，因此，在这一点上不会存在性能的差别。
（三）在使用物化视图上的差别
如果表上建立了可查询重写的物化视图，那么这两个查询在是否使用物化视图上有所差别。
CREATE TABLE T_NUM3_LHR(ID NUMBER,NUM NUMBER(1));
ALTER TABLE T_NUM3_LHR ADD PRIMARY KEY(ID);
INSERT INTO T_NUM3_LHR SELECT ROWNUM,MOD(ROWNUM,4) FROM DBA_OBJECTS;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+54916,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+109832,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+219664,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+439328,MOD(ROWNUM,4) FROM T_NUM3_LHR;
COMMIT;
INSERT INTO T_NUM3_LHR VALUES(1000000,4);
COMMIT;
SET AUTOT ON
SELECT * FROM T_NUM3_LHR WHERE NUM>3;
SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
LHR@orclasm > SET AUTOT ON
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 621453705
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">3)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1150 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 621453705
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1150 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由于采用的都是全表扫描，二者执行的时间和逻辑读完全一样。
下面建立一个物化视图：
SET AUTOT OFF
CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
LHR@orclasm > SET AUTOT OFF
LHR@orclasm > CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
Materialized view log created.
LHR@orclasm > CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
Materialized view created.
LHR@orclasm > show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
LHR@orclasm > SET AUTOT ON
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
Plan hash value: 4012093353
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 338 | 317 (3)| 00:00:04 |
| 1 | VIEW | | 13 | 338 | 317 (3)| 00:00:04 |
| 2 | UNION-ALL | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NUM">3 AND "NUM"<4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1153 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm >
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4274348025
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划可以看到，对于大于等于4的情况，Oracle直接扫描了物化视图了。而对于大于3的情况，Oracle同时扫描了物化视图和原表，显然效率比较低。
这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的，但是对于CBO来说，并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。
整个实验SQL语句：
DROP TABLE T_NUM1_LHR;
CREATE TABLE T_NUM1_LHR(ID NUMBER(1));
ALTER TABLE T_NUM1_LHR ADD CHECK(ID <4);
SET AUTOT ON
SELECT * FROM T_NUM1_LHR WHERE ID>3;
SELECT * FROM T_NUM1_LHR WHERE ID>=4;
DROP TABLE T_NUM2_LHR;
CREATE TABLE T_NUM2_LHR(ID NUMBER,NAME VARCHAR2(30));
CREATE INDEX IND_TNUM2_ID ON T_NUM2_LHR(ID);
INSERT INTO T_NUM2_LHR SELECT 3，OBJECT_NAME FROM DBA_OBJECTS;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
COMMIT;
INSERT INTO T_NUM2_LHR VALUES(4,'test');
COMMIT;
SET TIMING ON
SET AUTOT ON
SELECT * FROM T_NUM2_LHR WHERE ID>3;
SELECT * FROM T_NUM2_LHR WHERE ID>=4;
CREATE TABLE T_NUM3_LHR(ID NUMBER,NUM NUMBER(1));
ALTER TABLE T_NUM3_LHR ADD PRIMARY KEY(ID);
INSERT INTO T_NUM3_LHR SELECT ROWNUM,MOD(ROWNUM,4) FROM DBA_OBJECTS;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+54916,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+109832,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+219664,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+439328,MOD(ROWNUM,4) FROM T_NUM3_LHR;
COMMIT;
INSERT INTO T_NUM3_LHR VALUES(1000000,4);
COMMIT;
SET AUTOT ON
SELECT * FROM T_NUM3_LHR WHERE NUM>3;
SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
SET AUTOT OFF
CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
真题2、 一个RAC双节点的实例环境，面试人员使用的是实例2，而在实例1中已经使用“SELECT * FROM SCOTT.EMP FOR UPDATE;”给EMP表加锁：
SQL> SELECT * FROM SCOTT.EMP FOR UPDATE;
此时在实例2中，如果执行以下SQL语句尝试更新ENAME字段，那么必然会被行锁堵塞：
SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369;
请尝试解决这个故障。
答案：这道面试题中包含的知识点有：
① 如何在另外一个SESSION中查找被堵塞的SESSION信息；
② 如何找到产生行锁的BLOCKER；
③ 在杀掉BLOCKER进程之前会不会向面试监考人员询问，是否可以KILL掉阻塞者；
④ 在获得可以KILL掉进程的确认回复后，正确杀掉另一个实例上的进程。
正确的思路和解法应该如下：
（1）检查被阻塞会话的等待事件
更新语句回车以后没有回显，明显是被阻塞了，那么现在这个会话当前是什么等待事件呢？可以通过SESSION等待去获取这些信息：
SQL> SELECT SID,EVENT,USERNAME,SQL.SQL_TEXT FROM V$SESSION S,V$SQL SQL WHERE S.SQL_ID=SQL.SQL_ID AND SQL.SQL_TEXT LIKE 'UPDATE SCOTT.EMP SET ENAME%';
SID EVENT USERNAME SQL_TEXT
---------- ---------------------------------- ----------- ----------------------------------------------------------------
31 enq: TX - row lock contention SYS UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369
说明被阻塞的会话SID为31。以上使用的是关联V$SQL的SQL语句，实际上通过登录用户名等也可以快速定位被锁住的会话。
（2）查找 BLOCKER
得知等待事件是“enq: TX - row lock contention”，行锁，接下来就是要找到谁锁住了这个会话。在Oracle 10gR2以后，只需要查询GV$SESSION视图就可以迅速定位BLOCKER，通过BLOCKING_INSTANCE和BLOCKING_SESSION字段即可。
SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE,BLOCKING_SESSION FROM gv$session WHERE INST_ID=2 AND SID=31;
SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ----------------- ----------------
31 2 1 65
SQL> SELECT SID,EVENT,S.USERNAME,SQL.SQL_TEXT FROM GV$SESSION S,GV$SQL SQL WHERE (S.SQL_ID=SQL.SQL_ID OR S.PREV_SQL_ID=SQL.SQL_ID) AND S.INST_ID=SQL.INST_ID AND SID=65;
SID EVENT USERNAME SQL_TEXT
---------- ------------------------------ --------- -------------------------------------
65 SQL*Net message from client LHR SELECT * FROM SCOTT.EMP FOR UPDATE
可以看到，实例1上的SID为65的会话阻塞了实例2上的SID为31的会话，并且SQL语句为“SELECT * FROM SCOTT.EMP FOR UPDATE”。上述方法是最简单的，如果使用更传统的方法，那么实际上也并不难，从GV$LOCK视图中去查询即可，如下所示：
SQL> SQL> SELECT TYPE,ID1,ID2,LMODE,REQUEST FROM gv$lock WHERE sid=31 and INST_ID=2;
TY ID1 ID2 LMODE REQUEST
-- ---------- ---------- ---------- ----------
AE 100 0 4 0
TX 262148 1512 0 6
TM 73201 0 3 0
SQL> SELECT INST_ID,SID,TYPE,LMODE,REQUEST FROM gv$lock WHERE ID1=262148 and ID2=1512;
INST_ID SID TY LMODE REQUEST
---------- ---------- -- ---------- ----------
2 31 TX 0 6
1 65 TX 6 0
说明是实例1上的SID为65的会话阻塞了实例2上的SID为31的会话。
（3）乙方DBA需谨慎
第三个知识点是考核作为乙方的谨慎，即使查到了BLOCKER，是不是应该直接KILL掉，必须要先征询客户的意见，确认之后才可以杀掉。
（4）清除BLOCKER
已经确认了可以KILL掉SESSION之后，需要再找到相应SESSION的SERAIL#，这是KILL SESSION时必须输入的参数。
SQL> SELECT SID,SERIAL# FROM gv$session WHERE INST_ID=1 AND SID=65;
SID SERIAL#
---------- ----------
65 3707
如果是 11gR2 数据库，那么直接在实例2中加入@1参数就可以杀掉实例1中的会话，如果是10g，那么登入实例1再执行KILL SESSION的操作。
SQL> ALTER SYSTEM KILL SESSION '65,3707,@1' IMMEDIATE;
System altered.
再检查之前被阻塞的更新会话，可以看到已经更新成功了。
SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369;
1 row updated.
（5）延伸内容
在该问题中，面试官可能会再延伸的询问以下问题：
① 在V$LOCK视图中显示的ID1和ID2是什么意思？
答：在V$LOCK中，当TYPE列的值为TM锁时，则ID1列的值为DBA_OBJECTS.OBJECT_ID，ID2列的值为0；当TYPE列的值为TX锁时，则ID1列的值为视图V$TRANSACTION中的XIDUSN字段（Undo Segment Number，事务对应的撤销段序列号）和XIDSLOT字段（Slot Number，事务对应的槽位号），其中，ID1的高16位为XIDUSN，低16位为XIDSLOT。ID2列的值为视图V$TRANSACTION中的XIDSQN字段（Sequence Number：事务对应的序列号）。
当TYPE列的值为TX锁时，计算ID1列的值的公式为：
SELECT TRUNC(ID1/POWER(2,16)) AS XIDUSN,BITAND(ID1,TO_NUMBER('FFFF','XXXX')) + 0 AS XIDSLOT , ID2 XIDSQN FROM DUAL;
所有与锁有关的数据字典视图之间的关联关系如下图所示：
② 在使用KILL命令杀会话的时候加上IMMEDIATE和不加IMMEDIATE的区别是什么？KILLED状态的会话如何删除其OS进程？
答：一般情况下，可以通过执行SQL语句“ALTER SYSTEM KILL SESSION 'SID,SERIAL#'”直接杀掉会话。当SESSION是ACTIVE的时候，ALTER SYSTEM KILL SESSION只是将SESSION的状态标识为KILLED，SERVER变为PSEUDO状态，但可能并不会立即释放SESSION所有所持有的资源，所以，在执行完ALTER SYSTEM KILL SESSION后，会话还是一直存在（V$SESSION视图中存在，且后边OS进程也存在）。所以，在执行命令KILL SESSION的时候，可以在后边加上IMMEDIATE，这样在没有事务的情况下，相关会话就会立即被删除而不会变为KILLED的状态（V$SESSION视图中不存在），当有事务存在的情况下，会先进行回滚相关的事务，然后释放会话所占有的资源。
另外，由于变为KILLED状态的会话的PADDR列都变成了另外一个值，因此，通过平常的连接方式就没有办法关联到后台进程，在Oracle 11g下提供了CREATOR_ADDR列，该列可以关联到后台进程，对于Oracle 10g可以通过特殊的SQL找到后台的进程号。
Oracle 10g可以使用如下的脚本：
SELECT INST_ID, SPID, PROGRAM,'KILL -9 '|| SPID KILL9
FROM GV$PROCESS A
WHERE PROGRAM != 'PSEUDO'
AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SESSION)
AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$BGPROCESS)
AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SHARED_SERVER)
;
Oracle 11g可以使用如下的脚本：
SET LINE 9999
COL SESSIONID FORMAT A20
COL SESSIONID_KILLED FORMAT A20
COL KILL_SESSION FORMAT A60
SELECT A.INST_ID,
A.SID || ',' || A.SERIAL# || ',' ||
(SELECT SPID
FROM GV$PROCESS B
WHERE B.INST_ID = A.INST_ID
AND A.CREATOR_ADDR = B.ADDR --AND DECODE(A.STATUS,'KILLED',A.CREATOR_ADDR,A.PADDR) = B.ADDR
) SESSIONID,
A.PADDR,
A.STATUS,
A.PROGRAM,
'ALTER SYSTEM DISCONNECT SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE;' KILL_SESSION
FROM GV$SESSION A
WHERE A.USERNAME = 'SYS'
AND A.STATUS = 'KILLED';
必读推荐- 90%的面试者都不知道这道题的答案
原创 2017-09-11 杨廷琨 数据和云
小编寄语
亲爱的DBA同胞们，你们是否记得在你找工作时，印象最深刻的面试题呢？那些看似简单的题目，实则蕴藏很大的玄机。今天我们通过一道经典的 ORacle DBA面试题目，去发现我们在面试中，到底还缺少那些能力？
这道题看起来很简单，然而，90%的面试者都不知道答案。。。
请点击此处输入图片描述
面试题描述：
对于一个NUMBER(1)的列，查询中的WHERE条件如果分别是大于3和大于等于4，二者是否等价。
乍一看，这个问题并不难。请读者朋友们在继续读下文之前，用30秒的时间思考。
请点击此处输入图片描述
接下来我们通过杨长老的博客，来说明面试者在这道题目中给你挖下了那些坑。首先对于查询结果而言，二者没有任何区别。从这一点上讲无论是指定大于3还是指定大于等于4，二者结果都是一样的。
但是，结果集一样并不代表二者等价。
The
01
在Check约束下，二者的执行计划是不一样的
首先我们来看二者的执行计划：
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
如果表中恰好有上面的CHECK约束，可以发现，对于大于3和大于等于4这两个SQL的执行计划是不一致的。
对于后者，由于查询的条件违反了CHECK约束，因此Oracle在执行计划前面增加了一个FILTER，使得整个查询不需要在执行，因此这个查询不管表中数据有多少，都会在瞬间结束。
而对于大于3这种情况，虽然根据CHECK的约束和列定义，可以推断出这条查询不会返回任何记录，但是Oracle的优化器并没有聪明到根据列的精度来进行分析，因此这个查询会执行全表扫描。
也就是说，虽然这两个查询的最终结果一样，但是执行计划并不相同，而且对于大表而言，这种情况下性能也有较大的差别。
当然这种CHECK约束是特例的情况，一般情况下不会出现。原则上到底是选择大于3还是大于等于4，应该根据具体的业务来决定，而不要尝试利用Oracle的数据精度来设置查询条件。如果以后一旦字段的结构发生了修改，比如这个例子中字段的允许出现小数，那么这两个SQL的WHERE条件就不再等价了。
The
02
在有索引的情况下，二者的性能是否有差异
有朋友认为，如果是大于3的话，走索引扫描，会从3开始扫描，而大于等于4，会从4开始扫描。
根据这个观点对于大于3的情况，Oracle会先找到索引键值为3的块，然后开始扫描。而对于大于等于4的情况，则会找到索引键值为4的块，然后执行扫描。如果表中包含大量索引键值为3的记录，这两个查询就可能存在性能上的差异。
看一个具体的例子：
请点击此处输入图片描述
创建一张测试表，表中所有的ID都等于3，下面插入一条ID为4记录：
请点击此处输入图片描述
下面执行查询语句：
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
可以看到，无论是执行时间，还是逻辑读，两个SQL没有任何的差别。为了更好的证明Oracle并没有读取ID等于3的记录，执行下面的查询：
请点击此处输入图片描述
请点击此处输入图片描述
其实看过Concept对索引结构有一定了解就知道，根据Oracle索引结构的特点，无论是大于3还是大于等于4，二者的查询是扫描的叶节点都是同一个，因此不会在这一点上不会存在性能的差别。
The
03
在使用物化视图上的差别
如果表上建立了可查询重写的物化视图，两个查询在是否使用物化视图上有所差别。
例子仍然使用T_NUM表：
请点击此处输入图片描述
先构造一张大表，现在分别执行两个查询，检查执行结果：
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
由于采用的都是全表扫描，二者执行的时间和逻辑读完全一样。
下面建立一个物化视图：
请点击此处输入图片描述
下面检查系统设置是否满足查询重写：
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
请点击此处输入图片描述
从执行计划可以看到，对于大于等于4的情况，Oracle直接扫描了物化视图了。而对于大于3的情况，Oracle同时扫描了物化视图和原表，显然效率比较低。
这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的，但是对于CBO来说，并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。
总结一下两者的区别
请点击此处输入图片描述
1、在Check约束下，前者会执行全表扫描，后者经过check约束的检查后，通过filter结束查询，能够更高效地返回结果；
2、在使用索引的时候，由于Oracle索引结构的特点，两者扫描的节点都是从4开始，在执行计划和逻辑读，执行时间等各方面都不存在性能差异；
3、在使用物化视图的过程中，前者同时扫描物化视图和原表，效率较低。后者直接扫描物化视图。
删繁就简 - 云和恩墨的一道面试题解析
张乐奕，网名 Kamus
云和恩墨副总经理，Oracle ACE总监，ACOUG 联合创始人
我们有一道面试题，原以为很简单，但是却发现面试者能够完美解出的几乎没有，一部分人有思路，但是可能是因为面试紧张，很难在指定时间内完成解题，而更大一部分人连思路也不清晰。
请听题
题目是：请将 emp.empno=7369 的记录 ename 字段修改为“ENMOTECH”并提交，你可能会遇到各种故障,请尝试解决。
其实题目的设计非常简单，一个 RAC 双节点的实例环境，面试人员使用的是实例2，而我们在实例1中使用 select for update 将 EMP 表加锁:
SQL> SELECT * FROM emp FOR UPDATE;
此时在实例2中，如果执行以下 SQL 语句尝试更新 ename 字段，必然会被行锁堵塞:
SQL> UPDATE emp SET ename='ENMOTECH' WHERE empno=7369;
这道面试题中包含的知识点有：
-
如何在另外一个 session 中查找被堵塞的 session 信息；
-
如何找到产生行锁的 blocker；
-
在杀掉 blocker 进程之前会不会向面试监考人员询问，我已经找到了产生堵塞的会话，是不是可以kill掉；
-
在获得可以 kill 掉进程的确认回复后，正确杀掉另一个实例上的进程。
这道题我们期待可以在5分钟之内获得解决，实际上大部分应试者在15分钟以后都完全没有头绪。
注意：其实Oracle的任何复杂问题处理，都可以是由删繁就简的步骤逐层推演出来的，保持清醒的思路，对于DBA的工作非常重要。
正确的思路和解法应该如下：
检查被阻塞会话的等待事件
更新语句回车以后没有回显，明显是被锁住了，那么现在这个会话经历的是什么等待事件呢？
可以通过SESSION等待去获取这些信息：
SQL> SELECT sid,event,username,SQL.sql_text 2 FROM v$session s,v$sql SQL 3 WHERE s.sql_id=SQL.sql_id 4 AND SQL.sql_text LIKE 'update emp set ename%'; SID EVENT USERNAME --- ------------------------------ ---------- 79 enq: TX - ROW LOCK contention ENMOTECH SQL_TEXT --------------------------------------------------- UPDATE emp SET ename='ENMOTECH' WHERE empno=7369 |
以上使用的是关联 v$sql 的 SQL 语句，实际上通过登录用户名等也可以快速定位被锁住的会话。
查找 blocker
得知等待事件是 enq: TX – row lock contention，行锁，接下来就是要找到谁锁住了这个会话。在10gR2以后，只需要 gv$session 视图就可以迅速定位 blocker，通过 BLOCKING_INSTANCE 和 BLOCKING_SESSION 字段即可。
SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE, BLOCKING_SESSION FROM gv$session WHERE INST_ID=2 AND SID=79; SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION --- ------- ----------------- ---------------- 79 2 1 73 |
上述方法是最简单的，如果是使用更传统的方法，实际上也并不难，从 gv$lock 视图中去查询即可。
SQL> SELECT TYPE,ID1,ID2,LMODE,REQUEST
FROM v$lock WHERE sid=79;
TY ID1 ID2 LMODE REQUEST
-- ---------- ---------- ----- -------
TX 589854 26267 0 6
AE 100 0 4 0
TM 79621 0 3 0
SQL> SELECT INST_ID,SID,TYPE,LMODE,REQUEST
FROM gv$Lock WHERE ID1=589854 AND ID2=26267;
INST_ID SID TY LMODE REQUEST
---------- ---- -- ---------- ----------
2 79 TX 0 6
1 73 TX 6 0
|
乙方DBA需谨慎
第三个知识点是考核作为乙方的谨慎，即使你查到了 blocker，是不是应该直接 kill 掉，必须要先征询客户的意见，确认之后才可以杀掉。
清除blocker
已经确认了可以 kill 掉 session 之后，需要再找到相应 session的serail#，这是 kill session 时必须输入的参数。
SQL> SELECT SID,SERIAL#
FROM gv$session
WHERE INST_ID=1 AND SID=73;
SID SERIAL#
---------- ----------
73 15625
|
如果是 11gR2 数据库，那么直接在实例2中加入@1参数就可以杀掉实例1中的会话，如果是10g，那么登入实例1再执行 kill session 的操作。
SQL> ALTER system KILL SESSION '73,15625,@1'; System altered. |
再检查之前被阻塞的更新会话，可以看到已经更新成功了。
SQL> UPDATE emp SET ename='ENMOTECH' WHERE empno=7369; 1 ROW updated. |
对于熟悉整个故障解决过程的人，或者具备清晰思路的DBA，5分钟之内就可以解决问题。
深入一步
对于 TX 锁，在 v$lock 视图中显示的 ID1 和 ID2 是什么意思？ 解释可以从 v$lock_type 视图中获取。
SQL> SELECT ID1_TAG,ID2_TAG FROM V$LOCK_TYPE WHERE TYPE='TX'; ID1_TAG ID2_TAG --------------- ---------- usn<<16 | slot SEQUENCE |
所以 ID1 是事务的 USN+SLOT，而 ID2 则是事务的 SQN。这些可以从 v$transaction 视图中获得验证。
SQL> SELECT taddr
FROM v$session WHERE sid=73;
TADDR
----------------
000000008E3B65C0 SQL> SELECT XIDUSN,XIDSLOT,XIDSQN
FROM v$transaction
WHERE addr='000000008E3B65C0';
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
9 30 26267
|
如何和 ID1=589854 and ID2=26267 对应呢？ XIDSQN=26267 和 ID2=26267 直接就对应了，没有问题。 那么 ID1=589854 是如何对应的？将之转换为16进制，是 0x9001E，然后分高位和低位分别再转换为10进制，高位的16进制9就是十进制的9，也就是 XIDUSN=9，而低位的16进制1E转换为10进制是30，也就是 XIDSLOT=30。
文章写到这里，忽然感觉网上那些一气呵成的故障诊断脚本其实挺误人的，只需要给一个参数，运行一下脚本就列出故障原因。所以很少人愿意再去研究这个脚本为什么这么写，各个视图之间的联系是如何环环相扣的。所以当你不再使用自己的笔记本，不再能迅速找到你赖以生存的那些脚本，你还能一步一步地解决故障吗？
