Re: Dealing with "orphaned" sessions

> While all of the "orphaned" sessions look suspicious, from what he
> tells me, there really wouldn't be that much activity to roll back on
> a SHUTDOWN IMMEDIATE. The app is an ordering system running on a kiosk
> -- a cabinet containing a completely enclosed PC, with the only user
> access being the face of a touch-screen monitor. When they begin a
> session, a row is updated to "flag" that the application is in use. As
> they step their way through several pages of info, the only database
> activity would be a series of SELECTs. Only when they confirm a
> completed order does the app issue an INSERT command, after which it
> immediately does a disconnect and recycles back to its "Welcome"
> screen.
>
> Is there anything else I should/could be looking at that could cause
> inordinately long shutdowns?

Another reason that Oracle hangs at shutdown is de-allocating temp segments.
It actually takes longer than rollback in most sites.

Depending how large you set your extent, the speed that SMON de-allocates
temp segments can be very different. SMON only de-allocates 5 extents at a time, regardless of the size. If your extent size is very small, then you
would have lots of extents built up. ( in 8, temp segments is never de-allocated
until shutdown ).

Check you temp segment size and # of extents before shutdown. If the # of extents is very high, size the extent bigger will reduce the de-allocation time.

In addition to rollback and temp segs de-allocation, there are mysterious hangs
at shutdown. But that should not happen every single time. I would suggest building in looping count in your shutdown script. Issue a shutdown immediate,
wait for 20 minutes. If it still in shutdown mode, just kill -9 the background
processes.

Depending which version you use. In 8.0.5 there was a bug regarding multiple
db_writers with large buffer cache. It causes Oracle to hang in shutdown
immediate for several hours. The work around is shutdown abort, start DB
with a very small buffer cache and one single db_writer. Then shut it down
with immediate and re-start it with your original setting of SGA and multiple
db_writers.

-Bass Chorng Received on Mon Jun 04 2001 - 15:12:55 CDT