Re: locally managed tablespace & dictionary managed tablespace

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<avlvta$lk$2$8300dec7_at_news.demon.co.uk>...
> 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.

Every time I look at that list I shudder. I mean, they broke exp! How can they break exp??? Then they give some lame excuse about not using exp for backups, as if no one uses it to move production data around!

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

This is very interesting, as the OCI generator I use uses lots of cursors, basically like forms even for reports. The vendor simply says to up open_cursors until you stop running out of them...

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

I'll be looking for that, thanks!

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

Don't have anything set besides open_cursors, and this isn't pl stuff, but I'm interested in what you find.

It may be a coincidence, but I noticed that my largest table was almost exactly the same size as my original sort tempfile, before someone added another one to double the ts size. What I see now is 49.99% usage, and no access to the original file since the last bounce a week ago. That largest table was not the one that was being reported on when the sort blew. Someone on metalink saw the same 49.99% usage on an 8.1.7.4, after doubling the sort tempfile size.

It took two bounces of the db to get the sort ts usage down to near zero, then it went to 49.99% within a couple of days.

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

jg

--
@home is bogus
excellent!