RE: Size of Global temporary table.

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Fri, 3 Jul 2009 21:39:57 -0700 (PDT)
Message-ID: <475067.25131.qm_at_web45009.mail.sp1.yahoo.com>

I believe this is a currently limitation in the code...
�

Bug 7210183
SQL_ID VALUE IS NOT SAME IN V$TEMPSEG_USAGE AND OTHER VIEWS.
�

addresses this issue. Haven't checked this in 11.2 yet
�

Thanks

Fairlie Rego
Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405
�

• On Sat, 4/7/09, Yong Huang <yong321_at_yahoo.com> wrote:

From: Yong Huang <yong321_at_yahoo.com>
Subject: RE: Size of Global temporary table. To: paresh.patel_at_mantis-tgi.com
Cc: oracle-l_at_freelists.org, afatkulin_at_gmail.com Received: Saturday, 4 July, 2009, 1:16 AM

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,

� � �

--
http://www.freelists.org/webpage/oracle-l





      ____________________________________________________________________________________
Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 03 2009 - 23:39:57 CDT