Re: Memory Usage in Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 11 Jan 2007 16:54:30 -0800
Message-ID: <1168563269.886144@bubbleator.drizzle.com>

jeffchirco_at_gmail.com wrote:

> Ok I understand you now.  I do have a lot of compiled pl/sql procedures
> that create cursors.  We are moving to have all sql code stored in the
> database verses passing in the sql code.  But we don't page at all.  Is
> there something I need to tune for this or a parameter to change?
> 
> 
> EscVector wrote:

>> jeffchirco_at_gmail.com wrote:
>>> I don't understand the question about suing PL/SQL to process.
>> You can pass in sql or you can have your processes compiled in packages
>> procedures and functions. If your database has compiled pl/sql
>> procedures that create cursors, it can effectively eat up all your
>> memory if you have no programmatic limit on the cursor.
>>
>> psuedo code example:
>> call procedure GetRecords
>> create cursor as select * from 1 terabyte table;
>> Work with cursor
>> End procedure
>>
>> All available os memory will be allocated to the offending process,
>> system will swap, and die. It will die on the cursor create and never
>> get to the work.

Oracle has advisors built into the catalog that can be used to obtain recommendations on sizing. Have someone run the following:

SELECT view_name
FROM dba_views
WHERE view_name LIKE 'V%ADV%';

I would suggest a review of the contents of each.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jan 11 2007 - 18:54:30 CST