Sean Leach wrote:
>
> That is why I would think, but it sometimes will take several minutes to
> do a select count(*) on a table with 0 records. Usually it won't give an
> error, I will kill it after 2-3 minutes
>
> I would patch it up, except it shouldn't be the problem as the system
> worked great for a year before this. We can't think of anything changed
> since this started happening either.
>
> There aren't more connections than before (if there are, it would be like
> 5-10 more), and there is very little swapping (the load on the machine is very
> low as well)
>
> A shutdown immediate does not work when the problem is happening, it
> hangs, only a shutdown abort will do it (we wait for 2-3 minutes for an
> immediate, to make sure all transactions have been rolled back etc.)
>
> On Mon, 26 Nov 2001 17:08:59 -0800, Jim Kennedy wrote:
>
> > In Oracle writers don't block readers so you should be able to do
> > "select count(*) from table2;"
> >
> > What error do you get when the select fails?
> >
> > There are later patches for 8.1.7. It is now up to 8.1.7.3 last I
> > looked. Do you have more connections to the db now than before and you
> > are allocating too much ram on the server and hence swapping? Does a
> > shutdown immediate work?
> > Jim
> >
> > "Sean Leach" <sleachNOSPAM_at_netlojix.comnospam> wrote in message
> > news:20011126.163450.1295166342.813_at_netlojix.comnospam...
> >> Hi,
> >>
> >> Sorry, but here is a very vague trouble description:
> >>
> >> Platform: Sun Solaris 8 (Sparc), latest patch clusters DB: Oracle
> >> 8.1.7.0.0
> >>
> >> For some reason, the database will just hang, no errors in any of the
> >> alert or trace logs, and sometimes, it will correct itself, and other,
> >> we can do a "shutdown abort" to stop the instance, but all of the child
> >> SID processes are still there, and eventually go to a "<defunct>"
> >> state, such that we have to reboot the machine to fix it.
> >>
> >> Sometimes, we can do a SELECT COUNT(*) FROM table1, and it will
> >> succeed, but then a SELECT COUNT(*) FROM table2 will fail. Sounded
> >> like a table lock issue, but not sure...
> >>
> >> Sometimes, we can restart the listener, and that fixes it, other times,
> >> it doesn't.
> >>
> >> The db data is mounted on a NetApp, but we ran for over 200 days with
> >> no problems before, and then this problems just suddenly started
> >> happening. We have disk_async_io set to false, as we had a problem with
> >> asynchronous IO before, and that fixed THAT problem, but now we have
> >> this no error hanging. It sucks as I have no where to start looking.
> >> Any tips?
> >>
> >> Thanks,
> >> Sean
> >
> >
Sounds like you might have a long running transaction (if shutdown
immediate "hangs"). Look at V$SESSION_LONGOPS.
Did you check metalink for how to diagnose a "hung" instance? There is
at least 1 note there about this problem (not version specific).
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Mon Nov 26 2001 - 19:56:53 CST
