Re: Why is UGA size so large?

I don't suppose you could be persuaded to download the FAQ template from

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html and write up your demonstration as a FAQ and email it to me.

It looks like a useful test case to have to hand for each new release of Oracle.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






"Marcel D?rr" <MARCEL.D_at_GMX.CH> wrote in message 
news:646fd09f.0504050728.7b6df655_at_posting.google.com...


> Noons <wizofoz2k_at_yahoo.com.au> wrote in message 

> news:<424e2b9d$0$1886$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au>...

>> Marcel D?rr apparently said,on my timestamp of 1/04/2005 6:43 PM:

>>

>> There you go: remove the last row and extend again.  ;)

>> That should solve your problem.  I do recall this being documented

>> somewhere in one of the old PL/SQL manuals.  Only a total delete frees

>> up the space.  The other way used to be to keep an empty table around,

>> same definition, and copy it to the large PL/SQL table.

>> That shrunk the size, or so we were told.

>>

> So what I need to do is copy the sparse table to a temporary empty

> one, completely delete the sparse table and then copy the tempory

> table back, therefore making my initial table dense.

> Allthough I really don't like to take care of such memory mangement

> stuff, since now my application has to decide when it's worth to copy

> the collection twice to release the memory.

>

> I refined my testcase a bit and found the following statement to be

> true:

>

> The memory that is no longer used by deleted rows can be reused only

> for

> new rows in *the same table of the same session*.

>

> The Docu is a bit misleading on this, in the PL/SQL User's Guide and

> Reference Oracle 9i, on page 5-35 is stated:"Deleting Collection

> Elements (DELETE Method)

> The amount of memory allocated to a nested table can increase or

> decrease

> dynamically. As you delete elements, memory is freed page by page. If

> you

> delete the entire table, all the memory is freed."

> Now they use two times the same word "freed", when in fact there is

> quite a difference in what is happening if you delete the whole

> collection or only a part of it.

>

> Regards,

> Marcel