lock - 数据库编程 - 编程开发网

-------------------------------- | 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 | | 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------
5 recursive calls
0 db block gets
5879 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

两种扫描方式都是全表扫描，都会发生排序（sort操作）但是rebulid online操作比rebulid性能更好，从逻辑读次数可知。
rebulid操作会阻塞dml操作，而online操作不会（online操作降低了锁级别）

deadlock:
SQL>show user;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
21
SQL> update t set name='d' where id=1;

已更新 1 行。



SQL> SHOW USER;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
19
SQL> update t set name='s' where id=2;

已更新 1 行。


SQL> SHOW USER;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
21
SQL> update t set name='y' where id=2;
update t set name='y' where id=2
*

第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁


SQL> show user;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
19
SQL> update t set name='s' where id=1;

上述顺序按操作顺序排列。
告警日志里（alert)有警告：
Wed Apr 22 14:43:05 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5024.trc.
查看转储文件：

.......
.......
*** 2015-04-22 14:43:04.053
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00030014-000009b7 24 21 X 32 19 X
TX-0006000d-00000a27 32 19 X 24 21 X

session 21: DID 0001-0018-00000019 session 19: DID 0001-0020-00000014
session 19: DID 0001-0020-00000014 session 21: DID 0001-0018-00000019

Rows waited on:
Session 21: obj - rowid = 00012BAA - AAASuqAAEAAABuvAAB
.........
.........

上面的内容是不是太详细了！哪个session，rowid都告诉我们了！可以更加深入研究死锁。

lock(三)