Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sreedharan.

Asked: May 15, 2020 - 6:34 pm UTC

Last updated: May 19, 2020 - 1:32 am UTC

Version: 18 C

Viewed 1000+ times

You Asked

Hi,

I Have a package, getting the data from the multiple table once and put it in the one user defined type and then using the data from that type i have using another 4 or 5 types to manipulate the data as per business logic and send the outputs.

Will there be any memory issue if i use many Types in a single Procedure. it is for OLTP application which will have 500 to 1000 records for every manipulation.


and Connor said...

The overhead for a PLSQL type is negligible. What will matter is the data you store in it.

For example, if you had a nested table (type NT is table of NUMBER), then the nested table consumes virtually no space, but of course, if you stick a billion entries in that table, then we are going to consume a lot of memory.

You can monitor your PGA usage via the performance views

select
  st.sid,
  s.name, 
  st.value
from v$statname s, v$sesstat st
where st.STATISTIC# = s.STATISTIC#
and s.name like 'session pga memory%'



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database