RE: tuning : file number translation table

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Fri, 18 Jul 2003 10:45:46 -0400
Message-Id: <25956.338454@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C34D3B.45B5AFD0
Content-Type: multipart/alternative;

boundary="----_=_NextPart_001_01C34D3B.45B5AFD0"

------_=_NextPart_001_01C34D3B.45B5AFD0
Content-Type: text/plain;

charset="iso-8859-1"

Carol, you have problems with the operating system, not oracle. Increase the parameter NINODE
for your underlying OS. Inodes are being written to and from inode cache, thus causing waits.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:mgogala_at_oxhp.com

-----Original Message-----
From: carol.legros_at_accenture.com [mailto:carol.legros_at_accenture.com] Sent: Friday, July 18, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L Subject: tuning : file number translation table

I'm hoping someone out there has experienced this problem... I can't seem to find many posts
on MetaLink that discuss this.

My environment :

I am running a 500 Gig OLTP database in a Solaris environment. I have some 0+1 disk
available, but mostly RAID5 (array) for the datafiles.

This is not in production yet, but we're doing some load testing, and so far, I've had the
typical contentions with the "undo header" and "undo block" contention, "segment header"
and so on. I've reduced these issues significantly, but now I think I may have a problem
with "hot spots" and I/O.

The one latch that comes up with a high % (contention) is "file number translation table".
Its at %15. All other latch miss percentages are below 0. Seems like the access to the
files is being pounded.

Anyone had contention with this latch before ?

Another thing that make me feel this is possibly I/O related is that the tablespace and datafiles show an uneven amount of activity across all.... possibly because this app naturally does tons of INSERTS and few UPDATES.
Maybe I need to use partitioning to even out the activity.

The top wait stats are related to dispatchers and MTS. I have a lot of dispatchers and shared servers
(all are busy) but I suspect these wait stats are high because file access may now be the issue.
Should I consider fewer dispatchers and shared servers ? This may relieve the
"file number translation table" situation, but then I'm back to where I started before (lower number of
concurrent sessions with a reasonable response time).

Any advice or comments would be appreciated. Thanks in advance, Carol

------_=_NextPart_001_01C34D3B.45B5AFD0
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.00.3502.4856" name=GENERATOR></HEAD>
<BODY>
<DIV>Carol,
you have problems with the operating system, not oracle. Increase the parameter NINODE</DIV>
<DIV>for
your underlying OS. Inodes are being written to and from inode cache, thus causing waits.</DIV>
<DIV> </DIV>
Mladen Gogala Oracle DBA Phone:(203) 459-6855
 Email:mgogala_at_oxhp.com 
<BLOCKQUOTE>

<DIV align=left class=OutlookMessageHeader dir=ltr>-----Original Message----- From: carol.legros_at_accenture.com [mailto:carol.legros_at_accenture.com] Sent: Friday, July 18, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: tuning : file number translation table </DIV> I'm hoping someone out there has experienced this problem... I can't seem to find many posts <FONT face=sans-serif

  size=2>on MetaLink that discuss this.</FONT> <BR><BR><FONT face=sans-serif 
  size=2>My environment :</FONT> <BR><FONT face=sans-serif 
  size=2>-------------------------</FONT> <BR><FONT face=sans-serif size=2>I am

running a 500 Gig OLTP database in a Solaris environment.  I have some 0+1 disk available, but mostly RAID5 (array) for the datafiles. This is not in production yet, but we're doing some load testing, and so far, I've had the typical contentions with the "undo header" and "undo block" contention, "segment header" and so on.  I've reduced these issues significantly, but now I think I may have a problem with "hot spots" and I/O.   The one latch that comes up with a high % (contention) is "file number translation table". Its at %15.  All other latch miss percentages are below 0.  Seems like the access to the files is being pounded. Anyone had contention with this latch before ?   Another thing that make me feel this is possibly I/O related is that the tablespace and datafiles show an uneven amount of activity across all.... possibly because this app naturally does tons of INSERTS and few  UPDATES. Maybe I need to use partitioning to even out the activity. The top wait stats are related to dispatchers and MTS.  I have a lot of dispatchers and shared servers (all are busy) but I suspect these wait stats are high because file access may  now be the issue.   Should I consider fewer dispatchers and shared servers ?  This may relieve the "file number translation table" situation, but then I'm back to where I started before (lower number of concurrent sessions with a reasonable response time). Any advice or comments would be appreciated.  Thanks in advance, Carol     <IMG src="cid:968134414_at_18072003-21b1"></BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C34D3B.45B5AFD0--

------_=_NextPart_000_01C34D3B.45B5AFD0
Content-Type: image/gif;

name="ATT29161.gif"
Content-Disposition: attachment;

filename="ATT29161.gif" Received on Fri Jul 18 2003 - 09:45:46 CDT