Optimizer problem determination

BACKGROUND

The present invention relates generally to optimizer problem determination and in particular to optimization of query execution plans for relational databases.

Relational database management systems have long been central to the information economy, and the complexity of such systems has increased with the volume of data under management. At the enterprise level, major commercial relational database systems such as IBM® DB2®, Oracle® Database, and Microsoft® SQL Server® are deployed in environments where finding all available optimizations and performance enhancements becomes necessary to maintaining everyday usability of the database.

SUMMARY

A computer-implemented method includes receiving an artefact and a problem pattern, transforming the artefact into an abstracted artefact structure, and transforming the problem pattern into a query. The query is matched against the abstracted artefact structure. Any matched portions of the abstracted artefact structure are related back to corresponding result portions of the artefact. The corresponding result portions of the artefact are returned. A corresponding computer system and computer program product are also disclosed.

BRIEF DESCRIPTION OF THE DRAWINGS

<figref idref="DRAWINGS">FIG. 1</figref> is a block diagram of one embodiment of a computer system environment suitable for operation in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 2</figref> is a block diagram of an operational environment in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 3</figref> is a data flow diagram in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 4</figref> is a flow chart diagram showing various operational steps of the problem determination program according to at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 5</figref> is a representation of a graphical user interface in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 6</figref> is a portion of a query execution plan presented as example data in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 7</figref> is a portion of an abstraction, in a resource description framework, of the query execution plan of <figref idref="DRAWINGS">FIG. 6</figref>, also presented as example data in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 8</figref> is a portion of software code, in a resource description framework query language, for querying the abstracted query execution plan of <figref idref="DRAWINGS">FIG. 7</figref>, in accordance with at least one embodiment of the invention.

<figref idref="DRAWINGS">FIG. 9</figref> is a block diagram of one example of a computing apparatus suitable for executing the problem determination program in accordance with at least one embodiment of the invention.

DETAILED DESCRIPTION

Referring now to the invention in more detail, <figref idref="DRAWINGS">FIG. 1</figref> is a block diagram displaying an exemplary computer system environment suitable for operation of at least one embodiment of the invention. Within a computer system <b>100</b>, a problem determination program <b>101</b> may receive as input an artefact <b>102</b> and a problem pattern <b>103</b>. The problem determination program <b>101</b> may return results <b>104</b>, which include those portions of the artefact <b>102</b> that match the problem pattern <b>103</b>. Additionally, the problem determination program <b>101</b> may access a knowledge base <b>105</b>, including one or more predetermined problem patterns <b>103</b> and related solution recommendations <b>106</b>. The problem determination program <b>101</b> may further return those solution recommendations <b>106</b> that are applicable to the results <b>104</b>.

In general, the artefact <b>102</b> may be any data to be analyzed, and the problem pattern <b>103</b> may be any pattern to be matched within the artefact <b>102</b>. More particularly, the artefact <b>102</b> may be diagnostic data, for example relating to one or more computer systems. Broadly, the contemplated diagnostic data may be human-readable and intended for review by human users of the system to which the diagnostic data relates. Examples of possible artefacts <b>102</b> include log data relating to network usage, security, or software compiling, as well as software debug data or sensor data relating to some physical system external to the computer system <b>100</b>. The problem pattern may correspond to any sequence of data points or interrelationships of data points that are of diagnostic interest.

In some embodiments, such as the embodiment depicted in <figref idref="DRAWINGS">FIG. 2</figref>, the artefact <b>102</b> may become a query execution plan <b>202</b> for accessing a database management system. The database management system may be a Relational DataBase Management System (“RDBMS”) such as IBM® DB2®, Oracle® Database, or Microsoft® SQL Server®. The query execution plan may include a series of ordered steps, known as LOw LEvel Plan OPeratorS (“LOLEPOPS”). The LOLEPOPS may be understood, at the level of abstraction of the RDBMS user, as indivisible operations that are directly executed by the RDBMS, with each LOLEPOP carrying a stated cost. The stated cost for each LOLEPOP represents an estimate of server resources, and may be understood as being generated by the RDBMS based on a proposed query taken in view of the particular properties of the database. The overall query execution plan may likewise be machine-generated by the RDBMS, and may be understood as being already machine-optimized to include the lowest total cost LOLEPOPS attainable by the RDBMS's optimizer.

