Multidimensional repositories for problem discovery and capacity planning of database applications

[0001] This application claims priority to the following previously filed applications: This application is a divisional of U.S. application Ser. No. 09/549,824, entitled MULTIDIMENSIONAL REPOSITORIES FOR PROBLEM DISCOVERY AND CAPACITY PLANNING OF DATABASE APPLICATIONS, filed on Apr. 14, 2000 now U.S. Pat. No. 6,804,714 by Venkat Ranga Reddy Tummalapalli, incorporated herein by reference, which claims priority from U.S. Provisional Patent Application No. 60/129,658, filed on Apr. 16, 1999, entitled MULTIDIMENSIONAL REPOSITORIES FOR PROBLEM DISCOVERY AND CAPACITY PLANNING OF ORACLE APPLICATIONS the content of which is hereby incorporated by reference in its entirety.

[0002] The present invention relates to problem discovery and, more particularly, to problem discovery and capacity planning of database applications.

[0003] As computer systems become more complex, they become more difficult to manage efficiently, and the problems that occur within them become more difficult to isolate. One form of complex computer system on which society increasingly relies is the database system. Conventional database systems consist of one or more clients (“database applications”) and a server (a “database server”). When a client requires data, the client submits a query to the server, where the query includes criteria for selecting the data. The server retrieves the data that satisfies the specified criteria from a database and returns copies of the selected data to the client that submitted the query. More complex database systems may include numerous servers that share access to one or more databases, where each such server may be serving thousands of clients.

[0004] Another type of complex computer system is known as an application server system. An application server system typically consists of one or more clients (“browsers”), a server (“application server”), and applications (“cartridges”). Users of the browsers execute the cartridges by causing the browsers to send and receive messages through the application server to the cartridges. FIG. 1 is a block diagram of an exemplary application server system 100. The system 100 includes a plurality of browsers 102, 104 and 106 that communicate with a plurality of listeners 110, 116 and 122 over the Internet 108 according to the HTTP protocol. In response to requests from the browsers, the listeners cause a web application server 180 to invoke software modules, referred to herein as cartridges. In the illustrated embodiment, web application server 180 has initiated the execution of three cartridges 130, 134 and 138.

[0005] The web application server 180 is composed of numerous components, including transport adapters 112, 118 and 124, dispatchers 114, 120 and 126, an authentication server 152, a virtual path manager 150, a resource manager 154, a configuration provider 156 and a plurality of cartridge execution engines 128, 132 and 136. A typical operation within system 100 generally includes the following stages:

[0006] A browser transmits a request over the Internet 108.

[0007] A listener receives the request and passes it through a transport adapter to a dispatcher.

[0008] The dispatcher communicates with the virtual path manager 150 to determine the appropriate cartridge to handle the request.

[0009] At this point the dispatcher does one of two things. If the dispatcher knows about an unused instance for that cartridge, the dispatcher sends the request to that instance. If there are no unused cartridge instances for that cartridge, the dispatcher asks the resource manager 154 to create a new cartridge instance. After the instance starts up successfully, the cartridge notifies the resource manager of its existence. The resource manager 154 then notifies the dispatcher of the new instance. The dispatcher creates a revised request based on the browser request and sends the revised request to the new instance.

[0010] The cartridge instance handles the revised request and sends a response to the dispatcher.

[0011] The dispatcher passes the response back through the listener to the client.

[0012] Application server systems and database systems can be combined. For example, some or all of the cartridges that are operated through the browsers in an application server system may in fact be database applications that, in turn, issue queries to one or more database servers in response to messages from the browsers. Due to the complexity of such combined systems, it is exceedingly difficult to identify the cause of performance problems. For example, assume that a browser receives an extremely slow response to a message that is sent to an application server and dispatched to a cartridge, where the message causes the cartridge to issue a query to a database server, where the database server executes the query to retrieve data for the response. Under these conditions, the slow response time may be due to problems with any of the entities involved, or with communication problems between the entities.

[0013] The identification of unacceptably slow response times may be of interest to users as well as to the administrators responsible for managing the computer system. For example, the subscription agreement of a user may guarantee a particular level of performance (e.g. that 98% of all orders be processed in less than one minute). Users with such subscriptions would typically be interested to know when, and how often, the system is not meeting the specified level of performance.

[0014] The process of ensuring that the system is able to meet the performance requirements of users is generally referred to as capacity planning. Typically, there are six general phases in the capacity planning process:

[0015] (1) setting up the service level objectives;

[0016] (2) estimating the demand for the resources of the system;

[0017] (3) identifying resources that satisfy the estimated demand;

[0018] (4) implementing the system with the identified resources;

