RE: What causes TEMP segment use

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Sun, 11 Apr 2004 14:17:14 -0400
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73075BD2EA@xchgbrsm1.corp.espn.pvt>

Apologies ... After sending this message, the next article on MetaLink explained that there is an issue with using Lobs in pl/sql. They don't get reused effectively, and hence one must use temporary Lobs.

I am off to modify my code to use temporary lobs instead ...=20 This is embarrassing ... I should have finished reading the whole hit list on MetaLink.
Raj

---

--------=20

Rajendra dot Jamadagni at nospamespn dot com=20 All Views expressed in this email are strictly personal.=20 select standard_disclaimer from company_requirements;=20 QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jamadagni, Rajendra Sent: Sunday, April 11, 2004 1:56 PM
To: oracle-l_at_freelists.org
Subject: What causes TEMP segment use

I have a process as outlined below ...
Loop

1. dequeue a message from a specified queue.
2. message is of type CLOB.
3. in another loop, use dbms_lob.read to read 1024 characters from the lob at a time.
4. using utl_raw and utl_tcp send each chunk across network.
5. loop back to the dbms_lob.read call until done. End loop;

Now, there are no following query shows number of extents used in v$sort_usage growing ... we send messages continuously few a minute.

SELECT s1.username, s1.sid, s1.serial#, s2.contents, s1.sql_address, s2.extents
  FROM v$session s1, v$sort_usage s2
 WHERE s1.saddr =3D s2.session_addr
 order by 6 desc
/

The question is if I am not performing any queries in my code, why should my usage of TEMP be increasing?? Maybe I am missing something here, but can't put my finger on it. This process last week failed with ora-1652, so I am trying to investigate that as well.=20

This is all on a 9204 dual node RAC instance.

Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal.=20
select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art !

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------





----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Sun Apr 11 2004 - 13:13:58 CDT