Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
Q u a d r o
Вы точно сомнительную распределёную транзакцию правильно "убили" и в select * from dba_2pc_pending больше нет транзакций?

SQL> select * from dba_2pc_pending;

no rows selected

SQL>
6 май 06, 20:38    [2640820]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
....и если в dba_2pc_pending ничего нету, то что у вас возвращает:

SQL> select a.SID blocked, b.SID blocker
  2   from v$lock a, v$lock b
  3   where a.id1=b.id1
  4    and a.id2=b.id2
  5    and a.REQUEST > 0
  6    and b.block > 0;
6 май 06, 20:38    [2640821]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
Ерунду Вам tkprof
SELECT fa_id
from organizations 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         14          0         166
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.01       0.00          0         14          0         166
6 май 06, 20:40    [2640826]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
... пишет, давайте trc
6 май 06, 20:41    [2640828]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
andrey_anonymous
... пишет, давайте trc

/u01/app/oracle/admin/ft/udump/ft_ora_5213.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	SunOS
Node name:	OSCAR
Release:	5.8
Version:	Generic_117000-05
Machine:	sun4u
Instance name: ft
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 5213, image: oracle@OSCAR (TNS V1-V3)

*** 2006-05-06 12:25:54.757
*** SESSION ID:(42.16790) 2006-05-06 12:25:54.744
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=49 oct=42 lid=49 tim=6333348491148 hv=2973445348 ad='bc742f20'
ALTER Session SET EVENTS '10046 trace name context forever, level 8'
END OF STMT
EXEC #1:c=0,e=180,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=6333348477926
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
*** 2006-05-06 12:27:08.757
WAIT #1: nam='SQL*Net message from client' ela= 72267244 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=49 oct=3 lid=49 tim=6333420761925 hv=4071961467 ad='bb3e4e90'
SELECT fa_id
from organizations 
END OF STMT
PARSE #1:c=10000,e=2763,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=6333420761915
EXEC #1:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=6333420762170
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=146,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=6333420762467
WAIT #1: nam='SQL*Net message from client' ela= 17163 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=104,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333420779917
WAIT #1: nam='SQL*Net message from client' ela= 72146 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=118,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333420852296
WAIT #1: nam='SQL*Net message from client' ela= 62118 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=69,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333420914640
WAIT #1: nam='SQL*Net message from client' ela= 72080 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=82,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333420986933
WAIT #1: nam='SQL*Net message from client' ela= 64536 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=92,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421051680
WAIT #1: nam='SQL*Net message from client' ela= 65924 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=68,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421117787
WAIT #1: nam='SQL*Net message from client' ela= 75973 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=74,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421193955
WAIT #1: nam='SQL*Net message from client' ela= 66506 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=92,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421260693
WAIT #1: nam='SQL*Net message from client' ela= 67461 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=67,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421328331
WAIT #1: nam='SQL*Net message from client' ela= 74712 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=112,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421403347
WAIT #1: nam='SQL*Net message from client' ela= 66017 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=153,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6333421469745
WAIT #1: nam='SQL*Net message from client' ela= 68898 p1=1413697536 p2=1 p3=0
WAIT #1: nam='enqueue' ela= 1000158 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001793 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007692 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005666 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007642 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007688 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001785 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007590 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007665 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007716 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007705 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007695 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005744 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007613 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007670 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007676 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007578 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007654 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007667 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007674 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007615 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007657 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007752 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001824 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007584 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007639 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007680 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007673 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007617 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007647 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007531 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007684 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007772 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007572 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007570 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007726 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007697 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007679 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007696 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007625 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007672 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007710 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007739 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007636 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007663 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005664 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007693 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007639 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007685 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007688 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007666 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007716 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007691 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007670 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007714 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007665 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007697 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007704 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007674 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007713 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007422 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007668 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007689 p1=1415053316 p2=1114150 p3=1588
6 май 06, 20:46    [2640834]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
Q u a d r o
....и если в dba_2pc_pending ничего нету, то что у вас возвращает:

SQL> select a.SID blocked, b.SID blocker
  2   from v$lock a, v$lock b
  3   where a.id1=b.id1
  4    and a.id2=b.id2
  5    and a.REQUEST > 0
  6    and b.block > 0;



Wrote file afiedt.buf

  1  select a.SID blocked, b.SID blocker
  2       from v$lock a, v$lock b
  3       where a.id1=b.id1
  4        and a.id2=b.id2
  5        and a.REQUEST > 0
  6*       and b.block > 0
