Re: temporary space issue
From: ddf <oratune_at_msn.com>
Date: Wed, 9 Feb 2011 05:28:40 -0800 (PST)
Message-ID: <e1412284-d443-4bfb-9f25-706013fa3515_at_y36g2000pra.googlegroups.com>
On Feb 8, 6:58 pm, Mladen Gogala <mgog..._at_no.address.invalid> wrote:
> On Tue, 08 Feb 2011 10:25:58 -0800, ddf wrote:
> > 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
>
> David, V$TEMPSEG_USAGE is a synonym for V$SORT_USAGE
>
> --http://mgogala.byethost5.com- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 9 Feb 2011 05:28:40 -0800 (PST)
Message-ID: <e1412284-d443-4bfb-9f25-706013fa3515_at_y36g2000pra.googlegroups.com>
On Feb 8, 6:58 pm, Mladen Gogala <mgog..._at_no.address.invalid> wrote:
> On Tue, 08 Feb 2011 10:25:58 -0800, ddf wrote:
> > 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
>
> David, V$TEMPSEG_USAGE is a synonym for V$SORT_USAGE
>
> --http://mgogala.byethost5.com- Hide quoted text -
>
> - Show quoted text -
No,; it isn't:
V$TEMPSEG_USAGE (9i and later releases) shows the temporary segment usage, by user, for all tablespaces:
SQL> desc v$tempseg_usage
Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) USER VARCHAR2(30) SESSION_ADDR RAW(8) SESSION_NUM NUMBER SQLADDR RAW(8) SQLHASH NUMBER SQL_ID VARCHAR2(13) TABLESPACE VARCHAR2(31) CONTENTS VARCHAR2(9) SEGTYPE VARCHAR2(9) SEGFILE# NUMBER SEGBLK# NUMBER EXTENTS NUMBER BLOCKS NUMBER SEGRFNO# NUMBER
V$SORT_USAGE (8.1.5, 8.1.6, 8.1.7) shows the sort segment usage, by user, for all tablespaces:
SQL> desc v$sort_usage
Name Null? Type ----------------------------------------- -------- ---------------------------- USER VARCHAR2(30) SESSION_ADDR RAW(8) SESSION_NUM NUMBER SQLADDR RAW(8) SQLHASH NUMBER TABLESPACE VARCHAR2(31) CONTENTS VARCHAR2(9) SEGFILE# NUMBER SEGBLK# NUMBER EXTENTS NUMBER BLOCKS NUMBER SEGRFNO# NUMBER
They are different views.
David Fitzjarrell Received on Wed Feb 09 2011 - 07:28:40 CST