RE: SQL*net message from client severly impact the Parse call of an insert statement

John,

   These are just a couple of ideas coming to me (I haven't checked the attachments, answering to this through a web interface). First of all, having a _whole_ process much slowed by parsing proves, if nothing else, that you are doing too much of it. If it happened very few times you wouldn't even have noticed it. I am far from being a Java expert but there is the equivalent of bind variables with JDBC (forgot how it's called), your developers should use them. There is also the quick and dirty fix of forcing cursor sharing for the session, but don't mention it to them too quickly. Second, I am surprised by the kernel waiting for some client information in the parse phase. What does it need from the client side to parse? It has everything; unless, perhaps, it's the CBO requiring information about the session's own environment? But this resides on the server host too. The only explanation which makes sense to me is that parsing occurs as a separate phase, before execution, and that the wait is between parsing and execution. In C the 'parse' call can be either immediate (requiring one round-trip on its own) or delayed (parsing occurring when execution is requested). You probably are in the first case, and should ask your developers to do whatever is needed to be in the second one.

HTH S Faroult

>----- ------- Original Message ------- -----
>From: "Hatzistavrou John"
><John.Hatzistavrou.sema_at_mail.tellas.gr>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Tue, 30 Dec 2003 23:34:25
>
>Dear All,
>
>
>
>I am faced with the following situation.
>
>Oracle 8.1.7.4. 64 bit , Solaris 8
>
>
>
>There is a loader java process that when is
>executed against a test
>database(dwdsa)the response time is as expected to
>be. However when it
>is executed against the production instance (dwods)
>it is 2,5 to 3 times
>slower.
>
>I have traced the session on both occasions and
>reading the book of
>Carry Milsap I have spotted that the WAIT for
>SQL*Net message from
>client is very high for the PARSE call of an INSERT
>statement, whereas
>for the test instance there is no delay.
>
>I cannot however explain what might be the cause of
>this. Can somebody
>sched some light into this problem.
>
>Attached please find the SQL 10046 trace with level
>8
>
>
>
>a) Production trace (parser_dwods.zip)
>
>b) Test trace parser_dwdsa.zip
>
>
>
>
>
>Kind Regards,
>
>
>
>
>
>Hatzistavrou Yannis
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).