SQL> /

no rows selected

SQL> 
6 май 06, 20:47    [2640837]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10046
Well:

WAIT #1: nam='enqueue' ela= 1000158 p1=1415053316 p2=1114150 p3=1588

Decimal 1415053316 == Hex 54580004

ASCII(54) = 'T'
ASCII(58) = 'X'

So lock type is TX. Mode 4 (share). So run something like:

  SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
         id1, id2, lmode, request, type
    FROM V$LOCK
   WHERE (id1, id2, type) IN
             (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
   ORDER BY id1, request;

And look for locks held on ID1=1114150 (p2 from trace) and ID2=1588 (p3 from trace).

SY.
6 май 06, 21:08    [2640858]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
  SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
         id1, id2, lmode, request, type
    FROM V$LOCK
   WHERE (id1, id2, type) IN
             (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
   ORDER BY id1, request;

SQL> /

SESS                                                    ID1        ID2
------------------------------------------------ ---------- ----------
     LMODE    REQUEST TY
---------- ---------- --
Waiter: 12                                          1114150       1588
         0          4 TX

Waiter: 15                                          1114150       1588
         0          4 TX

Waiter: 19                                          1114150       1588
         0          4 TX

Waiter: 22                                          1114150       1588
         0          4 TX

Waiter: 29                                          1114150       1588
         0          4 TX

Waiter: 31                                          1114150       1588
         0          4 TX

Waiter: 38                                          1114150       1588
         0          4 TX

Waiter: 41                                          1114150       1588
         0          4 TX

Waiter: 45                                          1114150       1588
         0          4 TX

Waiter: 50                                          1114150       1588
         0          4 TX

Waiter: 52                                          1114150       1588
         0          4 TX

Waiter: 64                                          1114150       1588
         0          4 TX

Waiter: 67                                          1114150       1588
         0          4 TX

Waiter: 81                                          1114150       1588
         0          4 TX

Waiter: 83                                          1114150       1588
         0          4 TX

Waiter: 84                                          1114150       1588
         0          4 TX

Waiter: 92                                          1114150       1588
         0          4 TX

Waiter: 94                                          1114150       1588
         0          4 TX

Waiter: 96                                          1114150       1588
         0          4 TX

Waiter: 107                                         1114150       1588
         0          4 TX

Waiter: 110                                         1114150       1588
         0          4 TX

Waiter: 115                                         1114150       1588
         0          4 TX

Waiter: 116                                         1114150       1588
         0          4 TX

Waiter: 118                                         1114150       1588
         0          4 TX

Waiter: 119                                         1114150       1588
         0          4 TX

Waiter: 124                                         1114150       1588
         0          4 TX

Waiter: 130                                         1114150       1588
         0          4 TX

Waiter: 141                                         1114150       1588
         0          4 TX

Waiter: 142                                         1114150       1588
         0          4 TX

Waiter: 155                                         1114150       1588
         0          4 TX


30 rows selected.

SQL> 
6 май 06, 21:14    [2640863]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10046
Funny, no holders but plenty of waiters. Sorry, I have to go now. I'll be back in a few hours.

SY.
6 май 06, 21:19    [2640873]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
В дополнение к посту SY:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#18211
TX enqueue
These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4.

The solution is to increase the number of ITLs available, either by changing the INITTRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values).

Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each 'entry' in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.
Waits for TX in Mode 4 can also occur waiting for a PREPARED transaction.

See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about referential integrity and locking data explicitly

6 май 06, 21:39    [2640897]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
kapelan
andrey_anonymous
... пишет, давайте trc


гляньте-ка в trc еще разок - он еще не добрался до прошлого состояния (т.е. последний фетч и непонятно что потом на 12 часов) ?
Если случилось, то покажите хвостик trc
6 май 06, 21:43    [2640903]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722

гляньте-ка в trc еще разок - он еще не добрался до прошлого состояния (т.е. последний фетч и непонятно что потом на 12 часов) ?
Если случилось, то покажите хвостик trc

WAIT #1: nam='enqueue' ela= 3007689 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007587 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005729 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007602 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007716 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007593 p1=1415053316 p2=1114150 p3=1588
FETCH #1:c=0,e=521352138,p=0,cr=2,cu=10,mis=0,r=0,dep=0,og=4,tim=6333942890935
STAT #1 id=1 cnt=166 pid=0 pos=1 obj=27539 op='TABLE ACCESS FULL OBJ#(27539) (cr=14 r=0 w=0 time=495 us)'
6 май 06, 23:08    [2641057]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10046
kapelan
SQL> select * from dba_2pc_pending;

no rows selected


Anything in DBA_2PC_NEIGHBORS?

SY.
6 май 06, 23:36    [2641106]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
SY
kapelan
SQL> select * from dba_2pc_pending;

no rows selected


Anything in DBA_2PC_NEIGHBORS?

SY.


SQL> select * from DBA_2PC_NEIGHBORS;

no rows selected

SQL> 
6 май 06, 23:42    [2641113]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10046
And what about:

SELECT * 
FROM v$sysstat 
WHERE class=4;

SY.
6 май 06, 23:51    [2641121]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
SY
And what about:

SELECT * 
FROM v$sysstat 
WHERE class=4;

SY.
SQL> SELECT * 
  2  FROM v$sysstat 
  3  WHERE class=4;

STATISTIC# NAME
---------- -----------------------------------------
     CLASS      VALUE
---------- ----------
        22 enqueue timeouts
         4     140236

        23 enqueue waits
         4     172131

        24 enqueue deadlocks
         4          0


STATISTIC# NAME
---------- -----------------------------------------
     CLASS      VALUE
---------- ----------
        25 enqueue requests
         4   14921834

        26 enqueue conversions
         4    4543904

        27 enqueue releases
         4   14781145


6 rows selected.

SQL> 
6 май 06, 23:58    [2641127]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
у ждущей сесии:

v$session.row_wait_file#
v$session.row_wait_block#
v$session.row_row#

дайте дамп этого блока

alter system dump datafile <row_wait_file#> block min <row_wait_block#> block max <row_wait_block#>

будет интересно взглянуть
7 май 06, 06:34    [2641337]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
kapelan
WAIT #1: nam='enqueue' ela= 3007593 p1=1415053316 p2=1114150 p3=1588
FETCH #1:c=0,e=521352138,p=0,cr=2,cu=10,mis=0,r=0,dep=0,og=4,tim=6333942890935
STAT #1 id=1 cnt=166 pid=0 pos=1 obj=27539 op='TABLE ACCESS FULL OBJ#(27539) (cr=14 r=0 w=0 time=495 us)'

Что-то мне совсем не нравится отсутствие записей после STAT #1...
Кто-нибудь помнит столь молчаливые wait event кроме SQL*Net message from client и RPCшного аналога?
7 май 06, 11:55    [2641477]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
Красавчик Гарри
Guest
Я бы на месте автора включил как минимум event 10200 и 10228.
7 май 06, 13:09    [2641594]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
Q u a d r o
у ждущей сесии:

v$session.row_wait_file#
v$session.row_wait_block#
v$session.row_row#

дайте дамп этого блока

alter system dump datafile <row_wait_file#> block min <row_wait_block#> block max <row_wait_block#>

будет интересно взглянуть
SQL> select ROW_WAIT_FILE# ,
  2     ROW_WAIT_BLOCK#  ,
  3    ROW_WAIT_ROW#  
  4  from v$session 
  5  where sid =144;

ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------- --------------- -------------
             0               0             0

SQL> 
7 май 06, 17:03    [2641957]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
Красавчик Гарри
Я бы на месте автора включил как минимум event 10200 и 10228.

включил

/u01/app/oracle/admin/ft/udump/ft_ora_28417.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	SunOS
Node name:	OSCAR
Release:	5.8
Version:	Generic_117000-05
Machine:	sun4u
Instance name: ft
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 28417, image: oracle@OSCAR (TNS V1-V3)

*** 2006-05-07 09:26:01.008
*** SESSION ID:(144.20464) 2006-05-07 09:26:00.997
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e6
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 18003e6
Consistent read finished for block 6 : 18003e6
Consistent read started for block 6 : 018003e7
  env: (scn: 0x0000.07122353  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  380012b80  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0

тока зачем?
7 май 06, 17:31    [2641980]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
Курносый Джозеф
Guest
kapelan
тока зачем?

Да чтоб понять, какой хренью занимался этот серверный процесс до и после ожидания на блокировке.
7 май 06, 18:09    [2642012]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
Не надеясь на получение какой-то ясной картины:
alter system flush shared_pool;
после этого включить трассировку, 10046/8 и запустить запрос, который select * ... и select fa_id ...,
дабы во-первых увидеть сопутствующие рекурсивные запросы, во-вторых, может какая разника бросится в глаза и наведет на мысли...
7 май 06, 23:11    [2642400]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
Jonathan Lewis
We need more details about the TX enqueue - it's a transaction enqueue, not a table enqueue by the way, and although they most commonly appear because you have changed data, assuming that this is the case may take you looking in the wrong direction.

One reason why a session executing a select state might attempt to acquire a TX enqueue is if the table in question is subject to distributed transactions, hence two-phase commit.

Between the prepare and commit of a two phase commit, the state of a data block should be treated as indeterminate, so any query that starts in that time-window should not be allowed to see the old version of the block in case the distributed transaction has successfully committed, nor should it be allowed to see the new version of the block in case the distributed transaction has rolled back - so it has to acquire a TX mode 4 (share) lock on the TX slot of the distributed transaction and wait for the two-phase commit to complete.

NB Prior to about 9.2.0.6, this was the behaviour exhibited by Oracle in all cases of distributed transactions. In 9.2.0.6, Oracle rewrote the code, and a query against a block modified by an incoming XA transaction gets to see the earlier version of the block, although an Oracle-only incoming transaction still results in the enqueue appearing.
7 май 06, 23:33    [2642421]     Ответить | Цитировать Сообщить модератору
 Re: lock  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
YAP
Не надеясь на получение какой-то ясной картины:
alter system flush shared_pool;
после этого включить трассировку, 10046/8 и запустить запрос, который select * ... и select fa_id ...,
дабы во-первых увидеть сопутствующие рекурсивные запросы, во-вторых, может какая разника бросится в глаза и наведет на мысли...


SQL> alter system flush shared_pool;

System altered.

SQL>

/u01/app/oracle/admin/ft/udump/ft_ora_27161.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	SunOS
Node name:	IVAN
Release:	5.8
Version:	Generic_117000-05
Machine:	sun4u
Instance name: ft
Redo thread mounted by this instance: 1
Oracle process number: 72
Unix process pid: 27161, image: oracle@IVAN (TNS V1-V3)

*** 2006-05-07 15:28:49.847
*** SESSION ID:(42.28260) 2006-05-07 15:28:49.838
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=49 oct=42 lid=49 tim=6428444824458 hv=2973445348 ad='b8343908'
ALTER Session SET EVENTS '10046 trace name context forever, level 8'
END OF STMT
EXEC #1:c=10000,e=2047,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=6428444815899
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
*** 2006-05-07 15:29:06.926
WAIT #1: nam='SQL*Net message from client' ela= 16679296 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=49 oct=3 lid=49 tim=6428461507526 hv=2489000631 ad='b58047b8'
select fa_id
from organizations
END OF STMT
PARSE #1:c=0,e=2868,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=6428461507513
EXEC #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=6428461507776
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=217,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=6428461508144
WAIT #1: nam='SQL*Net message from client' ela= 17125 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=110,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461525559
WAIT #1: nam='SQL*Net message from client' ela= 56754 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=81,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461582529
WAIT #1: nam='SQL*Net message from client' ela= 59285 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=96,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461642075
WAIT #1: nam='SQL*Net message from client' ela= 62648 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=79,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461704931
WAIT #1: nam='SQL*Net message from client' ela= 57040 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=87,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461762198
WAIT #1: nam='SQL*Net message from client' ela= 55946 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=106,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461818390
WAIT #1: nam='SQL*Net message from client' ela= 76895 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=91,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461895523
WAIT #1: nam='SQL*Net message from client' ela= 57578 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=79,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428461953361
WAIT #1: nam='SQL*Net message from client' ela= 58330 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=95,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428462011912
WAIT #1: nam='SQL*Net message from client' ela= 64322 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=100,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428462076495
WAIT #1: nam='SQL*Net message from client' ela= 58126 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=71,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=6428462134869
WAIT #1: nam='SQL*Net message from client' ela= 58320 p1=1413697536 p2=1 p3=0
WAIT #1: nam='enqueue' ela= 1009655 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001749 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007613 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005761 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007614 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007686 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001748 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007672 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007644 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007707 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007619 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007728 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 1005687 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007545 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007685 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007689 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007661 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007675 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007655 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007680 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007630 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007726 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007664 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 2001782 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007681 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007671 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007682 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007713 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007680 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007654 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007610 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007676 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007590 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007701 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007637 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007684 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007651 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007735 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007691 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007729 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007685 p1=1415053316 p2=1114150 p3=1588
WAIT #1: nam='enqueue' ela= 3007679 p1=1415053316 p2=1114150 p3=1588
7 май 06, 23:34    [2642422]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Oracle Ответить