US20140101132A1 - Swapping expected and candidate affinities in a query plan cache - Google Patents
Swapping expected and candidate affinities in a query plan cache Download PDFInfo
- Publication number
- US20140101132A1 US20140101132A1 US13/794,523 US201313794523A US2014101132A1 US 20140101132 A1 US20140101132 A1 US 20140101132A1 US 201313794523 A US201313794523 A US 201313794523A US 2014101132 A1 US2014101132 A1 US 2014101132A1
- Authority
- US
- United States
- Prior art keywords
- affinity
- query
- expected
- candidate
- computer
- 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.)
- Abandoned
Links
Images
Classifications
-
- G06F17/30442—
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
Definitions
- This invention generally relates to computer database management systems and more specifically relates to query plans in a query plan affinity cache.
- Computer systems typically comprise a combination of computer programs and hardware, such as semiconductors, transistors, chips, circuit boards, storage devices, and processors.
- the computer programs are stored in the storage devices and are executed by the processors.
- computer systems are used for the storage, manipulation, and analysis of data.
- DBMS database management system
- relational database which organizes data in tables that have rows, which represent individual entries, tuples, or records in the database, and columns, fields, or attributes, which define what is stored in each entry, tuple, or record.
- Each table has a unique name or identifier within the database and each column has a unique name within the particular table.
- the database also has one or more indexes, which are data structures that inform the DBMS of the location of a certain row in a table given an indexed column value, analogous to a book index informing the reader of the page on which a given word appears.
- a query is an expression evaluated by the DBMS, in order to retrieve data from the database that satisfies or meets the criteria or conditions specified in the query. Although the query requires the return of a particular data set in response, the method of query execution is typically not specified by the query. Thus, after the DBMS receives a query, the DBMS interprets the query and determines what internal steps are necessary to satisfy the query.
- These internal steps may comprise an identification of the table or tables specified in the query, the row or rows selected in the query, and other information such as whether to use an existing index, whether to build a temporary index, whether to use a temporary file to execute a sort, and/or the order in which the tables are to be joined together to satisfy the query.
- these internal steps are referred to as a query plan (QP), a query execution plan (QEP), a query access plan (QAP), or an access plan (AP).
- QP query plan
- QEP query execution plan
- QAP query access plan
- the DBMS often saves the query plan and reuses it when the user or requesting program repeats the query, which is a common occurrence, instead of undergoing the time-consuming process of recreating the query plan.
- the DBMS may create many different access plans for any one query, each of which returns the required data set, yet the different access plans may provide widely different performance.
- the access plan selected by the DBMS needs to provide the required data at a reasonable cost, in terms of time and hardware resources.
- the DBMS often creates multiple prospective access plans and then chooses the best, or least expensive one, to execute.
- a method, computer-readable storage medium, and computer system are provided.
- a hit percentage of an expected affinity for a first query is calculated, wherein the expected affinity comprises a first address range in a query plan cache
- a hit percentage of a candidate affinity for the first query is calculated, wherein the candidate affinity comprises a second address range in a query plan cache
- query plans in the candidate affinity are swapped with query plans in the expected affinity.
- FIG. 1 depicts a high-level block diagram of an example system for implementing an embodiment of the invention.
- FIG. 2 depicts a block diagram of an example database management system, according to an embodiment of the invention.
- FIG. 3 depicts a block diagram of an example query plan affinity cache, according to an embodiment of the invention.
- FIG. 4 depicts a block diagram of an example data structure for affinity data, according to an embodiment of the invention.
- FIG. 5 depicts a flowchart of example processing for a query, according to an embodiment of the invention.
- FIG. 6 depicts a flowchart of example processing for a query plan affinity cache, according to an embodiment of the invention.
- FIG. 1 depicts a high-level block diagram representation of a server computer system 100 connected to a client computer system 132 via a network 130 , according to an embodiment of the present invention.
- server and “client” are used herein for convenience only, and in various embodiments a computer system that operates as a client computer in one environment may operate as a server computer in another environment, and vice versa.
- the mechanisms and apparatus of embodiments of the present invention apply equally to any appropriate computing system.
- the major components of the server computer system 100 comprise one or more processors 101 , a memory 102 , a terminal interface unit 111 , a storage interface unit 112 , an I/O (Input/Output) device interface unit 113 , and a network interface unit 114 , all of which are communicatively coupled, directly or indirectly, for inter-component communication via a memory bus 103 , an I/O bus 104 , and an I/O bus interface unit 105 .
- the server computer system 100 contains one or more general-purpose programmable central processing units (CPUs) 101 A, 101 B, 101 C, and 101 D, herein generically referred to as the processor 101 .
- the server computer system 100 contains multiple processors typical of a relatively large system; however, in another embodiment the server computer system 100 may alternatively be a single CPU system.
- Each processor 101 executes instructions stored in the memory 102 and may comprise one or more levels of on-board cache.
- the memory 102 may comprise a random-access semiconductor memory, storage device, or storage medium (either volatile or non-volatile) for storing or encoding data and programs.
- the memory 102 represents the entire virtual memory of the server computer system 100 , and may also include the virtual memory of other computer systems coupled to the server computer system 100 or connected via the network 130 .
- the memory 102 is conceptually a single monolithic entity, but in other embodiments the memory 102 is a more complex arrangement, such as a hierarchy of caches and other memory devices.
- 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.
- NUMA non-uniform memory access
- the memory 102 stores or encodes a database management system (DBMS) 150 , a result set 152 , a query 158 , and an application 160 .
- DBMS database management system
- the database management system 150 , the result set 152 , the query 158 , and the application 160 are illustrated as being contained within the memory 102 in the server 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 database management system 150 , the result set 152 , the query 158 , and the application 160 may be stored in memory in the client computer system 132 .
- the server computer system 100 may use virtual addressing mechanisms that allow the programs of the server 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.
- the database management system 150 , the result set 152 , the query 158 , and the application 160 are illustrated as being contained within the memory 102 , these elements are not necessarily all completely contained in the same storage device at the same time.
- the database management system 150 , the result set 152 , the query 158 , and the application 160 are illustrated as being separate entities, in other embodiments some of them, portions of some of them, or all of them may be packaged together.
- the DBMS 150 and/or the application 160 comprise instructions or statements that execute on the processor 101 or instructions or statements that are interpreted by instructions or statements that execute on the processor 101 , to carry out the functions as further described below with reference to FIGS. 2 , 3 , 4 , 5 , and 6 .
- the DBMS 150 and/or the application 160 are implemented in hardware via semiconductor devices, chips, logical gates, circuits, circuit cards, and/or other physical hardware devices in lieu of, or in addition to, a processor-based system.
- the DBMS 150 and/or the application 160 comprise data, in addition to instructions or statements.
- the memory bus 103 provides a data communication path for transferring data among the processor 101 , the 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 .
- IOPs I/O processors
- IOAs I/O adapters
- the I/O interface units support communication with a variety of storage and I/O devices.
- the terminal interface unit 111 supports the attachment of one or more user I/O devices 121 , which may comprise user output devices (such as a video display device, speaker, and/or television set) and user input devices (such as a keyboard, mouse, keypad, touchpad, trackball, buttons, light pen, or other pointing device).
- user input devices such as a keyboard, mouse, keypad, touchpad, trackball, buttons, light pen, or other pointing device.
- a user may manipulate the user input devices using a user interface, in order to provide input data and commands to the user I/O device 121 and the server computer system 100 , and may receive output data via the user output devices.
- a user interface may be presented via the user I/O device 121 , such as displayed on a display device, played via a speaker, or printed via a printer.
- the storage interface unit 112 supports the attachment of one or more disk drives or direct access storage devices 125 (which are typically rotating magnetic disk drive storage devices, although they could alternatively be other storage devices, including arrays of disk drives configured to appear as a single large storage device to a host computer).
- the storage device 125 may be implemented via any type of secondary storage device.
- the contents of the memory 102 , or any portion thereof, may be stored to and retrieved from the storage device 125 , as needed.
- the I/O device interface unit 113 provides an interface to any of various other input/output devices or devices of other types, such as printers or fax machines.
- the network interface unit 114 provides one or more communications paths from the server computer system 100 to other digital devices and client computer systems 132 ; such paths may comprise, e.g., one or more networks 130 .
- the memory bus 103 is shown in FIG. 1 as a relatively simple, single bus structure providing a direct communication path among the processors 101 , the memory 102 , and the I/O bus interface unit 105 , in fact the memory bus 103 may comprise multiple different buses or communication paths, which may be arranged in any of various forms, such as point-to-point links in hierarchical, star or web configurations, multiple hierarchical buses, parallel and redundant paths, or any other appropriate type of configuration.
- the I/O bus interface unit 105 and the I/O bus 104 are shown as single respective units, the server computer system 100 may, in fact, contain multiple I/O bus interface units 105 and/or multiple I/O buses 104 . While multiple I/O interface units are shown, which separate the system I/O bus 104 from various communications paths running to the various I/O devices, in other embodiments some or all of the I/O devices are connected directly to one or more system I/O buses.
- the server computer system 100 is a multi-user mainframe computer system, a single-user system, or a server computer or similar device that has little or no direct user interface, but receives requests from other computer systems (clients).
- the server computer system 100 is implemented as a desktop computer, portable computer, laptop or notebook computer, tablet computer, pocket computer, telephone, smart phone, pager, automobile, teleconferencing system, appliance, or any other appropriate type of electronic device.
- 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 server computer system 100 and the client computer system 132 .
- the network 130 may represent a storage device or a combination of storage devices, either connected directly or indirectly to the server computer system 100 .
- the network 130 may support wireless communications.
- the network 130 may support hard-wired communications, such as a telephone line or cable.
- the network 130 may be the Internet and may support IP (Internet Protocol).
- the network 130 is implemented as a local area network (LAN) or a wide area network (WAN).
- the network 130 is implemented as a hotspot service provider network.
- the network 130 is implemented an intranet. In another embodiment, the network 130 is implemented as any appropriate cellular data network, cell-based radio network technology, or wireless network. In another embodiment, the network 130 is implemented as any suitable network or combination of networks. Although one network 130 is shown, in other embodiments any number of networks (of the same or different types) may be present.
- the client computer system 132 may comprise some or all of the hardware and/or computer program elements of the server computer system 100 .
- the application 160 may be stored in a storage device at the client computer system 132 , may execute on a processor at the client computer system 132 , and may send the queries 158 to and receive the result sets 152 from the server computer system 100 via the network 130 .
- FIG. 1 is intended to depict the representative major components of the server computer system 100 , the network 130 , and the client computer system 132 . But, individual components may have greater complexity than represented in FIG. 1 , components other than or in addition to those shown in FIG. 1 may be present, and the number, type, and configuration of such components may vary. Several particular examples of such additional complexity or additional variations are disclosed herein; these are by way of example only and are not necessarily the only such variations.
- the various program components illustrated in FIG. 1 and implementing various embodiments of the invention may be implemented in a number of manners, including using various computer applications, routines, components, programs, objects, modules, data structures, etc., and are referred to hereinafter as “computer programs,” or simply “programs.”
- the computer programs comprise one or more instructions or statements that are resident at various times in various memory and storage devices in the server computer system 100 and that, when read and executed by one or more processors in the server computer system 100 or when interpreted by instructions that are executed by one or more processors, cause the server computer system 100 to perform the actions necessary to execute steps or elements comprising the various aspects of embodiments of the invention.
- aspects of embodiments of the invention may be embodied as a system, method, or computer program product.
- aspects of embodiments of the invention may take the form of an entirely hardware embodiment, an entirely program embodiment (including firmware, resident programs, micro-code, etc., which are stored in a storage device) or an embodiment combining program and hardware aspects that may all generally be referred to herein as a “circuit,” “module,” or “system.” Further, embodiments of the invention may take the form of a computer program product embodied in one or more computer-readable medium(s) having computer-readable program code embodied thereon.
- the computer-readable medium may be a computer-readable signal medium or a computer-readable storage medium.
- a computer-readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing.
- the computer-readable storage media may comprise: an electrical connection having one or more wires, a portable computer diskette, a hard disk (e.g., the storage device 125 ), a random access memory (RAM) (e.g., the memory 102 ), a read-only memory (ROM), an erasable programmable read-only memory (EPROM) or Flash memory, an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
- a computer-readable storage medium may be any tangible medium that can contain, or store, a program for use by or in connection with an instruction execution system, apparatus, or device.
- a computer-readable signal medium may comprise a propagated data signal with computer-readable program code embodied thereon, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof.
- a computer-readable signal medium may be any computer-readable medium that is not a computer-readable storage medium and that communicates, propagates, or transports a program for use by, or in connection with, an instruction execution system, apparatus, or device.
- Program code embodied on a computer-readable medium may be transmitted using any appropriate medium, including but not limited to, wireless, wire line, optical fiber cable, Radio Frequency, or any suitable combination of the foregoing.
- Computer program code for carrying out operations for aspects of embodiments of the present invention may be written in any combination of one or more programming languages, including object oriented programming languages and conventional procedural programming languages.
- the program code may execute entirely on the user's computer, partly on a remote computer, or entirely on the remote computer or server.
- the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider).
- LAN local area network
- WAN wide area network
- Internet Service Provider for example, AT&T, MCI, Sprint, EarthLink, MSN, GTE, etc.
- These computer program instructions may also be stored in a computer-readable medium that can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions stored in the computer-readable medium produce an article of manufacture, including instructions that implement the function/act specified by the flowchart and/or block diagram block or blocks.
- the computer programs defining the functions of various embodiments of the invention may be delivered to a computer system via a variety of tangible computer-readable storage media that may be operatively or communicatively connected (directly or indirectly) to the processor or processors.
- the computer program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatus, or other devices to produce a computer-implemented process, such that the instructions, which execute on the computer or other programmable apparatus, provide processes for implementing the functions/acts specified in the flowcharts and/or block diagram block or blocks.
- each block in the flowcharts or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s).
- the functions noted in the block may occur out of the order noted in the figures.
- two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved.
- Each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flow chart illustrations can be implemented by special purpose hardware-based systems that perform the specified functions or acts, in combinations of special purpose hardware and computer instructions.
- Embodiments of the invention may also be delivered as part of a service engagement with a client corporation, nonprofit organization, government entity, or internal organizational structure. Aspects of these embodiments may comprise configuring a computer system to perform, and deploying computing services (e.g., computer-readable code, hardware, and web services) that implement, some or all of the methods described herein. Aspects of these embodiments may also comprise analyzing the client company, creating recommendations responsive to the analysis, generating computer-readable code to implement portions of the recommendations, integrating the computer-readable code into existing processes, computer systems, and computing 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.
- computing services e.g., computer-readable code, hardware, and web services
- FIG. 2 depicts a block diagram of an example database management system 150 , according to an embodiment of the invention.
- the DBMS 150 comprises a parser 205 , a parsed statement 210 , an optimizer 215 , an execution engine 220 , a query plan affinity cache 225 , a database 240 , and affinity data 262 .
- the affinity data 262 is stored in the query plan affinity cache 225 .
- the database 240 comprises tables 245 , and optional indexes 250 .
- the tables 245 organize data in rows, which represent individual entries, tuples, or records and columns, fields, or attributes, which define what is stored in each row, entry, tuple, or record.
- Each table 245 has a unique name or identifier within a database 240 (but not necessarily a unique name across all databases) and each column has a unique name within the particular table 245 .
- the indexes 250 are data structures that inform the DBMS 150 of the location of a certain row in a table 245 , in response to the indexes 250 receiving an indexed column value.
- the parser 205 in the DBMS 150 receives the query 158 from the application 160 .
- the query 158 requests that the DBMS 150 search for or find a row or combination of rows of data from the tables 245 and store the data from those found rows into the result set 152 that meet or satisfy the criteria, keys, and or values specified by the query 158 .
- the application 160 sends the same query 158 multiple times to the DBMS 150 , which may or may not result in a different result set 152 , depending on whether the data in the DBMS 150 has changed between occurrences of the query 158 or whether different values are passed into the query 158 via parameter markers or host variables.
- the application 160 sends different queries 158 to the DBMS 150 .
- the parser 205 generates a parsed statement 210 from the query 158 , which the parser 205 sends to the optimizer 215 .
- the optimizer 215 performs query optimization on the parsed statement 210 .
- the optimizer 215 generates one or more query plans and stores them to the query plan affinity cache 225 , using the data in the affinity data 262 and using data such as resource availability, platform capabilities, query content information, etc., that is stored in the database 240 .
- the execution engine 220 reads the selected query plan from the query plan affinity cache 225 and executes the selected query plan, optionally using the indexes 250 , in order to find and retrieve the data from the tables 245 that satisfies the criteria of the query 158 .
- the execution engine 220 stores the resultant data that satisfies the criteria specified by the query 158 into the result set 152 , which the DBMS 150 returns to the application 160 , as a response to the query 158 .
- FIG. 2 illustrates the query plan affinity cache 225 as being contained within the DBMS 150
- the application 160 comprises the query plan affinity cache 225 .
- each application 160 has its own query plan affinity cache 225 within the respective application 160
- each application 160 has its own query plan affinity cache 225 outside the respective application 160
- the applications 160 share the same query plan affinity cache 225 .
- the query plan affinity cache 225 may be scoped system wide, application wide, object wide, to a thread, or any other scoping criteria.
- the application 160 is a user application, a third-party application, an operating system, or any portion, multiple, or combination thereof.
- the query plan affinity cache 225 may be in an SQL (Structured Query Language) package.
- the query plan affinity cache 225 may be stored on the same server computer system 100 as the DBMS 150 , may execute in the same or a different virtual machine as the DBMS 150 , or may be stored on a different computer from the DBMS 150 and accessed, e.g., via the network 130 .
- FIG. 3 depicts a block diagram of an example query plan affinity cache 225 , according to an embodiment of the invention.
- the example query plan affinity cache 225 comprises example affinities 302 , 304 , 306 , 308 , 310 , 312 , 314 , and 316 .
- Each of the affinities 302 , 304 , 306 , 308 , 310 , 312 , 314 , and 316 is identified by a respective affinity identifier and is stored in the query plan affinity cache 225 at a respective contiguous (virtual or physical) address range or a respective set of virtual or physical pages, in the memory 102 , on the storage devices 125 , or both.
- the address ranges at which the affinities are stored are non-overlapping, e.g., the address range (0000 through 00FF) of the affinity 302 does not overlap with the address range (0100 through 01FF) of the affinity 304 .
- some or all of the affinities are organized in a nested or hierarchical organization, at any number of nesting levels, so that the address ranges of some of the affinities are included within the address ranges of other affinities.
- the address range (0400 through 04FF) of the affinity 314 is included within the address range (0400 through 05FF) of the affinity 310
- the address range (0500 through 05FF) of the affinity 316 is included within the address range (0400 through 05FF) of the affinity 310
- the address range (0400 through 04FF) of the affinity 314 does not overlap the address range (0500 through 05FF) of the affinity 316 .
- Each of the affinities 302 , 304 , 306 , 308 , 310 , 312 , 314 , and 316 comprises one or more query plans, which are stored at addresses that are within the address range (greater than or equal to the lower bound of the address range and less than or equal to the upper bound of the address range) of the respective affinity.
- the affinity 302 comprises query plans QP 1 , QP 2 , and QP 3 , which are stored at addresses that are within the range of 0000 through 00FF.
- the query plans when executed by the execution engine 220 , implement respective queries 158 .
- each of the query plans comprise respective join trees, which specify the respective internal steps that the DBMS 150 reads and executes, in order to create the result set 152 that satisfies or meets the criteria specified by the query 158 .
- These internal steps may comprise an identification of the table or tables specified in the query 158 , the values in the row or rows to be selected by the query 158 , and other information such as whether to use an existing index, whether to build a temporary index, whether to scan each table (read the table in storage address order) or use an index to randomly access the table, whether to use a temporary file to execute a sort or hash, and/or the order in which the tables are to be joined together to satisfy the query 158 .
- a join operation is a relationship between two tables accessed by a query (a join query), and the DBMS 150 performs a join operation to connect (or join) data from two or more tables, wherein the DBMS 150 joins together the tuples with matching attributes, in order to form a new tuple.
- the join order is the order in which the DBMS 150 performs the join operations, in order to retrieve and join rows of data from the database tables into the result set 152 .
- the join trees are represented as tree graphs with nodes that identify tables used by the query and connections between the nodes that identify the join order of the rows returned from the tables. In other embodiments, the join trees may be represented via any appropriate technique.
- FIG. 4 depicts a block diagram of an example data structure for affinity data 262 , according to an embodiment of the invention.
- the affinity data 262 comprises any number of entries, each of which comprises a query identifier field 402 , an expected affinity identifier field 404 , an expected affinity hit/total field 406 , a candidate affinity identifier field 408 , a candidate affinity hit/total field 410 , and a query plan (QPLAN) pointer field 412 .
- QPLAN query plan
- the query identifier field 402 in each entry, uniquely identifies a query 158 .
- the expected affinity identifier field 404 in each entry, identifies the affinity, such as the affinity 302 , 304 , 306 , 308 , 310 , 312 , 314 , or 316 , in which the DBMS 150 expects to find the query plan that implements the query 158 identified by the query identifier 402 , in the same entry.
- the expected affinity hit/total field 406 specifies the expected affinity hit count and the expected affinity total count.
- the expected affinity hit count is the number of times that the DBMS 150 has found the query plan (that implements the query identified by the query identifier field 402 , in the same entry) in the affinity identified by the expected affinity identifier field 404 , in the same entry.
- the expected affinity total count, in each entry is the number of times that the DBMS 150 has received the query identified by the query identifier field 402 , in the same entry.
- the candidate affinity identifier field 408 in each entry, identifies the affinity, such as the affinity 302 , 304 , 306 , 308 , 310 , 312 , 314 , or 316 , in which the DBMS 150 expects to find the query plan that implements the query 158 identified by the query identifier 402 , in the same entry if the query plan is not found in the expected affinity identified by the expected affinity identifier field 402 , in the same entry.
- an affinity may be an expected affinity for a first set of queries, a candidate affinity for a second set of queries, and neither an expected affinity nor a candidate affinity for a third set of queries.
- the candidate affinity hit/total field 410 specifies the candidate affinity hit count and the candidate affinity total count.
- the candidate affinity hit count in each entry, is the number of times that the DBMS 150 has found the query plan (that implements the query identified by the query identifier field 402 , in the same entry) in the affinity identified by the candidate affinity identifier field 408 , in the same entry.
- the candidate affinity total count in each entry, is the number of times that the DBMS 150 has received the query identified by the query identifier field 402 , in the same entry.
- the expected affinity total count and the candidate affinity total count are identical, and both indicate the total number of the queries identified by the query identifier 402 , in the same entry, received by the DBMS 150 .
- the query plan (QPLAN) pointer field 412 specifies the address (within the query plan affinity cache 225 ) of the query plan that implements the query identified by the query identifier 402 , in the same entry.
- the query plan pointer field 412 in each entry, may specify an address that is within the address range of the affinity identified by the expected affinity identifier field 404 , in the same entry; may specify an address that is within the address range of the affinity identified by candidate affinity identifier field 408 , in the same entry; or may specify an address that is within an address range of an affinity that is different from both the affinity identified by the expected affinity identifier field 404 and different from the affinity identified by the candidate affinity identifier field 408 , in the same entry.
- FIG. 5 depicts a flowchart of example processing for a query, according to an embodiment of the invention.
- Control begins at block 500 .
- Control then continues to block 505 where the DBMS 150 receives a query 158 from an application 160 .
- Control then continues to block 510 where the DBMS 150 determines an expected affinity where the DBMS 150 expects the query plan that implements the received query 158 to be found or located.
- the DBMS 150 determines the expected affinity from a default affinity.
- the DBMS 150 determines that all queries from the same application have the same expected affinity, all queries from the same user have the same expected affinity, or all queries that specify the same database tables, schema, directory, or catalog have the same affinity.
- the DBMS 150 determines that all queries have the same expected affinity. In an embodiment, the DBMS 150 reads the expected affinity from the user I/O device 121 or receives the expected affinity from the application 160 , from the network 130 , from the client computer system 132 , or from a designer of the DBMS 150 . In an embodiment, the DBMS 150 selects the expected affinity that has the largest expected affinity hit percentage (expected affinity hit count divided by expected affinity total count) of all historical expected affinities in which the query plan that implemented the query identified by the query identifier 402 , in the same entry, was stored.
- expected affinity hit percentage expected affinity hit count divided by expected affinity total count
- the DBMS 150 makes the determination of block 515 by determining whether the address specified by the query plan pointer 412 in the entry with a query identifier 402 that matches the received query is within the address range of the affinity identified by the expected affinity identifier field 404 , in the same entry.
- the query plan that implements the query is located in the expected affinity, so control continues to block 520 where the DBMS 150 increments the affinity hit count, the affinity total count, and the candidate total count, but not the candidate hit count, in the entry in the affinity data 262 that comprises a query identifier 402 that matches the received query. Control then continues to block 525 where the DBMS 150 executes the found query plan, saving rows that satisfy the criteria of the query to the result set 152 and sends the result set 152 to the application 160 from which the DBMS 150 received the query 158 . Control then returns to block 505 where the DBMS 150 receives the same or a different query from the same or a different application, as previously described above.
- the DBMS 150 determines whether the query plan that implements the query is found in any affinity other than the expected affinity (whether the query plan is found in an unexpected affinity). In an embodiment, the DBMS 150 makes the determination of block 530 by determining whether the address specified by the query plan pointer 412 in the entry with a query identifier 402 that matches the received query is within the address range of any affinity (other than the affinity specified by the expected affinity identifier 404 , in the same entry).
- the query plan that implements the query is found in an affinity other than the expected affinity (the query plan is found in an unexpected affinity), so control continues to block 535 where the DBMS 150 increments the expected affinity total count but not the expected affinity hit count in the entry in the affinity data 262 with a query identifier 402 that matches the received query. If the unexpected affinity in which the query plan was found is equal to (is the same as) the candidate affinity specified by the candidate affinity identifier 408 , in the same entry, then the DBMS 150 increments the candidate affinity hit count and the candidate total count.
- the DBMS 150 increments the candidate affinity total count but does not increment in the candidate hit count in the entry with the query identifier 402 that matches the received query. Control then continues to block 525 , as previously described above.
- the DBMS 150 determines a candidate affinity for the query, creates and stores a query plan that implements the query to the address range of the expected affinity, creates an entry in the affinity data 262 that identifies the query, the expected affinity, and the candidate affinity, initializes the expected affinity hit count and the expected affinity total count to one, initializes the candidate affinity hit count and the candidate affinity total count to zero, and initializes the query plan pointer 412 to comprise the address of the query plan within the address range of the expected affinity.
- the DBMS 150 selects the candidate affinity that has the largest candidate affinity hit percentage (the candidate affinity hit count divided by the candidate affinity total count) of all historical candidate affinities in which the query plan that implemented the query identified by the query identifier 402 , in the same entry, was stored. Control then continues to block 525 , as previously described above.
- FIG. 6 depicts a flowchart of example processing for a query plan affinity cache, according to an embodiment of the invention.
- the processing of FIGS. 5 and 6 execute concurrently, substantially concurrently, or interleaved on the same or different of the processors 101 via multi-threading, multi-processing, time-slicing, or multi-programming techniques.
- Control begins at block 600 .
- Control then continues to block 605 where the DBMS 150 begins a loop that executes for each query in each affinity, once for each entry in the affinity data 262 .
- the DBMS 150 calculates the hit percentages of the expected affinity and the candidate affinity. In an embodiment, the DBMS 150 calculates the hit percentages by dividing the expected affinity hit count by the expected affinity total count to yield the hit percentage of the expected affinity and by dividing the candidate affinity hit count by the candidate affinity total count to yield the hit percentage of the candidate affinity.
- the DBMS 150 receives various threshold amounts from a designer of the DBMS 150 , from the user I/O device 121 , from the network 130 , from the client computer system 132 , or from the application 160 . If the determination at block 615 is true, then the hit percentage of the candidate affinity for the query is more than a threshold amount greater than the hit percentage of the expected affinity, so control continues to block 620 where the DBMS 150 swaps the query plans in the expected affinity and the candidate affinity.
- the DBMS 150 moves every query plan from the expected affinity to the candidate affinity and moves every query plan from the candidate affinity (that existed in the candidate affinity prior to the move of the query plans from the expected affinity to the candidate affinity) to the expected affinity.
- DBMS 150 changes the query plan pointers 412 in the entries in the affinity data 262 for every moved query plan to comprise the new addresses of the swapped query plans. Control then returns to block 605 where the DBMS 150 starts processing of the next entry in the affinity data 262 , as previously described above.
- the hit percentage of the candidate affinity for the query is not more than a threshold amount greater than the hit percentage of the expected affinity, so control returns to block 605 where the DBMS 150 starts processing of the next entry in the affinity data 262 , as previously described above.
- each affinity may be an expected affinity, a candidate affinity, or neither an expected affinity nor a candidate affinity).
- the DBMS 150 moves the recently accessed (more recently accessed than a first threshold time) query plans to a first contiguous address range, a first memory or storage device page, or a first set of memory or storage device pages within the respective affinity and moves the not recently accessed (less recently accessed than a second threshold time) query plans to a second contiguous address range, a second memory or storage device page, or a second set of memory or storage device pages, within the address range of the affinity.
- the first contiguous address range is different from and not overlapping with the second contiguous address range, within each respective affinity.
- a defragmenting algorithm physically organizes or moves the contents of an affinity into the smallest number of contiguous regions (fragments).
- Control then returns to block 605 , where the loop that processes all entries in the affinity data 262 restarts from the beginning of the affinity data 262 , as previously described above.
- the DBMS moves frequently or recently used query plans to proximate address ranges in the query plan cache and moves infrequently or not recently used query plans to other address ranges in the query plan cache, which reduces thrashing and increases performance.
Abstract
In an embodiment, a hit percentage of an expected affinity for a first query is calculated, wherein the expected affinity comprises a first address range in a query plan cache, a hit percentage of a candidate affinity for the first query is calculated, wherein the candidate affinity comprises a second address range in a query plan cache, and if the hit percentage of the candidate affinity is greater than the hit percentage of the expected affinity by more than a threshold amount, query plans in the candidate affinity are swapped with query plans in the expected affinity.
Description
- This application is a continuation of U.S. patent application Ser. No. 13/646,971, filed Oct. 8, 2012, entitled “SWAPPING EXPECTED AND CANDIDATE AFFINITIES IN A QUERY PLAN CACHE,” and incorporated by reference herein in its entirety.
- This invention generally relates to computer database management systems and more specifically relates to query plans in a query plan affinity cache.
- Computer systems typically comprise a combination of computer programs and hardware, such as semiconductors, transistors, chips, circuit boards, storage devices, and processors. The computer programs are stored in the storage devices and are executed by the processors. Fundamentally, computer systems are used for the storage, manipulation, and analysis of data.
- One mechanism for managing data is called a database management system (DBMS) or simply a database. Many different types of databases are known, but the most common is usually called a relational database, which organizes data in tables that have rows, which represent individual entries, tuples, or records in the database, and columns, fields, or attributes, which define what is stored in each entry, tuple, or record. Each table has a unique name or identifier within the database and each column has a unique name within the particular table. The database also has one or more indexes, which are data structures that inform the DBMS of the location of a certain row in a table given an indexed column value, analogous to a book index informing the reader of the page on which a given word appears.
- The most common way to retrieve data from a database is through statements called database queries, which may originate from user interfaces, application programs, or remote computer systems, such as clients or peers. A query is an expression evaluated by the DBMS, in order to retrieve data from the database that satisfies or meets the criteria or conditions specified in the query. Although the query requires the return of a particular data set in response, the method of query execution is typically not specified by the query. Thus, after the DBMS receives a query, the DBMS interprets the query and determines what internal steps are necessary to satisfy the query. These internal steps may comprise an identification of the table or tables specified in the query, the row or rows selected in the query, and other information such as whether to use an existing index, whether to build a temporary index, whether to use a temporary file to execute a sort, and/or the order in which the tables are to be joined together to satisfy the query. When taken together, these internal steps are referred to as a query plan (QP), a query execution plan (QEP), a query access plan (QAP), or an access plan (AP). The DBMS often saves the query plan and reuses it when the user or requesting program repeats the query, which is a common occurrence, instead of undergoing the time-consuming process of recreating the query plan.
- The DBMS may create many different access plans for any one query, each of which returns the required data set, yet the different access plans may provide widely different performance. Thus, especially for large databases, the access plan selected by the DBMS needs to provide the required data at a reasonable cost, in terms of time and hardware resources. Hence, the DBMS often creates multiple prospective access plans and then chooses the best, or least expensive one, to execute.
- A method, computer-readable storage medium, and computer system are provided. In an embodiment, a hit percentage of an expected affinity for a first query is calculated, wherein the expected affinity comprises a first address range in a query plan cache, a hit percentage of a candidate affinity for the first query is calculated, wherein the candidate affinity comprises a second address range in a query plan cache, and if the hit percentage of the candidate affinity is greater than the hit percentage of the expected affinity by more than a threshold amount, query plans in the candidate affinity are swapped with query plans in the expected affinity.
-
FIG. 1 depicts a high-level block diagram of an example system for implementing an embodiment of the invention. -
FIG. 2 depicts a block diagram of an example database management system, according to an embodiment of the invention. -
FIG. 3 depicts a block diagram of an example query plan affinity cache, according to an embodiment of the invention. -
FIG. 4 depicts a block diagram of an example data structure for affinity data, according to an embodiment of the invention. -
FIG. 5 depicts a flowchart of example processing for a query, according to an embodiment of the invention. -
FIG. 6 depicts a flowchart of example processing for a query plan affinity cache, according to an embodiment of the invention. - It is to be noted, however, that the appended drawings illustrate only example embodiments of the invention, and are therefore not considered a limitation of the scope of other embodiments of the invention.
- Referring to the Drawings, wherein like numbers denote like parts throughout the several views,
FIG. 1 depicts a high-level block diagram representation of aserver computer system 100 connected to aclient computer system 132 via anetwork 130, according to an embodiment of the present invention. The terms “server” and “client” are used herein for convenience only, and in various embodiments a computer system that operates as a client computer in one environment may operate as a server computer in another environment, and vice versa. The mechanisms and apparatus of embodiments of the present invention apply equally to any appropriate computing system. - The major components of the
server computer system 100 comprise one ormore processors 101, amemory 102, aterminal interface unit 111, astorage interface unit 112, an I/O (Input/Output)device interface unit 113, and anetwork interface unit 114, all of which are communicatively coupled, directly or indirectly, for inter-component communication via amemory bus 103, an I/O bus 104, and an I/Obus interface unit 105. - The
server computer system 100 contains one or more general-purpose programmable central processing units (CPUs) 101A, 101B, 101C, and 101D, herein generically referred to as theprocessor 101. In an embodiment, theserver computer system 100 contains multiple processors typical of a relatively large system; however, in another embodiment theserver computer system 100 may alternatively be a single CPU system. Eachprocessor 101 executes instructions stored in thememory 102 and may comprise one or more levels of on-board cache. - In an embodiment, the
memory 102 may comprise a random-access semiconductor memory, storage device, or storage medium (either volatile or non-volatile) for storing or encoding data and programs. In another embodiment, thememory 102 represents the entire virtual memory of theserver computer system 100, and may also include the virtual memory of other computer systems coupled to theserver computer system 100 or connected via thenetwork 130. Thememory 102 is conceptually a single monolithic entity, but in other embodiments thememory 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 stores or encodes a database management system (DBMS) 150, a result set 152, aquery 158, and anapplication 160. Although thedatabase management system 150, the result set 152, thequery 158, and theapplication 160 are illustrated as being contained within thememory 102 in theserver 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 thenetwork 130. For example, thedatabase management system 150, the result set 152, thequery 158, and theapplication 160 may be stored in memory in theclient computer system 132. Theserver computer system 100 may use virtual addressing mechanisms that allow the programs of theserver 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 thedatabase management system 150, the result set 152, thequery 158, and theapplication 160 are illustrated as being contained within thememory 102, these elements are not necessarily all completely contained in the same storage device at the same time. Further, although thedatabase management system 150, the result set 152, thequery 158, and theapplication 160 are illustrated as being separate entities, in other embodiments some of them, portions of some of them, or all of them may be packaged together. - In an embodiment, the DBMS 150 and/or the
application 160 comprise instructions or statements that execute on theprocessor 101 or instructions or statements that are interpreted by instructions or statements that execute on theprocessor 101, to carry out the functions as further described below with reference toFIGS. 2 , 3, 4, 5, and 6. In another embodiment, the DBMS 150 and/or theapplication 160 are implemented in hardware via semiconductor devices, chips, logical gates, circuits, circuit cards, and/or other physical hardware devices in lieu of, or in addition to, a processor-based system. In an embodiment, the DBMS 150 and/or theapplication 160 comprise data, in addition to instructions or statements. - The
memory bus 103 provides a data communication path for transferring data among theprocessor 101, thememory 102, and the I/Obus interface unit 105. The I/Obus 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/Obus interface unit 105 communicates with multiple I/O interface units O bus 104. - 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 I/O devices 121, which may comprise user output devices (such as a video display device, speaker, and/or television set) and user input devices (such as a keyboard, mouse, keypad, touchpad, trackball, buttons, light pen, or other pointing device). A user may manipulate the user input devices using a user interface, in order to provide input data and commands to the user I/O device 121 and theserver computer system 100, and may receive output data via the user output devices. For example, a user interface may be presented via the user I/O device 121, such as displayed on a display device, played via a speaker, or printed via a printer. - The
storage interface unit 112 supports the attachment of one or more disk drives or direct access storage devices 125 (which are typically rotating magnetic disk drive storage devices, although they could alternatively be other storage devices, including arrays of disk drives configured to appear as a single large storage device to a host computer). In another embodiment, thestorage device 125 may be implemented via any type of secondary storage device. The contents of thememory 102, or any portion thereof, may be stored to and retrieved from thestorage device 125, as needed. The I/Odevice interface unit 113 provides an interface to any of various other input/output devices or devices of other types, such as printers or fax machines. Thenetwork interface unit 114 provides one or more communications paths from theserver computer system 100 to other digital devices andclient computer systems 132; such paths may comprise, e.g., one ormore networks 130. - Although the
memory bus 103 is shown inFIG. 1 as a relatively simple, single bus structure providing a direct communication path among theprocessors 101, thememory 102, and the I/Obus interface unit 105, in fact thememory bus 103 may comprise multiple different buses or communication paths, which may be arranged in any of various forms, such as point-to-point links in hierarchical, star or web configurations, multiple hierarchical buses, parallel and redundant paths, or any other appropriate type of configuration. Furthermore, while the I/Obus interface unit 105 and the I/O bus 104 are shown as single respective units, theserver computer system 100 may, in fact, contain multiple I/Obus interface units 105 and/or multiple I/O buses 104. While multiple I/O interface units are shown, which separate the system I/O bus 104 from various communications paths running to the various I/O devices, in other embodiments some or all of the I/O devices are connected directly to one or more system I/O buses. - In various embodiments, the
server computer system 100 is a multi-user mainframe computer system, a single-user system, or a server computer or similar device that has little or no direct user interface, but receives requests from other computer systems (clients). In other embodiments, theserver computer system 100 is implemented as a desktop computer, portable computer, laptop or notebook computer, tablet computer, pocket computer, telephone, smart phone, pager, automobile, teleconferencing system, appliance, or any other appropriate type of electronic device. - 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 theserver computer system 100 and theclient computer system 132. In various embodiments, thenetwork 130 may represent a storage device or a combination of storage devices, either connected directly or indirectly to theserver computer system 100. In another embodiment, thenetwork 130 may support wireless communications. In another embodiment, thenetwork 130 may support hard-wired communications, such as a telephone line or cable. In another embodiment, thenetwork 130 may be the Internet and may support IP (Internet Protocol). In another embodiment, thenetwork 130 is implemented as a local area network (LAN) or a wide area network (WAN). In another embodiment, thenetwork 130 is implemented as a hotspot service provider network. In another embodiment, thenetwork 130 is implemented an intranet. In another embodiment, thenetwork 130 is implemented as any appropriate cellular data network, cell-based radio network technology, or wireless network. In another embodiment, thenetwork 130 is implemented as any suitable network or combination of networks. Although onenetwork 130 is shown, in other embodiments any number of networks (of the same or different types) may be present. - The
client computer system 132 may comprise some or all of the hardware and/or computer program elements of theserver computer system 100. In an embodiment, theapplication 160 may be stored in a storage device at theclient computer system 132, may execute on a processor at theclient computer system 132, and may send thequeries 158 to and receive the result sets 152 from theserver computer system 100 via thenetwork 130. -
FIG. 1 is intended to depict the representative major components of theserver computer system 100, thenetwork 130, and theclient computer system 132. But, individual components may have greater complexity than represented inFIG. 1 , components other than or in addition to those shown inFIG. 1 may be present, and the number, type, and configuration of such components may vary. Several particular examples of such additional complexity or additional variations are disclosed herein; these are by way of example only and are not necessarily the only such variations. The various program components illustrated inFIG. 1 and implementing various embodiments of the invention may be implemented in a number of manners, including using various computer applications, routines, components, programs, objects, modules, data structures, etc., and are referred to hereinafter as “computer programs,” or simply “programs.” - The computer programs comprise one or more instructions or statements that are resident at various times in various memory and storage devices in the
server computer system 100 and that, when read and executed by one or more processors in theserver computer system 100 or when interpreted by instructions that are executed by one or more processors, cause theserver computer system 100 to perform the actions necessary to execute steps or elements comprising the various aspects of embodiments of the invention. Aspects of embodiments of the invention may be embodied as a system, method, or computer program product. Accordingly, aspects of embodiments of the invention may take the form of an entirely hardware embodiment, an entirely program embodiment (including firmware, resident programs, micro-code, etc., which are stored in a storage device) or an embodiment combining program and hardware aspects that may all generally be referred to herein as a “circuit,” “module,” or “system.” Further, embodiments of the invention may take the form of a computer program product embodied in one or more computer-readable medium(s) having computer-readable program code embodied thereon. - Any combination of one or more computer-readable medium(s) may be utilized. The computer-readable medium may be a computer-readable signal medium or a computer-readable storage medium. A computer-readable storage medium, may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples (an non-exhaustive list) of the computer-readable storage media may comprise: an electrical connection having one or more wires, a portable computer diskette, a hard disk (e.g., the storage device 125), a random access memory (RAM) (e.g., the memory 102), a read-only memory (ROM), an erasable programmable read-only memory (EPROM) or Flash memory, an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer-readable storage medium may be any tangible medium that can contain, or store, a program for use by or in connection with an instruction execution system, apparatus, or device.
- A computer-readable signal medium may comprise a propagated data signal with computer-readable program code embodied thereon, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer-readable signal medium may be any computer-readable medium that is not a computer-readable storage medium and that communicates, propagates, or transports a program for use by, or in connection with, an instruction execution system, apparatus, or device. Program code embodied on a computer-readable medium may be transmitted using any appropriate medium, including but not limited to, wireless, wire line, optical fiber cable, Radio Frequency, or any suitable combination of the foregoing.
- Computer program code for carrying out operations for aspects of embodiments of the present invention may be written in any combination of one or more programming languages, including object oriented programming languages and conventional procedural programming languages. The program code may execute entirely on the user's computer, partly on a remote computer, or entirely on the remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider).
- Aspects of embodiments of the invention are described below with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products. Each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams may be implemented by computer program instructions embodied in a computer-readable medium. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified by the flowchart and/or block diagram block or blocks. These computer program instructions may also be stored in a computer-readable medium that can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions stored in the computer-readable medium produce an article of manufacture, including instructions that implement the function/act specified by the flowchart and/or block diagram block or blocks.
- The computer programs defining the functions of various embodiments of the invention may be delivered to a computer system via a variety of tangible computer-readable storage media that may be operatively or communicatively connected (directly or indirectly) to the processor or processors. The computer program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatus, or other devices to produce a computer-implemented process, such that the instructions, which execute on the computer or other programmable apparatus, provide processes for implementing the functions/acts specified in the flowcharts and/or block diagram block or blocks.
- The flowchart and the block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products, according to various embodiments of the present invention. In this regard, each block in the flowcharts or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). In some embodiments, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. Each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flow chart illustrations, can be implemented by special purpose hardware-based systems that perform the specified functions or acts, in combinations of special purpose hardware and computer instructions.
- Embodiments of the invention may also be delivered as part of a service engagement with a client corporation, nonprofit organization, government entity, or internal organizational structure. Aspects of these embodiments may comprise configuring a computer system to perform, and deploying computing services (e.g., computer-readable code, hardware, and web services) that implement, some or all of the methods described herein. Aspects of these embodiments may also comprise analyzing the client company, creating recommendations responsive to the analysis, generating computer-readable code to implement portions of the recommendations, integrating the computer-readable code into existing processes, computer systems, and computing 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 are not limited to use solely in any specific application identified and/or implied by such nomenclature. The exemplary environments illustrated in
FIG. 1 are not intended to limit the present invention. Indeed, other alternative hardware and/or program environments may be used without departing from the scope of embodiments of the invention. -
FIG. 2 depicts a block diagram of an exampledatabase management system 150, according to an embodiment of the invention. TheDBMS 150 comprises aparser 205, a parsedstatement 210, anoptimizer 215, anexecution engine 220, a queryplan affinity cache 225, adatabase 240, andaffinity data 262. In another embodiment, theaffinity data 262 is stored in the queryplan affinity cache 225. - The
database 240 comprises tables 245, andoptional indexes 250. The tables 245 organize data in rows, which represent individual entries, tuples, or records and columns, fields, or attributes, which define what is stored in each row, entry, tuple, or record. Each table 245 has a unique name or identifier within a database 240 (but not necessarily a unique name across all databases) and each column has a unique name within the particular table 245. Theindexes 250 are data structures that inform theDBMS 150 of the location of a certain row in a table 245, in response to theindexes 250 receiving an indexed column value. - The
parser 205 in theDBMS 150 receives thequery 158 from theapplication 160. Thequery 158 requests that theDBMS 150 search for or find a row or combination of rows of data from the tables 245 and store the data from those found rows into the result set 152 that meet or satisfy the criteria, keys, and or values specified by thequery 158. In an embodiment, theapplication 160 sends thesame query 158 multiple times to theDBMS 150, which may or may not result in a different result set 152, depending on whether the data in theDBMS 150 has changed between occurrences of thequery 158 or whether different values are passed into thequery 158 via parameter markers or host variables. In an embodiment, theapplication 160 sendsdifferent queries 158 to theDBMS 150. Theparser 205 generates a parsedstatement 210 from thequery 158, which theparser 205 sends to theoptimizer 215. Theoptimizer 215 performs query optimization on the parsedstatement 210. As a part of query optimization, theoptimizer 215 generates one or more query plans and stores them to the queryplan affinity cache 225, using the data in theaffinity data 262 and using data such as resource availability, platform capabilities, query content information, etc., that is stored in thedatabase 240. - The
execution engine 220 reads the selected query plan from the queryplan affinity cache 225 and executes the selected query plan, optionally using theindexes 250, in order to find and retrieve the data from the tables 245 that satisfies the criteria of thequery 158. Theexecution engine 220 stores the resultant data that satisfies the criteria specified by thequery 158 into the result set 152, which theDBMS 150 returns to theapplication 160, as a response to thequery 158. - Although
FIG. 2 illustrates the queryplan affinity cache 225 as being contained within theDBMS 150, in another embodiment, theapplication 160 comprises the queryplan affinity cache 225. In various embodiments, eachapplication 160 has its own queryplan affinity cache 225 within therespective application 160, eachapplication 160 has its own queryplan affinity cache 225 outside therespective application 160, or theapplications 160 share the same queryplan affinity cache 225. The queryplan affinity cache 225 may be scoped system wide, application wide, object wide, to a thread, or any other scoping criteria. In various embodiments, theapplication 160 is a user application, a third-party application, an operating system, or any portion, multiple, or combination thereof. In an embodiment, the queryplan affinity cache 225 may be in an SQL (Structured Query Language) package. In various embodiment, the queryplan affinity cache 225 may be stored on the sameserver computer system 100 as theDBMS 150, may execute in the same or a different virtual machine as theDBMS 150, or may be stored on a different computer from theDBMS 150 and accessed, e.g., via thenetwork 130. -
FIG. 3 depicts a block diagram of an example queryplan affinity cache 225, according to an embodiment of the invention. The example queryplan affinity cache 225 comprisesexample affinities affinities plan affinity cache 225 at a respective contiguous (virtual or physical) address range or a respective set of virtual or physical pages, in thememory 102, on thestorage devices 125, or both. In an embodiment, the address ranges at which the affinities are stored are non-overlapping, e.g., the address range (0000 through 00FF) of theaffinity 302 does not overlap with the address range (0100 through 01FF) of theaffinity 304. In another embodiment, some or all of the affinities are organized in a nested or hierarchical organization, at any number of nesting levels, so that the address ranges of some of the affinities are included within the address ranges of other affinities. For example, the address range (0400 through 04FF) of theaffinity 314 is included within the address range (0400 through 05FF) of theaffinity 310, and the address range (0500 through 05FF) of theaffinity 316 is included within the address range (0400 through 05FF) of theaffinity 310, but the address range (0400 through 04FF) of theaffinity 314 does not overlap the address range (0500 through 05FF) of theaffinity 316. - Each of the
affinities affinity 302 comprises query plans QP1, QP2, and QP3, which are stored at addresses that are within the range of 0000 through 00FF. The query plans, when executed by theexecution engine 220, implementrespective queries 158. - In an embodiment, each of the query plans comprise respective join trees, which specify the respective internal steps that the
DBMS 150 reads and executes, in order to create the result set 152 that satisfies or meets the criteria specified by thequery 158. These internal steps may comprise an identification of the table or tables specified in thequery 158, the values in the row or rows to be selected by thequery 158, and other information such as whether to use an existing index, whether to build a temporary index, whether to scan each table (read the table in storage address order) or use an index to randomly access the table, whether to use a temporary file to execute a sort or hash, and/or the order in which the tables are to be joined together to satisfy thequery 158. - In response to a
query 158 that requests data from multiple tables, theDBMS 150 joins tuples from these multiple tables (the tuples are often concatenated horizontally in the result set 152) in a join order, so as to find and retrieve the data from all the tables. Thus, a join operation is a relationship between two tables accessed by a query (a join query), and theDBMS 150 performs a join operation to connect (or join) data from two or more tables, wherein theDBMS 150 joins together the tuples with matching attributes, in order to form a new tuple. The join order is the order in which theDBMS 150 performs the join operations, in order to retrieve and join rows of data from the database tables into the result set 152. In an embodiment, the join trees are represented as tree graphs with nodes that identify tables used by the query and connections between the nodes that identify the join order of the rows returned from the tables. In other embodiments, the join trees may be represented via any appropriate technique. -
FIG. 4 depicts a block diagram of an example data structure foraffinity data 262, according to an embodiment of the invention. Theaffinity data 262 comprises any number of entries, each of which comprises aquery identifier field 402, an expectedaffinity identifier field 404, an expected affinity hit/total field 406, a candidateaffinity identifier field 408, a candidate affinity hit/total field 410, and a query plan (QPLAN)pointer field 412. - The
query identifier field 402, in each entry, uniquely identifies aquery 158. The expectedaffinity identifier field 404, in each entry, identifies the affinity, such as theaffinity DBMS 150 expects to find the query plan that implements thequery 158 identified by thequery identifier 402, in the same entry. - The expected affinity hit/
total field 406, in each entry, specifies the expected affinity hit count and the expected affinity total count. The expected affinity hit count, in each entry, is the number of times that theDBMS 150 has found the query plan (that implements the query identified by thequery identifier field 402, in the same entry) in the affinity identified by the expectedaffinity identifier field 404, in the same entry. The expected affinity total count, in each entry, is the number of times that theDBMS 150 has received the query identified by thequery identifier field 402, in the same entry. - The candidate
affinity identifier field 408, in each entry, identifies the affinity, such as theaffinity DBMS 150 expects to find the query plan that implements thequery 158 identified by thequery identifier 402, in the same entry if the query plan is not found in the expected affinity identified by the expectedaffinity identifier field 402, in the same entry. In an embodiment, an affinity may be an expected affinity for a first set of queries, a candidate affinity for a second set of queries, and neither an expected affinity nor a candidate affinity for a third set of queries. - The candidate affinity hit/
total field 410, in each entry, specifies the candidate affinity hit count and the candidate affinity total count. The candidate affinity hit count, in each entry, is the number of times that theDBMS 150 has found the query plan (that implements the query identified by thequery identifier field 402, in the same entry) in the affinity identified by the candidateaffinity identifier field 408, in the same entry. The candidate affinity total count, in each entry, is the number of times that theDBMS 150 has received the query identified by thequery identifier field 402, in the same entry. In an embodiment, the expected affinity total count and the candidate affinity total count are identical, and both indicate the total number of the queries identified by thequery identifier 402, in the same entry, received by theDBMS 150. - The query plan (QPLAN)
pointer field 412, in each entry, specifies the address (within the query plan affinity cache 225) of the query plan that implements the query identified by thequery identifier 402, in the same entry. In various embodiments, the queryplan pointer field 412, in each entry, may specify an address that is within the address range of the affinity identified by the expectedaffinity identifier field 404, in the same entry; may specify an address that is within the address range of the affinity identified by candidateaffinity identifier field 408, in the same entry; or may specify an address that is within an address range of an affinity that is different from both the affinity identified by the expectedaffinity identifier field 404 and different from the affinity identified by the candidateaffinity identifier field 408, in the same entry. -
FIG. 5 depicts a flowchart of example processing for a query, according to an embodiment of the invention. Control begins atblock 500. Control then continues to block 505 where theDBMS 150 receives aquery 158 from anapplication 160. Control then continues to block 510 where theDBMS 150 determines an expected affinity where theDBMS 150 expects the query plan that implements the receivedquery 158 to be found or located. In an embodiment, theDBMS 150 determines the expected affinity from a default affinity. In various embodiments, theDBMS 150 determines that all queries from the same application have the same expected affinity, all queries from the same user have the same expected affinity, or all queries that specify the same database tables, schema, directory, or catalog have the same affinity. In an embodiment, theDBMS 150 determines that all queries have the same expected affinity. In an embodiment, theDBMS 150 reads the expected affinity from the user I/O device 121 or receives the expected affinity from theapplication 160, from thenetwork 130, from theclient computer system 132, or from a designer of theDBMS 150. In an embodiment, theDBMS 150 selects the expected affinity that has the largest expected affinity hit percentage (expected affinity hit count divided by expected affinity total count) of all historical expected affinities in which the query plan that implemented the query identified by thequery identifier 402, in the same entry, was stored. - Control then continues to block 515 where the
DBMS 150 determines whether the query plan that implements the query is located or stored in the expected affinity. In an embodiment, theDBMS 150 makes the determination ofblock 515 by determining whether the address specified by thequery plan pointer 412 in the entry with aquery identifier 402 that matches the received query is within the address range of the affinity identified by the expectedaffinity identifier field 404, in the same entry. If the determination atblock 515 is true, then the query plan that implements the query is located in the expected affinity, so control continues to block 520 where theDBMS 150 increments the affinity hit count, the affinity total count, and the candidate total count, but not the candidate hit count, in the entry in theaffinity data 262 that comprises aquery identifier 402 that matches the received query. Control then continues to block 525 where theDBMS 150 executes the found query plan, saving rows that satisfy the criteria of the query to the result set 152 and sends the result set 152 to theapplication 160 from which theDBMS 150 received thequery 158. Control then returns to block 505 where theDBMS 150 receives the same or a different query from the same or a different application, as previously described above. - If the determination at
block 515 is false, then the query plan that implements the query is not located in the expected affinity, so control continues to block 530 where theDBMS 150 determines whether the query plan that implements the query is found in any affinity other than the expected affinity (whether the query plan is found in an unexpected affinity). In an embodiment, theDBMS 150 makes the determination ofblock 530 by determining whether the address specified by thequery plan pointer 412 in the entry with aquery identifier 402 that matches the received query is within the address range of any affinity (other than the affinity specified by the expectedaffinity identifier 404, in the same entry). - If the determination at
block 530 is true, then the query plan that implements the query is found in an affinity other than the expected affinity (the query plan is found in an unexpected affinity), so control continues to block 535 where theDBMS 150 increments the expected affinity total count but not the expected affinity hit count in the entry in theaffinity data 262 with aquery identifier 402 that matches the received query. If the unexpected affinity in which the query plan was found is equal to (is the same as) the candidate affinity specified by thecandidate affinity identifier 408, in the same entry, then theDBMS 150 increments the candidate affinity hit count and the candidate total count. If the unexpected affinity in which the query plan was found is not equal to (is different from) the candidate affinity specified by thecandidate affinity identifier 408, in the same entry, then theDBMS 150 increments the candidate affinity total count but does not increment in the candidate hit count in the entry with thequery identifier 402 that matches the received query. Control then continues to block 525, as previously described above. - If the determination at
block 530 is false, then the query plan that implements the query is not found in any affinity, so control continues to block 540 where theDBMS 150 determines a candidate affinity for the query, creates and stores a query plan that implements the query to the address range of the expected affinity, creates an entry in theaffinity data 262 that identifies the query, the expected affinity, and the candidate affinity, initializes the expected affinity hit count and the expected affinity total count to one, initializes the candidate affinity hit count and the candidate affinity total count to zero, and initializes thequery plan pointer 412 to comprise the address of the query plan within the address range of the expected affinity. In an embodiment, theDBMS 150 selects the candidate affinity that has the largest candidate affinity hit percentage (the candidate affinity hit count divided by the candidate affinity total count) of all historical candidate affinities in which the query plan that implemented the query identified by thequery identifier 402, in the same entry, was stored. Control then continues to block 525, as previously described above. -
FIG. 6 depicts a flowchart of example processing for a query plan affinity cache, according to an embodiment of the invention. In various embodiments, the processing ofFIGS. 5 and 6 execute concurrently, substantially concurrently, or interleaved on the same or different of theprocessors 101 via multi-threading, multi-processing, time-slicing, or multi-programming techniques. Control begins atblock 600. Control then continues to block 605 where theDBMS 150 begins a loop that executes for each query in each affinity, once for each entry in theaffinity data 262. So long as an entry in theaffinity data 262 remains unprocessed by the loop that starts atblock 605, control continues fromblock 605 to block 610 where theDBMS 150 calculates the hit percentages of the expected affinity and the candidate affinity. In an embodiment, theDBMS 150 calculates the hit percentages by dividing the expected affinity hit count by the expected affinity total count to yield the hit percentage of the expected affinity and by dividing the candidate affinity hit count by the candidate affinity total count to yield the hit percentage of the candidate affinity. - Control then continues to block 615 where the
DBMS 150 determines whether the hit percentage of the candidate affinity for the query is more than a threshold amount greater than the hit percentage of the expected affinity. In various embodiments, theDBMS 150 receives various threshold amounts from a designer of theDBMS 150, from the user I/O device 121, from thenetwork 130, from theclient computer system 132, or from theapplication 160. If the determination atblock 615 is true, then the hit percentage of the candidate affinity for the query is more than a threshold amount greater than the hit percentage of the expected affinity, so control continues to block 620 where theDBMS 150 swaps the query plans in the expected affinity and the candidate affinity. That is, theDBMS 150 moves every query plan from the expected affinity to the candidate affinity and moves every query plan from the candidate affinity (that existed in the candidate affinity prior to the move of the query plans from the expected affinity to the candidate affinity) to the expected affinity.DBMS 150 changes thequery plan pointers 412 in the entries in theaffinity data 262 for every moved query plan to comprise the new addresses of the swapped query plans. Control then returns to block 605 where theDBMS 150 starts processing of the next entry in theaffinity data 262, as previously described above. - If the determination at
block 615 is false, then the hit percentage of the candidate affinity for the query is not more than a threshold amount greater than the hit percentage of the expected affinity, so control returns to block 605 where theDBMS 150 starts processing of the next entry in theaffinity data 262, as previously described above. - After all entries have been processed by the loop that starts at
block 605, control continues fromblock 605 to block 625 where theDBMS 150 determines how recently the query plans have been accessed within each affinity (each affinity may be an expected affinity, a candidate affinity, or neither an expected affinity nor a candidate affinity). TheDBMS 150 moves the recently accessed (more recently accessed than a first threshold time) query plans to a first contiguous address range, a first memory or storage device page, or a first set of memory or storage device pages within the respective affinity and moves the not recently accessed (less recently accessed than a second threshold time) query plans to a second contiguous address range, a second memory or storage device page, or a second set of memory or storage device pages, within the address range of the affinity. The first contiguous address range is different from and not overlapping with the second contiguous address range, within each respective affinity. - Control then continues to block 630 where the
DBMS 150 improves the fitting of query plans to memory pages within each affinity, based on sizes of the query plans, minimizing the wasted space in each memory page using a defragmenting algorithm. A defragmenting algorithm physically organizes or moves the contents of an affinity into the smallest number of contiguous regions (fragments). - Control then returns to block 605, where the loop that processes all entries in the
affinity data 262 restarts from the beginning of theaffinity data 262, as previously described above. - In this way, in an embodiment, the DBMS moves frequently or recently used query plans to proximate address ranges in the query plan cache and moves infrequently or not recently used query plans to other address ranges in the query plan cache, which reduces thrashing and increases performance.
- The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. As used herein, the singular forms “a,” “an,” and “the” are intended to include the plural forms as well, unless the context clearly indicates otherwise. It will be further understood that the terms “comprises” and/or “comprising,” when used in this specification, specify the presence of the stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof. 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, embodiments of 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 embodiments of 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.
Claims (17)
1. A computer-readable storage medium encoded with instructions, wherein the instructions when executed comprise:
calculating an expected affinity for a first query, in response to receiving the first query;
calculating a hit percentage of an expected affinity for the first query, wherein the expected affinity comprises a first address range in a query plan cache;
calculating a hit percentage of a candidate affinity for the first query, wherein the candidate affinity comprises a second address range in a query plan cache; and
if the hit percentage of the candidate affinity is greater than the hit percentage of the expected affinity by more than a threshold amount, swapping query plans in the candidate affinity with query plans in the expected affinity.
2. The computer-readable storage medium of claim 1 , further comprising:
if a first query plan that implements the first query is found in the expected affinity, incrementing an expected affinity hit count for the first query and incrementing an expected affinity total count for the first query;
if the first query plan that implements the first query is found in an unexpected affinity, incrementing the expected affinity total count for the first query; and
if the first query plan that implements the first query is not found in the expected affinity and is not found in the unexpected affinity, creating the first query plan in the expected affinity, initializing the expected affinity hit count for the first query to one and initializing the expected affinity total count for the first query to one.
3. The computer-readable storage medium of claim 2 , further comprising:
if the first query plan that implements the first query is not found in the expected affinity and is not found in the unexpected affinity, determining a candidate affinity for the first query and initializing a candidate affinity hit count and a candidate affinity total count to zero.
4. The computer-readable storage medium of claim 3 , further comprising:
if the first query plan that implements the first query is found in the unexpected affinity and the unexpected affinity equals the candidate affinity, incrementing the candidate affinity hit count and incrementing the candidate total count for the first query; and
if the first query plan that implements the first query is found in the unexpected affinity and the unexpected affinity does not equal the candidate affinity, incrementing the candidate total count for the first query but not incrementing the candidate affinity hit count for the first query.
5. The computer-readable storage medium of claim 4 , wherein the calculating the hit percentage of the expected affinity for the first query further comprises:
dividing the expected affinity hit count by the expected affinity total count.
6. The computer-readable storage medium of claim 4 , wherein the calculating the hit percentage of the candidate affinity for the first query further comprises:
dividing the candidate affinity hit count by the candidate affinity total count.
7. The computer-readable storage medium of claim 1 , further comprising:
moving the query plans in the expected affinity that have been accessed more recently than a first threshold time to a third address range within the first address range of the expected affinity; and
moving the query plans in the expected affinity that have accessed less recently than a second threshold time to a fourth address range within the first address range of the expected affinity, wherein the third address range and the fourth address range are non-overlapping within the first address range.
8. The computer-readable storage medium of claim 1 , wherein the hit percentage comprises a number of times that a first query plan that implements the first query has been found in the expected affinity.
9. The computer-readable storage medium of claim 1 , wherein all queries from a same application have a same expected affinity.
10. The computer-readable storage medium of claim 1 , wherein all queries that specify a same schema have a same affinity.
11. A computer comprising:
a processor; and
memory communicatively connected to the processor, wherein the memory is encoded with instructions, and wherein the instructions when executed by the processor comprise
calculating an expected affinity for a first query, in response to receiving the first query,
calculating a hit percentage of an expected affinity for the first query, wherein the expected affinity comprises a first address range in a query plan cache,
calculating a hit percentage of a candidate affinity for the first query, wherein the candidate affinity comprises a second address range in a query plan cache,
if the hit percentage of the candidate affinity is greater than the hit percentage of the expected affinity by more than a threshold amount, swapping query plans in the candidate affinity with query plans in the expected affinity,
if a first query plan that implements the first query is found in the expected affinity, incrementing an expected affinity hit count for the first query and incrementing an expected affinity total count for the first query,
if the first query plan that implements the first query is found in an unexpected affinity, incrementing the expected affinity total count for the first query, and
if the first query plan that implements the first query is not found in the expected affinity and is not found in the unexpected affinity, creating the first query plan in the expected affinity, initializing the expected affinity hit count for the first query to one and initializing the expected affinity total count for the first query to one.
12. The computer of claim 11 , wherein the instructions further comprise:
if the first query plan that implements the first query is not found in the expected affinity and is not found in the unexpected affinity, determining a candidate affinity for the first query and initializing a candidate affinity hit count and a candidate affinity total count to zero.
13. The computer of claim 12 , wherein the instructions further comprise:
if the first query plan that implements the first query is found in the unexpected affinity and the unexpected affinity equals the candidate affinity, incrementing the candidate affinity hit count and incrementing the candidate total count for the first query;
if the first query plan that implements the first query is found in the unexpected affinity and the unexpected affinity does not equal the candidate affinity, incrementing the candidate total count for the first query but not incrementing the candidate affinity hit count for the first query;
moving the query plans in the expected affinity that have been accessed more recently than a first threshold time to a third address range within the first address range of the expected affinity; and
moving the query plans in the expected affinity that have accessed less recently than a second threshold time to a fourth address range within the first address range of the expected affinity, wherein the third address range and the fourth address range are non-overlapping within the first address range.
14. The computer of claim 13 , wherein the calculating the hit percentage of the expected affinity for the first query further comprises:
dividing the expected affinity hit count by the expected affinity total count.
15. The computer of claim 13 , wherein the calculating the hit percentage of the candidate affinity for the first query further comprises:
dividing the candidate affinity hit count by the candidate affinity total count.
16. The computer of claim 11 , wherein all queries from a same application have a same expected affinity.
17. The computer of claim 11 , wherein all queries that specify a same schema have a same affinity.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US13/794,523 US20140101132A1 (en) | 2012-10-08 | 2013-03-11 | Swapping expected and candidate affinities in a query plan cache |
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US13/646,971 US8812489B2 (en) | 2012-10-08 | 2012-10-08 | Swapping expected and candidate affinities in a query plan cache |
US13/794,523 US20140101132A1 (en) | 2012-10-08 | 2013-03-11 | Swapping expected and candidate affinities in a query plan cache |
Related Parent Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US13/646,971 Continuation US8812489B2 (en) | 2012-10-08 | 2012-10-08 | Swapping expected and candidate affinities in a query plan cache |
Publications (1)
Publication Number | Publication Date |
---|---|
US20140101132A1 true US20140101132A1 (en) | 2014-04-10 |
Family
ID=50433547
Family Applications (2)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US13/646,971 Expired - Fee Related US8812489B2 (en) | 2012-10-08 | 2012-10-08 | Swapping expected and candidate affinities in a query plan cache |
US13/794,523 Abandoned US20140101132A1 (en) | 2012-10-08 | 2013-03-11 | Swapping expected and candidate affinities in a query plan cache |
Family Applications Before (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US13/646,971 Expired - Fee Related US8812489B2 (en) | 2012-10-08 | 2012-10-08 | Swapping expected and candidate affinities in a query plan cache |
Country Status (1)
Country | Link |
---|---|
US (2) | US8812489B2 (en) |
Cited By (8)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20160259825A1 (en) * | 2015-03-06 | 2016-09-08 | Dell Products L.P. | Discovery of potential problematic execution plans in a bind-sensitive query statement |
US20160299799A1 (en) * | 2015-04-10 | 2016-10-13 | Microsoft Technology Licensing, Llc | Event processing system paging |
US10311051B1 (en) * | 2014-01-29 | 2019-06-04 | Bentley Systems, Incorporated | Storing modeling alternatives with unitized data |
US20200279175A1 (en) * | 2019-02-28 | 2020-09-03 | Entigenlogic Llc | Generating comparison information |
US11093493B1 (en) * | 2018-09-28 | 2021-08-17 | Amazon Technologies, Inc. | Dynamically switching between query and scan for optimizing table reads |
US11243949B2 (en) * | 2017-04-21 | 2022-02-08 | Microsoft Technology Licensing, Llc | Query execution across multiple graphs |
US11269880B2 (en) * | 2019-05-20 | 2022-03-08 | Google Llc | Retroreflective clustered join graph generation for relational database queries |
US11281671B2 (en) * | 2019-05-20 | 2022-03-22 | Google Llc | Retroreflective join graph generation for relational database queries |
Families Citing this family (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US9275111B2 (en) * | 2013-03-15 | 2016-03-01 | International Business Machines Corporation | Minimizing result set size when converting from asymmetric to symmetric requests |
US10740358B2 (en) | 2013-04-11 | 2020-08-11 | Oracle International Corporation | Knowledge-intensive data processing system |
JP7064333B2 (en) * | 2015-03-23 | 2022-05-10 | オラクル・インターナショナル・コーポレイション | Knowledge-intensive data processing system |
Citations (38)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5812996A (en) * | 1994-07-12 | 1998-09-22 | Sybase, Inc. | Database system with methods for optimizing query performance with a buffer manager |
US20050091196A1 (en) * | 2003-10-22 | 2005-04-28 | International Business Machines Corporation | Optimization of queries using retrieval status of resources used thereby |
US20060004695A1 (en) * | 2004-06-03 | 2006-01-05 | International Business Machines Corporation | Apparatus and method for autonomically generating a query implementation that meets a defined performance specification |
US20060026154A1 (en) * | 2004-07-30 | 2006-02-02 | Mehmet Altinel | System and method for adaptive database caching |
US20060248045A1 (en) * | 2003-07-22 | 2006-11-02 | Kinor Technologies Inc. | Information access using ontologies |
US20060294079A1 (en) * | 2003-02-20 | 2006-12-28 | Dettinger Richard D | Incremental data query performance feedback model |
US20070136238A1 (en) * | 2005-12-09 | 2007-06-14 | International Business Machines Corporation | System and method to improve processing time of databases by cache optimization |
US20070208690A1 (en) * | 2002-06-27 | 2007-09-06 | Siebel Systems, Inc. | Detecting and processing cache hits for queries with aggregates |
US20080086469A1 (en) * | 2006-10-04 | 2008-04-10 | International Business Machines Corporation | Model-based self-optimizing distributed information management |
US20080091642A1 (en) * | 2006-10-12 | 2008-04-17 | Robert Joseph Bestgen | Advising the generation of a maintained index over a subset of values in a column of a table |
US20080172369A1 (en) * | 2007-01-12 | 2008-07-17 | Shawn Joseph Baranczyk | Method, Apparatus, and Computer Program Product for Intelligent Open Query Cursor Management |
US20080177694A1 (en) * | 2007-01-19 | 2008-07-24 | Microsoft Corporation | Incremental repair of query plans |
US20080195578A1 (en) * | 2007-02-09 | 2008-08-14 | Fabian Hueske | Automatically determining optimization frequencies of queries with parameter markers |
US20080222093A1 (en) * | 2007-02-09 | 2008-09-11 | Wei Fan | Automatically and adaptively determining execution plans for queries with parameter markers |
US20090043863A1 (en) * | 2004-07-08 | 2009-02-12 | International Business Machines Corporation | System using virtual replicated tables in a cluster database management system |
US20090094191A1 (en) * | 2007-10-08 | 2009-04-09 | Microsoft Corporation | Exploiting execution feedback for optimizing choice of access methods |
US20090100114A1 (en) * | 2007-10-10 | 2009-04-16 | Robert Joseph Bestgen | Preserving a Query Plan Cache |
US20090100004A1 (en) * | 2007-10-11 | 2009-04-16 | Sybase, Inc. | System And Methodology For Automatic Tuning Of Database Query Optimizer |
US20090182720A1 (en) * | 2008-01-15 | 2009-07-16 | Cain Michael W | Maintained Symbol Table Only Index |
US20090228434A1 (en) * | 2008-03-06 | 2009-09-10 | Saileshwar Krishnamurthy | Addition and processing of continuous sql queries in a streaming relational database management system |
US20090276394A1 (en) * | 2008-05-05 | 2009-11-05 | International Business Machines Corporation | Obtaining a plan for executing a query in a relational database |
US20090327242A1 (en) * | 2008-06-30 | 2009-12-31 | Teradata Us, Inc. | Parallel, in-line, query capture database for real-time logging, monitoring and opitmizer feedback |
US20100036804A1 (en) * | 2008-08-05 | 2010-02-11 | International Business Machines Corporation | Maintained and Reusable I/O Value Caches |
US20100223305A1 (en) * | 2009-03-02 | 2010-09-02 | Oracle International Corporation | Infrastructure for spilling pages to a persistent store |
US20100223253A1 (en) * | 2009-03-02 | 2010-09-02 | International Business Machines Corporation | Automatic query execution plan management and performance stabilization for workloads |
US20100306188A1 (en) * | 2009-06-01 | 2010-12-02 | Microsoft Corporation | Persistent query plans |
US20110029508A1 (en) * | 2009-07-31 | 2011-02-03 | Al-Omari Awny K | Selectivity-based optimized-query-plan caching |
US20110035369A1 (en) * | 2009-08-04 | 2011-02-10 | Sybase, Inc. | Query Optimization with Memory I/O Awareness |
US20110072006A1 (en) * | 2009-09-18 | 2011-03-24 | Microsoft Corporation | Management of data and computation in data centers |
US7917502B2 (en) * | 2008-02-27 | 2011-03-29 | International Business Machines Corporation | Optimized collection of just-in-time statistics for database query optimization |
US20110258628A1 (en) * | 2010-04-15 | 2011-10-20 | Salesforce.Com, Inc. | System, method and computer program product for transporting a task to a handler, utilizing a queue |
US20110288847A1 (en) * | 2004-09-22 | 2011-11-24 | Microsoft Corpororation | Predicting database system performance |
US20120109936A1 (en) * | 2010-10-29 | 2012-05-03 | Nec Laboratories America, Inc. | Cost-effective data layout optimization over heterogeneous storage classes |
US20120136602A1 (en) * | 2010-05-07 | 2012-05-31 | Salesforce.Com, Inc. | Method and system for automated performance testing in a multi-tenant environment |
US20120151232A1 (en) * | 2010-12-12 | 2012-06-14 | Fish Iii Russell Hamilton | CPU in Memory Cache Architecture |
US8229917B1 (en) * | 2011-02-24 | 2012-07-24 | International Business Machines Corporation | Database query optimization using clustering data mining |
US20120239811A1 (en) * | 2011-03-18 | 2012-09-20 | Juniper Networks, Inc. | Modular transparent proxy cache |
US20120246411A1 (en) * | 2011-03-21 | 2012-09-27 | Microsoft Corporation | Cache eviction using memory entry value |
Family Cites Families (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US6256705B1 (en) | 1998-06-05 | 2001-07-03 | International Business Machines Corporation | System and method for organizing data stored in a log structured array |
US20050071564A1 (en) | 2003-09-25 | 2005-03-31 | International Business Machines Corporation | Reduction of cache miss rates using shared private caches |
-
2012
- 2012-10-08 US US13/646,971 patent/US8812489B2/en not_active Expired - Fee Related
-
2013
- 2013-03-11 US US13/794,523 patent/US20140101132A1/en not_active Abandoned
Patent Citations (41)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5812996A (en) * | 1994-07-12 | 1998-09-22 | Sybase, Inc. | Database system with methods for optimizing query performance with a buffer manager |
US20070208690A1 (en) * | 2002-06-27 | 2007-09-06 | Siebel Systems, Inc. | Detecting and processing cache hits for queries with aggregates |
US20060294079A1 (en) * | 2003-02-20 | 2006-12-28 | Dettinger Richard D | Incremental data query performance feedback model |
US20100287158A1 (en) * | 2003-07-22 | 2010-11-11 | Kinor Technologies Inc. | Information access using ontologies |
US20060248045A1 (en) * | 2003-07-22 | 2006-11-02 | Kinor Technologies Inc. | Information access using ontologies |
US20050091196A1 (en) * | 2003-10-22 | 2005-04-28 | International Business Machines Corporation | Optimization of queries using retrieval status of resources used thereby |
US20060004695A1 (en) * | 2004-06-03 | 2006-01-05 | International Business Machines Corporation | Apparatus and method for autonomically generating a query implementation that meets a defined performance specification |
US20090043863A1 (en) * | 2004-07-08 | 2009-02-12 | International Business Machines Corporation | System using virtual replicated tables in a cluster database management system |
US20060026154A1 (en) * | 2004-07-30 | 2006-02-02 | Mehmet Altinel | System and method for adaptive database caching |
US20080215580A1 (en) * | 2004-07-30 | 2008-09-04 | International Business Machines Corporation | System and method for adaptive database caching |
US20110288847A1 (en) * | 2004-09-22 | 2011-11-24 | Microsoft Corpororation | Predicting database system performance |
US20070136238A1 (en) * | 2005-12-09 | 2007-06-14 | International Business Machines Corporation | System and method to improve processing time of databases by cache optimization |
US20080086469A1 (en) * | 2006-10-04 | 2008-04-10 | International Business Machines Corporation | Model-based self-optimizing distributed information management |
US20080091642A1 (en) * | 2006-10-12 | 2008-04-17 | Robert Joseph Bestgen | Advising the generation of a maintained index over a subset of values in a column of a table |
US20080172369A1 (en) * | 2007-01-12 | 2008-07-17 | Shawn Joseph Baranczyk | Method, Apparatus, and Computer Program Product for Intelligent Open Query Cursor Management |
US20080177694A1 (en) * | 2007-01-19 | 2008-07-24 | Microsoft Corporation | Incremental repair of query plans |
US20080195578A1 (en) * | 2007-02-09 | 2008-08-14 | Fabian Hueske | Automatically determining optimization frequencies of queries with parameter markers |
US20080222093A1 (en) * | 2007-02-09 | 2008-09-11 | Wei Fan | Automatically and adaptively determining execution plans for queries with parameter markers |
US20090094191A1 (en) * | 2007-10-08 | 2009-04-09 | Microsoft Corporation | Exploiting execution feedback for optimizing choice of access methods |
US20090100114A1 (en) * | 2007-10-10 | 2009-04-16 | Robert Joseph Bestgen | Preserving a Query Plan Cache |
US20090100004A1 (en) * | 2007-10-11 | 2009-04-16 | Sybase, Inc. | System And Methodology For Automatic Tuning Of Database Query Optimizer |
US20090182720A1 (en) * | 2008-01-15 | 2009-07-16 | Cain Michael W | Maintained Symbol Table Only Index |
US20100287206A1 (en) * | 2008-01-15 | 2010-11-11 | International Business Machines Corporation | Maintained symbol table only index |
US7917502B2 (en) * | 2008-02-27 | 2011-03-29 | International Business Machines Corporation | Optimized collection of just-in-time statistics for database query optimization |
US20090228434A1 (en) * | 2008-03-06 | 2009-09-10 | Saileshwar Krishnamurthy | Addition and processing of continuous sql queries in a streaming relational database management system |
US20090276394A1 (en) * | 2008-05-05 | 2009-11-05 | International Business Machines Corporation | Obtaining a plan for executing a query in a relational database |
US20090327242A1 (en) * | 2008-06-30 | 2009-12-31 | Teradata Us, Inc. | Parallel, in-line, query capture database for real-time logging, monitoring and opitmizer feedback |
US20100036804A1 (en) * | 2008-08-05 | 2010-02-11 | International Business Machines Corporation | Maintained and Reusable I/O Value Caches |
US20100223305A1 (en) * | 2009-03-02 | 2010-09-02 | Oracle International Corporation | Infrastructure for spilling pages to a persistent store |
US20100223253A1 (en) * | 2009-03-02 | 2010-09-02 | International Business Machines Corporation | Automatic query execution plan management and performance stabilization for workloads |
US20100306188A1 (en) * | 2009-06-01 | 2010-12-02 | Microsoft Corporation | Persistent query plans |
US20110029508A1 (en) * | 2009-07-31 | 2011-02-03 | Al-Omari Awny K | Selectivity-based optimized-query-plan caching |
US20110035369A1 (en) * | 2009-08-04 | 2011-02-10 | Sybase, Inc. | Query Optimization with Memory I/O Awareness |
US20110072006A1 (en) * | 2009-09-18 | 2011-03-24 | Microsoft Corporation | Management of data and computation in data centers |
US20110258628A1 (en) * | 2010-04-15 | 2011-10-20 | Salesforce.Com, Inc. | System, method and computer program product for transporting a task to a handler, utilizing a queue |
US20120136602A1 (en) * | 2010-05-07 | 2012-05-31 | Salesforce.Com, Inc. | Method and system for automated performance testing in a multi-tenant environment |
US20120109936A1 (en) * | 2010-10-29 | 2012-05-03 | Nec Laboratories America, Inc. | Cost-effective data layout optimization over heterogeneous storage classes |
US20120151232A1 (en) * | 2010-12-12 | 2012-06-14 | Fish Iii Russell Hamilton | CPU in Memory Cache Architecture |
US8229917B1 (en) * | 2011-02-24 | 2012-07-24 | International Business Machines Corporation | Database query optimization using clustering data mining |
US20120239811A1 (en) * | 2011-03-18 | 2012-09-20 | Juniper Networks, Inc. | Modular transparent proxy cache |
US20120246411A1 (en) * | 2011-03-21 | 2012-09-27 | Microsoft Corporation | Cache eviction using memory entry value |
Cited By (13)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US10311051B1 (en) * | 2014-01-29 | 2019-06-04 | Bentley Systems, Incorporated | Storing modeling alternatives with unitized data |
US20160259825A1 (en) * | 2015-03-06 | 2016-09-08 | Dell Products L.P. | Discovery of potential problematic execution plans in a bind-sensitive query statement |
US20160299799A1 (en) * | 2015-04-10 | 2016-10-13 | Microsoft Technology Licensing, Llc | Event processing system paging |
US9952916B2 (en) * | 2015-04-10 | 2018-04-24 | Microsoft Technology Licensing, Llc | Event processing system paging |
US11243949B2 (en) * | 2017-04-21 | 2022-02-08 | Microsoft Technology Licensing, Llc | Query execution across multiple graphs |
US11093493B1 (en) * | 2018-09-28 | 2021-08-17 | Amazon Technologies, Inc. | Dynamically switching between query and scan for optimizing table reads |
US20200279175A1 (en) * | 2019-02-28 | 2020-09-03 | Entigenlogic Llc | Generating comparison information |
US11586939B2 (en) * | 2019-02-28 | 2023-02-21 | Entigenlogic Llc | Generating comparison information |
US11954608B2 (en) | 2019-02-28 | 2024-04-09 | Entigenlogic Llc | Generating comparison information |
US11269880B2 (en) * | 2019-05-20 | 2022-03-08 | Google Llc | Retroreflective clustered join graph generation for relational database queries |
US11281671B2 (en) * | 2019-05-20 | 2022-03-22 | Google Llc | Retroreflective join graph generation for relational database queries |
US20220171775A1 (en) * | 2019-05-20 | 2022-06-02 | Google Llc | Retroreflective join graph generation for relational database queries |
US11880370B2 (en) * | 2019-05-20 | 2024-01-23 | Google Llc | Retroreflective join graph generation for relational database queries |
Also Published As
Publication number | Publication date |
---|---|
US20140101131A1 (en) | 2014-04-10 |
US8812489B2 (en) | 2014-08-19 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US8812489B2 (en) | Swapping expected and candidate affinities in a query plan cache | |
US11860830B2 (en) | Combined row and columnar storage for in-memory databases for OLTP and analytics workloads | |
US9298774B2 (en) | Changing the compression level of query plans | |
US10664497B2 (en) | Hybrid database table stored as both row and column store | |
US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
US8700605B1 (en) | Estimating rows returned by recursive queries using fanout | |
US10296462B2 (en) | Method to accelerate queries using dynamically generated alternate data formats in flash cache | |
US9286393B2 (en) | Performing a function on rows of data determined from transitive relationships between columns | |
US8601474B2 (en) | Resuming execution of an execution plan in a virtual machine | |
US8332389B2 (en) | Join order for a database query | |
US8396852B2 (en) | Evaluating execution plan changes after a wakeup threshold time | |
US9218394B2 (en) | Reading rows from memory prior to reading rows from secondary storage | |
US20150006509A1 (en) | Incremental maintenance of range-partitioned statistics for query optimization | |
US10296497B2 (en) | Storing a key value to a deleted row based on key range density | |
US10783142B2 (en) | Efficient data retrieval in staged use of in-memory cursor duration temporary tables | |
US8442971B2 (en) | Execution plans with different driver sources in multiple threads | |
US9229961B2 (en) | Database management delete efficiency | |
US8396858B2 (en) | Adding entries to an index based on use of the index |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
AS | Assignment |
Owner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW Y Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:KONIK, RAFAL P.;MITTELSTADT, ROGER A.;MURAS, BRIAN R.;AND OTHERS;REEL/FRAME:031358/0072 Effective date: 20121001 |
|
STCB | Information on status: application discontinuation |
Free format text: ABANDONED -- FAILURE TO RESPOND TO AN OFFICE ACTION |