how row prefetch impact the memeory usage on oracle.
649748Aug 20 2009 — edited Aug 24 2009Support we have a table whose structure is like (a varchar2(4000),b varchar2(4000),b varchar2(4000),b varchar2(4000)). If we populate the table with row of maximum length, that is: every row will have4*4k=16k data. Then set row prefetch (in sqlplus, arraysize) to 5K, then for each fetch, oracle will send 16K * 5K =80M data to client. My question is: where does these 80M stored in oracle?
I first thought was these 80M will be get from PGA/UGA, but my test show me the UGA and PGA are less than 5M, that size is not enough to store 80M data. Will oracle store the data in socket buffer from OS level once any data is ready? Such as: get 15K data, put it to socket buffer, get another 15K data, put it to socket buffer, so there is no need for oracle to store these 80M data inside oracle? But 80M data is also too large for socket buffer.