Time-costly review of the query plan by a human user may therefore be needed to further improve performance, for example where the human user seeks to improve, debug, or verify the optimizer itself. Machine optimization may likewise fail where a performance enhancement cannot be implemented directly by optimizing the proposed query as-coded, for example, using Structured Query Language (“SQL”). Examples of this include cases where the recommended performance enhancement is to index a particular table in a particular way or to alter the proposed query to generate the same result but with better performance. In this class of embodiments, the problem pattern <b>103</b> comprises a sequence of LOLEPOPS having particular properties that are interest to a user as indicative of one or more of the aforementioned problems.

<figref idref="DRAWINGS">FIG. 2</figref> displays an operational environment in accordance with at least one embodiment of the invention wherein the artefact <b>102</b> becomes the query execution plan <b>202</b>. In the embodiment depicted in <figref idref="DRAWINGS">FIG. 2</figref>, the query execution plan <b>202</b> is generated from a test SQL query <b>212</b> and received by the query plan problem determination program <b>201</b>, along with the query plan problem pattern <b>203</b>. The query plan problem pattern <b>203</b> may be presented by a general user <b>210</b>. The general user <b>210</b> may create the query plan problem pattern <b>203</b> on his or her own, or may use a predetermined problem pattern taken from a query plan knowledge base <b>205</b>.

The query plan knowledge base <b>205</b> may include one or more predetermined problem patterns related to one or more query plan solution recommendations <b>206</b>. The predetermined problem patterns and related query plan solution recommendations <b>206</b> may be created by one or more experts <b>207</b>. The query plan problem determination program <b>201</b> may also access the query plan knowledge base <b>205</b> and match the query plan problem pattern <b>203</b> against the one or more predetermined problem patterns in the query plan knowledge base <b>205</b>. Matching the query plan problem pattern <b>203</b> against the one or more predetermined problem patterns may be accomplished by any search algorithm. Where the query plan problem determination program <b>201</b> detects a match between the query plan problem pattern <b>203</b> and the query execution plan <b>202</b>, the query plan problem determination program may return the one or more query plan solution recommendations <b>206</b> for those of the one or more predetermined problem patterns that are matched. The general user <b>210</b> may apply the returned query plan solution recommendation <b>206</b>, which may include one or more recommendations to engage in one or more database tuning operations, to achieve a performance enhancement <b>213</b>.

In an alternative case, the query plan problem determination program <b>201</b> may, instead of taking a specific problem pattern <b>103</b> defined by the user, iterate over all of the predetermined problem patterns in the query plan knowledge base <b>205</b>. Specifically, each problem pattern <b>103</b> may, in such cases, be understood as being received from the query plan knowledge base <b>205</b>. In such cases, the query plan problem determination program may directly recall and return the solution recommendations for the particular problem pattern <b>103</b>, if there are any results on the artefact <b>102</b>. Such a technique would enable query plan checks to be routinized—a user could, with now particular knowledge or training, run a general test of all predetermined problem patterns against a given artefact <b>102</b>.

Referring now to the data flow diagram of <figref idref="DRAWINGS">FIG. 3</figref>, <figref idref="DRAWINGS">FIG. 3</figref> shows how data moves through the problem determination program <b>101</b>, in accordance with at least one embodiment. The artefact <b>102</b> is transformed via an artefact transformation engine <b>314</b> into an abstracted artefact structure <b>311</b>. The problem pattern <b>103</b> is likewise transformed, via a problem pattern transformation engine <b>313</b>, into a query <b>310</b>. A matcher <b>315</b> compares the query <b>310</b> with the abstracted artefact structure <b>311</b>. The resulting matched portions <b>312</b> of the abstracted artefact structure <b>311</b> are related back to the original artefact <b>102</b> by an artefact detransformation engine <b>316</b>, and output includes the result portions <b>304</b> of the artefact <b>102</b>.

