Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQLs with temp segments
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
Yong Huang Received on Tue Jun 13 2006 - 09:44:21 CDT