Home » RDBMS Server » Server Administration » SQL session hangs when quering a table (10.2.0.3)
SQL session hangs when quering a table [message #307659] Wed, 19 March 2008 06:39 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

The problem started when starting up streams and a DBA could not make an export of a table.
I have a problem with this table, when using SQLPLUS the window freezes. And I have to ctrl-c out myself.
This is what I found in the alertlog:

Wed Mar 19 11:02:32 2008
GES: Potential blocker (pid=13480) on resource LB-E3360248-70CC2137;
 enqueue info in file e:\oracle\product\10.2.0\admin\db27\bdump\db271_lmd0_9948.trc and DIAG trace file
Wed Mar 19 11:36:31 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=162
System State dumped to trace file e:\oracle\product\10.2.0\admin\db27\udump\db271_ora_13480.trc

First trace:

Dump file e:\oracle\product\10.2.0\admin\db27\bdump\db271_lmd0_9948.trc
Mon Mar 17 14:19:02 2008
ORACLE V10.2.0.3.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 8 - type 8664, 4 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:18945M/32599M, Ph+PgF:27097M/55669M
Instance name: db271

Redo thread mounted by this instance: 0 <none>

Oracle process number: 18

Windows thread id: 9948, image: ORACLE.EXE (LMD0)


*** SERVICE NAME:() 2008-03-17 14:19:02.625
*** SESSION ID:(58.1) 2008-03-17 14:19:02.625
 syncr inc 42 lvl 1 from 2 rcvd (my inc,lvl: 0, 0) (42/4.0.0)
 syncr inc 42 lvl 1 from 1 rcvd (my inc,lvl: 42, 1) (42/5.0.0)
 s2m domain info received from 2 (42.6) 
* s2m: domain 0 valid according to instance 2 
 s2m domain info received from 1 (42.6) 
 ftd received from node 2 (42/6.0.0) 
 first hvmap received from 1 for domain 0 (42.7) 
* kjxhvmaph: domain 0 valid = 1 according to instance 1 
 last hvmap received from 1, (42.7) 
 first hvmap received from 1 for enqueue mappings (42.7) 
 last hvmap received from 1, (42.7) 
 ptmap received from node 1 (42.7) 
 ftd received from node 1 (42/7.0.0) 

Second trace:

Dump file e:\oracle\product\10.2.0\admin\db27\udump\db271_ora_13480.trc
Wed Mar 19 11:36:31 2008
ORACLE V10.2.0.3.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 8 - type 8664, 4 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:16685M/32599M, Ph+PgF:26602M/55669M
Instance name: db271

Redo thread mounted by this instance: 1

Oracle process number: 162

Windows thread id: 13480, image: ORACLE.EXE (SHAD)


*** 2008-03-19 11:36:31.155
*** SERVICE NAME:(DB27) 2008-03-19 11:36:31.155
*** SESSION ID:(35.2243) 2008-03-19 11:36:31.155
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 000007FF9F0475D0, mode: N, request: S
===================================================
SYSTEM STATE
------------
System global information:
     processes: base 000007FF98D4F8A8, size 220, cleanup 000007FF9F000078
     allocation: free sessions 000007FF98D772D0, free calls 0000000000000000
     control alloc errors: 0 (process), 0 (session), 0 (call)
     PMON latch cleanup depth: 0
     seconds since PMON's last scan for dead processes: 18
     system statistics:


I guess the second trace gives something:

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<

What can I do about it? The table cant be accessed at all.

Regards
H
Re: SQL session hangs when quering a table [message #307662 is a reply to message #307659] Wed, 19 March 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Metalink notes
- 278316.1: Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "
- 436336.1: "dc_table_scns" causes WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK in the alert log file
...
And several bugs reported with this message.

Regards
Michel
Re: SQL session hangs when quering a table [message #307681 is a reply to message #307659] Wed, 19 March 2008 08:17 Go to previous message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thank for a speedy response. We had to restart the DB.

Regards
H
Previous Topic: Database Down Automatically
Next Topic: ORA-01552: cant use system rollback segment for non-system tablespace
Goto Forum:
  


Current Time: Sun Dec 04 12:52:17 CST 2016

Total time taken to generate the page: 0.11219 seconds