Re: SQLs with temp segments

alek wrote:
> SQL> select sql_text from v$sqlarea s, v$tempseg_usage t where
> 2 s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;
>
> SQL_TEXT
> --------------------------------------------------------------------------------
> begin :id := sys.dbms_transaction.local_transaction_id; end;
>
> As far as I could understand from your reply the cause might be the
> existence of a temporary table used by some sessions. However, it's
> quite unclear to me how specifying the segtype='<some relevant tempseg
> type>' could help. At the time the
> "select sql_text from v$sqlarea s, v$tempseg_usage t where
> s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;" was issued I had
> three different segment types: DATA, LOB_DATA, INDEX and because each
> row form v$tempseg_usage has the same SQLHASH and SQLADDR I could
> conclude that a single SQL statement has generated those three
> temporary segments. But, the question is how the "select 'x' from
> dual;" statement can generate those temp segs?

Hi, Alec,

There's no cause-effect relationship between the SQL you see in your query and the fact that there's some entry in v$tempseg_usage, especially when segtype is not 'SORT' or 'HASH'. The SQL related columns of this view are really just for our convenience so we don't have to go from v$session to v$sql(area). Your query simply says for those sessions currenly using a temporary segment, return the latest SQLs those sessions ran (if they're still in shared pool of course).

I did the test on a very quiet database. In your test, is the SQL that calls dbms_transaction the only row returned? There's nothing between your query of v$tempseg_usage and select 'x' from dual? The shown SQL could be some other session's. But your current session should also be in v$tempseg_usage with 'DATA' under segtype.

Yong Huang Received on Tue Jun 13 2006 - 12:35:46 CDT