Re: Dealing with "orphaned" sessions

On 4 Jun 2001 13:12:55 -0700, bchorng_at_yahoo.com (Bass Chorng) wrote:

>> 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

At the risk of looking like a total fool, how would I check for temp segments? I've poked around with DBA_EXTENTS and DBA_SEGMENTS and don't see anything that identifies temp segments. If it is simply any segment in the TEMP tablespace, a SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'nameofmytempts' returns zero rows.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)