[0019] (5) determining whether the system actually satisfies the demand; and

[0020] (6) repeating steps (2) to (5) when the system fails to satisfy the demand.

[0021] The step of determining whether the system satisfies the demand may be accomplished, for example, by periodically analyzing statistical information relating to the system. However, the amount of processing that such analysis may require can be so enormous that, if performed at a reasonable frequency, the analysis overhead itself may result in a violation of the service level commitments made to users.

[0022] The better the tools that are made available to the capacity planner, the higher the likelihood that the implemented system will satisfy the anticipated demands. Further, when the implemented system is not satisfying the anticipated demands, the easier it will be to determine and fix the problems that are preventing the achievement of the desired performance levels.

[0023] A number of systems have been developed for problem identification and planning. For example, systems for problem determination in performance management are described in B. Arinze, M. Igbaria, and L. F. Young: “A Knowledge Based Decision Support System for Computer Performance Management,” Decision Support Systems 8, 501-515, 1992 and Bernard Domanski: “A PROLOG-based Expert System for Tuning MVS/XA,” Proceedings of the Computer Measurement Group, 160-166, 1987. A system for process control is described in D. R. Irwin: “Monitoring the Performance of Commercial T1-rate Transmission Service,” IBM Journal of Research and Development, 805-814, 1991. A system for planning cooking recipes is described in Janet Kolodner: “Case-Based Reasoning,” Morgan Kaufmann Publishers, Inc., 1993. Systems for problem identification of electrical circuits and analysis of financial statements are described in Robert Milne: “Using AI in the Testing of Printed Circuit Boards” National Aerospace & Electronics Conference, Dayton Ohio, May 1980, and Donald W. Kosy and Ben P. Wise: “Self-Explanatory Financial Planning Models,” Proceedings of the National Conference on Artificial Intelligence, 176-181, 1984. However, none of these systems address the domain of systems management, nor do they consider problem discovery and capacity planning.

[0024] An attempt to apply multidimensional database technology to systems management, which focuses on performance management for data from a single source, is described in Robert F. Berry and Joseph L. Hellerstein: “An Flexible and Scalable Approach to Navigating Measurement Data in Performance Management Applications,” Proceedings of the Second IEEE International Conference on Systems Management, June, 1996. Another attempt to use multidimensional navigation for sales/subscription handling is described in Business Objects: A. M. Burgeat and F. Prabel, “Data Warehousing: Delivering Decision Support to the Many,” Business Objects Corporation, 1996.

[0025] Based on the foregoing, it is clearly desirable to provide techniques that allow problems within complex computer systems to be isolated, and to assist in planning such systems to comply with user requirements, and to maximize system capacity and avoid bottlenecks.

[0026] Techniques are provided for facilitating database application management and, more specifically, for facilitating problem discovery and planning where the data being collected is from a plurality of data sources with a wide variety in type of instrumentation and a variety of data access components. In certain embodiments, the data access components include ROLAP/MOLAP clients. Use of the techniques described herein may reduce maintenance costs for database application systems, either in a conventional deployment scenario, or in a data center hosting scenario.

[0027] According to one aspect of the invention, techniques are provided for monitoring performance of an application server system, where the techniques involve collecting performance data from components of the application server system and storing the performance data as multidimensional data organized according to a plurality of dimensions within one or more database systems. One dimension of the plurality of dimensions is a hierarchical time dimension. Another dimension of the plurality of dimensions is a component dimension. An interface is presented for accessing and navigating through the multidimensional data within at least one of the one or more database systems. Drill down operations may be performed into one or more hierarchical dimensions of the plurality of dimensions in response to user input received through the interface.

[0040] A method and apparatus for problem discovery and capacity planning for an application server system is described. In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, to one skilled in the art that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

[0041] Techniques are provided that allow efficient, performance monitoring, service level troubleshooting, and capacity planning for an application server system. In general, the techniques involve capturing performance data from components of the application server system, and storing the data as multi-dimensional data within one or more data repositories. One of the dimensions used to organize the multi-dimensional data is time. Various tools may be used to selectively access and display the multi-dimensional data, and to perform drill-down operations to identify performance problems and perform capacity planning.

[0042] For performance monitoring, performance snapshots are taken over time. The performance snapshots indicate whether all of the performance elements are operating within bounds, and that the service level agreements are being satisfied. In one embodiment, the snapshots are stored in a historical database. Such databases are commonly referred to as data warehouses. A first performance snapshot is identified as a baseline snapshot against which subsequent performance snapshots are compared. When subsequent snapshots deviate from the baseline snapshot by a predetermined deviation threshold, a report is generated.

