Re: MTS preblem on Solaris/Oracle 7.0.16

In a previous article, tosi_at_bpb.it (Marco.Tosi) says:

>SERVER STATUS CURRENT SESSION
>--------- -------- ---------------
>DEDICATED ACTIVE 1
>DEDICATED INACTIVE 1
>NONE INACTIVE 49
>SHARED ACTIVE 1
>
 

>During normal activity the size of ROWCACHE and LIBRARYCACHE(TABLE/PROCEDURE)
>is continuously growing.

   From what tablle are you basing this assumption? The stats included in your message were cache hit & miss counts, not cache sizes. A usefull breakdown of the SGA contents is available in v$sgastat.

>until our SQL*Plus and SQL*Forms applications return the following error:
>ORA-04031 out of shared memory when trying to allocate 9712 bytes
>
>The size of shared pool buffer (9Mb !!!) seems not to be enough and we
>wouldn't like to increase it (if possible !!!)

   My guess is that your shared pool is being consumed by the private SQL areas of your 50 MTS connections. Remember that this area which is part of the process memory of a dedicated server must go in the SGA when connecting via MTS.

   I would suggest you look at v$open_cursor to see if private SQL areas are being over-used by your Forms apps( i.e. many open cursors). Also look for SQL which could be re-written to improve SQL sharability. Shared SQL means it is no longer such a sin to close a cursor and re-open it later. Chances are it is still sitting in the shared SQL area and the re-open will not be too painful.

• Doug --