US8903801B2 - Fully automated SQL tuning - Google Patents

Fully automated SQL tuning Download PDF

Info

Publication number
US8903801B2
US8903801B2 US12188975 US18897508A US8903801B2 US 8903801 B2 US8903801 B2 US 8903801B2 US 12188975 US12188975 US 12188975 US 18897508 A US18897508 A US 18897508A US 8903801 B2 US8903801 B2 US 8903801B2
Authority
US
Grant status
Grant
Patent type
Prior art keywords
query language
database query
tuning
language statements
subset
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active, expires
Application number
US12188975
Other versions
US20090077016A1 (en )
Inventor
Peter Belknap
Benoit Dageville
Karl Dias
Khaled Yagoub
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Oracle International Corp
Original Assignee
Oracle International Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Grant date

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30442Query optimisation
    • G06F17/30448Query rewriting and transformation
    • G06F17/30463Plan optimisation
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30289Database design, administration or maintenance
    • G06F17/30306Database tuning
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30442Query optimisation
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30477Query execution
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30533Other types of queries
    • G06F17/30536Approximate and statistical query processing
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30861Retrieval from the Internet, e.g. browsers
    • G06F17/30864Retrieval from the Internet, e.g. browsers by querying, e.g. search engines or meta-search engines, crawling techniques, push systems
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99932Access augmentation or optimizing

Abstract

Techniques are provided for a fully-automated process for tuning database query language statements that selects database query language statements for tuning, tunes the database query language statements and generates tuning recommendations, tests the tuning recommendations, and determines whether to implement the tuning recommendations based on the test results. The fully-automated tuning process may also automatically implement certain tuning recommendations and monitor the performance of the database query language statements for which tuning recommendations have been implemented.

Description

CROSS-REFERENCE TO RELATED APPLICATIONS; BENEFIT CLAIM

This application claims benefit of Provisional Appln. 60/972,681, filed Sep. 14, 2007, the entire contents of which is hereby incorporated by reference as if fully set forth herein, under 35 U.S.C. §119(e).

FIELD OF THE INVENTION

The present invention relates to the field of electronic database management.

BACKGROUND

SQL performance is a very critical component of the overall performance of a database system. Although database applications are tested and verified before delivery to customers, the performance of SQL statements in the database applications is difficult to predict. Every time an SQL statement is executed, a different execution plan may be generated by the query optimizer. Often, the execution plan is one that has not been tested before and one whose performance has not been analyzed and verified. Should the query optimizer choose a plan that does not give the best performance, the performance of the system as a whole may suffer.

SQL tuning is a process where the performance of certain SQL statements is analyzed and changes are implemented in an effort to improve the performance of the SQL statements. However, SQL tuning is complex and time-consuming, requiring expertise in query optimization, access design, and SQL design. Often, this difficult task is performed manually by the database administrator (DBA), who must also re-tune SQL statements as the workload set on the database system and the database system itself change over time.

An SQL Tuning Advisor, as described in “AUTOMATIC SQL TUNING ADVISOR”, application Ser. No. 10/936,778, filed Sep. 7, 2004, the entire contents of which is hereby incorporated by reference as if fully set forth herein, provides the DBA with the functionality of tuning SQL statements by generating tuning recommendations for the SQL statements. The SQL Tuning Advisor can, for example, perform access path analysis and recommend creating new indexes, perform statement structure analysis and recommend better written statements, and perform data statistics analysis and recommend replacing missing or stale data statistics with updated statistics.

However, using the SQL Tuning Advisor still requires significant time, effort, and involvement from DBAs. In order to use the SQL Tuning Advisor, DBAs are required to find SQL statements that are exhibiting poor performance as candidates for tuning, feed the candidate SQL statements into the SQL Tuning Advisor, and manually evaluate the results and tuning recommendations generated by the SQL Tuning Advisor to decide which tuning recommendations to implement. The SQL tuning process implemented by the SQL Tuning Advisor is itself time-consuming and, on a busy system, may need to be scheduled by the DBA. Furthermore after deciding which tuning recommendations to implements, DBAs must also monitor the performance of the database system after implementation of the tuning recommendations to check that performance gains have been achieved according to expectations. If the performance has instead deteriorated, DBAs must then perform further analysis to respond to the problem. To prepare for such a situation, DBAs also need to keep a record of prior tuning actions. Finally, this tuning process must be repeated periodically because both workload sets and database systems may change over time.

Thus, there is a need for a fully automated process for tuning SQL statements that require minimal time and effort from database administrators.

The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:

FIG. 1 is a flow diagram that illustrates steps in a fully-automatic process for tuning database query language statements.

FIG. 2 is a diagram of a computer system that may be used in an implementation of an embodiment of the present invention.

DETAILED DESCRIPTION

In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

Overview

Although the embodiments of the invention are described using the term “SQL”, the invention is not limited to just this particular database query language, and may be used in conjunction with other database query languages and constructs.

A process performs the following steps to provide users of database systems with a fully-automated process for tuning database query language (e.g., SQL) statements:

    • (1) identify SQL statements that may adversely impact the performance of the database system;
    • (2) tune the identified SQL statements to generate tuning recommendations for the SQL statements;
    • (3) test the recommendations and gather data about the performance of the SQL statements with the tuning recommendations incorporated;
    • (4) implement the tuning recommendations that meet certain performance improvement and/or confidence criteria; and
    • (5) monitor the database system after implementation of the tuning recommendations to measure the performance of SQL statements for which tuning recommendations have been implemented.

Additionally, the fully-automated tuning process runs in a controlled environment so that the resource-intensive tuning steps in the process do not impact other activities on the database system.

Finally, the fully-automated tuning process generates detailed reports that describe how the SQL statements are tuned and which tuning recommendations were implemented and that can be provided to DBAs.

Identifying SQL Statements for Tuning

In the fully-automated tuning process, candidate SQL statements for tuning are automatically identified. The candidate SQL statements identified for tuning are SQL statements that have been determined to impact the performance of the database system the most and for which the SQL Tuning Advisor's built-in algorithms are the most likely to help. The identification of candidate SQL statements for tuning is performed in step 102 of flow diagram 100 in FIG. 1.

In step 102, SQL statements are identified from a workload set, which is a collection of SQL statements and performance information associated with the SQL statements. According to one embodiment, the workload set is stored as a SQL Tuning Set. SQL Tuning Sets are described in “SQL TUNING SETS”, application Ser. No. 10/936,449, filed on Sep. 7, 2004, the entire contents of which are hereby incorporated by reference. An SQL Tuning Set is a persistent database object that stores one or more database query language statements and performance information for each statement. The performance information may include, for example, execution measurements and execution context of each statement.

The SQL statements and associated performance information may come from a source such as an Automatic Workload Repository (AWR), which stores the statements executed during a specific period of time and the performance information associated with the executed statements. Automatic Workload Repositories (AWRs) are described in further detail in “AUTOMATIC WORKLOAD REPOSITORY BATTERY OF PERFORMANCE STATISTICS”, application Ser. No. 10/934,344, filed on Sep. 3, 2004, the entire contents of which are hereby incorporated. According to one embodiment, the AWR collects and stores SQL statements and associated performance information from the past week and at an interval of one hour. The SQL statements collected by the AWR may include bind values and other critical components of the SQL's compilation environment, such as optimizer parameter values. The performance information associated with the SQL statements and collected by the AWR may include: total time spent in execution, CPU time, I/O time, the number of I/Os, and the frequency of execution.

The data collected by the Automatic Workload Repository (AWR) is analyzed in step 102 to determine which SQL statements fall within the following four categories:

(1) SQL statements that are high-load over the past week;

(2) SQL statements that are high-load over any day in the past week;

(3) SQL statements that are high-load over any hour in the past week; and

(4) SQL statements that have a high response time.

The first category of SQL statements includes SQL statements that are high-load over the past week. These are SQL statements that have the highest cumulative values of execution statistics, where each statistic represents a total amount of resources consumed over multiple executions of the SQL statement. Since one goal of performance tuning is to decrease the total amount of time spent in executing a workload set, the execution times of the SQL statements are key performance metrics. The cumulative combination of execution time and frequency of execution serves as a good proxy for measuring how significant an SQL statement is in a workload set. An SQL statement whose execution time (e.g., one-tenth of a second) is small may nonetheless constitute a large part of a workload set if the statement is executed very frequently relative to other statements (e.g., one million times a week) over the course of the past week. Thus, tuning the SQL statements that have high combinations of execution times and frequencies of execution will likely improve the overall performance of the database system.

The second category of SQL statements includes SQL statements that are high-load over any day in the past week. As just discussed, high-load statements are statements that have long execution times and that are frequently executed. The first category includes statements that are high-load over the course of a past week. However, statements that are not high-load over the entire course of the past week may nonetheless significantly affect the performance of the database system. For example, a particular SQL statement may be executed only on Mondays. On Mondays, however, this particular SQL statement may be executed very frequently and may have long execution times every time it is executed. Thus, while this particular statement may not be one of the highest load statements over the course of an entire week, this particular statement may nonetheless significantly impact the performance of the database system on Mondays. The second category of high-load SQL statements captures statements like this particular statement for tuning.

The third category includes SQL statements that are high-load over any hour in the past week. The first and second categories just discussed capture SQL statements that are high-load over the course of the past week or high-load over the course of a day in the past week, but do not capture SQL statements that are high-load over an hour in the past week. For example, a particular SQL statement may be executed on only Tuesdays, and only from ten o'clock in the morning to eleven o'clock in the morning. This SQL statement is thus unlikely to be included in the first two categories. Nonetheless, if this particular SQL statement has a long execution time and is frequently executed during the one-hour interval between ten o'clock and eleven o'clock on Tuesday, it may significantly impact the performance of the database system during that one-hour interval. Therefore, tuning this particular SQL statement may significantly improve the overall performance of the database system during key time periods. These types of SQL statements are included in the third category.

The fourth category includes SQL statements which have long execution times regardless of their frequencies of execution. An SQL statement that is not frequently executed, and thus may not be included in the first three categories, may nonetheless negatively impact the response time one user experiences if it takes a very long time to execute (e.g., thirty minutes). Therefore, tuning statements like this may significantly improve overall performance. According to one embodiment, only SQL statements that have been executed at least two times over the course of the past week are included in the fourth category of SQL statements. Limiting the inclusion of SQL statements to only those statements that have been executed at least twice ensures that no tuning resources are expended on the occasional SQL statement that has been executed only once over the course of an entire week. If an SQL statement is unlikely to be executed again, tuning the SQL statement will not be an efficient use of limited system resources.

SQL tuning is a resource-intensive process, and it is likely that not all the SQL statements identified and included in the four categories described above may be tuned within the limited amount of time and resources allotted to SQL tuning. Therefore, the SQL statements identified in the four categories may be further ranked and prioritized according to their relative importance so that during the tuning process, the SQL statements with the highest priorities are executed first.

According to one embodiment, SQL statements within a single category may be ordered by performance metrics that are being optimized by the tuning process. The performance metrics being optimized may be based on the most repeatable execution statistics. For example, CPU time and buffer gets are known to repeat very reliably over multiple executions, so the SQL statements in a single category may be ordered according to a combination of CPU time and buffer get statistics.

According to another embodiment, SQL statements are tuned in an order that interweaves statements from each of the four categories. Interweaving SQL statements from each of the four categories prevents a scenario where statements from certain categories are always tuned while statements from other categories are never tuned. Interweaving may be performed by assigning different weights to different categories. A higher weight may be assigned to a category that contains SQL statements over a larger span of time. For example, an interweaving scheme may tune three statements from the first category, two statements from each of the second and third categories, and one statement from the fourth category, before returning to the first category to select further statements for tuning.

According to another embodiment, a history of SQL statements that have been tuned recently is kept. This history contains information about which SQL statements have been tuned during a specific period of time, such as the preceding week or the preceding month. An SQL statement that has been tuned recently (i.e., the SQL statement is contained in the history) is automatically not identified for tuning because it is unlikely that an SQL statement that has already been tuned recently will benefit from further tuning. However, an SQL statement that has been tuned recently may nonetheless be identified for tuning if the SQL statement meets another criterion. For example, if the SQL statement's execution statistics indicate that the SQL statement's performance has deteriorated since the last time that the SQL statement has been tuned, then this indicates that conditions in the database system that affect the SQL statement may have changed since the last time that the SQL statement was tuned, and that retuning of the SQL statement may be beneficial.