Referring now to the flow chart diagram of <figref idref="DRAWINGS">FIG. 4</figref>, in accordance with at least one embodiment of the invention. At step <b>400</b> of the method according to the depicted embodiment, the problem determination program <b>101</b> receives the artefact <b>102</b> and problem pattern <b>103</b>. At step <b>401</b>, the problem determination program <b>101</b> transforms the artefact <b>102</b> into the abstracted artefact structure <b>311</b>. At step <b>402</b>, the problem determination program <b>101</b> transforms the problem pattern <b>103</b> into a query <b>310</b>. At step <b>403</b>, the problem determination program <b>101</b> matches the query <b>310</b> against the abstracted artefact structure <b>311</b>. Matching the query <b>310</b> against the abstracted artefact structure <b>311</b> may be accomplished by traversing hierarchical features of the abstracted artefact structure <b>310</b>, as described more fully below, or, more generally, by any search algorithm. At step <b>404</b>, the problem determination program <b>101</b> relates any matched portions <b>312</b> of the abstracted artefact structure <b>311</b> back to corresponding result portions <b>304</b> of the artefact <b>102</b>. At step <b>405</b>, the problem determination program <b>101</b> returns the corresponding result portions <b>304</b> of the artefact <b>102</b>.

The transformations of steps <b>401</b> and <b>402</b> may be made such that the abstracted artefact structure <b>311</b> can be queried or is traversable, in particular according to the query <b>310</b>. Specifically, the artefact <b>102</b> may be transformed so as to be expressed in Resource Description Framework (RDF) or other traversable structure. RDF is a standard maintained by the World Wide Web Consortium (W3C); generically, a “resource description framework” may be any data structure whose properties are similar to that of RDF. In particular, the RDF standard permits resources both to share predicates and relationships and to have their own unique ones. Using an externally imposed framework of predicates and relationships, the abstracted artefact structure <b>311</b> can represent hierarchical relationships within the artefact <b>102</b>. For example RDF relationships and predicates can model the input and output streams of various LOLEPOPS where the artefact <b>102</b> is a RDBMS query plan.

Where the abstracted artefact structure <b>311</b> is expressed in a resource description framework, it may be directly queried at the application level of the problem determination program <b>101</b> via query expressed in a resource description framework query language. More specifically, the SPARQL Protocol and RDF Query Language (SPARQL) (also a standard maintained by the W3C) provides a means of directly querying RDF data. Thus, the problem determination program may accomplish step <b>402</b> by expressing the query <b>310</b> in SPARQL (directed to RDF) or a resource description framework query language directed to a different or generic resource description framework. In such embodiments, step <b>403</b> is effected by directly executing the query <b>310</b> against the abstracted artefact structure <b>311</b> using any standardcompliant implementation of RDF and SPARQL, or a generic resource description framework and a corresponding resource description framework query language.

<figref idref="DRAWINGS">FIGS. 5-8</figref> display aspects of a worked example in accordance with at least one embodiment of the invention. <figref idref="DRAWINGS">FIG. 5</figref> displays an exemplary graphical user interface wherein a user can express the problem pattern <b>103</b> by selecting various properties of LOLEPOPS that a user might be interested in within an RDBMS query plan. In the depicted example, the user is concerned with LOLEPOPS that: (i) are of type Nested Loop Join (NLJOIN); (ii) have an outer input stream with cardinality greater than one (meaning that the specified LOLEPOP will be returning more than one row); (iii) have inner input stream of type TaBle Scan (TBSCAN); and (iv) the inner input stream has large cardinality. The depicted graphical user interface generates an example structure of a LOLEPOP that matches the selected properties. In this case, the described LOLEPOP is an NLJOIN of a fetch (FETCH) and a TBSCAN. Such pattern is costly as deduced by satisfying the cardinality conditions: the NLJOIN operator scans the entire table (TBSCAN) for each of the rows from the FETCH. It would likely be of value for a subject matter expert to spend time and attention to try to eliminate this pattern. In this circumstance, the query plan problem determination program <b>101</b> would output an expert provided recommendation to create an index of the target table of the TBSCAN.

