Re: Use of TEMP tablespace other than sorting

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>The temporary tablespace is also used for
>holding data for user copies of global temporary
>tables, temporary lobs, and intermediate portions
>of hash joins.

Got it.

>Look at v$sort_usage whilst the query is running
>to see how much space is being used for what
>type of operation.

OK, I will try that.

>A six-way hash join could burn a very large amount
>of temporary space - but 48 GB does seem rather
>extreme. How big are the relevant data subsets,
>and is Oracle perhaps making a very bad join order
>decision that comes close to producing a Cartesian
>join of two tables with no discards.

As far as I remember, the tables contain millions of rows.

But hey, I am just the database (and Unix) administrator, not the data administrator... I never touched an SQL line from any application. While I think that I am not that bad in doing administration, my capabilities of tuning (or even writing complex) queries is very limited. Anyway, it is never to late to learn, so what. I will involve in this developer task. I am expected to somehow solve the problem, since Oracle complains something about a tablespace, it *must* be related to administration (!). Received on Thu Feb 27 2003 - 17:48:04 CST