潛水珽的異想世界

問題描述:
在下drop table XXX 時,發生client連線當掉的問題.

解決方式與方法:
查看alert_log file找是否有相關的error訊息.

打開sql_trace的功能追蹤10046事件.
(請參考eygle blog:http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm)

將產生的sql dump file透過tkprof工具將trace file格式化,並擷取我們想要的部份.

********************************* report begin *********************************

DROP TABLE dbphr.P0001_DRUGITEM CASCADE CONSTRAINTS    <= drop table 的SQL statement

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 280 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                             55        3.07        168.96    <= 發現產生的一個library cache lock的wait event
********************************************************************************

SELECT COUNT(*)  
FROM
 ALL_POLICIES V  WHERE V.OBJECT_OWNER = :b1  AND V.OBJECT_NAME = :b2  AND
  POLICY_NAME LIKE  '%xdbrls%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.14       0.13          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         66          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.14       0.13          0         66          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 35     (recursive depth: 2)
********************************************************************************

SELECT COUNT(*)  
FROM
 USER_POLICIES V  WHERE V.OBJECT_NAME = :b1  AND POLICY_NAME LIKE  '%xdbrls%'
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.03       0.03          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         18          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.03       0.03          0         18          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 35     (recursive depth: 2)
********************************************************************************

select u2.name, o2.name
from
 ind$ i, obj$ o1, obj$ o2,    user$ u1, user$ u2 where    i.bo# = o1.obj# and
  o1.owner#=u1.user# and i.obj#=o2.obj#    and o2.owner#=u2.user# and u1.name=
  :1 and o1.name=:2 and i.type#=9

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          9          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS 
      0   NESTED LOOPS 
      0    NESTED LOOPS 
      1     NESTED LOOPS 
      1      TABLE ACCESS BY INDEX ROWID USER$
      1       INDEX UNIQUE SCAN I_USER1 (object id 44)
      1      TABLE ACCESS BY INDEX ROWID OBJ$
      1       INDEX RANGE SCAN I_OBJ2 (object id 37)
      0     TABLE ACCESS CLUSTER IND$
      1      INDEX UNIQUE SCAN I_OBJ# (object id 3)
      0    TABLE ACCESS BY INDEX ROWID OBJ$
      0     INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0   TABLE ACCESS CLUSTER USER$
      0    INDEX UNIQUE SCAN I_USER# (object id 11)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     4464     45.90      47.96         22       4173          0           0
Execute   5071     17.75      19.23         42       2446       2338         387
Fetch     5920     35.14     121.69     103099    2368881       1545       73778
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15455     98.79     188.89     103163    2375500       3883       74165

Misses in library cache during parse: 1547

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        9.34          9.34
  library cache lock                             55        3.07        168.96

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     3279      4.87       5.06         11        909          0           0
Execute   6558      3.48       3.49         20        659        229         154
Fetch    25860      2.85       5.75        397     116433          0       23331
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    35697     11.21      14.31        428     118001        229       23485

Misses in library cache during parse: 69

 4680  user  SQL statements in session.
 3064  internal SQL statements in session.
 7744  SQL statements in session.
********************************************************************************
Trace file: D:\oracle\admin\ntuhdb03\udump\ntuhdb03_ora_6436.trc
Trace file compatibility: 9.00.01
Sort options: default

     375  sessions in tracefile.
  1175151  user  SQL statements in trace file.
  749407  internal SQL statements in trace file.
    7744  SQL statements in trace file.
    2387  unique SQL statements in trace file.
  163214  lines in trace file.
 
 
********************************* report end *********************************** 
 
 
 
第一次在oracle中遇到這樣的問題,eygle給予的建議是說:建議重開機後試試,而我強烈懷疑是oracle本身的bug所造成的,
(因為之前也有曾經遇到相同的問題,而且還拋出了ora-600的error message)
check了一下Database版本,這裡的版本是9.2.0.1的版本,有些舊...

上metalink 查有關"library cache lock "的文件,其中有幾篇文件吸引到我的注意:
Note:263791.1
Note:3093080.8
bug 3093080

所以我懷疑是因為bug所造成的。