Performance Issues
|
| < Day Day Up > |
|
The first thing to do when you are faced with troubleshooting performance issues is to clearly document the issue. Document, if you can, when the poor performance started (or at least when the end user first really noticed it); what the symptoms are; if it occurs in one product, one form, or program; or is suddenly a systemwide problem. Is it just in one system (e.g., Production, the Vision instance, Test) or is it in all instances? If it is not happening in all instances, what is different between the ones where it is and the ones where it is not? Does it only happen at certain times of day (8:30 A.M. when most of the users are suddenly logging on) or at certain times of the month (financials closing)? Can it be attributed to peak load times? How many users, that you are aware of, are affected by this performance hit? Are all locations equally affected or can those users that are affected be separated into one location? Has anything recently changed at that location? Can it be reproduced consistently? Have the users found creative ways around the problem, and if so, what are they? What on the system has changed recently (e.g., new software, change in the network, OS upgrade, apps patches, upgrade)?
Further, this document can be viewed as a living document of your ongoing tuning efforts in reference to the particular issue that you are dealing with because it contains information relevant to the particular problem set.
Determining the version of a module or modules in question can be accomplished with the following command:
strings <form name or report name or program name> |grep '$Header' |more
To obtain a listing of modules contained within a Pro*C program you can run the following command and examine the resulting text file (modules.txt) to find the list:
Strings <Pro*C module> |grep '$Header' > modules.txt
If you are not on the latest patch set and you have to raise this issue with Oracle Support, they will undoubtedly ask you what version you are on and if you are not on the most current version, why you are not. Be aware that, if you are not on the latest patch set, you will likely be requested to apply the latest patch set, as the latest usually addresses any known performance issues.
The electronic documents in Table 14.5 and Table 14.6 are not only a record of what you are doing so you can let anyone know what progress you are making, it is also a document that allows you to maintain a record of what issues you have seen and what your processes and resolutions were on those issues as well as allowing you have a running record that you can provide to Oracle Support if you should have to raise an iTAR. Document as many hard facts as you can obtain and capture as many user perceptions of the issues as you can.
| Performance Analysis/Problem Resolution Document |
|---|
| DateAnalyst(s) involved: Who reported the issue? Is anyone else reporting same or similar issue? If so, who? Are all users reporting the issue in a single location or department or spread across areas? When was it first reported? What application group and modules are affected (e.g., AR, all Financials, Form paxtrax.fmx, or list all transactions)? Environment(s) in which it is appearing (i.e., Production, Development, Test, Vision): What time of day, if attributable to a particular time, are you seeing this occur? Does it occur only when load is high? Does it occur only when load is low? Is the issue reproducible? Steps required to reproduce. Is there currently a workaround for it? When was the last time that you noticed this working correctly? Notes: Upgrades since last worked correctly (e.g., OS, Network, patches, database, third party, other): How long did transaction used to take? How long does transaction take now? How long is transaction supposed to take? Customizations to component in question: Module name (short and long name): Module version: Form name: Form version: Patch set level: Pro*C program modules (if applicable): Org ID involved (if multi-org): Debugging enabled? Trace file created? Trace filename: TKPROF name generated from trace file: Log file created? Log filename: Errors observed: Notes: |
| Applications Tuning Document |
|---|
|
Tuning can typically be broken into categories that describe the location of the problem and by extension, the type of tuning that you have to do on account of those problems. These apparently broad categories usually translate into much narrower focus of ultimate tuning.
Transaction tuning focuses on a transaction or a group of transactions. A transaction can be a specific as a list of values that used to show up almost instantly and now the three users that use this screen have to wait 30 or 45 seconds for it to come back. A broader problem might be that a department in finance and accounting is seeing the posting of journals now taking 10 minutes and it used to only take 2. It can be as broad as AR has been incredibly slow since the application of the latest minipack. Document this category as closely as you can. It may turn out that what appears to be an overall slowdown in all of AR is actually only one or two processes that have actually slowed down noticeably; it may eventually turn out to be one change that fixes them both or you may have to change several things (each addressing one issue) to ultimately rid the system of all of the symptoms. With transaction tuning, the ultimate solution typically focuses on tuning the underlying SQL, addressing indexes, or gathering statistics.
Database and Network Tuning is usually the target when there is an overall slowdown in the performance of the entire system. This is where you will typically focus if multiple user groups across multiple departments are reporting the performance issues. A typical red flag in this case is if you are getting similar reports of slowdowns happening at peak load times. A common complaint might be: What is wrong with the system? Every day at 11:00 A.M. (or at 3:30 P.M. on Friday afternoon), it suddenly takes 2 minutes to do what took 20 seconds to do at 10:00 A.M. At 11:00 A.M., users are trying to finish up a bunch of things so they can have them done when they go to lunch or Friday at 3:30 P.M. so they can get finished up for the weekend. It might be a networking issue, it might be that a set of sysadmins did an OS patch and did not realize there might be ramifications, or it may be attributable to a database upgrade. Typically, these are the areas where you look for these kinds of issues.
Issues with the middle tier are the final broad area to break the problem down into. Has navigation between Forms or opening any form in a product group suddenly started taking an inordinately long time? Have all of the users in a particular location suddenly started noticing the problem? Are all complaints coming from a segment of the network with a common IP address scheme? Might it be an Apache/JServ load issue? Is everyone in one department seeing generalized slowdowns? Have you ruled out issues with the database and its surrounding environment and not been able to determine any particularly poorly running sql statements? You may find that, once you have ruled out other issues, your final avenue of attack is at the technology stack (or middle tier) level.
One safe place, usually, to start is to gather statistics. If you are new to 11i and the Oracle 8i database, but (especially if) you have been around Apps for a long time, remember that you are now dealing with the CBO and it depends almost exclusively on the availability of good statistics. Run the Concurrent Programs that gather and analyze statistics. Your best time to run these kinds of jobs is when there is no one on the system or when the number of active users is low. You can do this while you are searching Metalink for any documents that might show similar issues and resolutions. Put into your documentation how often you are gathering statistics, particularly on the poorly running processes or products. Add to the frequency the approach taken (e.g., analyze table, schema, all schema) as well as the tool used (e.g., analyze, dbms_stats, fnd_stats, dbms_utility, or which concurrent job you have running to accomplish this feat) and the estimate size that you are using to estimate sample size.
Forms Trace
If you are having form specific issues, you can have the user run a form level trace. You may want to not phrase the request quite that way, however, as many of the end users will feel intimidated by the request: "You want me to do what? I could never do something like that."
It is a simple thing to do and many users have been able to accomplish a significant amount of tuning diagnostics without even knowing they have done it. To turn tracing on, have the user navigate to [Help -> Diagnostics -> Trace -> Trace with binds and waits] on the Applications menu. You will often find that you will require quite a large trace file and you may need to have the user set the trace file size to unlimited. To accomplish this, they would simply navigate to the help menu again and go to [Help -> Diagnostics -> Trace -> Unlimited Trace File Size]. Once these have been done, the user can perform the offending transaction as they would regularly and then go back through the steps they just performed to shut off forms tracing (or else have them log out); otherwise, you will capture everything the user does subsequently and the trace file will be less useful and far bigger than necessary.
Initialization parameters helpful in tracing include the following:
max_dump_file_size = Unlimited timed_statistics = true
Alternatively, you can set these parameters by using the alter system command in later releases of the database.
The resulting trace file should be located in your User_Dump_Dest location from your init.ora file.
If you have performed the trace with binds and waits, you can use another Oracle designed tool to help in the analysis of the resulting trace file. TRCANLZR.sql will analyze the trace file for you and present it in a more understandable format. You can obtain this tool through Note 224270.1 and run it at the command line as follows:
Sqlplus apps/apps Start trcanlzr.sql UDUMP <trace_file_name>
10046 Trace
Cary Milsap of HOTSOS (http://www.hotsos.com) has done significant research on the use of the 10046-trace level 8 for finding bottlenecks in a given SQL session. By turning trace on for a session, you can determine what that session's top wait events are both statistically (by the number of times that that event occurred) and by the amount of or total time that the session spent in that wait event and apply your efforts to correcting those that will provide the most improvement to the transactions that were involved during that time.
Tracing Your Own Session
You can turn Event 10046 trace on for your own session to determine what that session is doing and find all of the places it is waiting and what significant points are causing your problems. If you can perform the actions that the end user is performing or if you can convince the user to work with you through tracing the problem, you can capture all of the parts of that session by either having that user log in and setting trace at their session level or you can log in as them and do it.
Because it is often difficult to determine which Apps session belongs to which user, tracing that session as an outside person looking in is often difficult. Apparently Oracle is also a fan of the 10046 trace, because it allows you to turn trace on at your session's level from the session level.
From the main application window, select Help/Diagnostics/Trace/Trace with Waits (or Trace with Waits and Binds) from the main menu bar. This will set Event 10046 trace level 8 (or 12) on for the session that performs these actions, effectively turning trace on at the session level for the session calling it.
Tracing Someone Else's Session
You can trace someone else's session, but due to the way that Apps logs into the database with the APPS ID, doing so can become extremely difficult, especially for a busy system. The means by which you can accomplish this can be gotten from the HOTSOS Web site.
Using a Special ID to Run 10046 Trace
In Apps, you can create a special user ID that an end user will log in with that has the profile of being used specifically to detect wait events. The 11i Oracle E-Business Suite provides an option that will allow a user to execute customized code at the beginning of every session that that user initiates. This option allows for the linking to a particular form, package, Concurrent Program or any other piece of code that requires the code to access the database. You can make use of this feature to create a user whose only purpose is to generate trace files for tuning long running processes to run whatever process is giving you problems under a session set to gather 100046 trace.
The first thing that you need to do is to make sure that the profile option Initialization SQL Statement — Custom is available on the system and is accessible to both users and to programs. To accomplish this, you will need to log in as a user to whom the responsibility of Application Developer has been granted. As this user, choose the Profile option on the Applications menu and query profile FND_INIT_SQL. Make sure that the User Access section has both Visible and Updateable checked, that the Program Access has both Visible and Updateable checked, and that under System Administrator Access, both Visible and Updateable are checked for the User row (not for the Site, Application, or Responsibility rows, however). Once you verify that the profile option FND_INIT_SQL has the required values, you can switch to the sysadmin user and create a new user. Make the name for this user something that will obviously point to its purpose. Suggestions would be to call it Trace or Tuning or Waits, something that will obviously designate it as special. For the remainder of this example, I will use the user "Tuning." Assign to this user the transactions that you wish to trace using Event 10046. I suggest you assign this user all responsibilities and guard the password to it, changing it before and after every use so the ID is not misused. Alternatively, you can assign it different responsibilities every time it is needed to run a transaction. Once the user is created, you can navigate to System Profile Values Form (Profiles -> System) and query up the profile option Initialization SQL Statement — Custom for the user you just created (Tuning) and within the Editor panel place the following code string:
Begin FND_CTL.fnd_sess_ctl ('', '', 'TRUE', 'TRUE', 'LOG', 'Alter session set events ''10046 trace name context forever, level 12'''); end; It is important to remember that all quotes in this command are single quotes and anywhere that more than one occurs, it is multiples of one and never a double quote. This tells the system to create a trace file for everything that is done by this user from the time the user logs on until the time it logs off and create it with all wait events and bind variables that are relevant to this session. For tuning slow running SQL, both waits and bind variables are important. Minimally, to determine what waits a statement is encountering, you need to set the number following the LEVEL portion of the statement to 8.
Now that you have this user, it can be used any time you need to create a trace file. Never use this ID to do anything for which you do not deliberately want to have a trace file created as these traces can get quite large and could quite quickly fill up the dump directory. Typically, this trace file will be created in the UDUMP destination from your initialization parameters. Never, while logged in as this user, turn tracing on at the menu level, as this will nullify the command that you put into its profile and turn all tracing off.
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 122