Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: query taking a long time to run via sqlnet

Re: query taking a long time to run via sqlnet

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Thu, 06 Nov 2003 06:44:26 -0800
Message-ID: <F001.005D5D56.20031106064426@fatcity.com>


Zabair,  

Have you tried trace?  

Did you check trace route to see how many hops it makes to the database server?

Zabair Ahmed <roon987_at_yahoo.co.uk> wrote: 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

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: M.Godlewski
  INET: mcgodlewski_at_yahoo.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).
Received on Thu Nov 06 2003 - 08:44:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US