Re: temporary space issue
From: ddf <oratune_at_msn.com>
Date: Tue, 8 Feb 2011 10:25:58 -0800 (PST)
Message-ID: <71744283-55e6-4fcf-8a3e-c29ccbe3812b_at_k17g2000pre.googlegroups.com>
On Feb 8, 10:29 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 08 Feb 2011 08:00:20 -0800, ddf wrote:
> > He's using 10.2.0.x so V$TEMPSEG_USAGE is a better view to query:
>
> > select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks from
> > v$tempseg_usage u, v$sql s
> > where s.sql_id = u.sql_id;
>
> This is a better view, because it shows the SQL that allocated the space,
> but I still have to join it with V$SESSION to get the SID and with the
> DBA_TABLESPACES to get the block size. Without block size, I can't deduce
> the usage in bytes. Basically, I don't think that the improvement is
> significant enough to change my script.
>
> --http://mgogala.byethost5.com
Date: Tue, 8 Feb 2011 10:25:58 -0800 (PST)
Message-ID: <71744283-55e6-4fcf-8a3e-c29ccbe3812b_at_k17g2000pre.googlegroups.com>
On Feb 8, 10:29 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 08 Feb 2011 08:00:20 -0800, ddf wrote:
> > He's using 10.2.0.x so V$TEMPSEG_USAGE is a better view to query:
>
> > select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks from
> > v$tempseg_usage u, v$sql s
> > where s.sql_id = u.sql_id;
>
> This is a better view, because it shows the SQL that allocated the space,
> but I still have to join it with V$SESSION to get the SID and with the
> DBA_TABLESPACES to get the block size. Without block size, I can't deduce
> the usage in bytes. Basically, I don't think that the improvement is
> significant enough to change my script.
>
> --http://mgogala.byethost5.com
V$SORT_USAGE reports on sort segment activity where V$TEMPSEG_USAGE reports on both sort and hash activity and either one could be consuming large amounts of temp space. I suppose it's personal preference; I prefer to see both the hash and sort activity in the temporary tablespace.
David Fitzjarrell Received on Tue Feb 08 2011 - 12:25:58 CST