PGA and UGA and collection memory consumption
688781Dec 8 2009 — edited Dec 11 2009I 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)