[0043] For service level troubleshooting, the techniques allow application service providers to track where their resources are going. Application providers use tools that allow drill down operations to track down the cause of performance problems encountered by specific users.

[0044] For capacity planning, the techniques allow administrators to quickly and efficiently answer questions like: “I have added two business units this quarter. I plan to add four more business units next quarter. How many servers will I need to buy?”

[0045] FIG. 2 is a block diagram of a system 200 that is configured for performance monitoring, service level troubleshooting, and capacity planning, according to an embodiment of the invention. System 200 generally includes (1) components (202-1 to 202-N) of an application server system 202, (2) data collection components 204 to collect information about the performance of the application server system (hereinafter “performance data”), (3) data storage components 206 to store the performance data, and (4) data access components 108 to access the performance data. As shall be explained hereafter, the data storage components 206 store the performance data in a manner that allows the data access components 208 to be used to easily and efficiently identify service level violations, identify the causes of service level violations, and perform capacity planning.

[0046] Application server system 202 includes numerous components 202-1 to 202-N. The specific types of and tasks of those components will vary for application server systems from implementation to implementation. In one implementation, system 202 includes a set of concurrent processing servers. A concurrent processing server is a batch processing facility built on the job processing facilities available on the operating system. A concurrent processing server helps execute non-interactive, data dependent programs simultaneously in the background.

[0047] Each concurrent processing server, in turn, may include numerous concurrent processing managers. A concurrent processing manager acts as an administrator of job processing and employs workers at the operating system to process application user requests. Each manager can run any program, or be specialized to run only certain kinds of programs. The programs run by a concurrent processing manager are referred to herein as concurrent programs. Concurrent managers continuously read requests from a master queue and run requests based on the requests' schedule, priority and incompatibility rules.

[0048] A concurrent program is a program that runs concurrently with other programs as background processes, while the user continues to work at a terminal. Concurrent managers can only take requests to execute concurrent programs. Once a manager picks a request, the manager must obtain information about the job, in order for the job to be executed. According to one embodiment, the information required for concurrent managers to execute a concurrent program is stored in database tables, along with other metadata about the concurrent program.

[0049] When a user submits a concurrent request to a concurrent manager, the request is uniquely identified by a request ID. According to one embodiment, the request ID is inserted into a database table, and the table acts as a request queue.

[0050] Data collection components 204 generally represent components that collect information about how one or more components of system 202 are operating. According to one embodiment, the data collection components 204 retrieve data from many different types of components within application server system 202. The format of the data thus retrieved may vary based on the type of the component from which the data is collected.

[0051] According to one embodiment, data collection components 204 include one or more of a “Data Gatherer”, “Forms Trace Cartridge”, “Apps Cartridge” and “PM2 Cartridge”. The Data Gatherer is a multithreaded server process that delivers performance data to requesting clients via IP sockets. The data is collected through collection cartridges that are shareable libraries that reside with the Data Gatherer. The Data Gatherer creates a framework for delivering performance data to client applications in a data driven manner.

[0052] The Forms Trace Cartridge, the Apps Cartridge and the PM2 Cartridge are types of collection cartridges. In particular, the Forms Trace Cartridge collects trace statistics for Forms servers. The Apps Cartridge collects database tier statistics for applications by logging into a global schema on the database that keeps a record of the database tier statistics. The PM2 Cartridge collects additional database statistics by, for example, logging into a custom schema which stores additional statistics based on the instrumentation provided through a PM2 toolkit.

[0053] Database performance monitor: Many database systems maintain information about their current performance. Typically, databases make this performance data available to users in the form of dynamic performance views. To obtain the current performance information from the database, users can issue queries against the dynamic performance views in the same manner as queries are issued against conventional relational tables. According to one embodiment, a database application, referred to herein as the “database performance monitor”, is used to periodically issue queries against the dynamic performance views of the databases used in application server system 202. The data retrieved in response to any given query reflects the performance statistics of the database server at the time the query was issued. Consequently, a historical record of the performance is created by storing each set of query results in a manner that is indexed based on the time the search results were obtained.

[0054] Data collected by the data collection components 204 is periodically loaded into data storage components 206. As shall be described in greater detail hereafter, the performance data is organized within the data storage components 206 based on, among other things, the time at which the performance data is generated, collected or loaded.

[0055] As mentioned above, data collection components 204 collect data from the various components of the application server system 202. According to one embodiment, the components from which data is collected include the concurrent processing servers, developer server forms, the database server hosting the applications instance, and operating system statistics for the nodes hosting the applications instance.

