Cursors and packages usage of SGA memory:questions

I'm curently using Oracle 7.0.16 (going to 7.1.x real soon). I'm experiencing 2 strange phenomenons. The first one regard the memory use by a package, procedure or function inside the sga. When I tried to pinned one package inside the SGA, I saw that if I do a:alter package xxxx compile, then call a dummy function inside the package in order to force Oracle to load it in the sga and then look at then memory used by this package either by using the dbms_shared_pool.size utility or querying the v$db_cache_object, the amount of memory used by the package was 300K. If I shutdown the database, start it up and then call the same dummy function,the amount of memory used by the package was 53K. Is anyone has an explanation for this ?

The second phenomenon regard the memory used by a cursor when I monitor the SQLAREA with the dba monitor utility. I had a PRO*C programm that open, and close the same 2 cursor over and over again for each record process in a table. The memory usage for those 2 cursors was increasing rapidly up to 10M and then there wasn't any memory left in the sga and I got an ora-4031. Also when the memory requirement was nearing 2M the processing speed was slowing to a crawl. I recompile my program by using the HOLD_CURSOR=YES option. After that the memory used by those 2 cursors was fixed (always the same amount), no more increase in memory usage.

Can anyone offer an explanation for this ? Received on Thu Dec 15 1994 - 07:22:00 CET