Re: query taking a long time to run via sqlnet

[Zabair Ahmed]

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" <[EMAIL PROTECTED]> wrote:

Zabair,

 

Have you tried trace?

 

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

Zabair Ahmed <[EMAIL PROTECTED]> 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