[0056] The data of the concurrent processing server is collected by the Apps Cartridge, which issues SQL commands against the database that is hosting the applications, using the FND tables in the Global Apps Schema. The data of the Developer Server Forms is collected by the Data Gatherer Forms Cartridge to obtain memory/CPU utilization statistics for the various runtime instances of Forms. Related data is also derived through instrumentation of Forms Trace.

[0057] The data from the dynamic performance views is collected for the database server that is hosting the applications instance. The Data Gatherer OS Cartridge collects the data of the operating system on the node that is hosting the applications instance. Such information may include, for example, system activity and resource utilization statistics.

[0058] The performance data collected by data collection components 204 is stored as multidimensional data within storage components 206. In the context of database systems, a “dimension” is a list of values that provide categories for data. A dimension acts as an index for identifying values of a variable. For example, because the performance data has separate values for each time interval, the data has a TIME dimension. That is, the data is organized by TIME. A dimension is similar to a key in a relational database. Data that is organized by two or more dimensions is referred to as “multidimensional data”. According to one embodiment, the performance data is organized within data storage components based on TIME, COMPONENT, REQUEST CLASS, and CLIENT.

[0059] A hierarchical dimension is a dimension where there is a hierarchical relationship between the values of the dimension. A common example of a hierarchical dimension is time. Possible values for time include a particular day, month, quarter, or year. The values are hierarchically related because a particular day is associated with a particular month, which is associated with a particular quarter, which is associated with a particular year. In addition to the TIME dimension, one or more of the other dimensions used to organize the performance data within data storage components 206 may be hierarchical.

[0060] For example, the COMPONENT dimension may have two levels, where a coarser level corresponds to concurrent processing server, and a finer level corresponds to the concurrent managers that correspond to the concurrent processing servers. Similarly, the CLIENT dimension may have the following hierarchical levels: domain name, subnet, and IP address.

[0061] Multidimensional data may be stored in relational database systems (“ROLAP” systems) or in specialized, “multidimensional” database systems (“MOLAP” systems). According to one embodiment of the invention, the data storage components 206 used to store the performance data include a ROLAP database 210 and a MOLAP database 212. Multidimensional database systems provide structures and access techniques specifically designed for multidimensional data, and therefore provide relatively efficient storage and access to multidimensional data.

[0062] Relational database systems store data in the form of related tables, where each table has one or more columns and zero or more rows. The conventional mechanism for storing multidimensional data in a relational database system is to store the data in tables arranged in what is referred to as a star schema. In relational database systems, a star schema is distinguished by the presence of one or more relatively large tables and several relatively smaller tables. Rather than duplicating the information contained in the smaller tables, the large tables contain references (foreign key values) to rows stored in the smaller tables. The larger tables within a star schema are referred to as “fact tables”, while the smaller tables are referred to as “dimension tables”. Database applications communicate with the relational database system by submitting commands that conform to the database language supported by the relational database system, the most common of which is the Structured Query Language (SQL).

[0063] ROLAP database 210 may employ techniques to facilitate the storage and retrieval of multidimensional data. For example, ROLAP database 210 may employ the techniques described in U.S. Pat. No. 6,484,179, entitled “Storing Multidimensional Data In A Relational Database Management System”, filed by Raymond Roccaforte on Oct. 25, 1999, the entire contents of which are incorporated herein by this reference.

[0064] In an alternative embodiment, ROLAP database 210 is a data warehouse that does not directly receive the performance data from data collection components 204. Rather, data collection components 204 store the performance information into a traditional relational database system. The data is then transferred from the traditional relational database system to ROLAP database 210 on a periodic basis.

[0065] Data access components 208 generally represent tools and applications that are used to analyze and present the performance data stored in data storage components 206. The specific type of application used for the analysis, and the capabilities thereof, will vary from implementation to implementation. In one embodiment, a ROLAP client 232, such as Oracle Discoverer, is used to access and analyze the performance data in ROLAP database 210, and a MOLAP client 230, such as Oracle Express, is used to access and analyze the data in MOLAP database 212.

[0066] Data access components 208 may involve more than one layer of software. For example, rather than interact with ROLAP client 232 directly, users may interact with a user layer tool 236 and a service reports tool 234. Those tools, in turn, interact with ROLAP client 232, and ROLAP client 232 interacts with ROLAP database 210. Similarly, system administrators may interact with a Capacity Planning Tool that interacts with MOLAP client 230, which in turn interacts with MOLAP database 212.

[0067] According to one embodiment, various views of the performance data are established based on the particular types of users that are interested in the data. For example, separate views of the system performance may be established for backup operators, database operators, and end users. In addition, users may be provided tools to define their own views of the performance data.

