Source: https://patents.google.com/patent/US7376682?oq=5179747
Timestamp: 2018-06-19 03:56:19
Document Index: 441373286

Matched Legal Cases: ['Application No. 60', 'art 200', 'art 300', 'art 400', 'art 600', 'art 1100']

US7376682B2 - Time model - Google Patents
Time model Download PDF
US7376682B2
US7376682B2 US10775513 US77551304A US7376682B2 US 7376682 B2 US7376682 B2 US 7376682B2 US 10775513 US10775513 US 10775513 US 77551304 A US77551304 A US 77551304A US 7376682 B2 US7376682 B2 US 7376682B2
US10775513
US20050055672A1 (en )
Quantifying the impact of wasteful operations on a database system is provided. One or more operations that are determined to be wasteful are received. The impact of the wasteful operations on performance in a database may then be quantified. The database is monitored to determine when a wasteful operation is being performed. When a wasteful operation is detected, a time value is recorded of the time spent on processing the wasteful operation. The time value is stored and used to quantify an impact of a performance problem in a database. The time value may be stored and associated with other time values that are recorded for the same wasteful operation. Thus, the impact of wasteful operations that are performed and processed in a database may be determined.
This application is a non-provisional application of and claims benefit to U.S. Provisional Application No. 60/500864, filed Sep. 5, 2003, which is incorporated by reference in its entirety for all purposes.
U.S. application Ser. No. 10/775,531, entitled, “AUTOMATIC DATABASE DIAGNOSTIC MONITOR ARCHITECTURE”, filed Feb. 9, 2004; and
U.S. application Ser. No. 10/775,733, entitled “CAPTURING SESSION ACTIVITY AS IN-MEMORY SNAPSHOTS USING A TIME-BASED SAMPLING TECHNIQUE WITHIN A DATABASE FOR PERFORMANCE TUNING AND PROBLEM DIAGNOSIS”, filed Feb. 9, 2004.
The present invention generally relates to databases and more specifically to apparatus and methods for quantifying the impact of wasteful operations on a database system.
Enterprise databases continue to grow in size and number resulting in increased systems management and administrative complexity. As the size and complexity of a database system increases, the likelihood that performance problems may result also increases.
Correction of a problem is often expensive and time-consuming (e.g., some database performance problems are due to improperly written applications that can take many months or years to rewrite); thus, it may not be feasible to correct all the problems that are detected. Eventually, a database administrator may analyze the performance problems and attempt to determine which problems should be corrected first. In some cases, the database administrator may fix what the administrator deems as the most serious problems to the database system. The database administrator may determine that the problems are the most serious from prior experience or by the class of problem that is being experienced. For example, an administrator may deem hard parses to be the most serious. Although a database administrator may think certain problems are the most serious, the problems may not be. Also, some database administrators may fix the problems that are most easily solved. Thus, the problems that impact database system performance the most may not be a high priority for correction or may not be fixed at all.
Accordingly, methods and apparatus are needed to quantify the impact of performance problems on a database system.
Embodiments of the present invention generally relate to quantifying the impact of wasteful operations on a database system. In one embodiment, one or more operations that are determined to be wasteful are received. The impact of the wasteful operations on performance in a database may then be quantified. The database is monitored to determine when a wasteful operation is being performed. When a wasteful operation is detected, a time value is recorded of the time spent on processing the wasteful operation. The time value is stored and used to quantify an impact of a performance problem in a database. The time value may be stored and associated with other time values that are recorded for the same wasteful operation. Thus, the impact of wasteful operations that are performed and processed in a database may be determined.
In one embodiment, a method for quantifying an impact of wasteful operations in a database is provided. The method comprises: receiving one or more operations that are determined to be wasteful in the database; detecting when wasteful operations are being performed; recording a time value that is spent on the wasteful operation; and storing the time value, wherein the time value is used to quantify the impact of a performance problem in the database.
In another embodiment, a method for quantifying wasteful operations in a database is provided. The method comprises: receiving a plurality of operations that are determined to be wasteful in the database; detecting when wasteful operations are being performed; recording a plurality of time values for time spent on performing the wasteful operation; and storing the plurality of time values, wherein time values are associated with a wasteful operation that the time values were recorded for, wherein the time values are used to quantify a performance problem in the database.
In yet another embodiment, a method for quantifying an impact of wasteful operations in a database is provided. The method comprises: determining one or more operations that are determined to be wasteful in the database; determining when to monitor the one or more operations, wherein the one or more operations are monitored when the one or more operations are being processed in the database; detecting when wasteful operations are being performed; quantifying an impact of the wasteful operation; and storing a value that quantifies the impact of the wasteful operation for a performance problem in the database.
FIG. 2 depicts a simplified flowchart and a method for quantifying an impact of wasteful operations on database system according to one embodiment of the present invention;
FIG. 3 depicts a simplified flowchart for generating a rules tree according to one embodiment of the present invention;
FIG. 4 depicts a simplified flowchart for diagnosing performance problems in a database system according to one embodiment of the present invention;
FIG. 5 depicts an example of a rules tree according to one embodiment of the present invention;
FIG. 6 depicts a simplified flowchart of a method for determining a recommendation for a solution to a performance problem according to one embodiment of the present invention;
FIGS. 7A and 7B depict an example of a report according to one embodiment of the present invention;
FIG. 8 depicts a system for sampling activity in the database system according to one embodiment of the present invention;
FIG. 9 depicts a simplified flowchart of a method for capturing information for session histories according to one embodiment of the present invention;
FIG. 10 depicts a more detailed block diagram of a system implementing an embodiment of the present invention;
FIG. 11 depicts a simplified flow chart of a method for filtering captured information according to one embodiment of the present invention; and
FIG. 12 is a block diagram of a database system for implementing an embodiment of the invention.
FIG. 1 depicts a system 100 for diagnosing performance problems in a database system according to one embodiment of the present invention. System 100 includes a client 102, an automatic database diagnostic monitor (ADDM) 104, a database system 105, and one or more users 108.
In one embodiment, database system 105 includes a database 106 and database server 107. Database server 107 is configured to receive and process requests for database 106. Database system 105 may be any database system and a person skilled in the art will appreciate other components and variations to database system 105. FIG. 12 provides a general description of a database system.
ADDM 104 is configured to perform a holistic analysis of operations that were performed in database system 105. ADDM 104 receives information for operations that were monitored in database 105. In one embodiment, the information includes statistics determined using a time model and a wait model, both of which will be described in more detail below. The time model and wait model quantify an impact of certain operations in database system 105. For example, the time model and wait model are used to determine time values that quantify the impact of operations in database system 105.
ADDM 104 is configured to perform a self-diagnosis of performance problems. The diagnosis includes determining a set of rules that detect and categorize one or more possible performance problems that may occur. Operations in database 106 are detected and information is recorded. Information collected is analyzed to determine if a condition is satisfied for a rule, thus indicating a performance problem associated with the rule may exist. In one embodiment, the condition may be satisfied when a threshold is reached. For example, when a certain time value for an operation is reached, the operation may be considered a performance problem. Also, the time value may be expressed as a certain percentage of time recorded for all operations. Other ways to measure the impact using the time values recorded may also be appreciated.
In one embodiment, ADDM 104 first reviews rules for general performance problems and then drills down to more narrow performance problems. Thus, a coarse granularity of performance problems is first reviewed and then finer granularities of the performance problems are reviewed until a root problem is identified. The root problem may be at any of the granularities reviewed and multiple root problems may be found.
In one embodiment, as will be described in more detail later, a performance problem classification system is reviewed to determine a root problem. In one embodiment, possible performance problems are determined and categorized in a structure, such as a rules tree. Each node of the tree has one or more rules associated with it along with conditions that determine whether the rules are satisfied. If a rule is satisfied at a node, a performance problem may exist.
In one embodiment, ADDM 104 traverses the rules tree by reviewing performance problems from a coarse granularity to a finer granularity. The rules tree may include symptoms, which are performance problems that may lead to other performance problems and to finally, a root performance problem. If a symptom does not lead to a root problem, the symptom may also be considered a root problem
If a specific problem exists at one level of the rules tree, ADDM 104 may determine other nodes that are connected to the current node. These connected nodes are performance problems that are related to and may be the cause of the performance problem of the parent node. As ADDM 104 traverses the rules tree, finer granularities of performance problems are determined until a root problem is determined.
Once a problem is determined, a recommendation for a solution may be determined. In one embodiment, the solution may be determined using a set of recommendation rules. The operation that caused the problem may be determined and information captured about the processing that was performed in database system 105 for the operation may be retrieved. The information is reviewed and a recommendation rule is used to determine a recommendation for a solution for the problem. In one embodiment, the recommendation is specific to requests that caused the problem and is not a generic recommendation. For example, the recommendation may be change the request that caused the operation to be performed to a different request. Also, rationales for making the changes may be output. The rationales may be that a request is causing a certain operation to be performed or that a certain amount of time is being spent on some operations.
ADDM 104 may organize any diagnostics performed in a report and send them to client 102 for display on an interface 110. In one embodiment, the diagnostics are automatically performed in a self-diagnosis. Thus, the diagnosis is run automatically and may detect problems before they cause a fatal error in database system 105. Also, client 102 may request, through interface 110, that certain diagnostics be performed by ADDM 104. For example, a determination if certain performance problems exist may be requested. ADDM 104 may process the requested diagnostics and return a report to interface 110. Thus, diagnosis may be performed on-demand.
The report that ADDM 104 generates may include the root problem found, any symptoms that were reviewed to determine the root problem, and also areas where no problems were found. Thus, a user may focus on the problems that were found and the symptoms that caused the problem. Also, because the areas where no problems were found are reported, a user does not have to waste time looking at those areas of database system 105 for a problem.
The time model and wait model will now be described. Both models are used to collect information that is used to quantify the impact of a performance problem in database system 105. The information is then used to determine performance problems in database system 105.
The time model is used to quantify of the impact of wasteful operations on database system 105. In one embodiment, the impact of wasteful operations is measured using time values. For example, the time value may measure the time spent processing a wasteful operation in database 106.
By quantifying the impact of wasteful operations in database system 105, a user may measure the impact of possible corrective actions before they are actually taken. For example, because wasteful operations are quantified in a time value, if a wasteful operation is corrected, the impact of correcting the wasteful operation is known because part of or all of the time spent on the operation may be eliminated.
In one embodiment, database time is measured using the time model. In one embodiment, database time is different from response time. The response time is the time a user 108 waits for a request to return from database system 105. Database time is the time spent in database server 107 servicing the request. In one embodiment, database time does not include the network portion of the user's response time. The network portion may include the time spent sending the request to database server 107 and the time spent to send the results to users 108. Also, parts of a request may be executed in parallel by different executing threads of database server 105. The threads are processes that operate to perform a task. If parts of the request are executed in parallel within the database system 105, the database time is the sum of all the time spent in the parallel executing threads of execution. This means that database time for a request executed in parallel may be much greater than the response time for the same request.
Operations that, when performed in database system 105, may be wasteful are determined. In one embodiment, a wasteful operation is an operation that may be attributed to some type of performance problem. For example, a wasteful operation may be an operation that does not need to be performed. Also, a wasteful operation may be an operation that may be performed more efficiently if performed in a different way. In one embodiment, operations that may be considered wasteful include hard parses, soft parses, configuration problems, improperly sized caches, and other operations are listed in Appendix A. It will be understood that other operations may be appreciated.
In one embodiment, the time values spent on wasteful operations are stored in a database. The actual operations that were processed in database system 105 for which the time value was recorded may also be associated with the time value that is stored. Also, accumulated time values on time spent on all operations for each wasteful operation may be stored. For example, time may be accumulated by request types, such as by SQL statements. Thus, a when a certain SQL statement is performed, database time is recorded for the operation. In one embodiment, database time for a request that is performed for multiple users is recorded. An active session sample, which is described later, may then be used to determine users 108 that requested the operation when it is determined that the operation may be a performance problem. For example, a time value for a hard parse operation requested by a first user 108-1 may be stored. If another request by a user 108-2 caused a hard parse operation, a time value for that hard parse operation is stored. Also, an accumulated time value for hard parse operations that includes the time values from the hard parse operations associated with the requests for user 108-1 and user 108-2 is stored and associated with the general category of hard parse operations.
FIG. 2 depicts a simplified flowchart 200 and a method for quantifying an impact of wasteful operations on database system 105 according to one embodiment of the present invention. In step 202, operations that would be wasteful are determined. The operations determined to be wasteful may be operations as described in Appendix A.
In one embodiment, rules associated with each wasteful operation may also be determined. The rules include conditions that are defined by the time model in determining when a wasteful operation is being performed. For example, a rule may include a condition that when a hard parse is being performed for a request, a hard parse wasteful operation is being performed.
In step 204, a condition check detects when a wasteful operation starts in database system 105. In one embodiment, when a condition is satisfied, an indication is made that a wasteful operation has begun.
In step 206, a timer for the wasteful operation is started. The operation may be timed using any methods capable of recording a time value, such as using a counter to time the wasteful operation. In one embodiment, the timer is started when the processing of a wasteful operation in database system 105 is detected. Thus, database time spent processing wasteful operations in database system 105 is recorded.
In step 208, a condition check detects that the wasteful operation ends. In one embodiment, when a condition is satisfied, an indication is made that a wasteful operation has ended. For example, a condition may be associated to an event that indicates when the wasteful operation ends.
In step 210, the timer for the wasteful operation is stopped. Accordingly, the time value that represents the time spent processing the operation in database system 105 has been recorded. The time value represents the time that is deemed to be wasteful for the operation that was performed. Accordingly, the timer may have timed the time spent processing wasteful operations in database system 105 rather than time spent waiting for a resource and time spent in communication between database server 105 and users 108.
In step 212, a time value for the time spent on the wasteful operation is stored. Also, the total time that is spent on a certain category of a wasteful operation in database 106 may also be stored.
The Wait Model
The wait model is a measurement of time spent in database server reads waiting for external events to complete. These events may be the completion of some requested service, such as a disk read, or they can be the release of some shared resource by another database server thread. This time (or some portion of it) is usually considered wasteful since the request cannot be further processed until the wait is finished. In one embodiment, wait classes are determined that categorize wait events that may occur in database system 105. For example, wait classes may include an application, an administration, concurrency, configuration, user I/O, network communications, and idle wait classes.
An application wait class includes lock waits caused by row level locking or explicit lock commands. Administration wait classes include database administrator commands that cause all other users to wait as in an index rebuild. A commit wait class includes a wait for redoing log write confirmation after a commit operation. A concurrency wait class includes concurrent parsing and buffer cache and lock contention waits. A configuration wait class includes wait time due to undersized buffer space, log file sizes, buffer cache size, shared pool size, ITL allocation, HW enqueue contention, and ST enqueue contention. A user I/O wait class includes waits for blocks to be read off a disk. A network communications wait class includes waits for data to send over the network. An idle wait class includes wait events that signify the session is inactive.
The Generation of a Rules Tree
In one embodiment, ADDM 104 is configured to traverse a rules tree to determine a performance problem. The rules tree may be a directed acyclic graph or dag. Each node in the tree is associated with a set of rules that examine a set of statistics (e.g., time spent on operations). Each rule has a threshold for the set of statistics that is examined. In one embodiment, the threshold is time based, typically a percentage of the total database time for a time period under analysis. A node is set to fire (i.e., is selected) if the rule threshold is surpassed, upon which further analysis is done to determine the problem, i.e. more data pieces are looked at and are correlated with each other. If a root problem is found recommendations will be generated. If a problem is detected by this node, it may be necessary to invoke nodes under these nodes (children of the current node) to further drill down. Accordingly, the finding by the parent node may be just a symptom (i.e., not a problem) and is associated with the problems found by the child nodes, if any. If the rule threshold is not exceeded, then a “no-problem is found” message is generated and no drill downs are performed. Thus, areas of the rules tree may be eliminated as not causing performance problems. This may be referred to as pruning the rules tree and has a bearing on the efficiency of the problem detection by ADDM 104. Each problem has an impact associated with it and can be used by the user to order the set of issues affecting the database system 105.
The rules tree is organized in such a way that time associated with the symptoms that a parent is responsible for is always greater than or equal to the sum of the times for its child's symptoms. For example, a symptom node may be Parses, and children nodes may be Hard parses and Soft Parses. The total time spent in hard and soft parses is combined into the time for the Parses node. Thus, generating the rules tree involves piecing the various nodes together and figuring out the relationship between them. In one embodiment, the rules tree is a static entity (not dynamically generated or modifiable by users).
FIG. 3 depicts a simplified flowchart 300 for generating a rules tree according to one embodiment of the present invention. In step 302, a specification of performance problems is received. The specification includes performance problems that may occur while processing operations in database 105. For example, performance problems may include operations, such as hard parses, soft parses, and other operations listed in Appendix A.
Performance problems may include symptoms that may lead to root problems. The performance problems range from problems that are considered at a coarse granularity to problems at a finer granularity. For example, a performance problem may be “parses” at a first level and a second level may include the problems “hard parses” and “soft parses”.
Additionally, rules associated with the performance problems are specified. The rules determine when a performance problem may be considered a problem. For example, a rule may specify that if a certain amount of time is spent on an operation, a performance problem may exist. A rule may be, for example, if 100 seconds were spent on hard parses for a number of operations, then a performance problem may exist for hard parses. A rule may also specify that a performance problem exists if a certain percentage of time is spent on an operation.
In step 304, a structure is generated that includes the performance problems. In one embodiment, the structure generated is in the form of a rules tree that may be traversed to determine a root problem. Relationships (e.g., parent/child relationships) associated with the nodes are stored in a database and those relationships are traversed to determine a root problem. In one embodiment, a first structure is generated for the time values determined for a wait model and a second structure is generated for the time values associated with the time model. The structures for the time model and wait model may include different performance problems or may include the same performance problems.
In step 306, information is received for the performance problems. For example, time values generated using the wait model and time model are received. The time values are then associated with the applicable performance problems. For example, time spent on hard parses may be monitored using the time model and wait model. The time values are then associated with the performance problems for hard parses.
Accordingly, rules trees for the wait model and time model are generated with the appropriate information that has been recorded. The rules trees may then be analyzed by ADDM 104 to determine where performance problems may exist.
Diagnosing Performance Problems in the Database
FIG. 4 depicts a simplified flowchart 400 for diagnosing performance problems in database system 105 according to one embodiment of the present invention.
In step 402, classifications for one or more performance problems in database system 105 are received. The performance problems may be classified as symptoms that lead to a root problem. For example, as different symptoms are encountered, the symptoms may lead to a root problem. Also, in some cases, symptoms may also be considered a root problem. A root problem may be considered a problem that may not lead to other problems. Also, a root problem may be a problem that has the greatest impact on performance in database system 105 compared to the symptoms. In one embodiment, the performance problems are classified in that certain operations may be associated with the performance problem. For example, a performance problem may be hard parse operations.
The one or more performance problems may have rules associated with them. For example, rules may be associated with a problem that include conditions that, when met, indicate that a problem may exist. In one embodiment, thresholds for the rules may be specified by a user. The threshold may be a time value or a percentage of total time spent. The percentage may compare the time spent on hard parse operations compared to total time spent processing operations in database 106. Also, a low time value for a rule may be specified. In this case, the rule may be triggered more often. Thus, it is more likely that an operation for the rule may be flagged as a possible performance problem.
In step 404, one or more database time values for the one or more performance problems are determined. In one embodiment, the database time values are determined using the wait model and the time model. As discussed above, the wait model and time model quantify the impact on database system 105 for the performance problems. For example, the time model measures database time. Database time is a concept that measures the amount of time taken in processing a request in database 106. In one embodiment, database time is distinct from user response time, which is a cumulative value that includes the time users 108 wait for resources to become available for processing the request and time spent in communication between server 107 and users 108 in performing the request.
In step 406, a performance problem based on the one or more time values for the one or more performance problems is determined. In one embodiment, a rules tree is traversed where different nodes are analyzed to determine if the time values associated with them trigger the rule associated with it. For example, a rule may specify that if a certain amount of time is spent for an operation associated with a node in the rules tree, then it is determined that a performance problem may exist. ADDM 104 may determine that this performance problem is a root problem. Also, ADDM 104 may determine that a finer granularity of a problem may exist and may try to drill down the rules tree to determine a more specific problem. In this case, ADDM 104 analyzes rules that are related to the identified node to determine if any rules are satisfied based on the time values associated with the related nodes. If any related nodes have their conditions satisfied, then a performance problem may exist for those nodes. The process of traversing the rules tree continues until it is determined that a root problem exists.
In step 408, a recommendation for a solution is generated for the performance problem determined in step 406. In one embodiment, the recommendation for the solution indicates which operations are causing the performance problem and in addition suggests recommendations for solutions to the operations that may alleviate the problem. Also, the recommendation may include actions to should be taken and the rationales behind taking the actions. In one embodiment, the recommendation for the solution, if performed, may reduce the time spent in processing the operation in database system 105.
Additionally, ADDM 104 may determine areas where the problems do not exist. For example, operations that have been performed that are not causing problems may be determined and also outputted. In this case, a user may disregard reviewing these operations and the results thereof when attempting to fix the performance problems of database system 105.
FIG. 5 depicts an example of a rules tree 500 according to one embodiment of the present invention. The rules tree includes one or more rules that are evaluated for each node.
In this example, a first rule R1 for a node 502 evaluates to true and its child node 504 is processed next. Node 502 is designated as a first symptom #1. A second rule R2 for a node 504 evaluates to true and its child nodes 506 and 508 are processed next. Node 504 is designated as a symptom #2. A third rule R3 for a node 506 evaluates to true and its child nodes 506 and 508 are processed next. Node 506 is designated as a symptom #3.
A fourth rule R3 for a node 510 evaluates to true, and fifth and sixth rules R5 and R6 for nodes 512 and 514, respectively, evaluate to false. In this case, node 510 does not have any child nodes and is thus considered a root problem. Nodes 502, 504, and 506 that were traversed to determine the root problem are considered the symptoms.
In one example, the first rule R1 may have been system waits. In one example, the threshold may set to a low value so that most system waits are analyzed. However, it does not mean there is a performance problem just because a system wait rule is triggered. It may be determined that the time spent is not significant or cannot be corrected.
The second rule R2 may be latch waits. The latch waits may be evaluated to true because a certain time value was recorded. For example, latch waits may account for greater than 1% to total elapsed database time. The rule also calls for its child rule to be evaluated.
The third rule R3 may be shared pool latch. This operation is a more specific operation than a latch wait. A certain time value associated with a shared pool latch causes the rule to be triggered. The rule also calls for its child rule to be evaluated.
The fourth rule R4 may be hard parse analyze. This rule does not have any children and thus is considered a root problem. In one embodiment, recommendation rules associated with node 510 are used to determine a recommendation for a solution to the root problem. Also, recommendations for correcting the symptoms determined may be provided.
Determining a Recommendation for a Performance Problem
FIG. 6 depicts a simplified flowchart 600 of a method for determining a recommendation for a solution to a performance problem according to one embodiment of the present invention.
In step 602, one or more categories for operations that caused the performance problem are determined. The categories may be the operations determined in step 406 of FIG. 4 or operations associated with nodes 502-510 of FIG. 5.
In step 604, the requests made by users 108 that caused the performance problem are determined. For example, an SQL statement may be the operation that is causing the problem identified. The SQL statement may have been structured in a way that a hard parse occurs in database 106. For discussion purposes, a single operation for a request from a user 108 is assumed but multiple requests may be determined for a performance problem.
In step 606, information is retrieved that was stored for the operation that was performed for user 108. For example, the information may include what operation was processed, what resources were accessed, how long the operation took to process, etc.
In one embodiment, the information that was stored may have been captured using snapshots of active sessions that were taken during a certain time interval. The snapshots of information include the operations that were being performed at a certain time and what the operations were doing. The operation may be determined in the snapshots and how the operation was processed may be determined based on a series of snapshots that are pieced together using statistically valid techniques. The snapshots of information over a certain time interval show what an operation is doing during the time interval. If a snapshot was taken every second, then the information for the operation at every second may be analyzed as a whole. The process of capturing the snapshots of information will be described in more detail below.
In step 608, the information captured is reviewed to determine the time spent on certain parts of the request. For example, ADDM 104 may associate a certain time value with a parse issue and then a certain time value with a hard parse issue.
In step 610, recommendation rules are reviewed to determine a recommendation rule where time spent for a certain part of the request meets the requirements of the recommendation rule. In one embodiment, each node in the rules tree is associated with one or more recommendation rules for a performance problem associated with a node. The recommendation rules classify problems and specify conditions that are met. For example, if there is a problem with data in a table that is causing a lot of time to be spent performing an operation with the table, a recommendation to optimize the table may be determined. Also, The set of recommendation rules may be used to determine which rules apply to the table. For example, if a rule is partition a table, a recommendation may be to partition the table. If the table is partitioned, however, the recommendation would not be given. Thus, recommendations are not general and depend on each situation.
In step 612, a recommendation for a solution is determined for the problem using the rule. For example, a solution may be a suggestion on how to change a request. For example, a different SQL statement based on the request may be determined. The recommendation may be to not use literals in a SQL statement but use bind variables instead. This would prevent excess hard parsing.
FIG. 7 depicts an example of a report 700 generated by ADDM 104 according to one embodiment of the present invention. As shown, report 700 includes four findings 702-1 to 702-4. Each finding 702 is associated with one or more operations. For example, finding 702-1 is associated with concurrent read and write activity. The data block that the operations were being performed in is also cited.
Each finding 702 includes an impact 704-1-704-4 on database system 105. The impact is quantified as a time value. For example, finding 702-1 includes an impact of 201 seconds, which is a 13% impact on total database time spent in database system 105. The impact in this case is the time taken in processing the operations for a finding 702 against the total database time processing operations. Thus, a user can see the impact that a performance problem has on database system 105.
Each finding 702 includes one or more recommendations 706-1-706-4. Recommendations 706 include actions 707-1-707-4 and rationales 708-1-708-4 for actions 707-1-707-4. Actions 707 indicate what may be done to alleviate the performance problem. For example, the requests that are causing the performance problem may be determined and a corrective action is recommended. For example, finding 702-1 recommends that application logic be investigated for a database block #40984 to determine the cause of high concurrent read and write activity. A user may then investigate the data block cited and determine why concurrent read and write activity is being performed. The user can thus concentrate on a certain data block in database 106.
Rationales indicate specific operations that may have caused the performance problem. For example, in finding 702-1, the SQL statement with an ID of “4vxy8fv4y3dhd” spent significant time on “buffer busy waits” for the data block. A user thus may investigate the operations cited and determine why the operation is being performed.
Symptoms 710-1-710-4 are also included. Symptoms 710 indicate which performance problems led to a root problem found. For example, symptom 710-1 indicates that a wait class “concurrency” was consuming significant time. A user may then investigate the symptoms that led to the root problem and thus does not need to determine the causes of a problem.
In some examples, such as in finding 702-4, recommendations may not be available. In these cases, additional information may be included in report 700. The additional information may include operations that may have caused a performance problem. Also, the additional information may include general information on operations rather than specific operations. For example, hard parses may be a problem but the exact requests that caused the hard parses may not have been identified.
Other areas that did not cause any problems are shown in additional information 712. The classes of problems where significant database time was not consumed are listed. Thus, a user may not need to review these areas of database system 105.
FIG. 8 depicts a system 800 for sampling activity in database system 105 according to one embodiment of the present invention. System 800 includes a session activity monitor (SAM) 802, one or more sessions 804, users 108, and a database 806.
Different information may be associated with a session 804. The information may include state information that provides the state of the request at the time. For example, the information includes information describing who a user 108 that requested the operation is (e.g., a user ID), what request (e.g., SQL command) is being processed, what the operation is doing (e.g., waiting, retrieving data, updating data, etc.), and other information. This information is useful when a performance problem is encountered and may be used to diagnose the problem. For example, certain requests and their activity may be viewed, how much time was spent on certain operations can be estimated, etc. and may be used in diagnosing a performance problem. This data may also be used to reconstruct the set of operations/activity in database system 105 for the time under consideration., etc. may be used in diagnosing a performance problem.
FIG. 9 depicts a simplified flowchart of a method for capturing information for session histories according to one embodiment of the present invention. In step 902, SAM 802 determines when it is time to capture activity information in database system 105. In one embodiment, a time interval is determined where information is captured periodically. For example, SAM 802 may capture information every second. Accordingly, a full trace of information is not captured in one embodiment. Rather, a sample of activity is captured where information is captured at certain time intervals.
In one embodiment, ADDM 104 uses statistical techniques to ensure that the active session samples are statistically significant (from the captured information). For example, ADDM 104 determines a performance problem as described above. The performance problem indicates which operations may be causing problems. Then, ADDM 104 looks at the individual requests that were made by users 107 that caused the problem. In order to do this, the samples of active session history are analyzed. The information in different snapshots for an operation is reviewed and a model of what was recorded is developed. The model is useful in determining what a request did in database server 107 (e.g., what operations were performed). If information captured is relatively unintrusive to the operation of database system 105, then snapshots of active sessions may be taken at continuous uniform intervals. The snapshots may provide a statistically significant picture of activity in database system 105. Then, ADDM 104 may use techniques to analyze the activity to determine which operations may be causing performance problems.
FIG. 10 depicts a more detailed block diagram of a system 1000 implementing an embodiment of the present invention. System 1000 depicts the data flow for data captured by SAM 802. SAM 802 includes modules, such as session sampler 1006, circular buffer view creator 1008, and database view creator 1010, that may be implemented in software, hardware, or any combination thereof. Users 108 connect to database system 105. Sessions 804 are created and represented by state objects. The state objects include information about the activity for sessions 804.
FIG. 11 depicts a simplified flow chart 1100 of a method for filtering captured information according to one embodiment of the present invention. In step 1102, captured information in circular buffer 1002 is reviewed. In one embodiment, the information is filtered on a user by user basis. For example, the information captured is associated with a session 804 and thus a user 108. The information for each session 804 is analyzed to determine if it should be deleted or stored. In another embodiment, the information captured for all active sessions is analyzed as a whole.
In step 1106, if the captured information is not important, then is step 1108, the information is discarded. If the captured information is important, the information is indexed instep 1108.
In step 1110, the indexed information is stored in database 806. The information may be archived for retrieval at a later time. In one embodiment, one difference between information in circular buffer 1002 and database 806 is information in circular buffer 1002 may be accessed faster.
FIG. 12 is a block diagram of a system 1200 for implementing an embodiment of the invention. System 1200 includes user computers 1205, 12 10, and 1220. User computers 1205, 121 0, and 1220 can be general purpose personal computers having web browser applications. Alternatively, user computers 1205, 12 10, and 1220 can be any other electronic device, such as a thin-client computer, Internet-enabled mobile telephone, or personal digital assistant, capable of displaying and navigating web pages or other types of electronic documents. Although system 1200 is shown with three user computers, any number of user computers can be supported.
A web server 1225 is used to process requests for web pages or other electronic documents from user computers 1205, 1210, and 1220. In an embodiment of the invention, the data analysis software operates within a web browser on a user computer. In this embodiment, all user interaction with the data analysis software is via web pages sent to user computers via the web server 1225.
Web application server 1230 operates the data analysis software. In an embodiment, the web application server 1230 is one or more general purpose computers capable of executing programs or scripts in response to the user computers 1205, 1210 and 1215. The web application can be implemented as one or more scripts or programs written in any programming language, such as Java™, C, or C++, or any scripting language, such as Pern, Python, or TCL.
In an embodiment, the web application server 1230 dynamically creates web pages for displaying the data analysis software. The web pages created by the web application server 1230 are forwarded to the user computers via web server 1225. Similarly, web server 1225 receives web page requests and input data from the user computers 1205, 1210 and 1220, and forwards the web page requests and input data to web application server 1230.
The system 1200 is one example for executing data analysis software according to an embodiment of the invention. In another embodiment, web application server 1230, web server 1225, and optionally database 1235 can be combined into a single server computer system. In alternate embodiment, all or a portion of the web application functions may be integrated into an application running on each of the user computers. For example, a Java™ or JavaScript™ application on the user computer is used to retrieve or analyze data and display portions of the data analysis application.
Problems Detected and Reported by ADDM 104
CPU bottlenecks due to Oracle as well as non-Oracle workloads.
Top SQL statements along with top objects by the following criteria (when applicable)
interconnect traffic in RAC
Top statements by PLSQL and JAVA execution time.
Excessive connection management (login/logoff).
Hard Parse contention due to
Shared pool undersizing
Bind size mismatch
Excessive soft parsing
Hot sequences leading to contention.
Excessive wait times caused by user locks (via the dbms_lock pkg)
Excessive wait times caused by DML locks (e.g.: lock table)
Excessive wait times caused by pipe put operations (e.g.: dbms_pipe.put)
Excessive wait times caused by concurrent updates to the same row (row lock waits)
Excessive wait times due to inadequate ITLs (large number of concurrent transactions updating a single block).
Excessive commits and rollbacks in the system leading to high overhead on a per transaction basis (logfile sync).
I/O capacity issues due to limited bandwidth and latency and potential causes (like excessive checkpointing due to logfile size and MTTR, excessive undo, etc.)
Inadequate I/O throughput for db block writes by DBWR.
System slowdown due to inability of archiver processes to keep up with redo generation.
Log buffer contention and sizing issues
Undersized redo logfile issues
Contention due to extent allocation
Contention due to moving the high watermark of an object
Undersized memory issues
Hot block (with block details) with high read/write contention within an instance and across the cluster.
Hot object with high read/write contention within an instance and across the cluster.
Buffer cache latch contention due to access patterns.
Cluster interconnect latency issues in a RAC environment.
Inability of LMS processes to keep up in a RAC environment leading to congestion for lock requests.
1. A method for quantifying an impact of wasteful operations in a database, the method comprising:
receiving one or more operations that are determined to be wasteful when performed in the database;
detecting when one or more wasteful operations are being performed in the database;
recording a time value that is spent on a wasteful operation;
generating information that quantifies the impact of a performance problem in the database based on the time value; storing the time value, wherein the time value is used to quantify the impact of a performance problem in the database;
wherein the time value measures time spent processing the wasteful operation in the database;
generating an accumulated time value associated with the wasteful operation based on the time value; and
wherein generating the accumulated time value associated with the wasteful operation comprises adding the time value to another time value associated with another operation, the another operation being a database operation that includes the wasteful operation and at least one or more other wasteful operations.
generating information indicative of the impact of the wasteful operation on the database based on the time value.
generating a mapping associating the time value with the wasteful operation.
4. The method of claim 1, further comprising analyzing the performance problem using the time value.
5. A method for quantifying wasteful operations in a database, the method comprising:
receiving a plurality of operations that are determined to be wasteful when performed in the database;
detecting when wasteful operations are being performed in the database;
recording a plurality of time values for time spent on performing each of the wasteful operations; and
storing the plurality of time values, wherein a time value is associated with a wasteful operation for which the time value was recorded, wherein the time values are used to quantify a performance problem in the database;
6. The method of claim 5, wherein the impact of the wasteful operation is known before a performance problem quantified by the time value associated with the wasteful operation is fixed.
generating information quantifying the impact of one or more performance problems in the database based on the plurality of time values for the wasteful operations.
8. The method of claim 7, wherein each time value in the plurality of time values measures the amount of time an associated wasteful operation is processed in the database.
9. The method of claim 5, further comprising determining one or more rules that are used to determine when a wasteful operation is being performed.
10. A method for quantifying an impact of wasteful operations in a database, the method comprising:
determining one or more operations that are determined to be wasteful when performed in the database;
determining when to monitor the one or more operations, wherein the one or more operations are monitored when the one or more operations are being processed in the database;
quantifying an impact of a wasteful operation; and
storing information indicative of a value that quantifies the impact of the wasteful operation for a performance problem in the database;
wherein the value measures time spent processing the wasteful operation in the database;
generating an accumulated value associated with the wasteful operation based on the time value; and
wherein generating the accumulated value associated with the wasteful operation comprises adding the value to another value associated with another operation, the another operation being a database operation that includes the wasteful operation and at least one or more other wasteful operations.
11. A computer program product having a computer-readable medium storing a set of code module which when executed by a processor of a computer system cause the processor to quantify an impact of wasteful operations in a database, the computer program product comprising:
code for receiving one or more operations that are determined to be wasteful when performed in the database;
code for detecting when one or more wasteful operations are being performed in the database;
code for recording a time value that is spent on a wasteful operation; and
code for generating information that quantifies the impact of a performance problem in the database based on the time value; storing the time value, wherein the time value is used to quantify the impact of a performance problem in the database;
code for generating an accumulated time value associated with the wasteful operation based on the time value; and
12. The computer program product of claim 11, wherein the time value measures time spent processing the wasteful operation in the database.
code for generating information indicative of the impact of the wasteful operation on the database based on the time value.
14. The computer program product of claim 11, further comprising code for analyzing the performance problem using the time value.
15. A computer program product having a computer-readable medium storing a set of code modules which when executed by a processor of a computer system cause the processor to quantify wasteful operations in a database, the computer program product comprising:
code for receiving a plurality of operations that are determined to be wasteful when performed in the database;
code for detecting when wasteful operations are being performed in the database;
code for recording a plurality of time values for time spent on performing each of the wasteful operations; and
code for storing the plurality of time values, wherein a time value is associated with a wasteful operation for which the time value was recorded, wherein the time values are used to quantify a performance problem in the database;
16. The computer program product of claim 15, wherein the impact of the wasteful operation is known before a performance problem quantified by the time value associated with the wasteful operation is fixed.
18. The computer program product of claim 17, wherein each time value associated with a wasteful operation measures the amount of time the wasteful operation is processed in the database.
US10775513 2003-09-05 2004-02-09 Time model Active 2025-10-30 US7376682B2 (en)
US50086403 true 2003-09-05 2003-09-05
US10775513 US7376682B2 (en) 2003-09-05 2004-02-09 Time model
US20050055672A1 true US20050055672A1 (en) 2005-03-10
US7376682B2 true US7376682B2 (en) 2008-05-20
ID=34228773
US10775513 Active 2025-10-30 US7376682B2 (en) 2003-09-05 2004-02-09 Time model
US (1) US7376682B2 (en)
Doppelhammer et al., "Database Performance in the Real World: TPC-D and SAP R/3," 1997, ACM, p. 123-124.
Hagmann et al., "Performance Analysis of Several Back-End Database Architectures," 1986, ACM, p. 1-26.
IBM Technical Disclosure Bulletin, "Software Monitor for Performance Tuning a Virtual Memory Computer System", May 1, 1973, vol. No. 15, Issue No. 12, pp. 3730-3733.
RYU et al., "Analysis of Database Performance with Dynamic Locking," 1990, ACM, p. 491-523.
Stonebraker et al., "Performance Enhancements to a Relational Database System," 1983, ACM, p. 167-185.
US20050055672A1 (en) 2005-03-10 application
US20100223437A1 (en) 2010-09-02 Method and system for spilling from a queue to a persistent store
US20090320021A1 (en) 2009-12-24 Diagnosis of application performance problems via analysis of thread dependencies
US20050055381A1 (en) 2005-03-10 Active queries filter extraction
US20060294058A1 (en) 2006-12-28 System and method for an asynchronous queue in a database management system
Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:RAMACHER, MARK;WOOD, GRAHAM S.;LOAIZA, JUAN;AND OTHERS;REEL/FRAME:014983/0423;SIGNING DATES FROM 20040203 TO 20040204
Free format text: CORRECTIVE ASSIGNMENT TO CORRECT THE SPELLING OF THE ASSIGNOR ADDRESS PREVIOUSLY RECORDED ON REEL 014983 FRAME 0423;ASSIGNORS:RAMACHER, MARK;WOOD, GRAHAM S.;LOUIZA, JUAN;AND OTHERS;REEL/FRAME:015104/0006;SIGNING DATES FROM 20040203 TO 20040204