Re: ORA-01575 messages/slow response time

David J DeWolfe wrote:
> About 2 weeks ago my production 7.1.4 database running on a Dec Alpha 8400
> with Digital Unix 3.2d started logging ora-01575 messages (timeout waiting for
> space management resource) to the alert log. We experienced this problem a
> while back after changing % increase on our application tablespaces to get the
> free space to coalesce. The oracle documnets that deal with ora-01575 indicate
> that these messages can occur because SMON may be coalescing free space. The
> problem was resolved by changing % increase back to zero. That was a few
> months ago. We're now experiencing 01575's again. I would very much like to
> hear any advise anyone may have in regards to determining what is happening to
> cause these messages. I do have the documents "solving ORA-01575", and
> "ORA-01575 under heavy database workload" which have yet to provide a solution
> for me. Any thoughts anyone?

Hi David, (please ignore if you already received this as my newsreader died

            while sending my first reply)

Check that no TS's still have pct_increase of zero.

Coalescing still occurs when Oracle cannot find a chunk equal to or bigger than
what it requires when extending tables etc.

Check that all users have a temporary tablespace of TEMP (or whatever you use).

Make sure that the TEMP TS has same size INITIAL and NEXT, and that MINEXTENTS=1
and MAXEXTENTS= whatever maximum your block size allows, and pct_increase of zero.
Make sure your INITIAL and NEXT are multiples of your SORT size (plus one block)
and should befairly large. IMPORTANT: Pre-Fragment your TEMP TS into INITIAL
chunk sizes. This reduces recursive SQL and storage management locks.

The other thing that can cause a side-effect of 1575 (which happens to be my
telephone extension :) is that you may have too few cursors per process. If
Oracle is coalescing free blocks (for whatever reason) and there are more blocks
than you have cursors it sometimes spits the dummy with 1575. Try setting
OPEN_CURSORS higher; I set it around the max number of extents value for the databse block size and it works fine. Not too high or you waste resources.

Check your TS's don't have lots of small fragments and find out why and fix it.

Check your DB objects and make sure none of yours or SYSTEM's (don't touch SYS)
are using non-zero pct_increase and reset their NEXT appropriately and set
pct_increase to zero.

Good Luck.

Bruce...

--
  -----------------
   T T T T T T T T        Bruce Pihlamae
   I I I I I I I I        bpihlama_at_nla.gov.au
   I I I I I I I I        National Library of Australia
   T T T T T T T T        Phone: +616 262-1575
  -----------------       Fax:   +616 273-2116
 ===================
 "If you swallow a live frog first thing in the morning;
  nothing worse will happen to either of you that day."