[0068] The performance data views may be made accessible to users in real time over the Internet. For example, in one embodiment, a customer that has purchased a particular service level can request a particular web page over the Internet. In response to the request, a web page is dynamically generated based on a particular view of the performance data. The data for that view is delivered by ROLAP client 232 based on data retrieved from ROLAP database 210. Further, the view in which the performance data is presented may be selected based on the identity of the user that submitted the request.

[0069] By making the views of the performance data available over the Internet to customers of the application server system 202, those users are able to compare the actual level of service they are receiving against the level of service to which they have subscribed. For example, a user may have subscribed to a service level that guarantees a maximum request processing time of 20 seconds. That user may access a view of the performance data that indicates the request processing time for the user. If the request processing time exceeds 20 seconds, then the user knows that its subscription guarantees are not being met.

[0070] The tools used to access the performance information preferably support conventional multi-dimensional data access, navigation, and presentation operations, such as those operations commonly referred to as Pivot, Rollup, and Drill Down. When used to process the performance data of application server system 202, these operations allow a user to easily and efficiently identify and isolate the causes of particular types of problems within application server system 202.

[0071] FIGS. 3-10 illustrate how system 200 may be used to identify and isolate the cause of particular types of problems within application server system 202. Various scenarios are presented hereafter to described how particular problems may be identified by using data access components 208 to navigate through a particular set of performance data. The particular types of performance data collected by data collection components 204 may vary from implementation to implementation. Therefore, the specific types of problems that can be identified, and the specific navigation steps that a user would take to identify the problems, will also vary from implementation to implementation.

[0072] The information collected by data collection components 204 from the concurrent processing managers may include how many long running requests are concurrently running during particular time intervals. FIGS. 3A and 3B illustrate how data access components 208 can be used to navigate through the long running request information to identify the time slots where concurrent processing servers proved to be bottlenecks.

[0073] Referring to FIG. 3A, in screen 302 the user is shown the total number of long running requests for all time intervals, all concurrent managers, and all concurrent processing servers. The user then “drills down” one level of the concurrent processing server dimension to show the total number of long running requests separately for each concurrent processing server. The drill down operation produces screen 304.

[0074] Based on screen 304, it appears that concurrent processing server CP1 has a significantly higher load than the other concurrent processing servers. Therefore, the user selects CP1 and drills down another level to show the total number of long running requests separately for each concurrent manager within CP1. This drill down operation produces screen 306.

[0075] Based on screen 306, it appears that the concurrent manager STDMGR has a significantly higher load than the other concurrent managers of CP1. Therefore, the user selects STDMGR and drills down one level of the time dimension to show the number of long running requests for STDMGR separately for each month. This drill down operation produces screen 308.

[0076] Based on screen 308, it appears that, during month MON1, STDMGR had a significantly higher load than during other months. Therefore, the user selects MON1 and drills down one more level of the time dimension to shown the number of long running requests for STDMGR separately for each day within MON1. This drill down operation produces screen 310.

[0077] Based on screen 310, it appears that, during day DAY1, STDMGR had a significantly higher load than during other days of month MON1. Therefore, the user selects DAY1 and drills down one more level of the time dimension to shown the number of long running requests for STDMGR separately for each hour within DAY1. This drill down operation produces screen 312. Screen 312 indicates that 30,000 long running requests were active in HR1 of DAY1 of MON1 in STDMGR. Based on this information, the user can attempt to resolve the bottleneck situation. For example, the user can attempt to reschedule some of the requests that are run in that particular time slot to run in other time slots. Alternatively, the user can create a specialized queue for the long running requests. By sending the long running requests to the specialized queue, those requests will not hold up other requests, sent to one or more other queues, during that bottleneck time slot.

[0078] The information collected by data collection components 204 from the concurrent processing servers may include the request run time of particular concurrent programs during particular time intervals. FIGS. 4A and 4B illustrate how data access components 208 can be used to navigate through the request run time information to identify the concurrent program that has the longest request run time and its particular time slot.

[0079] Referring to FIG. 4A, in screen 402 the user is shown the total request run time for all time intervals, all concurrent programs, and all concurrent managers. The user then “drills down” one level of the concurrent manager dimension to show the total number of long running requests separately for each concurrent manager. The drill down operation produces screen 404.

[0080] Based on screen 404, it appears that requests on concurrent manager CP1 have a significantly higher run time than requests on the other concurrent managers. Therefore, the user selects CP1 and drills down another level to show the request run time separately for each concurrent program within CP1. This drill down operation produces screen 406.

