OS : Solaris 5.10
DB Version : 11.2.0.4
Application setup brief description:
Below is schema setup on database
common code : contains the application logic (packages,procedures,functions etc) + synonym created for all the main schemas( main schema1,2,3,4 .....140) to use the common code hence schema qualifier is not there anywhere in code.. if any code needs to be executed then before that we have to set the "alter sesssion set current_schema=<main schema1/2/3/4/5...n> " or login with java application user which has the synonym created hence no schema qualifier before any object.
common data : contains common data as name suggests
template schema : contain the objects (tables,indexes)
main schema1: all object created same as tempate schema + required DML access granted to java user
main schema2: all object created same as tempate schema
main schema3: all object created same as tempate schema
main schema4: all object created same as tempate schema
.
.
main schema140: all object created same as tempate schema
main schema java1: is used to login from Tomcat application server to access the respective main schemaN
main schema java2: is used to login from Tomcat application server to access the respective main schemaN
main schema java3: is used to login from Tomcat application server to access the respective main schemaN
main schema java4: is used to login from Tomcat application server to access the respective main schemaN
.
.
main schema java140: is used to login from Tomcat application server to access the respective main schemaN
Issue: If I issue the below statement form all the 140 java user , this create the 140 different cursor as the executing user is different hence the child cursor is getting created ..
select column_name from employee where empid=:bind;
this is causing the shared pool to grow over a period of time and we reach to a level where we need to bounce the database sadly. we kept on increasing the SGA over last one year yet to solve the problem.. the sga advisory advice to increase the SGA ... current SGA_TARGET is 56GB
not sure how to deal with this kind of application setup.
I may have lost in describing the setup ,, please let me know if need more explanation.... expert advice required.
Update: Today while the shared pool needed to grow by few granuals which couldn't get competed as there was no space left in the SGA_TARGET hence either buffer_cache should have been shrunk (was not possible as it was used too) before the shared_pool could grow.. the shared pool grow operation took 20 minutes to complete and in the end it failed.. during this period it blasted ORA -04031 in alertlog and application got halt with pile up of 300+ active session with "shared pool latch wait + cursor pin wait" . didn't allow any query on DB .. before I could take the decision to bounce the DB, the shared_pool grow operation failed and DB worked normal..