<figref idref="DRAWINGS">FIG. 6</figref> depicts an example query access plan from an RDBMS. In the depicted example, LOLEPOP #2 is an NLJOIN with input streams of LOLEPOP #3 and LOLEPOP #5. Each LOLEPOP has an estimated Input/Output (I/O) cost (the bottom number) and a cumulative cost for itself and all LOLEPOPS below it (the second from the bottom number). The FETCH at LOLEPOP #3 has a cost of 26.0884 timerons, while the TBSCAN has a cost of 157.71 timerons (timerons are comparable to milliseconds but the cost figures are approximations and are best understood as relative to one another).

<figref idref="DRAWINGS">FIG. 7</figref> depicts an RDF representation of LOLEPOP #5 of <figref idref="DRAWINGS">FIG. 6</figref>. Each RDF statement is of the form Resource→Predicate→Object. In the depicted code, all statements refer to the resource “http://explainPlan/PlanPop/5”, equivalent to LOLEPOP #5. Various predicates are shown, each encoding a piece of information from the query access plan of <figref idref="DRAWINGS">FIG. 6</figref>. For example, there are predicates that specify LOLEPOP #5's total cost (157.71) and estimated cardinality (404.333).

<figref idref="DRAWINGS">FIG. 8</figref> depicts a SPARQL query that expresses the user preferences shown in the graphical user interface of <figref idref="DRAWINGS">FIG. 5</figref>. The URIs broadly match the RDF of <figref idref="DRAWINGS">FIG. 7</figref>, and the various query limitations match the elements of the problem pattern <b>103</b> as indicated by the user through the graphical user interface.

<figref idref="DRAWINGS">FIG. 9</figref> is a block diagram depicting components of a computer <b>900</b> suitable for executing the problem determination program <b>101</b>. <figref idref="DRAWINGS">FIG. 9</figref> displays the computer <b>900</b>, the one or more processor(s) <b>904</b> (including one or more computer processors), the communications fabric <b>902</b>, the memory <b>906</b>, the RAM <b>916</b>, the cache <b>918</b>, the persistent storage <b>908</b>, the communications unit <b>912</b>, the I/O interfaces <b>914</b>, the display <b>922</b>, and the external devices <b>920</b>. It should be appreciated that <figref idref="DRAWINGS">FIG. 9</figref> provides only an illustration of one embodiment and does not imply any limitations with regard to the environments in which different embodiments may be implemented. Many modifications to the depicted environment may be made.

As depicted, the computer <b>900</b> operates over a communications fabric <b>902</b>, which provides communications between the computer processor(s) <b>904</b>, memory <b>906</b>, persistent storage <b>908</b>, communications unit <b>912</b>, and input/output (I/O) interface(s) <b>914</b>. The communications fabric <b>902</b> may be implemented with any architecture suitable for passing data or control information between the processors <b>904</b> (e.g. microprocessors, communications processors, and network processors), the memory <b>906</b>, the external devices <b>920</b>, and any other hardware components within a system. For example, the communications fabric <b>902</b> may be implemented with one or more buses.

The memory <b>906</b> and persistent storage <b>908</b> are computer readable storage media. In the depicted embodiment, the memory <b>906</b> comprises a random access memory (RAM) <b>916</b> and a cache <b>918</b>. In general, the memory <b>906</b> may comprise any suitable volatile or non-volatile one or more computer readable storage media.

Program instructions for the problem determination program <b>101</b> may be stored in the persistent storage <b>908</b>, or more generally, any computer readable storage media, for execution by one or more of the respective computer processors <b>904</b> via one or more memories of the memory <b>906</b>. The persistent storage <b>908</b> may be a magnetic hard disk drive, a solid state disk drive, a semiconductor storage device, read-only memory (ROM), electronically erasable programmable read-only memory (EEPROM), flash memory, or any other computer readable storage media that is capable of storing program instructions or digital information.

