Abstract:
Disclosed is an improved method and system for implementing DBMS server performance optimization. According to some approaches, the method and system incorporates DBMS wizards recommendations with analytical queuing network models for purpose of evaluating different alternatives and selecting the optimum performance management solution with a set of expectations, enhancing autonomic computing by generating periodic control measures which include recommendation to add or remove indexes and materialized views, change the level of concurrency, workloads priorities, improving the balance of the resource utilization, which provides a framework for a continuous process of the workload management by means of measuring the difference between the actual results and expected, understanding the cause of the difference, finding a new corrective solution and setting new expectations.

Description:
CROSS-REFERENCE TO RELATED APPLICATIONS  
       [0001]     The present application claims the benefit of U.S. Provisional 60/724,447, which is hereby incorporated by reference in its entirety. 
     
    
     BACKGROUND OF THE INVENTION  
       [0002]     DBMS vendors incorporate complex parallel processing architectures to manage dynamic and constantly changing workloads, growing volume of data, increasing number of users, and implementation of the new applications. Leading DBMS products that support parallelism include Oracle 10g with GRID architecture, IBM&#39;s DB2 UDB ESE and NCR&#39;s Teradata with Massively Parallel Processing (MPP) architecture. Performance management of the concurrent Data Loading, Business Intelligence (BI) Decision Support (DSS) and Online Transaction Processing (OLTP) workloads in a parallel processing environment is difficult and brings many challenges.  
         [0003]     In a balanced parallel processing environment, the queries on many nodes are processed in parallel much faster. Change in a pattern of data access can significantly increase frequency of accessing specific data through one of the nodes and affect the balance of resource utilization. Data skewness, index and materialized views strategy, physical and virtual configuration, and selection of the OS and DBMS parameters can also affect the balance in usage of resources and performance of the applications in parallel processing environments.  
         [0004]     In an unbalanced environment, the response time of requests depends on the contention for the over-utilized devices. Queuing time for resources on the over-utilized node can slow down the response time for other queries.  
         [0005]     Wrong performance management and database tuning, priority allocation and concurrency control decisions can negatively affect the balance of utilization and level of service, customer satisfaction and business competitiveness. A DBA may spend significant amounts of time finding and fixing the problems. The time to identify and fix the problem could take days or even weeks. During this period the response time could be slow and reduced system throughput can negatively affect the business.  
         [0006]     To improve DBA productivity DBMS vendors often offer wizards and self healing solutions. IBM refers to such solutions as “autonomic computing”, Oracle calls it “self-healing” and NCR refers to this as “Teradata Active Systems Management” (TASM). A DBA typically should provide a set of SQL as input to the Wizard. Wizards recommend creation of new indexes and materialized views and estimate savings in CPU time and number of I/O operations only for the set of analyzed SQL. These recommendations have several limitations. If the DBA sends all SQL processed by DBMS to a wizard, it could take a very long time to perform an analysis. If DBA sends only SQL captured during short measurement interval, results could be inaccurate.  
         [0007]     Recommendations based on analysis of the critical SQL collected during short period of time do not guarantee that they address the continuous and most serious problems.  
         [0008]     There is a risk that recommendation is just a reaction based on occasional event.  
         [0009]     Another risk is that the implementation of recommendations can improve performance for the selected set of SQL, but potentially can negatively affect other SQL and workloads.  
         [0010]     DBMS wizard provide recommendations without setting the expectations about the expected outcome of their implementation on response time and throughput for each of the workloads. As a result, after implementation of the wizard&#39;s recommendations a DBA may not know if the goal is achieved or not. The DBA cannot compare multiple options and find the most effective solution, thus satisfying Service Level Objectives (SLOs) for different workloads.  
       SUMMARY OF THE INVENTION  
       [0011]     According to some embodiments, disclosed is an iterative method and system for implementing DBMS server performance optimization, incorporating DBMS wizards recommendations with analytical queuing network models for purpose of evaluating different alternatives and selecting the optimum performance management solution with a set of expectations, enhancing autonomic computing by generating periodic control measures which include recommendation to add or remove indexes and materialized views, change the level of concurrency, workloads priorities, improving the balance of the resource utilization, which provides a framework for a continuous process of the workload management by means of measuring the difference between the actual results and expected, understanding the cause of the difference, finding a new corrective solution and setting new expectations.  
         [0012]     One objective of an embodiment of the invention is to incorporate DBMS wizards with analytical models enabling evaluation of the alternatives and implement the optimization algorithm enabling continuous proactive performance management and control based on comparison of the actual results with expected and generating proactive corrective actions. This process includes continuous data collection, workload characterization, performance analysis and identification of the significant changes in response time and throughput, changes in pattern of usage of resources and changes in pattern of usage of data. Identification of the significant changes based on applying statistical process control narrows down the scope of analysis and identifies critical SQL which are sent to the DBMS wizards. Output from DBMS wizards is used as input by analytical models to predict the impact of each of the recommendations on performance of each of the business workload.  
         [0013]     Modeling results and Service Level Objectives (SLOs) of each workload are used by the optimization module to compare different options, generate recommendations and set up expectations. After implementation of the recommendations, actual results are compared with expected and root-cause analysis and modeling results again are used to generate corrective actions.  
         [0014]     Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims. Both the foregoing general description and the following detailed description are exemplary and explanatory, and are not intended to be limiting as to the scope of the invention.  
     
    
     BRIEF DESCRIPTION OF THE DRAWINGS  
       [0015]     The accompanying drawings are included to provide a further understanding of the invention and, together with the Detailed Description, serve to explain the principles of the invention.  
         [0016]      FIG. 1  shows an interaction between DBMS Wizard and prediction model according to some embodiments of the invention.  
         [0017]      FIG. 2  shows a data collection process periodically extracting OS and DBMS measurement data according to some embodiments of the invention.  
         [0018]      FIG. 3  shows workload characterization functions according to some embodiments of the invention.  
         [0019]      FIGS. 4   a  and  4   b  illustrate how performance measurement data and performance prediction results are used to set realistic SLO according to some embodiments of the invention.  
         [0020]      FIG. 5  shows how Index wizards are used to analyze critical SQL requests to generate tuning recommendations according to some embodiments of the invention.  
         [0021]      FIG. 6  illustrates how performance prediction results are used to set expectations, compare actual results with expected and recommend corrective actions according to some embodiments of the invention.  
         [0022]      FIG. 7  shows how the difference between actual and expected results is used to identify and measure the number of exceptions according to some embodiments of the invention.  
         [0023]      FIG. 8  shows a sequence of steps of a continuous process of proactive performance management based on using Wizard, Performance Prediction and Optimization Engine according to some embodiments of the invention.  
         [0024]      FIG. 9  illustrates an example computing architecture with which the invention may be implemented.  
     
    
     DETAILED DESCRIPTION  
       [0025]     The interaction between components of an embodiment of the invention and standard systems management functions is graphically shown on  FIG. 1 . The lines marked as “Blue” lines on  FIG. 1  represent a typical framework of continuous data collection by a DBMS and periodic usage of DBMS Wizards  104  by a DBA to identify tuning opportunities and to tune the DBMS. Examples of DBMS Wizards  104  include Oracle, DB2 UDB and Teradata Wizards. The Wizards  104  analyze SQL requests selected by the DBA and generate a list of recommendations with estimated benefits in reducing CPU time and number of I/O operations.  
         [0026]     In the present embodiment, IBM, Oracle and Teradata Wizard&#39;s recommendations do not include expectations how their implementation will affect the response time and throughput for individual workloads. The recommendations can potentially improve performance for set of SQL of one of the workloads, but can negatively affect another workloads performance. Without expectations, a DBA cannot verify if the implementation of the Wizard&#39;s recommendations achieved the goal or not. If results are not satisfactory it takes time to correct the problem. In a meantime, slow response time and reduced throughput can cause lost business and reduction in number of the business transactions processed by the server.  
         [0027]     SLOs  103 , evaluation plan  105  and modeling results of the Prediction Engine  106  are used iteratively by Optimization Engine  108  to evaluate different options and develop Control Recommendations  109  with the most effective measures. Performance prediction results of the Prediction engine  106  set up the expectations  110 . The difference between the Actual measurement results  101  and Expected  110 , described in evaluation plan  105 , Prediction engine  106  and Optimization engines  108  generate new corrective measures  109 , which are implemented automatically or after approval of DBA  107 .  
         [0028]     Incorporation of the DBMS Wizard  104  and the inventive embodiment(s), e.g., a predictive performance management software, provides a framework for continuous proactive performance management. As shown on  FIG. 1  the lines marked as “Blue” represent activities controlled by the DBA  107  during process of collecting data, usage of DBMS wizards  104  and implementing Wizard&#39;s recommendations. The lines marked as “Green” represent interactions with invention including extraction of the measurement data for workload characterization, identification of the critical SQL and send them as input to DBMS Wizard  104  to get recommendations, predict the impact of the recommendations. The lines marked as “Red” represent activity of the optimization engine, which use the SLO to identify the most effective measures and generate and advice. The lines marked as “Brown” show that actual results are compared with expected results, and the comparison result (e.g., the difference between the two results) is used to generate a plan with an objective to invoke a model and find optimum corrective measure. Each of the elements from  FIG. 1  is described in more detail below.  
         [0000]     Measure ( 101 )  
         [0029]     Measurement data  101  collected on DBMS servers  100 S are used for Workload Characterization  102 . Results of the Workload Characterization  102  are used to identify critical SQL affecting critical workloads performance. Critical SQL are sent as input to the DBMS Wizard  104 . The recommendations of the Wizard  104  are used to create a Plan of evaluation  105  for Prediction Engine  106 . Prediction Engine  106  predicts the impact of Wizard  104 &#39;s recommendations on response time and throughput of all workloads.  
         [0030]     The objective of the Measure (Data Collection) element  101  from  FIG. 1  is to collect data characterizing the utilization of resources, frequency of accessing data and other characteristics, including response time of SQL. The functions of the Data Collection ( 101 ) element are shown in more detail in  FIG. 2 .  
         [0031]     At  201 . 1 , data is periodically extracted from the OS and DBMS. The measurement data from  201 . 1  from the OS level is used to characterize overall server CPU utilization and I/O rate. The DBMS data characterize performance characteristics of SQL and sessions statistics.  
         [0032]     Pursuant to  201 . 2 , information is periodically extracted from catalog. The information from catalog provides information about tables, their sizes, views, columns, indexes. This action of extracting information from catalog is used to characterize databases, tables and views used by SQL. The data collection process also extracts data from a volume manager to find location of the database objects on physical disks.  
         [0033]     At  201 . 3 , SQL collection is enabled to extract SQL. Different DBMS products have different methods for extracting SQL. For example, the Oracle DBMS product allows one to extract data from SQLArea. The DB2 UDB ESE products allows one to capture SQL from SQL Dynamic Cache. The Teradata product allows one to extract SQL from Data Base Query Log.  
         [0000]     Workload Characterization ( 102 )  
         [0034]     The objective of Workload Characterization  102  element of  FIG. 1  is to aggregate activity within DBMS server into several workloads representative of different business processes or line of business and build performance, resource utilization and data usage profile for each of the workloads. Workload characterization results are used for performance evaluation and also as input to performance prediction engine.  
         [0035]     DBAs and analysts can use commercial performance management and performance prediction tools from IBM&#39;s Performance Expert, BMC Perform Predict, SAS Institute and others during Workload Characterization to identify major types of workload, create workload aggregation rules to aggregate measurement data and build performance, resource and data usage profiles for each workload. Results of the workload characterization are used by DBAs and performance analysts to identify critical workloads, and SQL and provide them as input by the wizard.  
         [0036]     Workload Characterization results provide a view on service provided for each of the line of business. Performance analysis results using business workload prospective allow to establish realistic SLO, SLA and incorporate Service Level Management based on different service demand and requirements for each line of business.  
         [0037]     Measurement data ( 101 ) provide an input for workload characterization. Performance, Resource utilization, Usage of data profiles provide an output of workload characterization.  
         [0038]     The functions of the Workload Characterization ( 102 ) element are shown in more detail in  FIG. 3 .  
         [0039]     At  202 . 1  major lines of business are identified and documented. Each line of business should represent important part of business. All performance evaluation and performance prediction results will refer to each workload performance.  
         [0040]     At  202 . 2  workload aggregation rules are built. Workload aggregation rules allow aggregatation of SQL activity by User, Program, Machine or Module name into specific workloads. These rules will be automatically applied, e.g., every hour during the workload characterization process. During performance evaluation, the analyst will be able to use root cause analysis to identify critical workload and critical element(s) of the workload, e.g., user information, further critical SQL, and other information which relating to elements which cause or potentially will cause the performance degradation.  
         [0041]     At  202 . 3  the physical and virtual configuration of the DBMS server is verified. The workload resource utilization profile is built based on collected measurement data on specific physical and virtual configuration(s).  
         [0042]     At  202 . 4  the workloads&#39; aggregation rules are applied against measurement data. Each captured SQL request and corresponding statistics is assigned to a specific workload.  
         [0043]     At  202 . 5  the workloads&#39; performance, resource utilization and data usage profiles are built and saved. The workload characterization process is performed periodically, for example, every hour. Analysis of the performance trend in usage of resources and data provides a valuable information for performance tuning. The workloads&#39; profiles are also used as input for prediction engine to evaluate the impact of the proposed changes.  
         [0000]     Establish Service Level Objectives (SLO) ( 103 )  
         [0044]     The objective of the SLO  103  element from  FIG. 1 . is to set realistic SLO. DBAs and performance analysts present to management and business users performance management reports with historical trend and performance prediction results showing the relationship between Service Level Objectives (SLOs) and corresponding hardware configuration requirements. Analysis of relationship between SLO and the Total Cost of Ownership (TCO) allow setting realistic SLO. The functions of the SLO ( 103 ) element are shown in more details in  FIG. 4   a.    
         [0045]     At  201  OS and DBMS performance measurement data are collected using standard OS and DBMS methods.  
         [0046]     At  202  results of performance measurements and monitoring are used for service level reporting. Standard performance management reports reflecting trend in response time, throughput and usage of resources be different workloads are generated and analyzed to understand the past and present level of service provided to the individual line on business.  
         [0047]     At  203  service level reporting results are used for planning SLA. Information about expected growth in number of users, increase volume of data and plan to implement new applications is presented by IT users. IT representative document the level of service they are planning to provide, based on expected workload and planned hardware upgrades.  
         [0048]     Iterative modeling and evaluation of the minimum configuration required to support specific SLA is used to find a relationship between specific SLO and hardware configuration requirements. Hardware, Software and People Cost are major component of the Total Cost of Ownership (TCO).  
         [0049]      FIG. 4   b  illustrates a model that shows a relationship between TCO and SLO for an example implementation.  401  represents a point at which TCO is estimated for a very aggressive SLO.  402  represents a point at which TCO is estimated for a non-aggressive SLO.  
         [0050]     Returning back to  FIG. 4   a , at  204 , a comparison is performed of the desired SLO and predicted required hardware configuration and corresponding TCO to find a compromise between performance requirements and expected hardware requirements and to justify and implement realistic SLA.  
         [0000]     Get DBMS Wizard Recommendations ( 104 )  
         [0051]     The objective of the Wizard  104  element from  FIG. 1  is to get and save DBMS. Wizards tuning recommendation. IBM&#39;s Design Adviser, NCR Index Wizard, Oracle&#39;s Wizard use critical SQL as input and generate the following recommendations:  
         [0052]     New Index Candidates  
         [0053]     Unused Indexes  
         [0054]     Materialized Views Candidates  
         [0055]     Summarization Candidates  
         [0056]     Unused Summary Tables  
         [0057]     Denormalization candidates  
         [0058]     Unused Tables  
         [0059]     The functions of the Wizard ( 104 ) element are shown in more detail in  FIG. 5 .  
         [0060]     At  204 . 1  the Wizards  104  generate list of New Index candidates. The Wizard  104  also estimates the savings in CPU service time and reduction in number of I/O operations as a result of the implementing new indexes. This information is used as input to Prediction Engine  106  to estimate the impact of proposed measures on each of workloads&#39; response time and throughput.  
         [0061]     At  204 . 2  a list of unused indexes is generated, e.g., by an Adviser. Dropping unused indexes can release significant amount of disk space and reduce cost of index maintenance  
         [0062]     At  204 . 3  the Wizard  104  generates a list of Materialized Views candidates. The Wizard  104  also estimates the savings in CPU service time and reduction in number of I/O operations as a result of the implementing new Materialized Views. This information is used as input to Prediction Engine  106  to estimate the impact of proposed measures on each of workloads&#39; response time and throughput.  
         [0063]     At  204 . 4  the Adviser builds a list of unused tables or dormant data. Dropping unused tables can release significant amount of disk space and reduce TCO.  
         [0064]     At  204 . 5  the Adviser generates a list of unused Materialized Views. Dropping unused Materialized Views can release significant amount of disk space and reduce cost of Materialized Views maintenance.  
         [0000]     Plan and Evaluation Scenario ( 105 )  
         [0065]     The objective of Plan  105  element from  FIG. 1  is to describe Wizard&#39;s recommendations as input for Prediction Engine  106 . DBA Wizards  104  provide a list of performance tuning recommendations with estimated reduction of CPU time and number of I/O operations as a result of implementation each of the recommendations for analyzed group of SQL.  
         [0066]     The Prediction Engine  106  predicts the impact of the Wizard&#39;s recommendation implementation on performance of all workloads.  
         [0067]     Evaluation Plans are used by modeling products, e.g., TeamQuest, BMC Perform Predict, HyPerformix and others, to describe “what if” questions which should be addressed by the model.  
         [0068]     Each workload includes many SQL which will not be affected after implementation of the specific recommendation. The average CPU time, average number of I/O operations per workload, should be recalculated based on difference in CPU time consumed by all SQLs of the workload and savings in CPU time and number of I/O operations saved as a result of implementing the recommendation.  
         [0000]     Performance Prediction Engine ( 106 )  
         [0069]     The objective of the Prediction Engine  106  element from  FIG. 1  is to predict performance and compare proposed measures, including different wizard&#39;s DBA tuning recommendations, on response time and throughput for different workloads.  
         [0070]     The Performance Prediction Engine  106  is based on analytical queueing network models or simulation models that predict the impact of the proposed changes on performance of each workload. For example, BMC Perform Predict and TeamQuest use analytic models and HiPerformix uses simulation modeling technique. Models are used to predict the impact of the different options and find the optimum solution by predicting the impact of the DBMS wizard recommendations on performance, response time, throughput and usage of resources for each of the workloads and recommend the most effective changes. Performance prediction allow one to compare the impact of the priority change on the performance of each workload and to select the optimum priority setting to satisfy SLO and SLA for different workloads. Modeling predicts the impact of the changing the level of parallelism on the performance of each workload and recommends what should be changed.  
         [0071]     Performance prediction show the expected impact of changing the level of concurrency in number of users and concurrent jobs on performance of each workload. Modeling results can be used to predict the impact of the memory redistribution and to recommend the optimum solution.  
         [0072]     Some advantages of performance prediction is in organizing proactive measures, ability to evaluate the impact of proposed hardware upgrade, performance tuning, workload and data growth and selection of the best solution based on expected results. It minimizes the risk of surprises, reduces uncertainty, provides a framework for continuous, and provides proactive closed loop management.  
         [0073]     The functions of the Prediction ( 106 ) element are shown in more detail in  FIG. 6 .  
         [0074]     At  207 . 1  seasonal peaks are analyzed and a representative modeling interval is selected to build a baseline model. Typically, planning is done for the peak activity during day time and night time.  
         [0075]     At  207 . 2  the model is built and calibrated. Commercial modeling tools use results of the workload characterization, workload forecasting and performance evaluation scenarios to predict the impact of the workload growth and justify hardware upgrade and database tuning measures needed to support business needs in a most effective manner.  
         [0076]     At  207 . 3  an evaluation scenario is used and the model is evaluated. The evaluation scenario of commercial modeling tools includes assumptions about expected workload growth and expected increase in volume of data processed by each workload.  
         [0077]     At  207 . 4  different changes are evaluated and future potential performance bottlenecks are identified. Commercial modeling tools typically use queuing network models to predict the impact of the planned changes.  
         [0078]     At  207 . 5  the impact of changes on response time, service/queue time for each workload is estimated. In some implementations, the most important criteria used to evaluate level of service are response time and throughput.  
         [0079]     At  207 . 6  the CPU Utilization, I/O Rate, disk space usage by each workload is predicted. The resource utilization profile is used to detect significant changes in profile of application caused by change of functionality or increased complexity of SQL.  
         [0080]     At  207 . 7  a report is prepared that includes, for example, performance prediction results, assumptions, findings and recommendations.  
         [0081]     At  207 . 8  modeling results and assumptions about expected growth are used to establish realistic expectations. Actual measurement results are compared with expected, and if necessary, corrective actions are recommended. Prediction results minimize the risk of surprises, reduces uncertainty, provides a framework for continuous, pro-active closed loop management.  
         [0000]     Implementation of the Recommendations ( 107 )  
         [0082]     The Implement ( 107 ) element from  FIG. 1  implements recommendations how to change workloads priorities, create indexes, materialized views or software parameters to reduce the difference between actual results and expected  109 . Recommendation can be approved by DBA or applied automatically.  
         [0000]     Optimization ( 108 )  
         [0083]     The Optimization  108  element from  FIG. 1  runs the prediction engine  106  iteratively to evaluate different wizard&#39;s recommendations and to identify a set of solution(s) having the biggest positive impact on major workloads.  
         [0000]     Control Recommendations ( 109 )  
         [0084]     The Control  109  element from  FIG. 1  keeps the list of most effective wizard&#39;s recommendations evaluated by the Prediction engine  106  and Optimization element  108  and is capable of improving the performance of the most critical workloads with set expectations  110 .  
         [0000]     Set Expectations ( 110 )  
         [0085]     The Expectation  110  element from  FIG. 1  sets expectation based on modeling results for each workload. Expectations include, for example, expected arrival rate, response time, CPU service time, number of I/O operations, CPU per request, number of I/Os per request for each of the modeling periods.  
         [0000]     Difference Between Actual Results and Expected ( 111 )  
         [0086]     The objective of A2E element  111  from  FIG. 1  to find the difference between Actual results and Expected. If actual results are significantly different from the expected than the root cause analysis determines the reason of the difference, and identifies the new set of critical SQL which are sent to the DBMS wizard  106  for analysis to recommend corrective action  109 .  
         [0087]      FIG. 7  illustrate comparison of the actual results with expected  111  in more details. This figure shows how to identify the continuous problem, e.g., based on number of exceptions during specific interval of time such as a one week interval.  
         [0088]     The results of the performance prediction at  701  show the expected impact of the workload growth. High limit of the acceptable SPC performance (e.g., 90 percentile)— 702  is not reached, however if the number of exceptions  703  per week (2) is greater than acceptable, it triggers the action—start of performance analysis to justify corrective actions.  
         [0089]     Root cause analysis applied to identify SQL causing exceptions is based on the following logic. First, the process analyzes the response time components and finds the biggest component(s), e.g., the CPU service time or queuing time. The process then determines which component is larger, e.g., CPU, I/O and interprocess communication time. The process identifies elements that may cause the problem(s), e.g., critical SQL and DB objects, including tables, views, materialized views, summary tables. Them a new evaluation plan is created and performance prediction results are used to find the best way to fix a problem.  
         [0000]     Illustrative Example of Incorporating Oracle SOL Access Adviser with Analytical and Performance Optimization Engines  
         [0090]     In one illustrative example of the invention shown in  FIG. 8 , the Oracle SQL Access Advisor  803  (Wizard) is invoked daily. The Oracle SQL Access Advisor uses critical SQL  802 , captured during data collection  801  and generates recommendations  804  based on analysis of SQL and SQL metrics such as CPU time, buffer gets, disk reads, number of rows processed, executions, elapsed time from SQL Cache. The recommendations from the Oracle SQL Access Adviser are saved daily in a repository.  
         [0091]     Periodically, the data from the repository of wizard&#39;s recommendations is accessed, in which all daily recommendations are sorted by potential savings in CPU and I/O. An Evaluation Plan  805  is created with an objective to predict the impact of the proposed tuning measures on performance of all workloads ( 806 ). Some of recommendations can improve performance for one of the workloads, but potentially negatively affect the performance of other workloads.  
         [0092]     The implementation uses a baseline model based on the peak hour activity to predict the impact of the Wizard&#39;s Index and Materialized View recommendations on response time and throughput for each of the workloads. The Optimization engine  807  invokes the Prediction Engine  806  iteratively to evaluate all Wizard&#39;s recommendations  804  and their impact on performance of each of the workloads. The Optimizer uses SLO and performance prediction results to select the recommendations  808  with the biggest positive impact on workloads&#39; response time and systems throughput.  
         [0093]     The final set of recommendations and corresponding performance prediction results are saved. They will be used as expectations. Recommendations to change priority or level of concurrency can be applied automatically. Creation of the new indexes or materialized views recommendations may require DBA approval  809  prior to the implementation  810 .  
         [0094]     After implementation of the wizard&#39;s recommendations, the actual performance measurement data will be compared with corresponding expectations (A2E)  811 . If actual results are significantly worse than expected, a root cause analysis will be performed with objective to identify the cause of the difference and to identify critical SQL causing the difference between actual and expected results. If critical SQL causing the difference between actual and expected results can be identified, than they will be processed by Wizard again with objective to create a new set of corrective recommendations.  
       System Architecture Overview  
       [0095]      FIG. 9  is a block diagram of an illustrative computing system  1400  suitable for implementing an embodiment of the present invention. Computer system  1400  includes a bus  1406  or other communication mechanism for communicating information, which interconnects subsystems and devices, such as processor  1407 , system memory  1408  (e.g., RAM), static storage device  1409  (e.g., ROM), disk drive  1410  (e.g., magnetic or optical), communication interface  1414  (e.g., modem or Ethernet card), display  1411  (e.g., CRT or LCD), input device  1412  (e.g., keyboard), and cursor control.  
         [0096]     According to one embodiment of the invention, computer system  1400  performs specific operations by processor  1407  executing one or more sequences of one or more instructions contained in system memory  1408 . Such instructions may be read into system memory  1408  from another computer readable/usable medium, such as static storage device  1409  or disk drive  1410 . 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/or software. In one embodiment, the term “logic” shall mean any combination of software or hardware that is used to implement all or part of the invention.  
         [0097]     The term “computer readable medium” or “computer usable medium” as used herein refers to any medium that participates in providing instructions to processor  1407  for execution. Such a medium may take many forms, including but not limited to, non-volatile media and volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as disk drive  1410 . Volatile media includes dynamic memory, such as system memory  1408 .  
         [0098]     Common forms of computer readable media includes, for example, floppy disk, flexible disk, hard disk, magnetic tape, any other magnetic medium, CD-ROM, any other optical medium, punch cards, paper tape, any other physical medium with patterns of holes, RAM, PROM, EPROM, FLASH-EPROM, any other memory chip or cartridge, or any other medium from which a computer can read.  
         [0099]     In an embodiment of the invention, execution of the sequences of instructions to practice the invention is performed by a single computer system  1400 . According to other embodiments of the invention, two or more computer systems  1400  coupled by communication link  1415  (e.g., LAN, PTSN, or wireless network) may perform the sequence of instructions required to practice the invention in coordination with one another.  
         [0100]     Computer system  1400  may transmit and receive messages, data, and instructions, including program, i.e., application code, through communication link  1415  and communication interface  1414 . Received program code may be executed by processor  1407  as it is received, and/or stored in disk drive  1410 , or other non-volatile storage for later execution.  
         [0101]     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. For example, the above-described process flows are described with reference to a particular ordering of process actions. However, the ordering of many of the described process actions may be changed without affecting the scope or operation of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than restrictive sense.