Patent Application
20080065588
Various embodiments of the present invention are hereinafter described in conjunction with the appended drawings:
It is to be noted, however, that the appended drawings illustrate only example embodiments of the invention, and are therefore not considered limiting of its scope, for the invention may admit to other equally effective embodiments.
Referring to the Drawings, wherein like numbers denote like parts throughout the several views,
The major components of the computer system 100 include one or more processors 101, a main memory 102, a terminal interface 111, a storage interface 112, an I/O (Input/Output) device interface 113, and communications/network interfaces 114, all of which are coupled for inter-component communication via a memory bus 103, an I/O bus 104, and an I/O bus interface unit 105.
The computer system 100 contains one or more general-purpose programmable central processing units (CPUs) 101A, 101B, 101C, and 101D, herein generically referred to as the processor 101. In an embodiment, the computer system 100 contains multiple processors typical of a relatively large system; however, in another embodiment the computer system 100 may alternatively be a single CPU system. Each processor 101 executes instructions stored in the main memory 102 and may include one or more levels of on-board cache.
The main memory 102 is a random-access semiconductor memory for storing or encoding data and programs. In another embodiment, the main memory 102 represents the entire virtual memory of the computer system 100, and may also include the virtual memory of other computer systems coupled to the computer system 100 or connected via the network 130. The main memory 102 is conceptually a single monolithic entity, but in other embodiments the main memory 102 is a more complex arrangement, such as a hierarchy of caches and other memory devices. For example, memory may exist in multiple levels of caches, and these caches may be further divided by function, so that one cache holds instructions while another holds non-instruction data, which is used by the processor or processors. Memory may be further distributed and associated with different CPUs or sets of CPUs, as is known in any of various so-called non-uniform memory access (NUMA) computer architectures.
The memory 102 includes a query parser 150, a query optimizer 152, an access plan 154, a database engine 156, a monitor 158, exit programs 160, a database 162, results 164, a log 166, and return code data 172. Although the query parser 150, the query optimizer 152, the access plan 154, the database engine 156, the monitor 158, the exit programs 160, the database 162, the results 164, the log 166, and the return code data 172 are illustrated as being contained within the memory 102 in the computer system 100, in other embodiments some or all of them may be on different computer systems and may be accessed remotely, e.g., via the network 130. The computer system 100 may use virtual addressing mechanisms that allow the programs of the computer system 100 to behave as if they only have access to a large, single storage entity instead of access to multiple, smaller storage entities. Thus, while the query parser 150, the query optimizer 152, the access plan 154, the database engine 156, the monitor 158, the exit programs 160, the database 162, the results 164, the log 166, and the return code data 172 are illustrated as being contained within the main memory 102, they are not necessarily all completely contained in the same storage device at the same time. Further, although the query parser 150, the query optimizer 152, the access plan 154, the database engine 156, the monitor 158, the exit programs 160, the database 162, the results 164, the log 166, and the return code data 172 are illustrated as being separate entities, in other embodiments some of them, or all of them, may be packaged together.
The query parser 150 responds to submission of a query from the client computer system 132 by providing the query optimizer 152 with parsed code that permits selecting from multiple access plans 154. The query optimizer 152 selects the manner in which queries will be processed by the database engine 156 against the database 162. The primary task of the query optimizer 152 is to determine the most efficient, least expensive, or least costly way to execute each particular query request against the database 162. To this end, the query optimizer 152 chooses one access plan from a group of possible access plans 154. The costs of a particular access plan 154 may be estimated resource requirements determined in terms of time and space. More specifically, the resource requirements may include system information such as the location of database tables and parts of tables, the size of such tables, network node locations, system operating characteristics and statistics, estimated runtime for a query, space usage, and other appropriate information. The access plan 154 contains low-level information indicating what steps the database engine 156 is to take to execute the query.
Once the query optimizer 152 has selected an access plan 154, the query optimizer 152 sends the access plan 154 to the database engine 156, which executes the access plan 154 against the database 162, to create the results 164. The results 164 may include one or more output data tables of records from the database 162, according to the specification included in a query received from the client computer system 132. The terminals 121, 122, 123, or 124 may displays the results 164 to the user, or the results 164 may be sent to the client 132 or to any appropriate computer attached to the network 130.
The database engine 156 includes a query governor 168 and internal storage 170. The query governor 168 determines when to instruct the monitor 158 to collect information. The monitor 158, if executed for a particular query, collects information related to the query and writes the collected information to the log 166. The log 166 may be a base table or some allocated portion of the main memory 102. The log 166 may later be accessed to retrieve query implementation information for purposes of, for example, determining system efficiency or diagnosing problems. The log 166 is further described below with reference to
In an embodiment, the query governor 168 includes instructions capable of executing on the processor 101 or statements capable of being interpreted by instructions executing on the processor 101 to perform the functions as further described below with reference to
The exit programs 160 may be provided by the client computer system 132, a user, or a third party. The exit programs 160 receive information regarding a query from the query governor 168 and return a return code to the query governor 168. The query governor 168 uses the return codes from the exit programs 160 to decide whether to continue execution of the query, cancel execution of the query, write information regarding the query to the log 166, and/or refrain from writing information regarding the query to the log 166. The query governor 168 may invoke multiple exit programs 160 and select between their return codes based on priorities of the return codes. Example return codes and their priorities are further described below with reference to
The database 162 is a repository for data. In an embodiment, the database 162 is a relational database composed of tables of rows (records) and columns (fields) and an index used to access the tables.
The memory bus 103 provides a data communication path for transferring data among the processor 101, the main memory 102, and the I/O bus interface unit 105. The I/O bus interface unit 105 is further coupled to the system I/O bus 104 for transferring data to and from the various I/O units. The I/O bus interface unit 105 communicates with multiple I/O interface units 111, 112, 113, and 114, which are also known as I/O processors (IOPs) or I/O adapters (IOAs), through the system I/O bus 104. The system I/O bus 104 may be, e.g., an industry standard PCI (Peripheral Component Interface) bus, or any other appropriate bus technology.
The I/O interface units support communication with a variety of storage and I/O devices. For example, the terminal interface unit 111 supports the attachment of one or more user terminals 121, 122, 123, and 124. The storage interface unit 112 supports the attachment of one or more direct access storage devices (DASD) 125, 126, and 127 (which are typically rotating magnetic disk drive storage devices, although they could alternatively be other devices, including arrays of disk drives configured to appear as a single large storage device to a host). The contents of the main memory 102 may be stored to and retrieved from the direct access storage devices 125, 126, and 127, as needed.
The I/O device interface 113 provides an interface to any of various other input/output devices or devices of other types. Two such devices, the printer 128 and the fax machine 129, are shown in the exemplary embodiment of
Although the memory bus 103 is shown in
The computer system 100 depicted in
The network 130 may be any suitable network or combination of networks and may support any appropriate protocol suitable for communication of data and/or code to/from the computer system 100. In various embodiments, the network 130 may represent a storage device or a combination of storage devices, either connected directly or indirectly to the computer system 100. In an embodiment, the network 130 may support the Infiniband architecture. In another embodiment, the network 130 may support wireless communications. In another embodiment, the network 130 may support hard-wired communications, such as a telephone line or cable. In another embodiment, the network 130 may support the Ethernet IEEE (Institute of Electrical and Electronics Engineers) 802.3x specification. In another embodiment, the network 130 may be the Internet and may support IP (Internet Protocol).
In another embodiment, the network 130 may be a local area network (LAN) or a wide area network (WAN). In another embodiment, the network 130 may be a hotspot service provider network. In another embodiment, the network 130 may be an intranet. In another embodiment, the network 130 may be a GPRS (General Packet Radio Service) network. In another embodiment, the network 130 may be a FRS (Family Radio Service) network. In another embodiment, the network 130 may be any appropriate cellular data network or cell-based radio network technology. In another embodiment, the network 130 may be an IEEE 802.11B wireless network. In still another embodiment, the network 130 may be any suitable network or combination of networks. Although one network 130 is shown, in other embodiments any number (including zero) of networks (of the same or different types) may be present.
The client computer system 132 may include some or all of the hardware and/or software elements previously described above for the computer system 100. The client computer system 132 includes an application 136, which sends queries to the query parser 150. Although the client computer system 132 is illustrated as being separate from and connected to the computer system 100 via the network 130, in another embodiment, the application 136 may be implemented as a software program and data stored in the memory 102 of the computer system 100.
It should be understood that
The various software components illustrated in
Moreover, while embodiments of the invention have and hereinafter will be described in the context of fully-functioning computer systems, the various embodiments of the invention are capable of being distributed as a program product in a variety of forms, and the invention applies equally regardless of the particular type of signal-bearing medium used to actually carry out the distribution. The programs defining the functions of this embodiment may be delivered to the computer system 100 via a variety of tangible signal-bearing media that may be operatively or communicatively connected (directly or indirectly) to the processor 101. The signal-bearing media may include, but are not limited to:
(1) information permanently stored on a non-rewriteable storage medium, e.g., a read-only memory device attached to or within a computer system, such as a CD-ROM readable by a CD-ROM drive;
(2) alterable information stored on a rewriteable storage medium, e.g., a hard disk drive (e.g., DASD 125, 126, or 127), CD-RW, or diskette; or
(3) information conveyed to the computer system 100 by a communications medium, such as through a computer or a telephone network, e.g., the network 130.
Such tangible signal-bearing media, when encoded with or carrying computer-readable and executable instructions that direct the functions of the present invention, represent embodiments of the present invention.
Embodiments of the present invention may also be delivered as part of a service engagement with a client corporation, nonprofit organization, government entity, internal organizational structure, or the like. Aspects of these embodiments may include configuring a computer system to perform, and deploying software systems and web services that implement, some or all of the methods described herein. Aspects of these embodiments may also include analyzing the client company, creating recommendations responsive to the analysis, generating software to implement portions of the recommendations, integrating the software into existing processes and infrastructure, metering use of the methods and systems described herein, allocating expenses to users, and billing users for their use of these methods and systems.
In addition, various programs described hereinafter may be identified based upon the application for which they are implemented in a specific embodiment of the invention. But, any particular program nomenclature that follows is used merely for convenience, and thus embodiments of the invention should not be limited to use solely in any specific application identified and/or implied by such nomenclature.
The exemplary environments illustrated in
Control then continues to block 310 where the query governor 168 receives an activation request and thresholds (e.g., the values for the predicted runtime threshold, the predicted storage threshold, the actual runtime threshold, and the actual storage threshold) from the application 136.
Control then continues to block 312 where the monitor 158 receives an activation request that requests giving control of logging to the query governor 168 and an optional override parameter from the application 136. In various embodiments, the override parameter is scoped to, applies to, or affects all queries directed to a specified database 162, all queries that execute in a specified job, or all queries executed in the computer system 100.
Control then continues to block 315 where the query parser 150 receives a query from the application 136 that is directed to the database 162. In an embodiment, the query may be in the format of an SQL (Structured Query Language) statement, but in other embodiments, any appropriate format may be used.
Control then continues to block 320 where the query parser 150 interprets or compiles the query to generate an internal query representation of the query. Control then continues to block 325 where the query optimizer 152 receives the internal query representation and generates one or more access plans 154. The access plans 154 represent the computer-generated sequence of operations to obtain the data specified by the query from the database 162.
Control then continues to block 330 where the query optimizer 152 calculates predicted costs for the various access plans 154 and selects the access plan 154 with the lowest cost. The costs are predicted in the sense that the calculation occurs prior to the execution of the access plan 154. In various embodiments, the predicted cost includes a predicted time (predicted runtime) for the execution of the access plan 154 and a predicted amount of the internal storage 170 needed to execute the access plan 154. Generation of the predicted cost involves consideration of both the available access paths (e.g., indexes and sequential reads) and system held statistics on the data to be accessed (e.g., the size of a database table and the number of distinct values in a particular column), to choose what the query optimizer 152 considers to be the most efficient access plan 154 for the query. In an embodiment, the query optimizer 152 may compare the costs of the access plans 154 until either no alternatives remain or a time limit expires. Selection of a plan may be performed by the query optimizer 152 according to a predetermined cost formula. The selection of the most efficient access plan 154 utilizes the query, the database 162 to which the query is directed, and system information that is available to the query optimizer 152. Such system information may include any variety of cost factors such as access paths, system held statistics, estimated runtime, system resources, and system usage.
Control then continues to block 330 where the database engine 156 processes the access plan 154, as further described below with reference to
If the determination at block 405 is true, then the predicted cost to execute the access plan 154 meets a condition, so control continues to block 440 where the query governor 168 determines whether exit program(s) 160 are registered for the condition that was previously determined to have been met at block 405. Thus, the query governor 168 may select a particular exit program or exit programs that meet the condition. If the determination at block 440 is true, then an exit program 160 is registered for the met condition, so control continues to block 445 where the query governor 168 invokes the exit program(s) 160 and receives a return code, as further described below with reference to
Control then continues to block 450 where the query governor 168 processes the return code, as further described below with reference to
If the determination at block 430 is false, then execution of the access plan 154 is to be canceled, so control continues to block 499 where the logic of
If the determination at block 420 is false, then the collect flag is false and data is not to be collected and logged, so control continues to block 430, as previously described above.
If the determination at block 440 is false, then an exit program 160 is not registered for the condition that was detected and met at block 405, so control continues from block 440 to block 455 where the query governor 168 sends an inquiry message and processes the response to the inquiry message, as further described below with reference to
If the determination at block 405 is false, then the predicted cost of executing the access plan does not meet the condition, so control continues to block 435 where the database engine 156 executes the access plan 154, as further described below with reference to
If the determination at block 505 is false, then the return code is not continue and log, so control continues to block 515 where the query governor 168 determines whether the return code from the exit program 160 requests canceling execution of the access plan 154 and collecting and logging data for the access plan 154. If the determination at block 515 is true, then the return code from the exit program 160 requests canceling execution of the access plan 154 and collecting and logging data for the access plan 154, so control continues to block 520 where the query governor 168 sets the continue flag to no, which causes the query governor 168 to cancel execution of the access plan 154 (see
If the determination at block 515 is false, then the return code is not cancel and log, so control continues to block 525 where the query governor 168 determines whether the return code from the exit program 160 requests execution of the access plan 154 to continue and data to not be logged. If the determination at block 525 is true, then the return code from the exit program 160 requests execution of the access plan 154 to continue and data to not be logged, so control continues to block 530 where the query governor 168 determines whether the override parameter has been received that requests overriding the return code that was received from the exit program 160. If the determination at block 530 is true, then the override parameter has been received, so control continues to block 535 where the query governor 168 sets the continue flag to yes, which causes the query governor 168 to continue execution of the access plan 154 (see
If the determination at block 530 is false, then the override parameter was not received, so control continues to block 540 where the query governor 168 sets the continue flag to yes, which causes the query governor 168 to continue execution of the access plan 154 (see
If the determination at block 525 is false, then the return code from the exit program 160 requests the sending of an inquiry message or the exit program 160 failed, so control continues to block 565 where the query governor 168 sends an inquiry message and processes the response, as further described below with reference to
If the determination at block 615 is true, then the response to the inquiry message is a request to continue execution of the access plan 154, so control continues to block 620 where the query governor 168 determines whether the override parameter has been received. If the determination at block 620 is true, then the override parameter has been received, so control continues to block 625 where the query governor 168 sets the continue flag to yes, which causes the query governor 168 to continue execution of the access plan 154 (see
If the determination at block 615 is false, then the response to the inquiry message is not a request to continue execution of the access plan 154, so control continues to block 632 where the query governor 168 determines whether the response to the inquiry message is a request to cancel execution of the access plan 154. If the determination at block 632 is true, then the response to the inquiry message is a request to cancel execution of the access plan 154, so control continues to block 635 where the query governor 168 sets the continue flag to no, which causes the query governor 168 to cancel execution of the access plan 154 (see
If the determination at block 632 is false, then the response to the inquiry message does not request canceling execution of the access plan 154, so control continues to block 640 where the query governor 168 processes other responses, as further described below with reference to
If the determination at block 705 is false, then the response to the inquiry message is not continue and log, so control continues to block 715 where the query governor 168 determines whether the response to the inquiry message requests canceling execution of the access plan 154 and collecting and logging data for the access plan 154. If the determination at block 715 is true, then the response to the inquiry message requests canceling execution of the access plan 154 and collecting and logging data for the access plan 154, so control continues to block 720 where the query governor 168 sets the continue flag to no, which causes the query governor 168 to cancel execution of the access plan 154 (see
If the determination at block 715 is false, then the response to the inquiry message is not cancel and log, so control continues to block 725 where the query governor 168 determines whether the response to the inquiry message requests execution of the access plan 154 to continue and data to not be logged. If the determination at block 725 is true, then the response to the inquiry message requests execution of the access plan 154 to continue and data to not be logged, so control continues to block 730 where the query governor 168 determines whether the override parameter has been received. If the determination at block 730 is true, then the override parameter has been received so control continues to block 735 where the query governor 168 sets the continue flag to yes, which causes the query governor 168 to continue execution of the access plan 154 (see
If the determination at block 730 is false, then the override parameter was not received, so control continues to block 740 where the query governor 168 sets the continue flag to yes, which causes the query governor 168 to continue execution of the access plan 154 (see
If the determination at block 725 is false, then no response to the inquiry message was received, so control continues to block 765 where the query governor 168 sets the continue flag to no, which causes the query governor 168 to cancel execution of the access plan 154 (see
At the expiration of the time period, control then continues to block 810 where the query governor 168 determines whether the actual cost of partially executing the access plan 154 meets a condition. For example, the query governor 168 determines whether the actual query runtime (the actual execution time of the access plan 154) is greater than the actual runtime threshold or the actual amount of the internal storage 170 that is needed to execute the access plan 154 is greater than the actual storage threshold.
If the determination at block 810 is true, then the actual cost of the partial execution of the access plan 154 meets the condition, so control continues to block 845 where the query governor 168 determines whether exit program(s) 160 are registered for the condition that was previously determined to have been met at block 810. Thus, the query governor 168 may select a particular exit program or exit programs that meet the condition. If the determination at block 845 is true, then an exit program 160 is registered for the met condition, so control continues to block 850 where the query governor 168 invokes the registered exit program(s) 160 and receives a return code, as further described below with reference to
Control then continues to block 855 where the query governor 168 processes the return code, as further described below with reference to
Control then continues to block 835 where the query governor 168 determines whether the continue flag is yes, indicating that execution of the access plan 154 is to continue. If the determination at block 835 is true, then execution of the access plan 154 is to continue, so control continues to block 840 where the query governor 168 determines whether execution of the query has been completed by the multiple partial executions of the access plan.
If the determination at block 840 is true, then execution of the query is complete, so control continues to block 899 where the logic of
If the determination at block 840 is false, then the execution of the query is not complete, so control returns to block 850 where the database engine 156 continues partially executing the query via the access plan, as previously described above.
If the determination at block 835 is false, then execution of the access plan 154 is to be canceled, so control continues to block 899 where the logic of
If the determination at block 825 is false, then the collect flag is false and data is not to be collected and logged, so control continues to block 835, as previously described above.
If the determination at block 845 is false, then an exit program 160 is not registered for the condition that was detected and met at block 810, so control continues from block 845 to block 860 where the query governor 168 sends an inquiry message and processes the response to the inquiry message, as further described below with reference to
If the determination at block 810 is false, then the actual cost of the partial execution of the access plan 154 does not meet the condition, so control continues to block 840, as previously described above.
If the determination at block 910 is true, then a registered exit program 160 remains that has not been processed by the loop that starts at block 910, so control continues to block 912 where the query governor 168 determines whether the execution criteria for the current exit program 160 is met. Examples of execution criteria include a specified job in which the condition occurred; a time, date, or day of the week at which the condition occurred; and a specified application or user that submitted the query that was being executed when the condition occurred; or any other appropriate execution criteria. Each of the exit programs 160 may have the same execution criteria, or some or all of the exit programs 160 may have different execution criteria.
If the determination at block 912 is true, then the execution criteria for the current exit program 160 is met, so control continues to block 915 where the query governor 168 sets the current exit program to be an uninvoked exit program that is registered for the met condition and met execution criteria and invokes the current exit program. The query governor 168 may provide to the current exit program 160, e.g., as parameters, the query, the access plan 154, the predicted cost of the access plan 154, the predicted query runtime, the predicted internal storage needed, the predicted runtime threshold, the predicted storage threshold, an identifier of the database 162, information about the system, job, or process that is to execute the access plan 154, information about the application 136 that requested the query, any other appropriate information, or any portion, multiple, or combination thereof. The current exit program 160 analyzes the provided information and returns a current return code, which the query governor 168 receives. The current return code is a request or recommendation of an action to be taken regarding continuing/canceling execution of the access plan and/or logging/not logging data. If the current exit program 160 does not exit, the query governor 168 sets the current return code to indicate that the current exit program 160 does not exist.
Control then continues to block 920 where the query governor 168 determines whether the current return code is the return code with the highest priority 290 in the return code data 172. If the determination at block 920 is true, then the current return code has the highest priority 290, so control continues to block 925 where the query governor 168 sets the saved return code to be the current return code. Control then continues to block 999 where the logic of
If the determination at block 920 is false, then the current return code is not the highest priority return code, so control continues to block 930 where the query governor 168 determines if the current return code has a higher priority 290 than the saved return code (which is the highest priority return code returned so far). If the determination at bock 930 is true, then the current return code does have a higher priority 290 than the saved return code, so control continues to block 935 where the query governor 168 sets the saved return code to be the current return code. Control then returns to block 910, as previously described above.
If the determination at block 930 is false, then the current return code does not have a higher priority 290 than the saved return code, so the query governor 168 sets the current exit program to be the next registered exit program for the met condition, and control returns to block 910, as previously described above.
If the determination at block 912 is false, then the execution criteria for the current exit program 160 is not met, so the query governor 168 sets the current exit program to be the next registered exit program for the met condition, and control returns to block 910, as previously described above.
If the determination at block 910 is false, then all of the registered exit programs have been processed by the loop that starts at block 910, so control continues to block 999 where the logic of
Although the logic of
In the previous detailed description of exemplary embodiments of the invention, reference was made to the accompanying drawings (where like numbers represent like elements), which form a part hereof, and in which is shown by way of illustration specific exemplary embodiments in which the invention may be practiced. These embodiments were described in sufficient detail to enable those skilled in the art to practice the invention, but other embodiments may be utilized and logical, mechanical, electrical, and other changes may be made without departing from the scope of the present invention. In the previous description, numerous specific details were set forth to provide a thorough understanding of embodiments of the invention. But, the invention may be practiced without these specific details. In other instances, well-known circuits, structures, and techniques have not been shown in detail in order not to obscure the invention.
Different instances of the word “embodiment” as used within this specification do not necessarily refer to the same embodiment, but they may. Any data and data structures illustrated or described herein are examples only, and in other embodiments, different amounts of data, types of data, fields, numbers and types of fields, field names, numbers and types of rows, records, entries, or organizations of data may be used. In addition, any data may be combined with logic, so that a separate data structure is not necessary. The previous detailed description is, therefore, not to be taken in a limiting sense, and the scope of the present invention is defined only by the appended claims.
