Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PGA and UGA and collection memory consumption

688781Dec 8 2009 — edited Dec 11 2009
I am being asked if using collections in my PL/SQL package could consume too much memory. So I digged up a PGA and UGA sql, and displaid the memory before the call to the package and after. Results, below, indicate that the package consume approx 584Kb, but the collection used in the package contains only approx 1Kb, so the question is can we determine what else is in memory ?

Start SQL*PLUS: 299Kb (PGA plus UGA)
Run the package once: 883Kb (PGA plus UGA)
Run the package several times: 883Kb (PGA plus UGA)

Collections used during sesion connection:

G_UserCpiCodeTable: 3 rows
G_ChartTypeCodeTable: 18 rows
G_ChartTypeCodeDisplTable: 18 rows
G_ChTypCodeDisplTable: 18 rows
G_ExcludedChartTypeCodeTable: 2 rows
G_CpiTable: 7 rows

Total: 66 rows. One colum per collection containing NUMBER, or VARCHAR2(30)

Collection used in the main function of the package:

V_ChartNumberTable: 3 rows
V_CpiCodeTable: 3 rows
V_ChTypCodeTable 3 rows
V_ChTypCodeDisplTable 3 rows
V_ChTypDescTable 3 rows
V_ChTypCodeNumericTable 3 rows
V_ChTypIndTempTable 3 rows
V_ChartNumberWithTypeTable 3 rows

Total: 24 rows. One column per collection containing NUMBER, or VARCHAR2(30)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2010
Added on Dec 8 2009
1 comment
873 views