[0081] Based on screen 406, it appears that the concurrent program FNDCPURGE has a significantly higher run time than the other concurrent programs of CP1. Therefore, the user selects FNDCPURGE and drills down one level of the time dimension to show the request run time for FNDCPURGE requests within CP1 separately for each month. This drill down operation produces screen 408.

[0082] Based on screen 408, it appears that, during month MON1, FNDCPURGE requests had a significantly higher run time than during other months. Therefore, the user selects MON1 and drills down one level of the time dimension to show the request run time for FNDCPURGE requests within CP1 separately for each day within MON1. This drill down operation produces screen 410.

[0083] Based on screen 410, it appears that, during DAY1 of month MON1, FNDCPURGE requests had a significantly higher run time than during other days of MON1. Therefore, the user selects DAY1 and drills down one level of the time dimension to show the request run time for FNDCPURGE requests within CP1 separately for each hour within DAY1. This drill down operation produces screen 412.

[0084] Based on this information, the user can investigate what peculiar circumstances occurred during that particular time interval with that particular concurrent program, and thereby identify and resolve the causes that led to the high request run times.

[0085] A business user, like ‘OE-SUPER’, will have a set of “Apps Responsibilities” assigned to him/her. Each Responsibility can issue concurrent requests or initiate Forms Sessions. The load that is generated by adding a business unit could be associated to the Apps Responsibility and is comprised of the Concurrent Manager load and the Forms Sessions load. Scenario 3 involves the use of system 200 for capacity planning for concurrent managers and Forms servers. For example, the question posed by a capacity planner may be: “I have added 2 business units over the past 2 months, what will happen if I add 4 more business units in the next 6 months. FIGS. 5A and 5B illustrate how a planner may navigate through the collected data to answer this question.

[0086] Referring to FIG. 5A, it illustrates a screen that indicates the disk usage, memory usage, and CPU usage, for all business units, all Apps Responsibilities, all time intervals, all Apps users, all data servers, all forms servers, and all concurrent processing servers. The planner can select to have the information displayed separately for each business unit, resulting in screen 504. The planner may then drill down to display the information for a particular business unit. Screen 506 is produced when the planner performs a drill down operation based on the FINANCE business unit.

[0087] The planner may then perform a drill down operation based on a specific Apps Responsibility of the selected business unit. Screen 508 is produced when a planner performs a drill down operation based on the PO Apps Responsibility. The planner may then compare the resource usage of the PO Apps Responsibility for the FINANCE business unit to the PO Apps Responsibility of other business units during a particular month (MM1). The screen that displays this information to the planner is illustrated as screen 510.

[0088] In scenario 3, the planner drills down to the resource consumption for CPU/Memory/Disk along the various dimensions. The planner first drills down along the Business Unit, pivoted it to ‘FINANCE’ and then drills down along the APPS Responsibility, Time and Apps User Dimensions respectively. The planner then performs a pivot operation to ‘PO’, ‘MM1’ and ‘FINSUP’ and finds the resource consumption for CPU/Memory/Disk.

[0089] Scenario 4 involves performance information maintained for each concurrent program in the application server system 202. Referring to FIG. 6, screen 602 shows the average request times for all concurrent programs, all time intervals, and all execution modes. Screen 604 shows the same information as screen 602, with values shown for each individual concurrent program. Screen 606 shows the same information as screen 604, but only for execution mode “C”. Screen 608 illustrates the average request time for a particular concurrent program POPRG1 on a per-day basis.

[0090] The user can pivot any of the dimensions and drill down along others to explore the average request times for the various concurrent programs.

[0091] Alternatively, a user could drill down to see the requests that ran for a particular concurrent program. Request wait time could be used to see if a particular concurrent program has waited for longer periods than usual. If such has occurred, a user could navigate through the performance data to relate that occurrence with possible causes, such as concurrent processes scheduled for the manager, or availability of the manager during that time interval.

[0092] If the wait time is more than the runtime, then the administrator can choose to run those requests in a separate concurrent queue, like a short-running-request queue. Similarly, the administrator could create a queue for long running requests, resulting in an overall throughput improvement in the application server system 202.

[0093] FIG. 7 illustrates screens 702-710 of a scenario in which an Apps database administrator (DBA) pivots along the business unit of his/her choice and drills down along the time axis to find the number of concurrent requests processed in that time slot, for the concurrent program of interest.

[0094] In particular, screen 702 shows the number of requests for all time, all business units, and all concurrent programs. The administrator then navigates to screen 704, where the number of requests is shown for individual business units. The administrator then navigates to screen 706, where the number of requests for a particular business unit B1 is shown for each concurrent program. Screen 708 shows the number of requests for a B1 and a particular concurrent program CP1 for individual months. Screen 710 shows the same information for individual days of a selected month MM1.

