Memory usage, dbms_session [message #39581] |
Wed, 24 July 2002 09:19 |
Dana Baldwin
Messages: 1 Registered: July 2002
|
Junior Member |
|
|
We have a memory usage issue where we are reaching the 2 Gig memory address limit on Windows 2000. Loading Advanced server is an option, but not an immediate one. There is a package DBMS_SESSION.FREE_UNUSED_USER_MEMORY that frees up session memory for PL/SQL indexed tables that are no longer in use, as the session can hold these open until it ends. Does anyone have experience using this package? Oracle's documentation says to use it "infrequently and judiciously". That's way too vague for me...
|
|
|
Re: Memory usage, dbms_session [message #39589 is a reply to message #39581] |
Wed, 24 July 2002 12:07 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
I'd say that mainly applies when you have large data structures at the Package level rather than local to procedure/functions (as package level variables are persistent for the druartion of the session).
dbms_session.reset_package resets all package state in a session. One use of that is when you have connection pooling (say a web environment). It's automatically used between the end of one transaction and the start of another (by the application server). If you don't need to maintain package state between transactions you could use that too. Connection pooling could maybe help in your case, as could MTS maybe??
Not sure if it's true across versions, but a pl/sql varchar2 variable over size 500 will actually consume 2000 bytes.
Is there anything in your SGA bigger than in needs to be?? (buffer cache or shared pool etc)
|
|
|