FW: oracle database performance/availability/outage issue

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

Actually this parameter still exists in 9i and 10g with the default of 4 x TRANSACTIONS. Still maybe a search on the parameter and 8.1 will turn up something of use.  

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

  ---

	From: Powell, Mark D 
	Sent: Wednesday, February 07, 2007 1:34 PM
	To: oracle-l_at_freelists.org
	Subject: RE: oracle database performance/availability/outage

issue                  

        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:49:54 CST