[0095] Scenarios 1-5 demonstrate how application server system management is facilitated by the flexibility obtained by storing the performance data as multidimensional data through which one may navigate using tools that support drill down, roll up, and pivot operations.

[0096] The integration of trace data, forms sessions and Concurrent manager data into a multidimensional repository facilitates End to End Management for Applications. Service Level triggers along the Service level parameters in the Multidimensional repository facilitate Service Level Violation Reporting and fits well into a Service Level Management framework.

[0097] FIG. 8 illustrates a series of screens that may be presented to an end user in response to navigation commands sent by the end user to navigate through the performance data generated for a particular session of the user.

[0098] Screen 802 shows the NCA Client time, Apps Server time and DB Server time for all time intervals, all NCA clients, all Apps servers and all DB servers. Screen 804 shows the same information as screen 802, with the separate values shown for each Apps server. Screen 806 shows the NCA Client time, Apps Server time and DB Server time for the SALES Apps Server for particular time intervals. Screen 808 shows the NCA Client time, Apps Server time and DB Server time for the SALES Apps server for particular intervals for particular DB Servers. Screen 810 shows the NCA Client time, Apps Server time and DB Server time for particular time intervals, particular NCA Clients, a particular Apps Server, and a particular DB server.

[0099] Combining the network latencies with the processing times of the database and the application servers provides a comprehensive view of the end-to-end performance metrics for the end user.

[0100] FIG. 9 illustrates a series of screens that may be presented in response to navigation commands sent by a user to identify the source of a Service Level Agreement (SLA) violation. An SLA violation is a condition or set of conditions that causes one or more guarantees in a Service Level Agreement to be broken. In scenario 7, the user is notified of an error by a monitor previously registered by the user. The drills down to a specific concurrent request, and the SQL database command that generated an error. In the illustrated scenario, the user determines that the error was caused because the SQL command did not include the correct number of arguments.

[0101] In particular, screen 902 presents information related to the SLA violation. Screen 904 shows the number of concurrent requests for all concurrent programs, all concurrent managers, and all modes of completion. Screen 906 illustrates the same information for each mode of completion. Screen 908 shows the same information for all concurrent programs where completion status is “Error”, and where separate values are shown for each concurrent manager. Screen 910 illustrates the error occurrences that occurred in the POXCON concurrent manager, the PL/SQL blocks that caused the errors, and the request times of the errors.

[0102] FIG. 10 illustrates a series of screens that may be presented in response to navigation commands sent by a user. In scenario 8, a user pivots the Apps User in which the user is interested (VISION in this case) and does a drill down along the Database User Dimension. The user pivots the Database User (APPS in this case) and does a drill down along the Forms/Database session to find the Forms Client time, Forms Server time, the Database Execution time and the Network Latency time

[0103] In particular, at screen 1002, the DB time, Forms Server time, and Forms Client time are shown for all forms sessions, all database sessions, all Apps users and all database users. Screen 1004 shows the same information, with different values shown for each Apps user. Screen 1006 shows the same information for each database user of the VISION Apps user. Screen 1008 shows the same information for each database session for the APPS database user associated with the VISION Apps user.

[0104] Snapshot Comparisons

[0105] As mentioned above, performance data is periodically loaded into data storage components 206. Within data storage components 206, the performance data is organized, among other things, based on time. The performance data that is associated with a particular time or time interval can be considered a “snapshot” of the performance of application server system 202 at that particular time. According to one embodiment, one of the snapshots is selected as a “baseline snapshot”. Preferably, the snapshot that is selected as the baseline snapshot is a snapshot that reflects the performance of application server system 202 during an interval in which all user service level guarantees are met.

[0106] According to one embodiment, the baseline snapshot is compared to subsequent snapshots. The comparison may be performed, for example, at the time the subsequent snapshots are loaded into data storage components 206. If the deviation between the baseline snapshot and the subsequent snapshot does not exceed a certain threshold, then no action is taken. However, if the deviation between the baseline snapshot and the subsequent snapshot exceeds the threshold, then corrective action is taken. Corrective action may include, for example, sending a message to an administrator of application server system 202 to alert the administrator to the possibility of a performance problem. Alternatively, if the deviation indicates violation of the service level agreements of one or more customers, the corrective action may include compensating the customers for the violation and/or sending messages to inform the customers about the violation.

[0107] The operation of comparing snapshots may consume a significant amount of resources, depending on the size of the application server system 202 and the amount of performance data collected. Therefore, according to one embodiment, every snapshot is not automatically compared to the baseline snapshot. Rather, snapshots are selected for comparison based on some criteria such as, for example, the passage of a time interval from the time of the last snapshot comparison.

