Prev: Help! weird Common Table Expressions behavior with SYSTEM TEMP TABLESPACE
Next: DBMS survey
From: Lennart on 13 Nov 2009 06:50
On Nov 13, 12:09 pm, Oleg Kozlovski <oleg...(a)googlemail.com> wrote:
> Hi all!
>
> I have a quite complicated query, which uses 4 or 5 common table
> expressions and produces about 155000 rows. But recently it started to
> consume the disc space at alarming rate. Investigations shows that it
> is due to the SYSTEM TEMPORARY TABLESPACE with the page size 8K.
> Dropping this TS solves the problem, query works fine (having only one
> TS with 4K, created by default). But I need the bigger TS for other
> queries.
>
> What could cause this? It worked fine before, both TS exist for a
> quite long time...

How does the access plan look, are there any large sorts? Is
statistics up to date? Are all indexes that existed when the query
worked ok still there? Lot of questions, but I would start examinig
things like that.

/Lennart
From: Oleg Kozlovski on 13 Nov 2009 08:41
On Nov 13, 1:50 pm, Lennart <erik.lennart.jons...(a)gmail.com> wrote:
> On Nov 13, 12:09 pm, Oleg Kozlovski <oleg...(a)googlemail.com> wrote:
>
> > Hi all!
>
> > I have a quite complicated query, which uses 4 or 5 common table
> > expressions and produces about 155000 rows. But recently it started to
> > consume the disc space at alarming rate. Investigations shows that it
> > is due to the SYSTEM TEMPORARY TABLESPACE with the page size 8K.
> > Dropping this TS solves the problem, query works fine (having only one
> > TS with 4K, created by default). But I need the bigger TS for other
> > queries.
>
> > What could cause this? It worked fine before, both TS exist for a
> > quite long time...
>
> How does the access plan look, are there any large sorts? Is
> statistics up to date? Are all indexes that existed when the query
> worked ok still there? Lot of questions, but I would start examinig
> things like that.
>
> /Lennart

no changes in the DB except the tablespace. Besides, now I'm doing
this:

1. drop ts_8K + execute = OK
2. create ts_8K + execute = Disk full
3. drop ts_8K + execute = OK
4. create ts_8K + execute = Disk full
5. ...
no other changes...
the access plans do differ depending on whether ts_8k exist or not.
 | 
Pages: 1
Prev: Help! weird Common Table Expressions behavior with SYSTEM TEMP TABLESPACE
Next: DBMS survey