Re: query taking a long time to run via sqlnet
I have included the following in my sqlnet.ora
trace_file_client = osstrace_timestamp_client = truetrace_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 := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN 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 AddressGuardWant to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
Re: query taking a long time to run via sqlnet
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 := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN 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
query taking a long time to run via sqlnet
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 := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN 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