[0108] Many database systems support triggers. Triggers allow a user to specify an action to be performed by the database system in response to a particular event. According to one embodiment, triggers are defined to fire when performance data indicates that a service level is violated. The action performed by the trigger may be, for example, the generation of a service level violation report. Such a report may be automatically sent to both the application server administrator and the customer whose service level was violated.

[0109] FIG. 11 is a block diagram that illustrates a computer system 1100 upon which an embodiment of the invention may be implemented. Computer system 1100 includes a bus 1102 or other communication mechanism for communicating information, and a processor 1104 coupled with bus 1102 for processing information. Computer system 1100 also includes a main memory 1106, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 1102 for storing information and instructions to be executed by processor 1104. Main memory 1106 also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor 1104. Computer system 1100 further includes a read only memory (ROM) 1108 or other static storage device coupled to bus 1102 for storing static information and instructions for processor 1104. A storage device 1110, such as a magnetic disk or optical disk, is provided and coupled to bus 1102 for storing information and instructions.

[0110] Computer system 1100 may be coupled via bus 1102 to a display 1112, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 1114, including alphanumeric and other keys, is coupled to bus 1102 for communicating information and command selections to processor 1104. Another type of user input device is cursor control 1116, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor 1104 and for controlling cursor movement on display 1112. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allows the device to specify positions in a plane.

[0111] The invention is related to the use of computer system 1100 for implementing the techniques described herein. According to one embodiment of the invention, those techniques are implemented by computer system 1100 in response to processor 1104 executing one or more sequences of one or more instructions contained in main memory 1106. Such instructions may be read into main memory 1106 from another computer-readable medium, such as storage device 1110. Execution of the sequences of instructions contained in main memory 1106 causes processor 1104 to perform the process steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the invention. Thus, embodiments of the invention are not limited to any specific combination of hardware circuitry and software.

[0112] The term “computer-readable medium” as used herein refers to any medium that participates in providing instructions to processor 1104 for execution. Such a medium may take many forms, including but not limited to, non-volatile media, volatile media, and transmission media. Non-volatile media includes, for example, optical or magnetic disks, such as storage device 1110. Volatile media includes dynamic memory, such as main memory 1106. Transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus 1102. Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infra-red data communications.

[0113] Common forms of computer-readable media include, for example, a floppy disk, a flexible disk, hard disk, magnetic tape, or any other magnetic medium, a CD-ROM, any other optical medium, punchcards, papertape, any other physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, any other memory chip or cartridge, a carrier wave as described hereinafter, or any other medium from which a computer can read.

[0114] Various forms of computer readable media may be involved in carrying one or more sequences of one or more instructions to processor 1104 for execution. For example, the instructions may initially be carried on a magnetic disk of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to computer system 1100 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on bus 1102. Bus 1102 carries the data to main memory 1106, from which processor 1104 retrieves and executes the instructions. The instructions received by main memory 1106 may optionally be stored on storage device 1110 either before or after execution by processor 1104.

[0115] Computer system 1100 also includes a communication interface 1118 coupled to bus 1102. Communication interface 1118 provides a two-way data communication coupling to a network link 1120 that is connected to a local network 1122. For example, communication interface 1118 may be an integrated services digital network (ISDN) card or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, communication interface 1118 may be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface 1118 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.

[0116] Network link 1120 typically provides data communication through one or more networks to other data devices. For example, network link 1120 may provide a connection through local network 1122 to a host computer 1124 or to data equipment operated by an Internet Service Provider (ISP) 1126. ISP 1126 in turn provides data communication services through the world wide packet data communication network now commonly referred to as the “Internet” 1128. Local network 1122 and Internet 1128 both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link 1120 and through communication interface 1118, which carry the digital data to and from computer system 1100, are exemplary forms of carrier waves transporting the information.

[0117] Computer system 1100 can send messages and receive data, including program code, through the network(s), network link 1120 and communication interface 1118. In the Internet example, a server 1130 might transmit a requested code for an application program through Internet 1128, ISP 1126, local network 1122 and communication interface 1118. In accordance with the invention, one such downloaded application implements the techniques described herein.

[0118] The received code may be executed by processor 1104 as it is received, and/or stored in storage device 1110, or other non-volatile storage for later execution. In this manner, computer system 1100 may obtain application code in the form of a carrier wave.

[0119] In the foregoing specification, the invention has been described with reference to specific embodiments thereof. It will, however, be evident that various modifications and changes may be made thereto without departing from the broader spirit and scope of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense.