Re: Database corrupted?

From: Al Liu <angang.liu_at_saic.com>
Date: Fri, 11 Jan 2002 10:30:33 -0600
Message-ID: <3c3ea2af@cpns1.saic.com>

When I use 10046 I got

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 787 787 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3 0.00 0.00 787 787 4 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS

"Al Liu" <angang.liu_at_saic.com> wrote in message news:3c3e8f2b_at_cpns1.saic.com...
> The results of these two queries are
> select count(*) from uet$;
> COUNT(*)
> ----------
> 2987
> select count (*) from fet$;
> COUNT(*)
> ----------
> 53
>
>
> "Stefan" <sroesch3_at_attbi.com> wrote in message
> news:Hrw%7.24596$762.223414_at_rwcrnsc54...
> > Hi Al,
> >
> > you will never see ora-3113 in the alert.log as it is always a client
> > problem. To check if you have problem with your extents run the
following
> > queries
> >
> > select count(*) from uet$;
> > select count (*) from fet$;
> >
> > How many rows are returned?
> >
> > The other thing to check is set event 10046 with level 8 in one session
> and
> > check for the wait events.
> >
> > Regards
> > Stefan
> >
> >
> > Al Liu wrote:
> >
> > > ORA-03113 was not found in the alert.log, but error1092 was. About ten
> > > minutes after I see the messages (they came up only a few seconds
> apart),
> > > I got my connection back. I'm not aware of NT shutdown in the past
week.
> I
> > > suspect the problem have something to do with extents. Whenever I
query
> > > something about extents the query is very slow (usually takes a few
> > > seconds, now take several minutes).
> > >
> > > "Ron Reidy" <rereidy_at_indra.com> wrote in message
> > > news:3C3CC9DE.D7AA3C73_at_indra.com...
> > >> Al Liu wrote:
> > >> >
> > >> > We have a ORACLE 8.0.5 database on NT4 served as data source for an
> > >> > Intergraph GIS system. The size of database is ~2gb, the drive the
> > > database
> > >> > residing is 4 gb. In past three days, I have had the following
> problems
> > >> >
> > >> > -some times when I try to connect database I got messages
"ORA-03113:
> > >> > end-of-file on communication channel" and " DBW0:terminating
instance
> > > due to
> > >> > error 1092". Then after a while I can connect with either sqlplus
or
> > >> > svrmgr30. However, connection is much slower, taking 20-30 seconds.
> > >> >
> > >> > -queries generally runs slower than before, especially count(*)
> query,
> > > maybe
> > >> > 10 times slower.
> > >> >
> > >> > -When I run snap8.sql usually take less than 2 minutes. It hangs
when
> I
> > > run
> > >> > it at retrieving datafile information. I checked the query, seems
> like
> > >> > dba_extents was the key of slowdown. I queried dba_extents alone
and
> > > noticed
> > >> > that it stopped at four or five segments for a long time before
> moving
> > >> > forward again (total rows retrieved by the query is 2987). Query of
> > >> > user_extents with a login of a user also run very slow.
> > >> >
> > >> > -The GIS system has real performance problem, some task used to
take
> > > less
> > >> > than a minute may take more than a hour now.
> > >> >
> > >> > I checked V$datafile looks like all datafiles are online. I'm not
> sure
> > > all
> > >> > above symptoms were related to the same problem. Please help me to
> > > diagnose.
> > >> >
> > >> > Thanks.
> > >> >
> > >> > Al
> > >> Did you check the alert log when you got ora-03113? How long has it
> > >> been since your system (NT) was rebooted?
> > >> --
> > >> Ron Reidy
> > >> Oracle DBA
> >
>
>
Received on Fri Jan 11 2002 - 10:30:33 CST