Troubles with parses

From: Veiga <roberto.veiga_at_gmail.com>
Date: Mon, 31 Jan 2005 21:44:54 -0300
Message-ID: <5509fa24050131164432fdf17f@mail.gmail.com>

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

Received on Mon Jan 31 2005 - 19:47:29 CST