Re: locally managed tablespace & dictionary managed tablespace

There have been various strange bugs that have come and gone with versions of Oracle. I can't think of any which are outstanding, but if you check your version of oracle, then download the patchset notes for all newer versions from metalink, you may find details (or at least hints) of things that might be bugs in your version.

One of the non-bug issues with sizing TEMP, though, is that if you run a query that uses a disk-based sort to return data to the end-user, the sort space is not freed until the cursor is closed.

In a Forms-like environment, this can have a big impact on the available space, as it is easy to run up lots of different screens in the same Forms session, and leave lots of cursors open because you've left the screens open with just a few rows fetched.

Furthermore, to reduce network traffic (in all environments) Oracle has tried to batch calls to the server. Amongst other things, this means that when the front-end issues a 'close cursor' call, it may not be sent until the next 'open cursor' call has to be sent. So sometimes you will see an open cursor at the server, holding a sort extent, when you KNOW that the client has closed all cursors.

One thought I've just had, which I haven't checked, is that pl/sql holds cursors after the close, as does the use of session_cached_cursors. I need to see what these two do about cursors which have acquired sort extents - they ought to drop them as all rows will have been fetched, but "ought" doesn't always apply.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Joel Garry wrote in message
<91884734.0301091146.ebf76df_at_posting.google.com>...


>

>More generally, how do you decide how big to make the sort ts?  It

has


>to be a judgement call, but how can you make such a judgement without

>knowing ahead of time how much sort an application with new reports

>will reasonably want?  How do you keep runaway sorts from determining

>the size (especially if you have too much data for a complete test

>database)?  I know which report did this, it was the only thing

>running and got an ora 1652, but running it a second time (with all

>the extents already allocated) completed (maybe Oracle is slow [at

>least 3 hours] about deallocating unused extents from a previous

>sort?).  Jonathan referred to some bugs in

>http://www.jlcomp.demon.co.uk/faq/fulltemp.html does anyone know

which


>bugs those are?  Oh, and thanks Jonathan for explaining the bit about

>why I was wasting an almost-extent at the end of the ts.

>