--------------------------------
| 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都告诉我们了！可以更加深入研究死锁。