Tom

I am having trouble dropping a composite partitioned table due to the number of enqueue_resources (1000) being exceeded. I have read several threads on Metalink that suggest setting enqueue_resources to either number of tables + 40% or number of objects that might be locked + number of sessions + number of datafiles + number of PQ slaves + a 10% contingency. This is just test - the production table will be huge in terms of the number of extents and on a Metalink thread, Jonathan Lewis mentions that to create a local index on a single partition requires one resource per extent for the index and one per extent for the table.

My question therefore - are these enqueue_resources something that we can be fairly free with or does upping the value have implications ? I have almost 1000 table/index partitions and 3000 table/index subpartitions, which suggests to me that I need to increase the value by at least 200-500% just to get this table dropped.

Thanks
Adrian

Tom

I did wonder about the same thing but I am quoting Jonathan Lewis on this one so I tend not to question too much ;)

Here's JL's quote from a posting on a Metalink thread on enqueues and enqueue_resources:-

<quote>
2) Do you really mean enqueues here, or enqueue resources ?
The latter is usually somewhere around 'number of files'
+ number of users + number of PQ slaves + number of objects
which might be locked simultaneously. Bear in mind for the
latter that if you create a local index on a partitioned table of 100 extents, then you need 100 enqueue resources for the table partitions and 100 for the index partitions. You can reduce the requirement (and find some application bugs, and protect the system somewhat) by disabling DML_LOCKs on objects or globally.
<unquote>

Regards
Adrian

ORACLE VERSION 8.0.4
ENQUEUE RESOURCES 600

I was building one index using nologging and parallel degree 8. This process was running almost for 30 mins. Suddenly process died throwing messages parallel server 7 died unexpectedly.

Alert log file was having following entries

Errors in file /u01/app/oracle/admin/tc1/bdump/p007_141140_tc1.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00052: maximum number of ENQUEUE resources (600) exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-00052: maximum number of enqueue resources (600) exceeded

There was another file created by PMON process, pmon_147858_tc1.trc with following entries

Tue Oct 12 18:01:24 2004
*** SESSION ID:(1.1) 2004.10.12.18.01.24.000
kxfpsig: signaling ORA-12805 in coordinator...
ORA-12805: server 7 on inst (1) died

These were some other processes running apart from this Index building. In one of the above answers you mentioned, "Each table that your transaction modifies will require an ENQUEUE (not each ROW you modify -- you only need one enqueue per table)."

What was the problem? Index creation or other processes?

I again started index creation, but with parallel degree 4. This time index creation was successful.

First time when index creation was going on, it created couple of temporary objects in the tablespace. These objects were present even after the process terminated. It took 2-3 hours to clean up these objects. Which process is responsible for this clean up job?

Can you please provide some link about enqueue's and their functioning?

I'm again facing the problem of enqueues, when I'm building a local pratitioned Index. No parallel clause.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00052: maximum number of enqueue resources (600) exceeded

When I executed following query

SELECT sid,COUNT(*) FROM V$ENQUEUE_LOCK GROUP BY sid

I got following output.

2 556
4 1
6 1

Process 2 is using almost all the enqueues. This process is DBW0!!!! There were some processes running in DB, I thought it is due to those processes. But even after those processes finished this count remain same. What could be the problem ?

In the above question I've asked about the enqueue locks. They all are MR locks. I was reading one article on metalink where I found that these locks are always there, each per datafile. My DB has 556 datafiles so this number is correct. My enqueue limit is 600, I think I need to resize this figure.

Please confirm if I'm working in right direction.

Can you please tell me how to calculate this number?

dml_locks=210
sessions=115
enqueue_resource=600 -- explicitly specified in init.ora


SELECT sid,TYPE,COUNT(*) FROM V$ENQUEUE_LOCK GROUP BY sid, TYPE

2,MR,556
4,RT,1
6,TS,1


What do you recommend? comment out enqueue_resource parameter in init.ora or set it to a value using following formula?

database files + ((SESSIONS - 10) * 2) + 55? i.e. 556+ (115-10)*2+55 = 821 or make it 1000 ?


I was trying to build a local partitioned Index with 22 partitions. Is there any way to know how much enqueue a process will require and how many of them are available?

If I set the value of enqueue_resources to a higher value lets say 5000 or 10000. Will this make any negative impact?

Should I comment out dml_locks or leave it with value 210?

I commented out these 2 parameters, but when I restarted the database it failed after mounting, throwing ORA-52 in alert log. I'm using oracle 8.0.4 is it mandatory to specify this parameter in this version or this problem is due to some other parameter which I should have commented.

Hi Tom,
When one of my developers try to run the update statement, he receives the following error:
ORA-00055: maximum number of DML locks exceeded
The value I found in that database is :dml_locks=504.
I changed the value to 700 and restarted the database.when the same developer tries to run the query again today he receives the same error. The Parameter values I found on the database is: enqueue_resources is 800,dml_locks is 700 and sessions is 115. The size of this development database is just 8 GB. How to proceed?
Thanks in advance,
D.Sasi Kumar

Hi Tom,
Thanks for replying immediately. Yes, developer trying to update m X n rows.It searches for the criteria in table 1, if founds it updates all the corresponding records in table 2. This is done for all records in table 1.
Thanks in advance,
D.Sasi Kumar

Hi Tom,
Thanks for so quick response. Actually the first table loops into 22 tables approx through 5 lakhs record.

Thanks,
D.Sasi Kumar

Tom,

I got ora-0052 error on one of my oracle 8.1.7.3 server, and after that instance crashed. In this thread you have said , you need one enqueue for each table been modified by transaction. In my schema i have 100 tables and enqueue parameter is set for 700. Then how come this error has crashed the instance. Here is outcome from alert log and trace file for your reference , please advise
-----------
Sat Jul 15 14:33:48 2006
Errors in file /appl/oracle/SE/admin/i_live4/bdump/ckpt_27636_i_live4.trc:
ORA-00052: maximum number of enqueue resources (700) exceeded
Sat Jul 15 14:33:48 2006
CKPT: terminating instance due to error 52
Instance terminated by CKPT, pid = 27636
Sat Jul 15 14:49:35 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.1.7.3.0.

-------
Dump file /appl/oracle/SE/admin/i_live4/bdump/ckpt_27636_i_live4.trc
Oracle8i Release 8.1.7.3.0 - Production
JServer Release 8.1.7.3.0 - Production
ORACLE_HOME = /appl/oracle/SE/product/817
System name: AIX
Node name: cccbrs_ms01
Release: 3
Version: 4
Machine: 0046B7CA4C00
Instance name: i_live4
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 27636, image: oracle@cccbrs_ms01 (CKPT)

*** 2006-07-15 14:33:48.483
*** SESSION ID:(4.1) 2006-07-15 14:33:48.387
error 52 detected in background process
ORA-00052: maximum number of enqueue resources (700) exceeded
------------------------------------

Earlier i have seen this problem (ora-0052) but that doesn't crashed the instance. what could be the cause of instance crash. Please advise.

Regards
Vivek

If a user process encounters ORA-52 while executing a SQL statement then at most that user SQL is rolled back. But when it impacts an important background process (CKPT in your case) then crashing the instance may be the only valid response in 8i.
.
I forget what views exist in 8i but v$resource_limit may be the easiest way to see if you are getting close to allocating the maximum number of enqueue_resources. Then use v$lock or v$resource to figure out why so many are getting allocated. It may just be that it is set too low in the first place.
.
Number of users * numbers of tables accessed at the same time is probably the largest portion of the enqueue usage but the v$ views above will show other activity as well. You may want to see what the default value is if you do not set the parameter explicitly.

Hi Tom,
When Im trying to create index on a table. Im getting following error. And the session is getting expired. Please help me on this asap.

CREATE INDEX GPM_SUPPORTCONTACTS_PNL_IDX ON GPM_SUPPORTCONTACTS( PANELIST_ID, CO
UNTRY_PANEL_ID )
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

Waiting for your reply.

Thanks & Regards,
Prasanna

Hi Tom,
When I am trying to connect to otacle 10 server from another server in oracle 9i thrugh database link the error is showing

ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 52
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00604: error occurred at recursive SQL level 52
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedst+38 CALLrel _ksedst1+0 0 1
_ksedmp+898 CALLrel _ksedst+0 0
_ksfdmp+14 CALLrel _ksedmp+0 3
_kgefec+144 CALLreg 00000000 D4B9448 3
_ksupop+1902 CALLrel _kgefec+0 D4B9448 0 0 1
_opiodr+2734 CALLrel _ksupop+0 2
_ttcpip+1273 CALLreg 00000000 43 11 EADF6F8 0
_opitsk+1017 CALL??? 00000000
_opiino+1087 CALLrel _opitsk+0 0 0
_opiodr+1099 CALLreg 00000000 3C 4 EADFC90
_opidrv+819 CALLrel _opiodr+0 3C 4 EADFC90 0
_sou2o+45 CALLrel _opidrv+0 3C 4 EADFC90
_opimai_real+112 CALLrel _sou2o+0 EADFC84 3C 4 EADFC90
_opimai+92 CALLrel _opimai_real+0 2 EADFCBC
_OracleThreadStart@ CALLrel _opimai+0
4+708
77E66060 CALLreg 00000000