Tuning Identified SQL Statements

Once the SQL statements have been identified, they are tuned in order of priority. The tuning of candidate SQL statements is performed in step 104 in FIG. 1. According to one embodiment, the fully automated tuning process feeds the SQL statements, one-by-one and in order of priority, to the SQL Tuning Advisor, which then generates a set of tuning recommendations for each SQL statement.

According to one embodiment, the SQL Tuning Advisor uses the database system's query optimizer that is set to operate in tuning mode. Normally, the query optimizer of a database system compiles SQL statements and generates execution plans. In normal mode, the query optimizer operates with very strict time constraints, usually a fraction of a second, during which time it must find a good execution plan for an SQL statement. In tuning mode, however, the optimizer is under much less stringent time constraints, and can perform longer and more thorough analyses to determine whether the execution plan produced for an SQL statement in the normal mode can be further improved. The output of the query optimizer in tuning mode is not execution plans, but a set of estimates to better inform the cost-based optimizer with the information it requires for producing superior execution plans.

During tuning, the SQL Tuning Advisor performs four types of analyses: (1) statistics analysis; (2) SQL profiling; (3) access path analysis; and (4) SQL structure analysis.

In normal mode, the query optimizer relies on object statistics to generate execution plans. If the statistics are stale or missing, the execution plans that are generated may be poor. During tuning, the SQL Tuning Advisor checks the object statistics and generates recommendations to gather relevant statistics for objects with stale or missing statistics. The identification of inaccurate statistics for an SQL statement is described in further detail in “HIGH LOAD SQL DRIVEN STATISTICS COLLECTION”, application Ser. No. 10/936,427, filed Sep. 7, 2004, the entire contents of which are incorporated by reference herein.

In addition, the SQL Tuning Advisor also produces auxiliary information that includes statistics for objects with no statistics and statistic adjustment factors for different estimates made by the query optimizer. The auxiliary information may be collected by gathering additional information using sampling and partial execution techniques. According to one embodiment, this auxiliary information is stored in an object called an SQL Profile. Each SQL Profile is specific to and associated with a single SQL statement. SQL Profiles are described in further detail in “SQL PROFILE”, application Ser. No. 10/936,205, filed Sep. 7, 2004, the entire contents of which are incorporated by reference herein.

SQL Profiles store statistic adjustment factors that are not themselves statistics but that adjust the pre-existing statistics and/or estimates of statistics of database objects. For example, a cardinality estimate for a particular query predicate in a particular SQL statement may be 10%, while the actual cardinality for that particular query predicate is 30%. The SQL Profile for the particular SQL statement may then contain a cardinality adjustment factor of 3 for the particular query predicate, so that a query optimizer that is generating an execution plan for the particular SQL statement may consult the SQL Profile to multiply any pre-existing cardinality estimates for the particular predicate in the particular SQL statement by 3. As such, execution plans that are generated based on SQL Profiles will be based on more accurate statistical estimates and will therefore be more optimized.

According to one embodiment, the SQL Advisor generates tuning recommendations that recommend the acceptance of SQL Profiles. Once an SQL Profile for a particular SQL statement is accepted, the SQL Profile is stored persistently in a data dictionary that associates the SQL Profile with the particular SQL statement. After the SQL Profile is stored persistently, the next time that the particular SQL statement is analyzed by the query optimizer operating in normal mode, the execution plan for the particular SQL statement will be generated based on statistics that are adjusted by the adjustment factors stored in the SQL Profile that is associated with the particular SQL statement.

One advantage of using SQL Profiles is that, unlike stored outlines, an SQL Profile does not freeze the execution plan of an SQL statement. Because an SQL Profile contains adjustment factors for statistics, these adjustment factors continue to be relevant even when objects such as tables and indexes grow, shrink, are created or are deleted, and even when the data distribution or access path of the associated SQL statement changes. Therefore, an SQL Profile need not be regenerated frequently. After a long period of time, however, an SQL Profile may become outdated. At such times, a new SQL Profile may be generated for the associated SQL statement by running the SQL Tuning Advisor again on the associated SQL statement.

The SQL Tuning Advisor may also perform access path analysis. During access path analysis, the SQL Tuning Advisor explores whether a new index can significantly enhance the performance of an SQL statement. If such an index is identified, a tuning recommendation is generated to recommend the creation of the index.

Finally, the SQL Tuning Advisor performs SQL structure analysis. During SQL structure analysis, the SQL Tuning Advisor identifies common problems with the structure of SQL statements that can lead to poor performance, including syntactic, semantic, or design problems. Further details on SQL structure analysis are described in “SQL STRUCTURE ANALYZER”, application Ser. No. 10/936,426, filed Sep. 7, 2004, the entire contents of which is hereby incorporated by reference as if fully set forth herein. Relevant suggestions for restructuring SQL statements are generated as tuning recommendations.

Testing the Tuning Recommendations

In order to determine how the tuning recommendations generated by the SQL Tuning Advisor will affect the performance of SQL statements and to determine whether to implement the tuning recommendations on the database system, the tuning recommendations are tested in a test environment. According to one embodiment, testing and execution services such as the ones described in “TEST EXECUTION OF USER SQL IN DATABASE SERVER CODE”, application Ser. No. 12/217,249, filed on Jul. 2, 2008, the entire contents of which is hereby incorporated by reference as if fully set forth herein, are used to perform testing of the tuning recommendations.

In the test environment, the SQL statements are executed with the tuning recommendations enabled, and execution statistics such as execution time are gathered. This is performed in step 106 in FIG. 1. To use the allocated testing time most efficiently, the query plans are executed in rounds with time limits that increase until at least one plan least amount of system resources). Once this decision is made, the system will terminate the execution of the plan shown to be worse; this way, time will not be wasted executing a query plan which might take a very long time to complete when another plan has been shown to complete in much less tie. Further details regarding the testing of alternative execution plans are described in “TEST EXECUTION OF USER SQL IN DATABASE SERVER CODE”, application Ser. No. 12/217,249, filed on Jul. 2, 2008.

Choosing Tuning Recommendations to Implement

One goal of the step of selecting which tuning recommendations to implement is to choose the subset of the tuning recommendations generated from the tuning step that would improve performance the most without implementing too much change in the database system. According to one embodiment, the results from the testing step just described are analyzed, and only the tuning recommendations that result in substantial benefits are implemented. Furthermore, in order to limit the number of changes made to the database system on a daily basis, a limit on the number of actions taken during maintenance may also be configured so that tuning recommendations are implemented only if the number of changes made does not already exceed the pre-configured limit. As described above, SQL statements which have been identified as good candidates for tuning are tuned in order of priority. Therefore, the tuning recommendations for the highest-priority SQL statements would be implemented first and will unlikely be precluded from implementation by the cap on number of changes. The determination of which tuning recommendations are to be implemented is made in step 108 of FIG. 1.

According to one embodiment, only tuning recommendations that have resulted in performance improvement that meet a specific set of criteria are implemented. In one example, only tuning recommendations that improve the execution time by at least threefold are implemented. The threefold improvement is measured in terms of specific execution statistics which are known to have consistent values across multiple executions. CPU time and buffer gets (logical I/Os) are two metrics that may be used to measure improvement because CPU time and buffer gets are both repeatable measurements that encompass important factors in SQL performance.

According to another embodiment, only tuning recommendations that meet a specific set of safety criteria are implemented. For example, in one example, only tuning recommendations that do not increase the I/O times of SQL statements are implemented, even if the tuning recommendations decrease the overall execution times of the SQL statements.

In step 110, the tuning recommendations that have been determined to be implemented are implemented. For SQL Profiles, for example, this means that the SQL Profiles are stored persistently in a data dictionary that associates the SQL Profiles with specific SQL statements. As discussed above, once a particular SQL Profile is stored persistently, the next time that the particular SQL statement is analyzed by the query optimizer operating in normal mode, the execution plan for the particular SQL statement will be generated based on statistics that are adjusted by the adjustment factors stored in the SQL Profile that is associated with the particular SQL statement.

Running the Fully-Automated Tuning Process in a Controlled Environment

The automatic tuning of SQL statements is a resource-intensive and time-consuming process. The tuning process is run in a controlled environment so that other activities on the machine on which the tuning process is run are not interrupted. Furthermore, in the controlled environment, the tuning process may be restricted to run only for a specified period of time.

According to one embodiment, the automatic tuning process is run in an automated maintenance task that is run on a nightly basis. The frequency, begin time, and end time that the automatic tuning process is run may be customized by the DBA.

Within the specified period of time (e.g., one hour), the tuning process performs the steps of: identifying candidate SQL statements from the AWR for tuning; tuning each SQL statement individually be calling the SQL Tuning Advisor; testing the SQL profiles generated by SQL Tuning Advisor; and optionally implementing the SQL profiles if they meet the criteria (e.g., three-fold performance improvement).

According to one embodiment, the fully-automated tuning process described above is performed by an automated maintenance task. The automated maintenance task caps the resources used by the tuning process to a specific percentage of the total resource consumption on the machine on which the tuning process is being made. The automated maintenance task also caps the total amount of time for the tuning process so that the tuning process does not run for an inordinately long period of time. Finally, the amount of time the tuning process spends tuning each individual SQL statement may be capped by the tuning process itself to prevent the tuning process from spending too much time analyzing one particular SQL statement. In addition, the total amount of time the tuning process spends tuning all the selected SQL statements may also be capped by the tuning process itself. For example, the automated maintenance task may cap the total amount of time spent by the tuning process to four hours, while the tuning process may cap the own total tuning time to one hour and cap the tuning time for a single SQL statement to twenty minutes. According to one embodiment, when any of these time limits is reached, the tuning process may be notified and in response may complete the current tuning activity in such a manner that ensures that the partial results will be consumable by the user at a later time.

According to one embodiment, a separate and independent mechanism monitors the total amount of time that the tuning process has used. If the separate and independent mechanism detects that the total amount of time used by the tuning process has exceeded the specified cap, then the tuning process is terminated.

According to another embodiment, the tuning process monitors whether or not its actions are causing any contentions with other sessions in the database system. When the tuning process detects such a contention, it will stop tuning the SQL statement currently being tuned and commence tuning the SQL statement with the next highest priority. If this action does not immediately resolve the contention, the tuning process may be aborted entirely.

Monitoring the System after Implementation of Tuning Recommendations

After the tuning recommendations are implemented, the database system is monitored to detect when the tuning recommendations are used and to measure the benefits form the tuning recommendations. This is performed in step 112 of FIG. 1. According to one embodiment, if the benefit from a particular tuning recommendation is less than its anticipated benefit, the recommendation is reverted.

Reporting Results of the Tuning Process

According to one embodiment, information gathered during the tuning process is compiled into a report, which can then be reviewed by the DBA. This report may include, for example, a list of the SQL statements that were tuned, the tuning recommendations generated by the SQL Tuning Advisor, the results from testing the tuning recommendations, and a list of which tuning recommendations were implemented. The DBA may examine this report to implement additional tuning recommendations or to remove any tuning recommendations that were automatically implemented.

Hardware Overview

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

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

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

The term “machine-readable medium” as used herein refers to any medium that participates in providing data that causes a machine to operation in a specific fashion. In an embodiment implemented using computer system 200, various machine-readable media are involved, for example, in providing instructions to processor 204 for execution. Such a medium may take many forms, including but not limited to storage media and transmission media. Storage media includes both non-volatile media and volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as storage device 210. Volatile media includes dynamic memory, such as main memory 206. Transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus 202. Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infra-red data communications. All such media must be tangible to enable the instructions carried by the media to be detected by a physical mechanism that reads the instructions into a machine.

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

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

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

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

Computer system 200 can send messages and receive data, including program code, through the network(s), network link 220 and communication interface 218. In the Internet example, a server 230 might transmit a requested code for an application program through Internet 228, ISP 226, local network 222 and communication interface 218.

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

In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details that may vary from implementation to implementation. Thus, the sole and exclusive indicator of what is the invention, and is intended by the applicants to be the invention, is the set of claims that issue from this application, in the specific form in which such claims issue, including any subsequent correction. Any definitions expressly set forth herein for terms contained in such claims shall govern the meaning of such terms as used in the claims. Hence, no limitation, element, property, feature, advantage or attribute that is not expressly recited in a claim should limit the scope of such claim in any way. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense.

Claims (30)

What is claimed is:
1. A computer-implemented method comprising steps of:
from a workload set, identifying a plurality of database query language statements for automatic tuning, wherein the workload set comprises database query language statements and current performance data for the database query language statements;
executing each database query language statement from said plurality of query language statements against a database;
collecting new performance data from said executing each database query language statement, said collecting comprising measuring resource usage by said executing each database query language statement;
detecting that conditions in said database that affect executing said plurality of database query language statements changed based at least in part on comparison of the new performance data with the current performance data;
in response to the detecting, tuning a subset of database query language statements from said plurality of database query language statements, said subset of database query language statements comprising a database query language statement from said plurality of database query language statements, wherein the new performance data is different from the current performance data for the database query language statement;
wherein the tuning the subset of database query language statements comprises generating a plurality of tuning recommendations for execution of the subset of database query language statements;
testing the plurality of tuning recommendations against said database, wherein the testing the plurality of tuning recommendations comprises, for each tuning recommendation of said plurality of tuning recommendations:
executing a respective database query language statement from said subset of database query language statements with said each tuning recommendation enabled;
measuring resource usage by said executing the respective database query language statement with said each tuning recommendation enabled, wherein the resource usage comprises processor time or buffer gets; and
measuring benefits based on performance improvement of said executing the respective database query language statement with said each tuning recommendation enabled;
based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a specific set of criteria;
implementing the subset of said plurality of tuning recommendations; and
wherein the steps are automatically performed by one or more computing devices.
2. The computer-implemented method of claim 1, wherein the workload set comprises database query language statements that have been executed over a specific period of time and performance data for the database query language statements that have been executed over the specific period of time.
3. The computer-implemented method of claim 2, wherein the identifying the plurality of database query language statements for automatic tuning comprises identifying a set of database query language statements as having long execution times and high frequencies of execution during the specific period of time.
4. The computer-implemented method of claim 1, wherein the subset of database query language statements are prioritized, further comprising:
automatically tuning the subset of database query language statements in order of priority.
5. The computer-implemented method of claim 1, wherein the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements are performed in a controlled environment on a machine in a manner such that activities in the controlled environment do not interrupt activities on the machine outside the controlled environment.
6. The computer-implemented method of claim 5, wherein the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.
7. The computer-implemented method of claim 5, wherein a time limit enforcement mechanism external to the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.
8. The computer-implemented method of claim 5, wherein:
the controlled environment periodically checks if the activities in the controlled environment are causing any contention with an activity outside the controlled environment; and
the controlled environment aborts the activities in the controlled environment upon detection that the activities in the controlled environment are causing a contention with the activity outside the controlled environment.
9. The computer-implemented method of claim 1, wherein the tuning the subset of database query language statements is performed until one or more time periods are reached or exceeded, thereby generating the plurality of tuning recommendations for a particular subset of database query language statements of said subset of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a particular database query language statement of said subset of database query language statements.
10. The computer-implemented method of claim 9, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.
11. The computer-implemented method of claim 9, wherein the tuning the subset of database query language statements until one or more time periods are reached or exceeded includes at least one of:
tuning a single database query language statement of said subset of database query language statements until a particular time period of said one or more time periods is reached or exceeded; and
tuning multiple database query language statements of said subset of database query language statements until a single time period of said one or more time periods is reached or exceeded.
12. The computer-implemented method of claim 1, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.
13. The computer-implemented method of claim 1, wherein the resource usage comprises both processor time and buffer gets.
14. The computer-implemented method of claim 1,
wherein the implementing the subset of said plurality of tuning recommendations includes storing in association with a particular database query language statement of said subset of database query language statements respective one or more adjustment factors.
15. The computer-implemented method of claim 14 further comprises:
generating an execution plan for said particular database query language statement, wherein the generating the execution plan comprises determining that said particular database query language statement is associated with said respective one or more adjustment factors, wherein the execution plan is generated based on said respective one or more adjustment factors.
16. A computer-readable non-transitory storage medium storing instructions, wherein the instructions include instructions which, when executed by one or more processors, cause the one or more processors to perform steps of:
from a workload set, identifying a plurality of database query language statements for automatic tuning, wherein the workload set comprises database query language statements and current performance data for the database query language statements;
executing each database query language statement from said plurality of query language statements against a database;
collecting new performance data from said executing each database query language statement, said collecting comprising measuring resource usage by said executing each database query language statement;
detecting that conditions in said database that affect executing said plurality of database query language statements changed based at least in part on comparison of the new performance data with the current performance data;
in response to the detecting, tuning a subset of database query language statements from said plurality of database query language statements, said subset of database query language statements comprising a database query language statement from said plurality of database query language statements, wherein the new performance data is different from the current performance data for the database query language statement;
wherein the tuning the subset of database query language statements comprises generating a plurality of tuning recommendations for execution of the subset of database query language statements;
testing the plurality of tuning recommendations against said database, wherein the testing the plurality of tuning recommendations comprises, for each tuning recommendation of said plurality of tuning recommendations:
executing a respective database query language statement from said subset of database query language statements with said each tuning recommendation enabled;
measuring resource usage by said executing the respective database query language statement with said each tuning recommendation enabled, wherein the resource usage comprises processor time or buffer gets; and
measuring benefits based on performance improvement of said executing the respective database query language statement with said each tuning recommendation enabled;
based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a specific set of criteria; and
implementing the subset of said plurality of tuning recommendations.
17. The computer-readable storage medium of claim 16, wherein the workload set comprises database query language statements that have been executed over a specific period of time and performance data for the database query language statements that have been executed over the specific period of time.
18. The computer-readable storage medium of claim 17, wherein the identifying the plurality of database query language statements for automatic tuning comprises identifying a set of database query language statements as having long execution times and high frequencies of execution during the specific period of time.
19. The computer-readable storage medium of claim 16, wherein the subset of database query language statements are prioritized, the instructions further comprising instructions for:
automatically tuning the subset of database query language statements in order of priority.
20. The computer-readable storage medium of claim 16, wherein the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements are performed in a controlled environment on a machine in a manner such that activities in the controlled environment do not interrupt activities on the machine outside the controlled environment.
21. The computer-readable storage medium of claim 20, wherein the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.
22. The computer-readable storage medium of claim 20, wherein a time limit enforcement mechanism external to the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.
23. The computer-readable storage medium of claim 20, wherein:
the controlled environment periodically checks if the activities in the controlled environment are causing any contention with an activity outside the controlled environment; and
the controlled environment aborts the activities in the controlled environment upon detection that the activities in the controlled environment are causing a contention with the activity outside the controlled environment.
24. The computer-readable storage medium of claim 16, wherein the tuning the subset of database query language statements is performed until one or more time periods are reached or exceeded, thereby generating the plurality of tuning recommendations for a particular subset of database query language statements of said subset of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a particular database query language statement of said subset of database query language statements.
25. The computer-readable storage medium of claim 24, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.
26. The computer-readable storage medium of claim 24, wherein the tuning the subset of database query language statements until one or more time periods are reached or exceeded includes at least one of:
tuning a single database query language statement of said subset of database query language statements until a particular time period of said one or more time periods is reached or exceeded; and
tuning multiple database query language statements of said subset of database query language statements until a single time period of said one or more time periods is reached or exceeded.
27. The computer-readable storage medium of claim 16, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.
28. The computer-readable storage medium of claim 16, wherein the
resource usage comprises both processor time and buffer gets.
29. The computer-readable storage medium of claim 16,
wherein the implementing the subset of said plurality of tuning recommendations includes storing in association with a particular database query language statement of said subset of database query language statements respective one or more adjustment factors.
30. The computer-readable storage medium of claim 29, wherein the instructions further comprise instructions for:
generating an execution plan for said particular database query language statement,
wherein the generating the execution plan comprises determining that said particular database query language statement is associated with said respective one or more adjustment factors, wherein the execution plan is generated based on said respective one or more adjustment factors.
US12188975 2007-09-14 2008-08-08 Fully automated SQL tuning Active 2029-06-18 US8903801B2 (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
US97268107 true 2007-09-14 2007-09-14
US12188975 US8903801B2 (en) 2007-09-14 2008-08-08 Fully automated SQL tuning

Applications Claiming Priority (3)

Application Number Priority Date Filing Date Title
US12188975 US8903801B2 (en) 2007-09-14 2008-08-08 Fully automated SQL tuning
US14270556 US9734200B2 (en) 2007-09-14 2014-05-06 Identifying high risk database statements in changing database environments
US14549449 US9720941B2 (en) 2007-09-14 2014-11-20 Fully automated SQL tuning

Related Child Applications (1)

Application Number Title Priority Date Filing Date
US14549449 Continuation US9720941B2 (en) 2007-09-14 2014-11-20 Fully automated SQL tuning

Publications (2)

Publication Number Publication Date
US20090077016A1 true US20090077016A1 (en) 2009-03-19
US8903801B2 true US8903801B2 (en) 2014-12-02

Family

ID=40455647

Family Applications (3)

Application Number Title Priority Date Filing Date
US12188975 Active 2029-06-18 US8903801B2 (en) 2007-09-14 2008-08-08 Fully automated SQL tuning
US14270556 Active 2034-12-12 US9734200B2 (en) 2007-09-14 2014-05-06 Identifying high risk database statements in changing database environments
US14549449 Active 2028-12-08 US9720941B2 (en) 2007-09-14 2014-11-20 Fully automated SQL tuning

Family Applications After (2)

Application Number Title Priority Date Filing Date
US14270556 Active 2034-12-12 US9734200B2 (en) 2007-09-14 2014-05-06 Identifying high risk database statements in changing database environments
US14549449 Active 2028-12-08 US9720941B2 (en) 2007-09-14 2014-11-20 Fully automated SQL tuning

Country Status (1)

Country Link
US (3) US8903801B2 (en)

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20150081669A1 (en) * 2007-09-14 2015-03-19 Oracle International Corporation Fully automated sql tuning
US20150149441A1 (en) * 2013-11-25 2015-05-28 Anisoara Nica Data Statistics in Data Management Systems

Families Citing this family (22)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7877373B2 (en) * 2006-06-30 2011-01-25 Oracle International Corporation Executing alternative plans for a SQL statement
EP3361438A1 (en) * 2007-03-19 2018-08-15 Marketshare Partners Llc Automatically prescribing total budget for marketing and sales resources and allocation across spending categories
US8341178B2 (en) * 2007-09-18 2012-12-25 Oracle International Corporation SQL performance analyzer
US8335767B2 (en) * 2007-10-17 2012-12-18 Oracle International Corporation Maintaining and utilizing SQL execution plan histories
US20090144117A1 (en) * 2007-11-29 2009-06-04 David Cavander Automatically prescribing total budget for marketing and sales resources and allocation across spending categories
JP5530368B2 (en) * 2008-02-21 2014-06-25 マーケットシェア パートナーズ リミテッド ライアビリティ カンパニー Automatic designation of the total budget and sales resources of marketing, and distribution over the expenditure category
US8140548B2 (en) * 2008-08-13 2012-03-20 Microsoft Corporation Constrained physical design tuning
EP2329403A4 (en) * 2008-08-15 2012-10-10 Marketshare Partners Llc Automated decision support for pricing entertainment tickets
US8417691B2 (en) * 2009-12-09 2013-04-09 International Business Machines Corporation Client and database problem determination and monitoring
US9201752B2 (en) * 2010-01-19 2015-12-01 Ca, Inc. System and method for correlating empirical data with user experience
US8527543B1 (en) 2010-09-16 2013-09-03 Quest Software, Inc. System for categorizing database statements for performance tuning
US20130035975A1 (en) * 2011-08-05 2013-02-07 David Cavander Cross-media attribution model for allocation of marketing resources
US9047396B2 (en) 2011-10-31 2015-06-02 International Business Machines Corporation Method, system and computer product for rescheduling processing of set of work items based on historical trend of execution time
US9355009B2 (en) * 2011-10-31 2016-05-31 International Business Machines Corporation Performance of scheduled tasks via behavior analysis and dynamic optimization
US8856102B2 (en) * 2012-11-07 2014-10-07 International Business Machines Corporation Modifying structured query language statements
US9946750B2 (en) * 2013-12-01 2018-04-17 Actian Corporation Estimating statistics for generating execution plans for database queries
US20160004621A1 (en) * 2014-07-07 2016-01-07 Oracle International Corporation Proactive impact measurement of database changes on production systems
US10019480B2 (en) * 2014-11-14 2018-07-10 International Business Machines Corporation Query tuning in the cloud
US9892160B2 (en) 2015-04-07 2018-02-13 International Business Machines Corporation Database statistics based on transaction state
US9934278B2 (en) * 2015-05-04 2018-04-03 Quest Software Inc. Method of optimizing complex SQL statements using a region divided preferential SQL rewrite operation
US20170083573A1 (en) * 2015-07-29 2017-03-23 Algebraix Data Corp. Multi-query optimization
CN106126403A (en) * 2016-06-16 2016-11-16 北京中亦安图科技股份有限公司 Oracle database fault analysis method and apparatus

Citations (158)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US4769772A (en) 1985-02-28 1988-09-06 Honeywell Bull, Inc. Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases
US4803614A (en) 1985-02-21 1989-02-07 Hitachi, Ltd. System for retrieving distributed information in a data base
US4829427A (en) 1984-05-25 1989-05-09 Data General Corporation Database query code generation and optimization based on the cost of alternate access methods
US4956774A (en) 1988-09-02 1990-09-11 International Business Machines Corporation Data base optimizer using most frequency values statistics
US5091852A (en) 1988-01-29 1992-02-25 Hitachi, Ltd. System for optimizing query processing in a relational database
US5251131A (en) 1991-07-31 1993-10-05 Thinking Machines Corporation Classification of data records by comparison of records to a training database using probability weights
US5287459A (en) 1991-10-03 1994-02-15 International Business Machines Corporation Method and apparatus for reducing response time in automated library data retrieval systems
US5301317A (en) 1992-04-27 1994-04-05 International Business Machines Corporation System for adapting query optimization effort to expected execution time
US5315580A (en) 1990-09-28 1994-05-24 Hewlett-Packard Company Network monitoring device and system
US5325525A (en) 1991-04-04 1994-06-28 Hewlett-Packard Company Method of automatically controlling the allocation of resources of a parallel processor computer system by calculating a minimum execution time of a task and scheduling subtasks against resources to execute the task in the minimum time
US5339429A (en) 1991-05-08 1994-08-16 Hitachi, Ltd. Parallel processing system and compiling method used therefor
US5379424A (en) 1990-05-10 1995-01-03 Kabushiki Kaisha Toshiba Distributed database management system for retrieving data files from databases selected based upon retrieval time
US5412806A (en) * 1992-08-20 1995-05-02 Hewlett-Packard Company Calibration of logical cost formulae for queries in a heterogeneous DBMS using synthetic database
US5412804A (en) 1992-04-30 1995-05-02 Oracle Corporation Extending the semantics of the outer join operator for un-nesting queries to a data base
US5444820A (en) 1993-12-09 1995-08-22 Long Island Lighting Company Adaptive system and method for predicting response times in a service environment
US5452468A (en) 1991-07-31 1995-09-19 Peterson; Richard E. Computer system with parallel processing for information organization
US5459837A (en) 1993-04-21 1995-10-17 Digital Equipment Corporation System to facilitate efficient utilization of network resources in a computer network
US5469560A (en) 1991-04-10 1995-11-21 International Business Machines Corporation Prioritizing pending read requests in an automated storage library
US5495606A (en) 1993-11-04 1996-02-27 International Business Machines Corporation System for parallel processing of complex read-only database queries using master and slave central processor complexes
US5495419A (en) 1994-04-19 1996-02-27 Lsi Logic Corporation Integrated circuit physical design automation system utilizing optimization process decomposition and parallel processing
US5504894A (en) 1992-04-30 1996-04-02 International Business Machines Corporation Workload manager for achieving transaction class response time goals in a multiprocessing system
US5537588A (en) 1994-05-11 1996-07-16 International Business Machines Corporation Partitioned log-structured file system and methods for operating the same
US5551027A (en) 1993-01-07 1996-08-27 International Business Machines Corporation Multi-tiered indexing method for partitioned data
US5572640A (en) 1994-12-01 1996-11-05 Hewlett-Packard Company Batch transfer system and method for high performance graphic display of network topology
US5574900A (en) 1994-02-25 1996-11-12 International Business Machines Corporation System and method for optimizing parallel processing of database queries
US5590319A (en) 1993-12-15 1996-12-31 Information Builders, Inc. Query processor for parallel processing in homogenous and heterogenous databases
US5642515A (en) 1992-04-17 1997-06-24 International Business Machines Corporation Network server for local and remote resources
US5671403A (en) 1994-12-30 1997-09-23 International Business Machines Corporation Iterative dynamic programming system for query optimization with bounded complexity
US5675791A (en) 1994-10-31 1997-10-07 International Business Machines Corporation Method and system for database load balancing
US5680547A (en) 1993-08-04 1997-10-21 Trend Micro Devices Incorporated Method and apparatus for controlling network and workstation access prior to workstation boot
US5694591A (en) 1995-05-02 1997-12-02 Hewlett Packard Company Reducing query response time using tree balancing
US5710915A (en) 1995-12-21 1998-01-20 Electronic Data Systems Corporation Method for accelerating access to a database clustered partitioning
US5761654A (en) * 1996-06-05 1998-06-02 Oracle Corporation Memory structure and method for tuning a database statement using a join-tree data structure representation, including selectivity factors, of a master table and detail table
US5765150A (en) 1996-08-09 1998-06-09 Digital Equipment Corporation Method for statistically projecting the ranking of information
US5764912A (en) 1995-08-10 1998-06-09 Advanced System Technologies Method and apparatus for determining response time in computer applications
US5787251A (en) 1992-12-21 1998-07-28 Sun Microsystems, Inc. Method and apparatus for subcontracts in distributed processing systems
US5794227A (en) 1989-12-23 1998-08-11 International Computers Limited Optimization of the order in which the comparisons of the components of a boolean query expression are applied to a database record stored as a byte stream
US5797136A (en) 1995-10-05 1998-08-18 International Business Machines Corporation Optional quantifiers in relational and object-oriented views of database systems
US5822748A (en) 1997-02-28 1998-10-13 Oracle Corporation Group by and distinct sort elimination using cost-based optimization
US5852820A (en) 1996-08-09 1998-12-22 Digital Equipment Corporation Method for optimizing entries for searching an index
US5857180A (en) 1993-09-27 1999-01-05 Oracle Corporation Method and apparatus for implementing parallel operations in a database management system
US5860069A (en) 1997-04-11 1999-01-12 Bmc Software, Inc. Method of efficient collection of SQL performance measures
US5875445A (en) 1997-05-29 1999-02-23 Oracle Corporation Performance-related estimation using pseudo-ranked trees
US5918225A (en) 1993-04-16 1999-06-29 Sybase, Inc. SQL-based database system with improved indexing methodology
US6002669A (en) * 1996-03-26 1999-12-14 White; Darryl C. Efficient, multi-purpose network data communications protocol
US6003022A (en) 1994-06-24 1999-12-14 International Buisness Machines Corporation Database execution cost and system performance estimator
US6009265A (en) 1994-02-25 1999-12-28 International Business Machines Corporation Program product for optimizing parallel processing of database queries
US6026394A (en) 1993-01-20 2000-02-15 Hitachi, Ltd. System and method for implementing parallel operations in a database management system
US6026390A (en) 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US6026391A (en) 1997-10-31 2000-02-15 Oracle Corporation Systems and methods for estimating query response times in a computer system
US6061676A (en) 1996-05-29 2000-05-09 Lucent Technologies Inc. Effecting constraint magic rewriting on a query with the multiset version of the relational algebric theta-semijoin operator
US6205451B1 (en) 1998-05-22 2001-03-20 Oracle Corporation Method and apparatus for incremental refresh of summary tables in a database system
US6289335B1 (en) 1997-06-23 2001-09-11 Oracle Corporation Fast refresh of snapshots containing subqueries
US6298342B1 (en) 1998-03-16 2001-10-02 Microsoft Corporation Electronic database operations for perspective transformations on relational tables using pivot and unpivot columns
US20010047372A1 (en) 2000-02-11 2001-11-29 Alexander Gorelik Nested relational data model
US6334128B1 (en) 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US6339768B1 (en) 1998-08-13 2002-01-15 International Business Machines Corporation Exploitation of subsumption in optimizing scalar subqueries
US6353826B1 (en) 1997-10-23 2002-03-05 Sybase, Inc. Database system with methodology providing improved cost estimates for query strategies
US6356889B1 (en) 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US20020038313A1 (en) 1999-07-06 2002-03-28 Compaq Computer Corporation System and method for performing database operations on a continuous stream of tuples
US6370524B1 (en) 1999-04-02 2002-04-09 Oracle Corp. System and method for processing queries having an inner query block containing a grouping operator
US20020099521A1 (en) 1999-04-30 2002-07-25 Tao-Heng Yang Method and mechanism for profiling a system
US6430550B1 (en) 1999-12-03 2002-08-06 Oracle Corporation Parallel distinct aggregates
US6438562B1 (en) 1999-08-24 2002-08-20 Oracle Corporation Parallel index maintenance
US6438558B1 (en) 1999-12-23 2002-08-20 Ncr Corporation Replicating updates in original temporal order in parallel processing database systems
US20020138376A1 (en) 1997-10-29 2002-09-26 N_Gine, Inc. Multi-processing financial transaction processing system
US20020188600A1 (en) 2001-03-15 2002-12-12 International Business Machines Corporation Outerjoin and antijoin reordering using extended eligibility lists
US20030033291A1 (en) 2001-08-03 2003-02-13 David Harris SQL execution analysis
US6526526B1 (en) * 1999-11-09 2003-02-25 International Business Machines Corporation Method, system and program for performing remote usability testing
US6529901B1 (en) 1999-06-29 2003-03-04 Microsoft Corporation Automating statistics management for query optimizers
US6529896B1 (en) 2000-02-17 2003-03-04 International Business Machines Corporation Method of optimizing a query having an existi subquery and a not-exists subquery
US20030065644A1 (en) 2001-09-28 2003-04-03 Horman Randall W. Database diagnostic system and method
US20030088541A1 (en) * 2001-06-21 2003-05-08 Zilio Daniel C. Method for recommending indexes and materialized views for a database workload
US20030093408A1 (en) * 2001-10-12 2003-05-15 Brown Douglas P. Index selection in a database system
US20030115212A1 (en) 1999-09-22 2003-06-19 John F. Hornibrook System and process for evaluating the performance of a database system
US20030135480A1 (en) 2002-01-14 2003-07-17 Van Arsdale Robert S. System for updating a database
US6598038B1 (en) * 1999-09-17 2003-07-22 Oracle International Corporation Workload reduction mechanism for index tuning
US20030159136A1 (en) 2001-09-28 2003-08-21 Huang Xiao Fei Method and system for server synchronization with a computing device
US20030182276A1 (en) 2002-03-19 2003-09-25 International Business Machines Corporation Method, system, and program for performance tuning a database query
US20030212668A1 (en) 2002-05-13 2003-11-13 Hinshaw Foster D. Optimized database appliance
US20030212647A1 (en) 2002-05-07 2003-11-13 Matthew Jay Bangel Method, system and program product for maintaining a change history for a database design
US20030229639A1 (en) * 2002-06-07 2003-12-11 International Business Machines Corporation Runtime query optimization for dynamically selecting from multiple plans in a query based upon runtime-evaluated performance criterion
US20040003004A1 (en) * 2002-06-28 2004-01-01 Microsoft Corporation Time-bound database tuning
US20040015600A1 (en) 2002-02-21 2004-01-22 Ashutosh Tiwary Workload post-processing and parameterization for a system for performance testing of N-tiered computer systems using recording and playback of workloads
US6684203B1 (en) 1999-11-08 2004-01-27 Oracle International Corporation Using global temporary tables to transform queries
US6694306B1 (en) 1999-10-06 2004-02-17 Hitachi, Ltd. System and method for query processing using virtual table interface
US20040181521A1 (en) * 1999-12-22 2004-09-16 Simmen David E. Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US20040205062A1 (en) * 2000-06-30 2004-10-14 Brown Douglas P. Analysis method and apparatus for a parallel system
US6807546B2 (en) 2002-08-12 2004-10-19 Sybase, Inc. Database system with methodology for distributing query optimization effort over large search spaces
US20040220911A1 (en) 2003-04-30 2004-11-04 Zuzarte Calisto P. Method and system for aggregation subquery join elimination
US20040244031A1 (en) 2003-02-26 2004-12-02 Peter Martinez System and method for a network of interactive televisions
US20050028134A1 (en) 2003-07-07 2005-02-03 Netezza Corporation SQL code generation for heterogeneous environment
US20050055382A1 (en) 2000-06-28 2005-03-10 Lounas Ferrat Universal synchronization
US20050086195A1 (en) * 2003-09-04 2005-04-21 Leng Leng Tan Self-managing database architecture
US6901405B1 (en) 2000-12-20 2005-05-31 Microsoft Corporation Method for persisting a schedule and database schema
US20050120000A1 (en) * 2003-09-06 2005-06-02 Oracle International Corporation Auto-tuning SQL statements
US20050165741A1 (en) * 2003-12-24 2005-07-28 Gordon Mark R. System and method for addressing inefficient query processing
US20050177971A1 (en) 2004-01-19 2005-08-18 Antonio Porco Roll cleaning apparatus
US6934699B1 (en) 1999-09-01 2005-08-23 International Business Machines Corporation System and method for loading a cache with query results
US20050187971A1 (en) 2004-02-19 2005-08-25 Hassan Ahmed E. System and method for searching a remote database
US6941360B1 (en) 1999-02-25 2005-09-06 Oracle International Corporation Determining and registering participants in a distributed transaction in response to commencing participation in said distributed transaction
US20050198013A1 (en) 2004-03-08 2005-09-08 Microsoft Corporation Structured indexes on results of function applications over data
US20050203933A1 (en) 2004-03-09 2005-09-15 Microsoft Corporation Transformation tool for mapping XML to relational database
US20050203940A1 (en) 2004-03-12 2005-09-15 Sybase, Inc. Database System with Methodology for Automated Determination and Selection of Optimal Indexes
US6954776B1 (en) 2001-05-07 2005-10-11 Oracle International Corporation Enabling intra-partition parallelism for partition-based operations
US20050234965A1 (en) 2004-04-20 2005-10-20 Reuters Limited Computing algebraic equations
US6961729B1 (en) 2001-01-25 2005-11-01 Oracle International Corporation Processing in parallel units of work that perform DML operations on the same spanning rows
US20050262060A1 (en) 2004-05-12 2005-11-24 Oracle International Corporation End-to-end tracing for database applications
US20050278577A1 (en) * 2004-06-01 2005-12-15 Roongko Doong Automatically generating observations of program behavior for code testing purposes
US20050278357A1 (en) * 2004-06-10 2005-12-15 Brown Paul G Detecting correlation from data
US20050283458A1 (en) * 2004-06-22 2005-12-22 Microsoft Corporation Automatic detection of frequently used query patterns in a query workload
US20050283471A1 (en) 2004-06-22 2005-12-22 Oracle International Corporation Multi-tier query processing
US6980988B1 (en) 2001-10-01 2005-12-27 Oracle International Corporation Method of applying changes to a standby database system
US6990503B1 (en) 2002-04-12 2006-01-24 Ncr Corporation Rescheduling transactions in a database system
US20060026115A1 (en) 2004-07-27 2006-02-02 Oracle International Corporation Reusing optimized query blocks in query processing
US20060026133A1 (en) 2004-07-27 2006-02-02 Oracle International Corporation Determining query cost based on subquery filtering factor
US20060031200A1 (en) 2004-08-05 2006-02-09 International Business Machines Corporation Method and system for tracking performance by breaking down a query
US20060036989A1 (en) * 2004-08-10 2006-02-16 Microsoft Corporation Dynamic physical database design
US20060041537A1 (en) 2004-08-17 2006-02-23 Oracle International Corporation Selecting candidate queries
US7007007B2 (en) * 1998-05-14 2006-02-28 Microsoft Corporation Test generator for database management systems providing tight joins
US20060085378A1 (en) * 2004-10-15 2006-04-20 Microsoft Corporation Schema for physical database tuning
US20060085484A1 (en) * 2004-10-15 2006-04-20 Microsoft Corporation Database tuning advisor
US20060101224A1 (en) * 2004-11-08 2006-05-11 Shah Punit B Autonomic self-tuning of database management system in dynamic logical partitioning environment
US20060107141A1 (en) * 2003-11-12 2006-05-18 International Business Machines Corporation Database mining system and method for coverage analysis of functional verification of integrated circuit designs
US7089225B2 (en) 2003-11-25 2006-08-08 International Business Machines Corporation Efficient heuristic approach in selection of materialized views when there are multiple matchings to an SQL query
US20060195416A1 (en) * 2005-02-28 2006-08-31 Ewen Stephan E Method and system for providing a learning optimizer for federated database systems
US20060212428A1 (en) * 2005-03-15 2006-09-21 International Business Machines Corporation Analysis of performance data from a relational database system for applications using stored procedures or SQL
US20060218123A1 (en) 2005-03-28 2006-09-28 Sybase, Inc. System and Methodology for Parallel Query Optimization Using Semantic-Based Partitioning
US20070038618A1 (en) * 2000-05-26 2007-02-15 Edward Kosciusko System and method for automatically generating database queries
US20070038595A1 (en) 2005-08-11 2007-02-15 Bhaskar Ghosh Query processing in a parallel single cursor model on multi-instance configurations, using hints
US7185000B1 (en) 2000-06-30 2007-02-27 Ncr Corp. Method and apparatus for presenting query plans
US20070061379A1 (en) 2005-09-09 2007-03-15 Frankie Wong Method and apparatus for sequencing transactions globally in a distributed database cluster
US7194452B2 (en) * 2000-03-31 2007-03-20 Microsoft Corporation Validating multiple execution plans for database queries
US20070078825A1 (en) * 2005-09-30 2007-04-05 Sap Ag Systems and methods for repeatable database performance testing
US20070083500A1 (en) * 2005-10-07 2007-04-12 Bez Systems, Inc. Method of incorporating DBMS wizards with analytical models for DBMS servers performance optimization
US20070136383A1 (en) * 2005-12-13 2007-06-14 International Business Machines Corporation Database Tuning Method and System
US20070214104A1 (en) * 2006-03-07 2007-09-13 Bingjie Miao Method and system for locking execution plan during database migration
US7305410B2 (en) 2002-12-26 2007-12-04 Rocket Software, Inc. Low-latency method to replace SQL insert for bulk data transfer to relational database
US20080010240A1 (en) 2006-06-30 2008-01-10 Mohamed Zait Executing alternative plans for a SQL statement
US20080040196A1 (en) 2006-07-06 2008-02-14 International Business Machines Corporation Method, system and program product for hosting an on-demand customer interaction center utility infrastructure
US20080052271A1 (en) 2006-08-26 2008-02-28 Eric Lam Method To Converge A Plurality Of SQL Statements Into SQL Skeletons For Enhanced Database Performance Analysis And Tuning
US20080077348A1 (en) * 2006-03-27 2008-03-27 Infineon Technologies Ag Integrated circuit and method for determining the operating range of an integrated circuit
US20080098003A1 (en) * 2006-10-20 2008-04-24 Oracle International Corporation Database workload replay remapping infrastructure
US20080114718A1 (en) 2006-11-09 2008-05-15 Mark John Anderson Apparatus and method for database execution detail repository
US20080126393A1 (en) * 2006-11-29 2008-05-29 Bossman Patrick D Computer program product and system for annotating a problem sql statement for improved understanding
US7383247B2 (en) 2005-08-29 2008-06-03 International Business Machines Corporation Query routing of federated information systems for fast response time, load balance, availability, and reliability
US20080178079A1 (en) * 2007-01-18 2008-07-24 International Business Machines Corporation Apparatus and method for a graphical user interface to facilitate tuning sql statements
US20080215536A1 (en) * 2004-06-03 2008-09-04 International Business Machines Corporation Autonomically generating a query implementation that meets a defined performance specification
US20080228710A1 (en) 2005-03-24 2008-09-18 International Business Machines Corporation Building database statistics across a join network using skew values
US20080235183A1 (en) * 2007-03-21 2008-09-25 Oliver Draese Workload Aware Checking of Database Reorganization
US20090006445A1 (en) 2007-06-28 2009-01-01 Esther Shemenzon Binding between net technologies and SQL server statements
US20090018992A1 (en) 2007-07-12 2009-01-15 Ibm Corporation Management of interesting database statistics
US20090037923A1 (en) 2007-07-31 2009-02-05 Smith Gary S Apparatus and method for detecting resource consumption and preventing workload starvation
US20090077017A1 (en) 2007-09-18 2009-03-19 Oracle International Corporation Sql performance analyzer
US20090327254A1 (en) * 2008-06-26 2009-12-31 Microsoft Corporation Configuration-parametric query optimization
US20090327214A1 (en) * 2008-06-25 2009-12-31 Microsoft Corporation Query Execution Plans by Compilation-Time Execution
US20100005340A1 (en) * 2008-07-02 2010-01-07 Oracle International Corporation Test execution of user SQL in database server code

Family Cites Families (49)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US65644A (en) * 1867-06-11 Dominico cheekeni
US203940A (en) * 1878-05-21 Improvement in construction of ships
US177971A (en) * 1876-05-30 Improvement in spermatic trusses
US38595A (en) * 1863-05-19 Improvement in patient-elevators
GB9526096D0 (en) * 1995-12-20 1996-02-21 British Telecomm Specifying indexes for relational databases
US6223171B1 (en) * 1998-08-25 2001-04-24 Microsoft Corporation What-if index analysis utility for database systems
US6366901B1 (en) * 1998-12-16 2002-04-02 Microsoft Corporation Automatic database statistics maintenance and plan regeneration
US6374257B1 (en) * 1999-06-16 2002-04-16 Oracle Corporation Method and system for removing ambiguities in a shared database command
US6363371B1 (en) * 1999-06-29 2002-03-26 Microsoft Corporation Identifying essential statistics for query optimization for databases
JP3587100B2 (en) * 1999-09-17 2004-11-10 セイコーエプソン株式会社 The method of manufacturing a semiconductor device including a nonvolatile memory transistor
US6594820B1 (en) * 1999-09-28 2003-07-15 Sun Microsystems, Inc. Method and apparatus for testing a process in a computer system
US7272589B1 (en) * 2000-11-01 2007-09-18 Oracle International Corporation Database index validation mechanism
US7979384B2 (en) * 2003-11-06 2011-07-12 Oracle International Corporation Analytic enhancements to model clause in structured query language (SQL)
US6915290B2 (en) * 2001-12-11 2005-07-05 International Business Machines Corporation Database query optimization apparatus and method that represents queries as graphs
US6996556B2 (en) * 2002-08-20 2006-02-07 International Business Machines Corporation Metadata manager for database query optimizer
US20040243555A1 (en) * 2003-05-30 2004-12-02 Oracle International Corp. Methods and systems for optimizing queries through dynamic and autonomous database schema analysis
US7685095B2 (en) * 2003-12-16 2010-03-23 Oracle International Corporation Executing a parallel single cursor model
US7412439B2 (en) * 2004-01-07 2008-08-12 International Business Machines Corporation Method for statistics management
US7797286B2 (en) * 2004-05-21 2010-09-14 Sap Ag System and method for externally providing database optimizer statistics
US7353219B2 (en) * 2004-05-28 2008-04-01 International Business Machines Corporation Determining validity ranges of query plans based on suboptimality
US8346761B2 (en) * 2004-08-05 2013-01-01 International Business Machines Corporation Method and system for data mining for automatic query optimization
US8046354B2 (en) * 2004-09-30 2011-10-25 International Business Machines Corporation Method and apparatus for re-evaluating execution strategy for a database query
US7831592B2 (en) * 2004-10-29 2010-11-09 International Business Machines Corporation System and method for updating database statistics according to query feedback
US8161038B2 (en) * 2004-10-29 2012-04-17 International Business Machines Corporation Maintain optimal query performance by presenting differences between access plans
US7949631B2 (en) * 2005-01-27 2011-05-24 International Business Machines Corporation Time-based rebuilding of autonomic table statistics collections
US20060230016A1 (en) * 2005-03-29 2006-10-12 Microsoft Corporation Systems and methods for statistics over complex objects
US20060242102A1 (en) * 2005-04-21 2006-10-26 Microsoft Corporation Relaxation-based approach to automatic physical database tuning
US20060294058A1 (en) * 2005-06-28 2006-12-28 Microsoft Corporation System and method for an asynchronous queue in a database management system
US7415455B2 (en) * 2005-08-24 2008-08-19 International Business Machines Corporation Self-healing RDBMS optimizer
US7805443B2 (en) * 2006-01-20 2010-09-28 Microsoft Corporation Database configuration analysis
US7685194B2 (en) * 2006-08-31 2010-03-23 Microsoft Corporation Fine-grained access control in a database by preventing information leakage and removing redundancy
US7634459B1 (en) * 2006-11-16 2009-12-15 Precise Software Solutions Ltd. Apparatus, method and computer-code for detecting changes in database-statement execution paths
US7877374B2 (en) * 2006-12-01 2011-01-25 Microsoft Corporation Statistics adjustment to improve query execution plans
US7739269B2 (en) * 2007-01-19 2010-06-15 Microsoft Corporation Incremental repair of query plans
US20080195577A1 (en) * 2007-02-09 2008-08-14 Wei Fan Automatically and adaptively determining execution plans for queries with parameter markers
US7925647B2 (en) * 2007-07-27 2011-04-12 Oracle International Corporation Techniques for optimizing SQL statements using user-defined indexes with auxiliary properties
US7644063B2 (en) * 2007-08-17 2010-01-05 International Business Machines Corporation Apparatus, system, and method for ensuring query execution plan stability in a database management system
US8903801B2 (en) 2007-09-14 2014-12-02 Oracle International Corporation Fully automated SQL tuning
US9213740B2 (en) * 2007-10-11 2015-12-15 Sybase, Inc. System and methodology for automatic tuning of database query optimizer
DE102007049446A1 (en) * 2007-10-16 2009-04-23 Cequr Aps Catheter introducer
US8335767B2 (en) * 2007-10-17 2012-12-18 Oracle International Corporation Maintaining and utilizing SQL execution plan histories
US8117146B2 (en) * 2008-02-20 2012-02-14 Oracle International Corporation Computing the values of configuration parameters for optimal performance of associated applications
US7917502B2 (en) * 2008-02-27 2011-03-29 International Business Machines Corporation Optimized collection of just-in-time statistics for database query optimization
US8577871B2 (en) * 2008-03-31 2013-11-05 Oracle International Corporation Method and mechanism for out-of-the-box real-time SQL monitoring
US8060495B2 (en) * 2008-10-21 2011-11-15 International Business Machines Corporation Query execution plan efficiency in a database management system
US8135702B2 (en) * 2008-10-27 2012-03-13 Teradata Us, Inc. Eliminating unnecessary statistics collections for query optimization
US8898142B2 (en) * 2009-01-29 2014-11-25 Hewlett-Packard Development Company, L.P. Risk-premium-based database-query optimization
US8688689B2 (en) * 2010-06-30 2014-04-01 Oracle International Corporation Techniques for recommending alternative SQL execution plans
WO2014077807A1 (en) * 2012-11-14 2014-05-22 Hewlett-Packard Development Company, L.P. Updating statistics in distributed databases

Patent Citations (186)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US4829427A (en) 1984-05-25 1989-05-09 Data General Corporation Database query code generation and optimization based on the cost of alternate access methods
US4803614A (en) 1985-02-21 1989-02-07 Hitachi, Ltd. System for retrieving distributed information in a data base
US4769772A (en) 1985-02-28 1988-09-06 Honeywell Bull, Inc. Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases
US5091852A (en) 1988-01-29 1992-02-25 Hitachi, Ltd. System for optimizing query processing in a relational database
US4956774A (en) 1988-09-02 1990-09-11 International Business Machines Corporation Data base optimizer using most frequency values statistics
US5794227A (en) 1989-12-23 1998-08-11 International Computers Limited Optimization of the order in which the comparisons of the components of a boolean query expression are applied to a database record stored as a byte stream
US5379424A (en) 1990-05-10 1995-01-03 Kabushiki Kaisha Toshiba Distributed database management system for retrieving data files from databases selected based upon retrieval time
US5315580A (en) 1990-09-28 1994-05-24 Hewlett-Packard Company Network monitoring device and system
US5325525A (en) 1991-04-04 1994-06-28 Hewlett-Packard Company Method of automatically controlling the allocation of resources of a parallel processor computer system by calculating a minimum execution time of a task and scheduling subtasks against resources to execute the task in the minimum time
US5469560A (en) 1991-04-10 1995-11-21 International Business Machines Corporation Prioritizing pending read requests in an automated storage library
US5339429A (en) 1991-05-08 1994-08-16 Hitachi, Ltd. Parallel processing system and compiling method used therefor
US5452468A (en) 1991-07-31 1995-09-19 Peterson; Richard E. Computer system with parallel processing for information organization
US5251131A (en) 1991-07-31 1993-10-05 Thinking Machines Corporation Classification of data records by comparison of records to a training database using probability weights
US5287459A (en) 1991-10-03 1994-02-15 International Business Machines Corporation Method and apparatus for reducing response time in automated library data retrieval systems
US5642515A (en) 1992-04-17 1997-06-24 International Business Machines Corporation Network server for local and remote resources
US5301317A (en) 1992-04-27 1994-04-05 International Business Machines Corporation System for adapting query optimization effort to expected execution time
US5412804A (en) 1992-04-30 1995-05-02 Oracle Corporation Extending the semantics of the outer join operator for un-nesting queries to a data base
US5504894A (en) 1992-04-30 1996-04-02 International Business Machines Corporation Workload manager for achieving transaction class response time goals in a multiprocessing system
US5412806A (en) * 1992-08-20 1995-05-02 Hewlett-Packard Company Calibration of logical cost formulae for queries in a heterogeneous DBMS using synthetic database
US5787251A (en) 1992-12-21 1998-07-28 Sun Microsystems, Inc. Method and apparatus for subcontracts in distributed processing systems
US5551027A (en) 1993-01-07 1996-08-27 International Business Machines Corporation Multi-tiered indexing method for partitioned data
US6026394A (en) 1993-01-20 2000-02-15 Hitachi, Ltd. System and method for implementing parallel operations in a database management system
US5918225A (en) 1993-04-16 1999-06-29 Sybase, Inc. SQL-based database system with improved indexing methodology
US5459837A (en) 1993-04-21 1995-10-17 Digital Equipment Corporation System to facilitate efficient utilization of network resources in a computer network
US5680547A (en) 1993-08-04 1997-10-21 Trend Micro Devices Incorporated Method and apparatus for controlling network and workstation access prior to workstation boot
US5857180A (en) 1993-09-27 1999-01-05 Oracle Corporation Method and apparatus for implementing parallel operations in a database management system
US5495606A (en) 1993-11-04 1996-02-27 International Business Machines Corporation System for parallel processing of complex read-only database queries using master and slave central processor complexes
US5444820A (en) 1993-12-09 1995-08-22 Long Island Lighting Company Adaptive system and method for predicting response times in a service environment
US5590319A (en) 1993-12-15 1996-12-31 Information Builders, Inc. Query processor for parallel processing in homogenous and heterogenous databases
US5574900A (en) 1994-02-25 1996-11-12 International Business Machines Corporation System and method for optimizing parallel processing of database queries
US6009265A (en) 1994-02-25 1999-12-28 International Business Machines Corporation Program product for optimizing parallel processing of database queries
US5495419A (en) 1994-04-19 1996-02-27 Lsi Logic Corporation Integrated circuit physical design automation system utilizing optimization process decomposition and parallel processing
US5537588A (en) 1994-05-11 1996-07-16 International Business Machines Corporation Partitioned log-structured file system and methods for operating the same
US6003022A (en) 1994-06-24 1999-12-14 International Buisness Machines Corporation Database execution cost and system performance estimator
US5675791A (en) 1994-10-31 1997-10-07 International Business Machines Corporation Method and system for database load balancing
US5572640A (en) 1994-12-01 1996-11-05 Hewlett-Packard Company Batch transfer system and method for high performance graphic display of network topology
US5671403A (en) 1994-12-30 1997-09-23 International Business Machines Corporation Iterative dynamic programming system for query optimization with bounded complexity
US5694591A (en) 1995-05-02 1997-12-02 Hewlett Packard Company Reducing query response time using tree balancing
US5764912A (en) 1995-08-10 1998-06-09 Advanced System Technologies Method and apparatus for determining response time in computer applications
US5797136A (en) 1995-10-05 1998-08-18 International Business Machines Corporation Optional quantifiers in relational and object-oriented views of database systems
US5710915A (en) 1995-12-21 1998-01-20 Electronic Data Systems Corporation Method for accelerating access to a database clustered partitioning
US6002669A (en) * 1996-03-26 1999-12-14 White; Darryl C. Efficient, multi-purpose network data communications protocol
US6026390A (en) 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US6061676A (en) 1996-05-29 2000-05-09 Lucent Technologies Inc. Effecting constraint magic rewriting on a query with the multiset version of the relational algebric theta-semijoin operator
US5761654A (en) * 1996-06-05 1998-06-02 Oracle Corporation Memory structure and method for tuning a database statement using a join-tree data structure representation, including selectivity factors, of a master table and detail table
US5852820A (en) 1996-08-09 1998-12-22 Digital Equipment Corporation Method for optimizing entries for searching an index
US5765150A (en) 1996-08-09 1998-06-09 Digital Equipment Corporation Method for statistically projecting the ranking of information
US5822748A (en) 1997-02-28 1998-10-13 Oracle Corporation Group by and distinct sort elimination using cost-based optimization
US5860069A (en) 1997-04-11 1999-01-12 Bmc Software, Inc. Method of efficient collection of SQL performance measures
US5875445A (en) 1997-05-29 1999-02-23 Oracle Corporation Performance-related estimation using pseudo-ranked trees
US6289335B1 (en) 1997-06-23 2001-09-11 Oracle Corporation Fast refresh of snapshots containing subqueries
US6353826B1 (en) 1997-10-23 2002-03-05 Sybase, Inc. Database system with methodology providing improved cost estimates for query strategies
US20020138376A1 (en) 1997-10-29 2002-09-26 N_Gine, Inc. Multi-processing financial transaction processing system
US6026391A (en) 1997-10-31 2000-02-15 Oracle Corporation Systems and methods for estimating query response times in a computer system
US6298342B1 (en) 1998-03-16 2001-10-02 Microsoft Corporation Electronic database operations for perspective transformations on relational tables using pivot and unpivot columns
US7007007B2 (en) * 1998-05-14 2006-02-28 Microsoft Corporation Test generator for database management systems providing tight joins
US6205451B1 (en) 1998-05-22 2001-03-20 Oracle Corporation Method and apparatus for incremental refresh of summary tables in a database system
US6339768B1 (en) 1998-08-13 2002-01-15 International Business Machines Corporation Exploitation of subsumption in optimizing scalar subqueries
US6356889B1 (en) 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6334128B1 (en) 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US6941360B1 (en) 1999-02-25 2005-09-06 Oracle International Corporation Determining and registering participants in a distributed transaction in response to commencing participation in said distributed transaction
US6370524B1 (en) 1999-04-02 2002-04-09 Oracle Corp. System and method for processing queries having an inner query block containing a grouping operator
US20020099521A1 (en) 1999-04-30 2002-07-25 Tao-Heng Yang Method and mechanism for profiling a system
US6529901B1 (en) 1999-06-29 2003-03-04 Microsoft Corporation Automating statistics management for query optimizers
US20020038313A1 (en) 1999-07-06 2002-03-28 Compaq Computer Corporation System and method for performing database operations on a continuous stream of tuples
US6438562B1 (en) 1999-08-24 2002-08-20 Oracle Corporation Parallel index maintenance
US6934699B1 (en) 1999-09-01 2005-08-23 International Business Machines Corporation System and method for loading a cache with query results
US6598038B1 (en) * 1999-09-17 2003-07-22 Oracle International Corporation Workload reduction mechanism for index tuning
US20030115212A1 (en) 1999-09-22 2003-06-19 John F. Hornibrook System and process for evaluating the performance of a database system
US6615222B2 (en) * 1999-09-22 2003-09-02 International Business Machines Corporation System and process for evaluating the performance of a database system
US6694306B1 (en) 1999-10-06 2004-02-17 Hitachi, Ltd. System and method for query processing using virtual table interface
US6684203B1 (en) 1999-11-08 2004-01-27 Oracle International Corporation Using global temporary tables to transform queries
US6526526B1 (en) * 1999-11-09 2003-02-25 International Business Machines Corporation Method, system and program for performing remote usability testing
US6430550B1 (en) 1999-12-03 2002-08-06 Oracle Corporation Parallel distinct aggregates
US8386450B2 (en) * 1999-12-22 2013-02-26 International Business Machines Corporation Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US7890491B1 (en) * 1999-12-22 2011-02-15 International Business Machines Corporation Query optimization technique for obtaining improved cardinality estimates using statistics on automatic summary tables
US20040181521A1 (en) * 1999-12-22 2004-09-16 Simmen David E. Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US6438558B1 (en) 1999-12-23 2002-08-20 Ncr Corporation Replicating updates in original temporal order in parallel processing database systems
US20010047372A1 (en) 2000-02-11 2001-11-29 Alexander Gorelik Nested relational data model
US6529896B1 (en) 2000-02-17 2003-03-04 International Business Machines Corporation Method of optimizing a query having an existi subquery and a not-exists subquery
US7194452B2 (en) * 2000-03-31 2007-03-20 Microsoft Corporation Validating multiple execution plans for database queries
US7337169B2 (en) * 2000-03-31 2008-02-26 Microsoft Corporation Validating multiple execution plans for database queries
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US8019750B2 (en) * 2000-05-26 2011-09-13 Computer Associates Think, Inc. System and method for automatically generating database queries
US20070038618A1 (en) * 2000-05-26 2007-02-15 Edward Kosciusko System and method for automatically generating database queries
US20050055382A1 (en) 2000-06-28 2005-03-10 Lounas Ferrat Universal synchronization
US20040205062A1 (en) * 2000-06-30 2004-10-14 Brown Douglas P. Analysis method and apparatus for a parallel system
US7234112B1 (en) 2000-06-30 2007-06-19 Ncr Corp. Presenting query plans of a database system
US7185000B1 (en) 2000-06-30 2007-02-27 Ncr Corp. Method and apparatus for presenting query plans
US7155428B1 (en) * 2000-06-30 2006-12-26 Ncr Corp. Emulating a database system
US6901405B1 (en) 2000-12-20 2005-05-31 Microsoft Corporation Method for persisting a schedule and database schema
US6961729B1 (en) 2001-01-25 2005-11-01 Oracle International Corporation Processing in parallel units of work that perform DML operations on the same spanning rows
US20020188600A1 (en) 2001-03-15 2002-12-12 International Business Machines Corporation Outerjoin and antijoin reordering using extended eligibility lists
US6954776B1 (en) 2001-05-07 2005-10-11 Oracle International Corporation Enabling intra-partition parallelism for partition-based operations
US20030088541A1 (en) * 2001-06-21 2003-05-08 Zilio Daniel C. Method for recommending indexes and materialized views for a database workload
US20030033291A1 (en) 2001-08-03 2003-02-13 David Harris SQL execution analysis
US20030065644A1 (en) 2001-09-28 2003-04-03 Horman Randall W. Database diagnostic system and method
US20030159136A1 (en) 2001-09-28 2003-08-21 Huang Xiao Fei Method and system for server synchronization with a computing device
US6980988B1 (en) 2001-10-01 2005-12-27 Oracle International Corporation Method of applying changes to a standby database system
US20030093408A1 (en) * 2001-10-12 2003-05-15 Brown Douglas P. Index selection in a database system
US7499907B2 (en) 2001-10-12 2009-03-03 Teradata Us, Inc. Index selection in a database system
US20030135480A1 (en) 2002-01-14 2003-07-17 Van Arsdale Robert S. System for updating a database
US20040015600A1 (en) 2002-02-21 2004-01-22 Ashutosh Tiwary Workload post-processing and parameterization for a system for performance testing of N-tiered computer systems using recording and playback of workloads
US20030182276A1 (en) 2002-03-19 2003-09-25 International Business Machines Corporation Method, system, and program for performance tuning a database query
US7139749B2 (en) * 2002-03-19 2006-11-21 International Business Machines Corporation Method, system, and program for performance tuning a database query
US6990503B1 (en) 2002-04-12 2006-01-24 Ncr Corporation Rescheduling transactions in a database system
US20030212647A1 (en) 2002-05-07 2003-11-13 Matthew Jay Bangel Method, system and program product for maintaining a change history for a database design
US20030212668A1 (en) 2002-05-13 2003-11-13 Hinshaw Foster D. Optimized database appliance
US20060129542A1 (en) * 2002-05-13 2006-06-15 Hinshaw Foster D Optimized database appliance
US20030229639A1 (en) * 2002-06-07 2003-12-11 International Business Machines Corporation Runtime query optimization for dynamically selecting from multiple plans in a query based upon runtime-evaluated performance criterion
US20040003004A1 (en) * 2002-06-28 2004-01-01 Microsoft Corporation Time-bound database tuning
US7155459B2 (en) * 2002-06-28 2006-12-26 Miccrosoft Corporation Time-bound database tuning
US6807546B2 (en) 2002-08-12 2004-10-19 Sybase, Inc. Database system with methodology for distributing query optimization effort over large search spaces
US7305410B2 (en) 2002-12-26 2007-12-04 Rocket Software, Inc. Low-latency method to replace SQL insert for bulk data transfer to relational database
US20040244031A1 (en) 2003-02-26 2004-12-02 Peter Martinez System and method for a network of interactive televisions
US20040220911A1 (en) 2003-04-30 2004-11-04 Zuzarte Calisto P. Method and system for aggregation subquery join elimination
US20050028134A1 (en) 2003-07-07 2005-02-03 Netezza Corporation SQL code generation for heterogeneous environment
US7526508B2 (en) * 2003-09-04 2009-04-28 Oracle International Corporation Self-managing database architecture
US20050086195A1 (en) * 2003-09-04 2005-04-21 Leng Leng Tan Self-managing database architecture
US20050138015A1 (en) * 2003-09-06 2005-06-23 Oracle International Corporation High load SQL driven statistics collection
US20050177557A1 (en) * 2003-09-06 2005-08-11 Oracle International Corporation Automatic prevention of run-away query execution
US20050187917A1 (en) * 2003-09-06 2005-08-25 Oracle International Corporation Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer
US7805411B2 (en) * 2003-09-06 2010-09-28 Oracle International Corporation Auto-tuning SQL statements
US20050125427A1 (en) * 2003-09-06 2005-06-09 Oracle International Corporation Automatic SQL tuning advisor
US20050125452A1 (en) * 2003-09-06 2005-06-09 Oracle International Corporation SQL profile
US20050125393A1 (en) * 2003-09-06 2005-06-09 Oracle International Corporation SQL tuning sets
US20050120000A1 (en) * 2003-09-06 2005-06-02 Oracle International Corporation Auto-tuning SQL statements
US7664730B2 (en) * 2003-09-06 2010-02-16 Oracle International Corporation Method and system for implementing a SQL profile
US7747606B2 (en) * 2003-09-06 2010-06-29 Oracle International Corporation Automatic SQL tuning advisor
US20060107141A1 (en) * 2003-11-12 2006-05-18 International Business Machines Corporation Database mining system and method for coverage analysis of functional verification of integrated circuit designs
US7089225B2 (en) 2003-11-25 2006-08-08 International Business Machines Corporation Efficient heuristic approach in selection of materialized views when there are multiple matchings to an SQL query
US20050165741A1 (en) * 2003-12-24 2005-07-28 Gordon Mark R. System and method for addressing inefficient query processing
US20080168058A1 (en) 2003-12-24 2008-07-10 Gordon Mark R System for addressing inefficient query processing
US20050177971A1 (en) 2004-01-19 2005-08-18 Antonio Porco Roll cleaning apparatus
US20050187971A1 (en) 2004-02-19 2005-08-25 Hassan Ahmed E. System and method for searching a remote database
US20050198013A1 (en) 2004-03-08 2005-09-08 Microsoft Corporation Structured indexes on results of function applications over data
US20050203933A1 (en) 2004-03-09 2005-09-15 Microsoft Corporation Transformation tool for mapping XML to relational database
US7406477B2 (en) 2004-03-12 2008-07-29 Sybase, Inc. Database system with methodology for automated determination and selection of optimal indexes
US20050203940A1 (en) 2004-03-12 2005-09-15 Sybase, Inc. Database System with Methodology for Automated Determination and Selection of Optimal Indexes
US20050234965A1 (en) 2004-04-20 2005-10-20 Reuters Limited Computing algebraic equations
US20050262060A1 (en) 2004-05-12 2005-11-24 Oracle International Corporation End-to-end tracing for database applications
US20050278577A1 (en) * 2004-06-01 2005-12-15 Roongko Doong Automatically generating observations of program behavior for code testing purposes
US20080215536A1 (en) * 2004-06-03 2008-09-04 International Business Machines Corporation Autonomically generating a query implementation that meets a defined performance specification
US7647293B2 (en) * 2004-06-10 2010-01-12 International Business Machines Corporation Detecting correlation from data
US20050278357A1 (en) * 2004-06-10 2005-12-15 Brown Paul G Detecting correlation from data
US20050283458A1 (en) * 2004-06-22 2005-12-22 Microsoft Corporation Automatic detection of frequently used query patterns in a query workload
US20050283471A1 (en) 2004-06-22 2005-12-22 Oracle International Corporation Multi-tier query processing
US7246108B2 (en) 2004-07-27 2007-07-17 Oracle International Corporation Reusing optimized query blocks in query processing
US20060026133A1 (en) 2004-07-27 2006-02-02 Oracle International Corporation Determining query cost based on subquery filtering factor
US20060026115A1 (en) 2004-07-27 2006-02-02 Oracle International Corporation Reusing optimized query blocks in query processing
US20060031200A1 (en) 2004-08-05 2006-02-09 International Business Machines Corporation Method and system for tracking performance by breaking down a query
US20060036989A1 (en) * 2004-08-10 2006-02-16 Microsoft Corporation Dynamic physical database design
US20060041537A1 (en) 2004-08-17 2006-02-23 Oracle International Corporation Selecting candidate queries
US20060085378A1 (en) * 2004-10-15 2006-04-20 Microsoft Corporation Schema for physical database tuning
US20060085484A1 (en) * 2004-10-15 2006-04-20 Microsoft Corporation Database tuning advisor
US20060101224A1 (en) * 2004-11-08 2006-05-11 Shah Punit B Autonomic self-tuning of database management system in dynamic logical partitioning environment
US20060195416A1 (en) * 2005-02-28 2006-08-31 Ewen Stephan E Method and system for providing a learning optimizer for federated database systems
US20060212428A1 (en) * 2005-03-15 2006-09-21 International Business Machines Corporation Analysis of performance data from a relational database system for applications using stored procedures or SQL
US20080228710A1 (en) 2005-03-24 2008-09-18 International Business Machines Corporation Building database statistics across a join network using skew values
US20060218123A1 (en) 2005-03-28 2006-09-28 Sybase, Inc. System and Methodology for Parallel Query Optimization Using Semantic-Based Partitioning
US20070038595A1 (en) 2005-08-11 2007-02-15 Bhaskar Ghosh Query processing in a parallel single cursor model on multi-instance configurations, using hints
US7383247B2 (en) 2005-08-29 2008-06-03 International Business Machines Corporation Query routing of federated information systems for fast response time, load balance, availability, and reliability
US20070061379A1 (en) 2005-09-09 2007-03-15 Frankie Wong Method and apparatus for sequencing transactions globally in a distributed database cluster
US20070078825A1 (en) * 2005-09-30 2007-04-05 Sap Ag Systems and methods for repeatable database performance testing
US20070083500A1 (en) * 2005-10-07 2007-04-12 Bez Systems, Inc. Method of incorporating DBMS wizards with analytical models for DBMS servers performance optimization
US8180762B2 (en) * 2005-12-13 2012-05-15 International Business Machines Corporation Database tuning methods
US20070136383A1 (en) * 2005-12-13 2007-06-14 International Business Machines Corporation Database Tuning Method and System
US20070214104A1 (en) * 2006-03-07 2007-09-13 Bingjie Miao Method and system for locking execution plan during database migration
US20080077348A1 (en) * 2006-03-27 2008-03-27 Infineon Technologies Ag Integrated circuit and method for determining the operating range of an integrated circuit
US20080010240A1 (en) 2006-06-30 2008-01-10 Mohamed Zait Executing alternative plans for a SQL statement
US20080040196A1 (en) 2006-07-06 2008-02-14 International Business Machines Corporation Method, system and program product for hosting an on-demand customer interaction center utility infrastructure
US20080052271A1 (en) 2006-08-26 2008-02-28 Eric Lam Method To Converge A Plurality Of SQL Statements Into SQL Skeletons For Enhanced Database Performance Analysis And Tuning
US20080098003A1 (en) * 2006-10-20 2008-04-24 Oracle International Corporation Database workload replay remapping infrastructure
US20080114718A1 (en) 2006-11-09 2008-05-15 Mark John Anderson Apparatus and method for database execution detail repository
US20080126393A1 (en) * 2006-11-29 2008-05-29 Bossman Patrick D Computer program product and system for annotating a problem sql statement for improved understanding
US20080178079A1 (en) * 2007-01-18 2008-07-24 International Business Machines Corporation Apparatus and method for a graphical user interface to facilitate tuning sql statements
US20080235183A1 (en) * 2007-03-21 2008-09-25 Oliver Draese Workload Aware Checking of Database Reorganization
US20090006445A1 (en) 2007-06-28 2009-01-01 Esther Shemenzon Binding between net technologies and SQL server statements
US20090018992A1 (en) 2007-07-12 2009-01-15 Ibm Corporation Management of interesting database statistics
US20090037923A1 (en) 2007-07-31 2009-02-05 Smith Gary S Apparatus and method for detecting resource consumption and preventing workload starvation
US20090077017A1 (en) 2007-09-18 2009-03-19 Oracle International Corporation Sql performance analyzer
US20090327214A1 (en) * 2008-06-25 2009-12-31 Microsoft Corporation Query Execution Plans by Compilation-Time Execution
US20090327254A1 (en) * 2008-06-26 2009-12-31 Microsoft Corporation Configuration-parametric query optimization
US7966313B2 (en) * 2008-06-26 2011-06-21 Microsoft Corporation Configuration-parametric query optimization
US7970755B2 (en) * 2008-07-02 2011-06-28 Oracle Int'l. Corp. Test execution of user SQL in database server code
US20100005340A1 (en) * 2008-07-02 2010-01-07 Oracle International Corporation Test execution of user SQL in database server code

Non-Patent Citations (38)

* Cited by examiner, † Cited by third party
Title
Agrawal et al., "Automated Selection of Materialized Views and Indexes for SQL Databases", In Proceedings of the 26th International Conference on the Very Large Databases, 2000, 10 pages. *
Agrawal et al., "Database Tuning Advisor for Microsoft SQL Server 2005: Demo", In Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data, 2005, pp. 930-932. *
Ahmed, Rafi, et al., "Cost-Based Query Transformation in Oracle," Copyright 2006 VLDB, ACM 1595933859, pp. 1026-1036.
Bello, Randall G. et al. "Materialized Views in Oracle," VLDB '98, Proceedings of 24th International Conference on Very Large Data Bases, Aug. 24-27, 1998, New York City, New York, USA, pp. 659-664.
Bergsten, et al., "Prototyping DBS3 a Shared-Memory Parallel Database System", IEEE 818622954, 226-234, 1991, pp. 226-234.
Bhide, Anupam, "An Analysis of Three Transaction Processing Architectures", Computer Science Division, UC Berkeley, Proceeding of the 14th VLDB Conference,1998, pp. 339-350.
Borla-Salamet, Pascale, "Compiling Control into Database Queries for Parallel Execution Management," IEEE Conference on Parallel Distributed Information Systems, 1991, ISBN 0-8186-2295-4, pp. 271-279.
Chaudhuri, Surajit et al., "Including Group-By in Query Optimization," Proceedings of the 20th VLDB Conference-1994, pp. 354-366.
Chaudhuri, Surajit et al., "Including Group-By in Query Optimization," Proceedings of the 20th VLDB Conference—1994, pp. 354-366.
Chaudhuri, Surajit, "An Overview of Query Optimization in Relational Systems", Microsoft Research, 1998, 10 pages.
Copeland, George et al., "Data Placement in Bubba," ACM 0897912683, 1988, pp. 99-108.
Dageville et al., "Automatic SQL Tuning in Oracle 10g", In Proceedings of the Thirtieth International Conference on Very Large Databases, vol. 30, 2004, pp. 1098-1109. *
Dayal, Umeshwar, "Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates and Quantifiers", Proceedings of the 13th VLDB Conference, Brighton 1987, pp. 197-208.
Dehaan, David, "A Rewriting Algorithm for Multi-Block Aggregation Queries and Views using Prerequisites and Compensations", University of Waterloo, Canada, Technical Report CS-2004-25, May 3, 2004, 39 pages.
Deutsch, Alin et al., "Minimization and Group-By Detection for Nested XQueries", University of California, San Diego, 2003, 15 pages.
Dewitt, et al., "A Performance Analysis of the Gamma Database Machine," Computer Sciences Department, University of Wisconsin, 1988, 33 pages.
Englert, Susan et al., "A Benchmark of NonStop SQL Release 2 Demonstrating Near-Linear Speedup and Scaleup on Large Databases", Technical Report 89.4, Tandem Part No. 27469, May 1989, 32 pages.
Erickson, Gail et al., "Improving Performance with SQL Server 2000 Indexed Views," Microsoft TechNet, Sep. 2000, located on the internet at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx?pf=true, retrieved on Nov. 11, 2006, 10 pages.
Galindo-Legaria, Cesar et al., "Outerjoin Simplification and Reordering for Query Optimization," ACM Transactions on Database Systems, vol. 22, No. 1, Mar. 1997, pp. 43-74.
Gopalkrishnand, Vivikanand, et al. "Issues of Object-Relational View Design in Data Warehousing Environment," IEEE 1998, 0780347781, pp. 2732-2737.
Graefe, Goetz, "Encapsulation of Parallelism in the Volcano Query Processing System," Mar. 1990, 14 pages.
Hayu, John, "Analytic SQL Features in Oracle9i", An Oracle Technical White Paper, Dec. 2001, 32 pages.
Hirano, et al., "Load Balancing Algorithm for Parallel Processing on Shared Memory Multiprocessor", IEEE, pp. 210-217, 1991.
Hong, et al., "Optimization of Parallel Query Execution Plans in XPRS ", Proceedings of the First International Conference on Parallel and Distributed Information Systems, IEEE, 1991, 8 pages.
Kung, Chenho, "Object Subclass Hierarchy in SQL: A Simple Approach," Communications of the AC, Jul. 1990, vol. 33, No. 7, pp. 117-125.
Leverenz et al., "Oracle 8i Concepts Release 8.1.5- A67781-01", Oracle Corporation, Feb. 1999, located on the internet at , 122 pages.
Leverenz et al., "Oracle 8i Concepts Release 8.1.5- A67781-01", Oracle Corporation, Feb. 1999, located on the internet at <http://www.csee.umbc.edu/help/oracle8/server.815/a67781/toc.htm>, 122 pages.
Lumpkin, George et al., "Query Optimization in Oracle 9i", Oracle Corporation, Oracle White Paper, Feb. 2002, pp. 1-30.
Mishra, Priti et al., "Join Processing in Relational Databases," ACM Computing Surveys, vol. 24, No. 1, Mar. 1992, pp. 63-113.
Moro, Gianluca et al. "Incremental maintenance of multi-source views," Database Conference, 2001, ADC 2001, Proceedings, 12th Australasian, Jan. 2001, pp. 13-20.
Muralikrishna, M., "Improved Unnesting Algorithms for Join Aggregate SQL Queries", VLDB Conference, Canada, 1992, 12 pages.
Najjar, Faiza et al. "Cardinality estimation of distributed join queries," Sep. 1-3, 1999; Database and Expert Systems Applications, 1999, Proceedings, Tenth International Workshop on, pp. 66-70.
Oracle® Database Performance Tuning Guide l0g Release 2 (10.2) "Using Plan Stability" pp. 18-1 to 18-10, 10gR2 released Jul. 11, 2005.
Pirahesh, Hamid, et al., "Extensible/Rule Base Query Rewrite Optimization in Starburst", IBM Almaden Research Center, ACM, Sigmod, dated 1992, 10 pages.
Selinger, P. Griffiths, et al., "Access Path Selection in a Relational Database Management System", Proceedings of the 1979 ACM SIGMOD International Conference on the Management of Data, 1979, pp. 23-34.
Seshadri, Preveen, "Cost-Based Optimization for Magic: Algebra and Implementation," SIGMOND '96, 1996 ACM 0-89791-794-4, pp. 435-446.
Stonebraker, Michael, et al. "The Design of XPRS," Proceedings of the 14th VLDB Conference, 1988, 18 pages.
Tandem, "A Benchmark of NonStop SQL on the Debit Credit Transaction", The Tandem Performance Group, 1988, 26 pages.

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20150081669A1 (en) * 2007-09-14 2015-03-19 Oracle International Corporation Fully automated sql tuning
US9720941B2 (en) * 2007-09-14 2017-08-01 Oracle International Corporation Fully automated SQL tuning
US20150149441A1 (en) * 2013-11-25 2015-05-28 Anisoara Nica Data Statistics in Data Management Systems

Also Published As

Publication number Publication date Type
US9720941B2 (en) 2017-08-01 grant
US20150324432A1 (en) 2015-11-12 application
US9734200B2 (en) 2017-08-15 grant
US20150081669A1 (en) 2015-03-19 application
US20090077016A1 (en) 2009-03-19 application

Similar Documents

Publication Publication Date Title
US6349325B1 (en) Prioritized agent-based hierarchy structure for handling performance metrics data in a telecommunication management system
US6691067B1 (en) Enterprise management system and method which includes statistical recreation of system resource usage for more accurate monitoring, prediction, and performance workload characterization
US7831566B2 (en) Systems and methods of hierarchical storage management, such as global management of storage operations
US6938035B2 (en) Reduce database monitor workload by employing predictive query threshold
Herodotou et al. No one (cluster) size fits all: automatic cluster sizing for data-intensive analytics
US20050172306A1 (en) Systems, methods and computer programs for determining dependencies between logical components in a data processing system or network
US7509343B1 (en) System and method of collecting and reporting system performance metrics
Chen et al. Finding the right data for software cost modeling
Gmach et al. Capacity management and demand prediction for next generation data centers
US20130283102A1 (en) Deployment of Profile Models with a Monitoring Agent
US20100161589A1 (en) Graphical Representation of Query Optimizer Search Space In A Database Management System
US20120060142A1 (en) System and method of cost oriented software profiling
US20070150599A1 (en) Generation of resource-usage profiles for application sessions of a number of client computing devices
Ostrand et al. Predicting the location and number of faults in large software systems
US20040133395A1 (en) System and method for statistical performance monitoring
US20050096949A1 (en) Method and system for automatic continuous monitoring and on-demand optimization of business IT infrastructure according to business objectives
US6801903B2 (en) Collecting statistics in a database system
US20070174840A1 (en) Determining the level of availability of a computing resource
US20130132405A1 (en) Dynamically Associating Different Query Execution Strategies with Selective Portions of a Database Table
US20090075648A1 (en) Mobile phone network optimisation systems
Cherkasova et al. Anomaly? application change? or workload change? towards automated detection of application performance anomaly and change
US20090182779A1 (en) Complex dependencies for efficient data warehouse updates
Ganapathi et al. Statistics-driven workload modeling for the cloud
Herodotou et al. Profiling, what-if analysis, and cost-based optimization of mapreduce programs
US20090077235A1 (en) Mechanism for profiling and estimating the runtime needed to execute a job

Legal Events

Date Code Title Description
AS Assignment

Owner name: ORACLE INTERNATIONAL CORPORATION, CALIFORNIA

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:BELKNAP, PETER;DAGEVILLE, BENOIT;DIAS, KARL;AND OTHERS;REEL/FRAME:021694/0601;SIGNING DATES FROM 20080926 TO 20081003

Owner name: ORACLE INTERNATIONAL CORPORATION, CALIFORNIA

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:BELKNAP, PETER;DAGEVILLE, BENOIT;DIAS, KARL;AND OTHERS;SIGNING DATES FROM 20080926 TO 20081003;REEL/FRAME:021694/0601

CC Certificate of correction
MAFP

Free format text: PAYMENT OF MAINTENANCE FEE, 4TH YEAR, LARGE ENTITY (ORIGINAL EVENT CODE: M1551)

Year of fee payment: 4