RE: oracle database performance/availability/outage issue

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 7 Feb 2007 13:34:07 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A97A297F@usahm208.amer.corp.eds.com>

DML_LOCKS is a database parameter on version 8.1 that you can still set. You might need more that whatever the default is on your system though I seem to remember the number 500 in relation to a limit but my memory fails when ever I actually try to thing of a fact I need at the time.  

                                                                    D S
S M A
                                                                    e e
y o d
NAME                            VALUE                               f s

s d j

------------------------------- ----------------------------------- - -

- - -
DESCRIPTION

---

dml_locks                       100                                 N N

N N N
dml locks - one for each table modified in a transaction

• Mark D Powell -- Phone (313) 592-5148

  ---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex zeng

	Sent: Wednesday, February 07, 2007 11:12 AM
	To: oracle-l_at_freelists.org
	Subject: oracle database performance/availability/outage issue
	
	

	If you have such experience or insight to the issue, please

kindly provide your help.          

        Platform

        DB: 8.1.6

        OS: HP-UX 11.00

        Hardware: PA-RISK, 8 cpu/16G memory          

        Symptom

1. DB operation on data dictionary (such as create table) is hung. All are hung at 'latch free' events. The latch name is 'dml lock allocation'.
2. # of DB processes will climbing up till maximum connections (150); then the database is unavailable to all new connections
3. Before # of DB processes overflow, some DB light operations on user table are not affected, but not all. Huge operations (such as backend job) on user tables are hung too because it might trigger data dictionary operation.

        What actions have been taken, are being taken or need to be taken

1. Disabled large jobs such - in vain
2. Restarted database - in vain. This helps little. After times passes, the number of processes will climb up and database goes down again eventually.
3. Upper processes limit to 250 in database - GDBA is helping to make the parameter change right now and the database will restart soon. But I don't think this can resolve the issue. It only delays the outage.
4. Logged TAR to metalink - 8.1.6 is out of support scope now. The possibility to get support form Oracle Corp is low.
5. Further investigation needs to be taken upon the "data dictionary" hung/latch free event. This is where we need your suggestion and help.

        Thanks         

        Alex         

	-- 
	--The mount is there. 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 07 2007 - 12:34:07 CST