RE: Size of Global temporary table.
- From: Yong Huang <yong321@xxxxxxxxx>
- To: paresh.patel@xxxxxxxxxxxxxx
- Date: Fri, 3 Jul 2009 08:16:24 -0700 (PDT)
Paresh,
I remember v$tempseg_usage (i.e. v$sort_usage) records the previous SQL. I
found
my old study note that says
"the two SQL columns (sqladdr and sqlhash) in v$sort_usage record the SQL that
*last* ran using temporary segment. If the SQL is currently running consuming
temp space, you have to find it in v$session sql_address and sql_hash_value.
V$session.prev_sql_addr and prev_hash_value are exactly the same as
v$sort_usage.sqladdr and sqlhash, respectively."
So if you want to join, it's prev_sql_id that matches sql_id of v$tempseg_usage.
Yong Huang
-----Original Message-----
You are right Alex. Thanks for your help.
When I use session_address to figure out sql_id from v$session and
v$tempseg_usage views, both show different SQL_IDs.
Here is the query I used,
select a.session_addr,b.saddr,a.sql_id sqlid_tempseg ,b.sql_id
sqlid_session,sid from V$TEMPSEG_USAGE a, v$session b
WHERE a.session_addr= b.saddr;
If I take sql_id which v$session shows and check in v$sql, it shows the
sql which populate this global temporary table, which is correct but
sql_id which v$tempseg_usage has, shows different sql in v$sql.
Does anyone have any idea why both views show different sql_id?
Thanks,
Paresh Patel,
--
//www.freelists.org/webpage/oracle-l
Other related posts:
