Ankur, You said that API shows a delay. Are you sure the API call is the one causing the problem or is it the SQL generated from the API call. If you combine your API and SQL log files, by writing them to the same file, you will be able to see which one is giving you the problem. e.g. it may take the API 0.001 seconds to call the SQL, it may take 60 seconds for the SQL to run and then another 0.001 seconds to return the results to the API. If the SQL is taking a long time to run, you may want to extract the SQL statement and run an EXPLAIN PLAN to see if you are using indexes correctly (or at all!). If the delay is in the API, you could be looking at a network/connectivity problem between the AR Server and the database (are you running your database remotely?)
If you combine you API and SQL log files, you should be able to get a better understanding of what the API call is trying to do ... remember, one single API call (arCreateEntry) could generate multiple SQL statements and API's, so you need to make sure you understand what function you are testing in the application and what API and SQL is being executed. This will give you the big picture. Thanks, Mike. Michael Worts Remedy Specialist IGS, Business Consulting Services ITIL Service Manager Certified Tel: +44 (0)1962 822273 Mob: +44 (0)7801 755346 Internet: [EMAIL PROTECTED] Joe DeSouza <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 16/03/2007 11:59 Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: Performance Issues ** My guess is it might be one of these few things.. Memory leak on the current version/patch of the ARS you are on.. If this is the problem, in my experience with ARS 6.3 patch 19 is stable enough. Patch 20 reccommended though for the new DST update. However 20 is pretty new and though there are no known issues with memory leaks, there have been reported problems with the DST update itself. Another reason could be data fragmentation on the disk. This could be rectified with an backup of the database after stopping the ARS. Delete the database and recreate it using the same original parameters and restore the database. Another possible reason might be that there are certain searches happening on the current table whose record count is 700K. And the searches happening on this table is not using any defined indexes. You say it is a home grown application so it is very possible the developers who built it might have not defined the indexes required. You might need to see if your table is indexed properly. Look for all possible searches happening through qualifications used in 'set field if' actions of workflow, or qualifications used in table fields, qualifications used in search menus that might be defined and are used on this form, etc.. Hope this helps.. Joe D'Souza ----- Original Message ---- From: Ankur Gulati (GR/EIL) <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Friday, March 16, 2007 7:15:38 AM Subject: Performance Issues ** Hi Listers, We have a home grown application with few integrations with external interfaces. Since last few weeks the application performance has depleted by 60%. Earlier it was 20% CPU utilization and now the remedy application takes 70-75% at any given time. The data would not be more than 700,000. Some times the external integrations would fail, restart of AR Server sometimes do help, but again in 1 or 2 days it starts to suck all the system resources and takes 70% utilization. The system takes more than 4 mins to save a record and some times it takes lil more than that too, same is the case with the searches too. SQL and API Log shows lot of delay. mainly APIs are having delay but I don't have much knowledge to interpret API log. I don't understand what has affected the application suddenly. As far as hardware goes...I don't know the exact specs but I am sure tht it is under utilized. the System is ARS 6.0.1 on Solaris 9, database :Oracle 9i we also noticed lot of inactive remedy sessions in the oracle. Any idea what are they? Can they affect the performance. There aren't many indexes but still the system was running fine till few weeks back. I need help as from where should I start looking at the probable performance issues and what all things I can tweak to improve it. Incase you need more details, please do let me know. Regards Ankur Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out. __20060125_______________________This posting was submitted with HTML in it___ Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