The media used by the persistent storage <b>908</b> may also be removable. For example, a removable hard drive may be used for persistent storage <b>908</b>. Other examples include optical and magnetic disks, thumb drives, and smart cards that are inserted into a drive for transfer onto another computer readable storage medium that is also part of the persistent storage <b>908</b>.

The communications unit <b>912</b>, in these examples, provides for communications with other data processing systems or devices. In these examples, the communications unit <b>912</b> may comprise one or more network interface cards. The communications unit <b>912</b> may provide communications through the use of either or both physical and wireless communications links. In the context of some embodiments of the present invention, the source of the various input data may be physically remote to the computer <b>900</b> such that the input data may be received and the output similarly transmitted via the communications unit <b>912</b>.

The I/O interface(s) <b>914</b> allow for input and output of data with other devices that may operate in conjunction with the computer <b>900</b>. For example, the I/O interface <b>914</b> may provide a connection to the external devices <b>920</b>, which may be as a keyboard, keypad, a touch screen, or other suitable input devices. External devices <b>920</b> may also include portable computer readable storage media, for example thumb drives, portable optical or magnetic disks, and memory cards. Software and data used to practice embodiments of the present invention may be stored on such portable computer readable storage media and may be loaded onto the persistent storage <b>908</b> via the I/O interface(s) <b>914</b>. The I/O interface(s) <b>914</b> may similarly connect to a display <b>922</b>. The display <b>922</b> provides a mechanism to display data to a user and may be, for example, a computer monitor.

The present invention may be a system, a method, and/or a computer program product. The computer program product may include a computer readable storage medium (or media) having computer readable program instructions thereon for causing a processor to carry out aspects of the present invention.

The computer readable storage medium can be a tangible device that can retain and store instructions for use by an instruction execution device. The computer readable storage medium may be, for example, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of the computer readable storage medium includes the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanically encoded device such as punch-cards or raised structures in a groove having instructions recorded thereon, and any suitable combination of the foregoing. A computer readable storage medium, as used herein, is not to be construed as being transitory signals per se, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide or other transmission media (e.g., light pulses passing through a fiber-optic cable), or electrical signals transmitted through a wire.

Computer readable program instructions described herein can be downloaded to respective computing/processing devices from a computer readable storage medium or to an external computer or external storage device via a network, for example, the Internet, a local area network, a wide area network and/or a wireless network. The network may comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers and/or edge servers. A network adapter card or network interface in each computing/processing device receives computer readable program instructions from the network and forwards the computer readable program instructions for storage in a computer readable storage medium within the respective computing/processing device.

Computer readable program instructions for carrying out operations of the present invention may be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, or either source code or object code written in any combination of one or more programming languages, including an object oriented programming language such as Smalltalk, C++ or the like, and conventional procedural programming languages, such as the “C” programming language or similar programming languages. The computer readable program instructions may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and 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). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate arrays (FPGA), or programmable logic arrays (PLA) may execute the computer readable program instructions by utilizing state information of the computer readable program instructions to personalize the electronic circuitry, in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer readable program instructions.

These computer readable 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 in the flowchart and/or block diagram block or blocks. These computer readable program instructions may also be stored in a readable storage medium that can direct a computer, a programmable data processing apparatus, and/or other devices to function in a particular manner, such that the computer readable storage medium having instructions stored therein comprises an article of manufacture including instructions which implement aspects of the function/act specified in the flowchart and/or block diagram block or blocks.

The computer readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus or other device to produce a computer implemented process, such that the instructions which execute on the computer, other programmable apparatus, or other device implement the functions/acts specified in the flowchart and/or block diagram block or blocks.

The flowchart and 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 flowchart or block diagrams may represent a module, segment, or portion of computer program instructions, which comprises one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, 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. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems that perform the specified functions or acts or carry out combinations of special purpose hardware and computer instructions.

The descriptions of the various embodiments of the present invention have been presented for purposes of illustration, but are not intended to be exhaustive or limited to the embodiments disclosed. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The terminology used herein was chosen to best explain the principles of the embodiments, the practical application or technical improvement over technologies found in the marketplace, or to enable others of ordinary skill in the art to understand the embodiments disclosed herein.