FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 31, '05 at 7:47p
activeJan 31, '05 at 7:47p
posts1
users1
websiteoracle.com

1 user in discussion

Veiga: 1 post

People

Translate

site design / logo © 2022 Grokbase