L> explain plan for update wms.inf_lmis_imp_rk_bill t set t.upd_state='1' wher
e t.cgddh=:Var1;
explain plan for update wms.inf_lmis_imp_rk_bill t set t.upd_state='1' where t.c
gddh=:Var1
*
第 1 行出现错误:
ORA-04021: 等待锁定对象 时发生超时

TKPROF: Release 11.2.0.4.0 - Development on 星期一 6月 6 15:28:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: hydee_ora_16188.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
explain plan for update wms.inf_lmis_imp_rk_bill t set t.upd_state='1' where t.cgddh=:Var1
Error encountered: ORA-04021
********************************************************************************
explain plan set statement_id = 'SYS' for
update wms.inf_lmis_imp_rk_bill t set t.upd_state='1' where t.cgddh=:Var1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 5.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 5.02 0 0 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 95
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
row cache lock 1 5.01 5.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 23.12 23.12
********************************************************************************

********************************************************************************
SQL ID: gx4mv66pvj3xz Plan Hash: 1932954096
select con
#,type#
,condlength,intcols,robj
#,rcon#
,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 18 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 18 0 7
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
7 4 7 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=9 pr=0 pw=0 time=0 us cost=2 size=255 card=5)
7 4 7 INDEX RANGE SCAN I_CDEF2 (cr=6 pr=0 pw=0 time=12 us cost=1 size=0 card=5)(object id 54)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: shared pool 1 0.00 0.00
********************************************************************************
SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388
select intcol
#,nvl(pos#
,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 0.00 0.00 0 28 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 0.00 0.00 0 28 0 7
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=16 card=1)
1 1 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 57)

********************************************************************************
SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959
select col
#, grantee#
, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col
#, grantee#

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 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=0 us cost=4 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959
select grantee
#,privilege#
,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee
#,privilege#
,nvl(col#,0) order by
grantee#
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 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=4 size=15 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=3 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
1 1 1 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 4m7m0t6fjcs5x Plan Hash: 1935744642
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
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 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 1 2 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=0 us)
1 1 1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=70 card=1)(object id 79)
********************************************************************************
SQL ID: dr364d31qvq4g Plan Hash: 0
explain plan for update wms.inf_lmis_imp_rk_bill t set t.upd_state='1' where
t.cgddh=:Var1
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 mode: ALL_ROWS
Parsing user id: 95
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
row cache lock 1 6.00 6.00
SQL*Net break/reset to client 2 0.16 0.16
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 18.68 18.68
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 95

********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 2 0.00 5.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 5.02 0 0 0 0
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 23.12 48.93
row cache lock 2 6.00 11.02
SQL*Net break/reset to client 2 0.16 0.16
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 19 0.03 0.02 0 0 0 0
Execute 36 0.01 0.03 0 4 27 4
Fetch 96 0.01 0.03 4 116 1 82
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 151 0.06 0.09 4 120 28 86
Misses in library cache during parse: 8
Misses in library cache during execute: 9
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.02
latch: shared pool 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
4 user SQL statements in session.
33 internal SQL statements in session.
37 SQL statements in session.
********************************************************************************
Trace file: hydee_ora_16188.trc
Trace file compatibility: 11.1.0.7
Sort options: default
293 sessions in tracefile.
4 user SQL statements in trace file.
33 internal SQL statements in trace file.
37 SQL statements in trace file.
16 unique SQL statements in trace file.
60841 lines in trace file.
53 elapsed seconds in trace file.

先看完这个note
How to Analyze ORA-04021 and ORA-4020 Errors (Doc ID 1486712.1)