Oracle-L: query taking a long time to run via sqlnet

From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Wed, 05 Nov 2003 06:19:35 -0800
Message-ID: <F001.005D5A9E.20031105061935@fatcity.com>

I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.

DECLARE

        p_xml_in LONG(32760);
        p_xml_out LONG(32760);
BEGIN
        p_xml_in := '<ITEM><USER_ID>D3846</USER_ID><GUTO_ID></GUTO_ID><ACTION>GI

VEUP</ACTION><ACTION_DATE>28/10/2003</ACTION_DATE><MEDIA_TYPE>13</MEDIA_TYPE><AG ENT_ID>A001</AGENT_ID><VERIFIER_ID></VERIFIER_ID><CAMPAIGN_IMPACT></CAMPAIGN_IMP ACT><CAMPAIGN_CODE></CAMPAIGN_CODE><SALES_CHANNEL>1</SALES_CHANNEL><NEW_OCCUPANT
><ICE_CUSTOMER_ID></ICE_CUSTOMER_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><TAKEON_DATE
>29/10/2003</TAKEON_DATE><SALUTATION></SALUTATION><FIRST_NAME></FIRST_NAME><INIT
IALS></INITIALS><LAST_NAME></LAST_NAME><FULL_NAME>New Customer</FULL_NAME></NEW_ OCCUPANT><CUSTOMER><ICE_CUSTOMER_ID>383700</ICE_CUSTOMER_ID><PREMISE_ID>4115853< /PREMISE_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><NO_NEW_CONTACT_REASON>99</NO_NEW_CO NTACT_REASON><RESP_NEW_ADDR>N</RESP_NEW_ADDR><CALLBACK_DATE></CALLBACK_DATE><CAL LBACK_COMMENTS></CALLBACK_COMMENTS><ACCOUNT><PREMISE_ID>4115853</PREMISE_ID><FIN AL_BILL_TO_OFFICE>Y</FINAL_BILL_TO_OFFICE><CORRECT_ADDRESS>Y</CORRECT_ADDRESS><N AME>W Smith</NAME><CUST_NOT_FLAG>Y</CUST_NOT_FLAG><BUSINESS_FLAG>N</BUSINESS_FLA G><REFERENCE>952117400012</REFERENCE><PRODUCT><PRODUCT_CODE>E</PRODUCT_CODE><BUS INESS_FLAG>N</BUSINESS_FLAG><REFERENCE>952117400012</REFERENCE><KEY_TYPE>1</KEY_

TYPE><SERVICE_REFERENCE>576931001022</SERVICE_REFERENCE><REASON></REASON><GET_RE
ADING>N</GET_READING><CONTINUE_SUPPLY>N</CONTINUE_SUPPLY><ESTIMATE_BILL>N</ESTIM
ATE_BILL><RAISE_FINAL_INVOICE>Y</RAISE_FINAL_INVOICE><PAYMENT_CARD_REQD>N</PAYME

NT_CARD_REQD><PAYMENT_VALUE></PAYMENT_VALUE><SYSTEM_CODE>J</SYSTEM_CODE></PRODUC T></ACCOUNT></CUSTOMER></ITEM>';

pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out); EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1 , 255));
RAISE;
END;
/

The same query when I run it via a client/server connection takes fore ever to run, it's been almost 2hrs and it's still running....

I wonder what I could do to get to the bottom of this query taking a very long time to execute using a client/server connection.

No errors in the alert.log so far.

Oracle 9.2.0.4
HP-UX11
Connection is TCP/IP.

TIA Zabair

Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Zabair=20Ahmed?=
  INET: roon987_at_yahoo.co.uk

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).

Received on Wed Nov 05 2003 - 08:19:35 CST