ITPub博客

首页 > 数据库 > Oracle > 云和恩墨的两道Oracle面试题

云和恩墨的两道Oracle面试题

原创 Oracle 作者:lhrbest 时间:2017-09-11 17:56:24 0 删除 编辑
云和恩墨的两道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的数据精度来设置查询条件。如果以后一旦字段的结构发生了修改,比如这个例子中字段的允许出现小数,那么这两个SQLWHERE条件就不再等价了。

若表属于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 3OBJECT_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 3OBJECT_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

说明被阻塞的会话SID31以上使用的是关联V$SQLSQL语句,实际上通过登录用户名等也可以快速定位被锁住的会话。

(2)查找 BLOCKER

得知等待事件是enq: TX - row lock contention,行锁,接下来就是要找到谁锁住了这个会话。在Oracle 10gR2以后,只需要查询GV$SESSION视图就可以迅速定位BLOCKER,通过BLOCKING_INSTANCEBLOCKING_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上的SID65的会话阻塞了实例2上的SID31的会话,并且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上的SID65的会话阻塞了实例2上的SID31的会话。

(3)乙方DBA需谨慎


第三个知识点是考核作为乙方的谨慎,即使查到了BLOCKER,是不是应该直接KILL掉,必须要先征询客户的意见,确认之后才可以杀掉。

(4)清除BLOCKER

已经确认了可以KILLSESSION之后,需要再找到相应SESSIONSERAIL#,这是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视图中显示的ID1ID2是什么意思?

答:V$LOCK中,当TYPE列的值为TM锁时,则ID1列的值为DBA_OBJECTS.OBJECT_IDID2列的值为0;当TYPE列的值为TX锁时,则ID1列的值为视图V$TRANSACTION中的XIDUSN字段(Undo Segment Number,事务对应的撤销段序列号)和XIDSLOT字段(Slot Number,事务对应的槽位号),其中,ID1的高16位为XIDUSN,低16位为XIDSLOTID2列的值为视图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的状态标识为KILLEDSERVER变为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、在使用物化视图的过程中,前者同时扫描物化视图和原表,效率较低。后者直接扫描物化视图。




删繁就简 - 云和恩墨的一道面试题解析

 2016-01-20 张乐奕 


张乐奕,网名 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; 


这道面试题中包含的知识点有:

  1. 如何在另外一个 session 中查找被堵塞的 session 信息;

  2. 如何找到产生行锁的 blocker;

  3. 在杀掉 blocker 进程之前会不会向面试监考人员询问,我已经找到了产生堵塞的会话,是不是可以kill掉;

  4. 在获得可以 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。


文章写到这里,忽然感觉网上那些一气呵成的故障诊断脚本其实挺误人的,只需要给一个参数,运行一下脚本就列出故障原因。所以很少人愿意再去研究这个脚本为什么这么写,各个视图之间的联系是如何环环相扣的。所以当你不再使用自己的笔记本,不再能迅速找到你赖以生存的那些脚本,你还能一步一步地解决故障吗?











About Me

.............................................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店

.............................................................................................................................................


DBA笔试面试讲解群1
DBA笔试面试讲解群2
欢迎与我联系



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2144810/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1153
  • 访问量
    7097052