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

From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Thu, 06 Nov 2003 07:44:32 -0800
Message-ID: <F001.005D5D6E.20031106074432@fatcity.com>

I have included the following in my sqlnet.ora

trace_file_client = oss
trace_timestamp_client = true
trace_level_client = 16

Am no expert on interpreting the trace file and Oracle are not being very helpful at the moment.

"M.Godlewski" <mcgodlewski_at_yahoo.com> wrote: 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

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 Thu Nov 06 2003 - 09:44:32 CST