Re: SQLs with temp segments

From: <yong321_at_yahoo.com>
Date: 13 Jun 2006 07:44:21 -0700
Message-ID: <1150209861.899263.155670@y43g2000cwc.googlegroups.com>

alek wrote:
> Hi,
>
> I have a 10.2.0.1 oracle database and I want to find out all SQLs which
> currently generate temporary segments using v$tempseg_usage and
> v$sqlarea views. I used the following SQL-SELECT but the result is
> quite surprising to me:
>
> SQL> select sql_text from v$sqlarea s, v$tempseg_usage t where
> s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;
>
> SQL_TEXT
> --------------------------------------------------------------------------------
> select 'x' from dual
> select 'x' from dual
> select 'x' from dual
>
> >From my point of view the above statements are unlikely to use temp
> segments. Further searchings show that the involved session is an
> inactive one.
>
> SQL> select sid, serial#, status from v$session s where
> s.SADDR='6030C500';
>
> SID SERIAL# STATUS
> ---------- ---------- --------
> 29 60334 INACTIVE
>
> The SADDR was taken from the SESSION_ADDR column of the v$tempseg_usage
> view.
> My questions:
> 1. Is it possible such simple queries to generate temp segs?
> 2. Are the SQLADDR and SQLHASH the right fields to join v$tempseg with
> v$sqlarea?

Ever wondered why Oracle encourages use of v$tempseg_usage and stops documenting v$sort_usage? You need to have segtype='<some relevant temp seg type>' in the where clause. Otherwise, you could do something like this:

SQL> create global temporary table gtt (a number);

Table created.

SQL> insert into gtt values (1);

1 row created.

SQL> select 'x' from dual;

'
-
x

SQL> select sql_text from v$sqlarea s, v$tempseg_usage t where s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;

SQL_TEXT

---

select 'x' from dual

Yong Huang Received on Tue Jun 13 2006 - 09:44:21 CDT