| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fwd: "low open file" at solaris
Hi, yong,
As my earlier email already said, the dbwr opened 1023 open files.
Checkpoint on this database seems pretty slow, it took 10 minutes to
finish a checkpoint.
But statspack does not show much such kind of wait events like "file open "etc.
I tried truss -p dbwr-pid and I found some strang things, open file
limit is 1024, and database has 1124 datafiles. So, as oleg says,
maybe oracle dbwr will have to close some datafile, and open another
several datafiles, do some write and close it and switch to some other
datafiles ...
Here is some truss result from the dbwr process;
open("/oracle/SOLD/data15/soldindx01sn.dbf", O_RDWR|O_DSYNC) = 235
fcntl(235, F_DUPFD, 0x00000100) = 439
close(235) = 0
fcntl(439, F_SETFD, 0x00000001) = 0
fcntl(439, F_GETFL, 0x00000000) = 8258
fcntl(439, F_SETLK, 0xFFFFFFFF7FFFE2D8) = 0
fcntl(440, F_SETLK, 0xFFFFFFFF7FFFE1F8) = 0
close(440) = 0
open("/oracle/SOLD/data14/soldindx01ro.dbf", O_RDWR|O_DSYNC) = 235
fcntl(235, F_DUPFD, 0x00000100) = 440
close(235) = 0
fcntl(440, F_SETFD, 0x00000001) = 0
fcntl(440, F_GETFL, 0x00000000) = 8258
fcntl(440, F_SETLK, 0xFFFFFFFF7FFFE2D8) = 0
fcntl(441, F_SETLK, 0xFFFFFFFF7FFFE1F8) = 0
close(441) = 0
open("/oracle/SOLD/data15/soldindx01ua.dbf", O_RDWR|O_DSYNC) = 235
fcntl(235, F_DUPFD, 0x00000100) = 441
close(235) = 0
fcntl(441, F_SETFD, 0x00000001) = 0
fcntl(441, F_GETFL, 0x00000000) = 8258
fcntl(441, F_SETLK, 0xFFFFFFFF7FFFE2D8) = 0
I am not quite familiar with these syscalls, I did a man fcntl and
think it is reach os limit, it can only 1024 datafiles limit and when
it want to write to other remaining datafiles, it has to close
somefile and open some others.This should slow down the database, but
we can't notice it.
SQL> select type,count(*) from v$lock group by type;
TY COUNT(*)
-- ----------
MR 1128 --This is wired, MR lock means dbwr open the file ... RT 1 TM 15 TS 1 TX 3 XR 1
Thanks.
On Fri, 17 Dec 2004 05:45:13 -0800 (PST), Yong Huang <yong321_at_yahoo.com> wrote:
> Very interesting case. This is the first time I know a database that has
> more than 1024 datafiles.
>
> When it comes to checkpoint, does alert.log show any error? Can you count
> the number of files in `pfiles` output? Or `ls /proc/<DBWnpid>/fd | wc
> -l`?
>
> What if you manually alter system checkpoint (probably on a dev DB that
> refreshed from prod, if you want to be cautious)?
>
> Yong
>
> --- zhu chao <zhuchao_at_gmail.com> wrote:
>
> > Hi, yong
> > We do have database with more than 1024 datafiles. This seems wired.
> > DBWR should open all datafiles, but from os point view, they can open
> > open 1024 files, now actually the dbwr reaches this limit. but
> > database still runs OK.
> >
> > hostname$> pfiles 13704|more
> > 13704: ora_dbw0_SOLD
> > Current rlimit: 1024 file descriptors
> > 0: S_IFCHR mode:0666 dev:244,0 ino:405605 uid:0 gid:3 rdev:13,2
> > O_RDONLY|O_LARGEFILE
> > 1: S_IFCHR mode:0666 dev:244,0 ino:405605 uid:0 gid:3 rdev:13,2
> > O_RDONLY|O_LARGEFILE
> > ....
> > 1022: S_IFCHR mode:0640 dev:244,7005 ino:32 uid:32000 gid:101
> > rdev:247,202
> > O_RDWR|O_DSYNC|O_LARGEFILE FD_CLOEXEC
> > 1023: S_IFCHR mode:0644 dev:244,7018 ino:40 uid:32000 gid:101
> > rdev:247,849
> > O_RDWR|O_DSYNC|O_LARGEFILE FD_CLOEXEC
> > SQL> show parameter db_files
> >
> > NAME TYPE VALUE
> > ------------------------------------ -----------
> > ------------------------------
> > db_files integer 2000
> > SQL> select count(*) from v$datafile;
> >
> > COUNT(*)
> > ----------
> > 1128
> >
> > On Thu, 16 Dec 2004 05:46:59 -0800 (PST), Yong Huang <yong321_at_yahoo.com>
> > wrote:
> > > There should not be performance degradation. If one process wants to
> > open
> > > rlim_fd_max files at the same time, the process open() call fails. It
> > > won't slow down anything. Default 1024 is more than enough in most
> > cases.
> > >
> > > Background processes hold all datafiles open. Imagine a database that
> > has
> > > close to 1000 datafiles? Oracle Apps DB may be an example but still
> > far
> > > lower than that (older version has more than 300 datafiles but I don't
> > > remember newer versions).
> > >
> > > Server processes only open datafiles as needed. In addition, in
> > dedicated
> > > configuration, each server process connects to one client (sqlplus
> > e.g.).
> > > I don't remember the files (including sockets etc) opened by a server
> > (or
> > > dispatcher?) process in shared server configuration. Does one process
> > > serve one client or many?
> > >
> > > Yong
> > >
> > > --- zhu chao <zhuchao_at_gmail.com> wrote:
> > >
> > > > Hi, all,
> > > > I believe many people are running oracle 9i on solaris platform.
> > > > Does anyone noticed this message while starting oracle instance at
> > > > solaris:
> > > > "Wed Dec 15 21:00:06 2004
> > > > Oracle instance running on a system with low open file
> > > > descriptor limit. Tune your system to increase this
> > > > limit to avoid severe performance degradation.
> > > > "
> > > > According to metalink, os parameter "open files" (rlim_fd_max at
> > > > solaris?) need to be tuned as formular: 2*db_files +
> > > > controlfile+logfile ..., if my db_files=1000, then I have to set
> > > > rlim_fd_max to near 2400. Without changing this parameter, will
> > there
> > > > really be some performance degradation?
> > > >
> > > > If I reduce the db_files to 100 or so, then no warning any more.
> > > >
> > > > Thanks.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 17 2004 - 09:39:44 CST
 |
 |