Troubles with parses

I am having big troubles with parses.
The application was developed  using Java technology ( oc4j, jdbc
connection pooling).

The application send a sql statement to the oracle db using a connection pool.
I read an java code that you show how to reuse the cursors and I show
to the java guys,
but they said to me that a static method cannot be used by the fact
that they are using
a connection pool. They need to close the connection from the pool.

I found an information that in JDBC 3.0 there is a "statement pool" concept.
They try to use the JDBC 3.0 but the problem ( many soft parses that
consume too much cpu)
persists.

Is there any way to resolve this problem? Do you have any example code?
There is a lot of "SQL*Net message to/from client" WAITS? Is this correlated
with the many parses problems?
I change the init.ora (session_cached_cursors=100). I think the
problem was minimized.


Here it is an example of trace file and tkprof.



********************************************************************************

SELECT  ITPD_ITM_PDD AS CODIGO_ITEM,                           ITPD_QTD_TAL   
     AS QUANTIDADE_ITEM,                  ITPD_VLR_FCL      AS VALOR_ITEM      
              
FROM
  ITM_PDD, ADM_EMP_SRV                            WHERE  ADMS_SRV_COD      = 
  ITPD_ADMS_SRV_COD             AND  ADMS_FRMS_COD     = ITPD_ADMS_FRMS_COD   
           AND  ADMS_ADM_COD      = ITPD_ADMS_ADM_COD             AND  
  ADMS_INF_BFR      = :1                             AND  ITPD_PDD_MIL      = 
  :2                             AND  ITPD_PDD_NUM      = :3                  
        ORDER BY  ITPD_VLR_FCL                               


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      208      0.53       1.78          0          0          0           0
Execute    208      0.08       1.90          0          0          0           0
Fetch      216      0.11       0.29          0       2081          0         513
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      632      0.72       3.97          0       2081          0         513

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 48  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1256        0.00          0.00
  SQL*Net message from client                  1256        0.35          6.04
  latch free                                      1        0.11          0.11
********************************************************************************

=====================
PARSING IN CURSOR #373 len=607 dep=0 uid=48 oct=3 lid=48 tim=118816735
hv=2311509455 ad='171f262c'
    SELECT  ITPD_ITM_PDD AS CODIGO_ITEM,                          
ITPD_QTD_TAL      AS QUANTIDADE_ITEM,                     ITPD_VLR_FCL    
 AS VALOR_ITEM                  FROM  ITM_PDD, ADM_EMP_SRV            
               WHERE  ADMS_SRV_COD      = ITPD_ADMS_SRV_COD           
 AND  ADMS_FRMS_COD     = ITPD_ADMS_FRMS_COD            AND 
ADMS_ADM_COD      = ITPD_ADMS_ADM_COD             AND  ADMS_INF_BFR   
  = :1                             AND  ITPD_PDD_MIL      = :2        
                    AND  ITPD_PDD_NUM      = :3                       
ORDER BY  ITPD_VLR_FCL
END OF STMT
PARSE #373:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=118816735
XCTEND rlbk=0, rd_only=1
EXEC #373:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=118816735
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
FETCH #373:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=0,dep=0,og=3,tim=118816735
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 2 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #373: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
=====================

Thanks in advance
--
http://www.freelists.org/webpage/oracle-l

Other related posts: