Abstract: A method, apparatus and program product to optimize a database query. In a first system having a first dataspace, a query plan is generated for the database query. Remote statistics collection associated with the database query is initiated on a second system having a second dataspace. The remote statistics collection uses the second dataspace, which includes a current copy of at least a portion of the first dataspace on the first system. Database statistics are collected for the first system by analyzing the second dataspace and then communicating the collected database statistics to the first system for use in optimizing database queries executed by the first system.

Abstract: A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

Abstract: Methods, apparatus, and products are disclosed for caching an access plan for a query that include: receiving, in a SQL module of a DBMS, a SQL query that specifies data for retrieval from a database, the database characterized by database statistics, the SQL query characterized by one or more host variable values; generating, by the SQL module, an access plan in dependence upon the SQL query; calculating, by the SQL module, a routing code for the SQL query in dependence upon the host variable values of the SQL query and a portion of the database statistics; and storing, by the SQL module, the access plan in an access plan cache, including associating with the access plan the routing code for the SQL query and the portion of the database statistics used to calculate the routing code.

Abstract: Methods, systems, and products are provided for code path tracking. Embodiments include identifying an instrumented trace point in software code to be path tracked; identifying a function executed at the instrumented trace point in the software code; identifying parameters for the function executed at the instrumented trace point; and recording a description of the function, the parameters, and the result of the execution of the function using the parameters.

Abstract: Methods, apparatus, and products are disclosed for displaying explain data for a SQL query of a database that include: executing, by a SQL execution module, a SQL query in a database management system; accumulating, by the SQL execution module, actual run time data for the SQL query while executing the SQL query; and displaying, by an explain module, explain data for the SQL query while executing the SQL query, including displaying the accumulated actual run time data for the SQL query as part of the explain data for the SQL query.

Abstract: A database query optimizer includes an access plan storage mechanism that stores execution detail corresponding to a query statement in an access plan cache. By storing execution detail in the access plan cache along with the query statement, the execution detail is available when needed to debug database problems, even after a problem occurs. The execution detail stored in the access plan cache may be mined and analyzed after-the-fact, producing a very powerful tool for debugging database problems. Snapshots of the execution detail may also be created by a user. In addition, the user may setup autonomic snapshots and comparisons so that a user may be alerted from a degradation in database performance between two snapshots.

Abstract: An apparatus, program product and method identify a range of values in a table and advise the generation of a maintained index over the identified range of values. Additionally, a method that determines a range of values and generates a maintained temporary index is also provided. By doing so, the maintained index that was advised may be generated over the range of values in the column and used to access data in the table.

Abstract: A database management system predicts a selectivity for database query conditions requiring a join of records from different tables. The system identifies at least one skewed value in a field specified in the join condition, and constructs, for each skewed value, a set of hypothetical query predicates in which the field specified in the join condition is replaced with a constant equal to the skewed value. The system then predicts the selectivity for the hypothetical predicates, using any appropriate prediction technique. The selectivities of the hypothetical predicates are used to predict a selectivity for the original query.

Abstract: A database management system predicts a selectivity for database query conditions requiring a join of records from different tables. The system identifies at least one skewed value in a field specified in the join condition, and constructs, for each skewed value, a set of hypothetical query predicates in which the field specified in the join condition is replaced with a constant equal to the skewed value. The system then predicts the selectivity for the hypothetical predicates, using any appropriate prediction technique. The selectivities of the hypothetical predicates are used to predict a selectivity for the original query.

Abstract: A database management system predicts a selectivity for database query conditions requiring a join of records from different tables. The system identifies at least one skewed value in a field specified in the join condition, and constructs, for each skewed value, a set of hypothetical query predicates in which the field specified in the join condition is replaced with a constant equal to the skewed value. The system then predicts the selectivity for the hypothetical predicates, using any appropriate prediction technique. The selectivities of the hypothetical predicates are used to predict a selectivity for the original query.

Abstract: A latent auxiliary database structure constructed for executing a query is automatically saved for reuse in subsequent query execution instances. The latent auxiliary database structure is not maintained concurrently with changes to records in the database tables. If a query execution strategy is subsequently invoked which uses the latent auxiliary database structure, a determination is made dynamically whether to update the previously saved latent structure from database changes recorded in one or more change logs, or to rebuild the latent structure from scratch. Where the cost of updating from the change log is deemed less than the cost of a rebuild from scratch, the previously saved latent structure is updated from the log.

Abstract: A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

Abstract: In one aspect, a database analytical tool or function analyzes join queries to detect queries which could be reduced if a referential integrity relationship were known. In another aspect, a probable latent referential integrity relationship is detected using one or more heuristics. Preferably, a join query is analyzed to detect a joined table which has no other conditions placed on it, and no referential integrity relationship already defined. For any such query, the analysis attempts to verify a probable latent referential integrity relationship using at least one heuristic, such as comparing cardinality of the potential primary key with the size of the table and/or with the cardinality of the foreign key. It is further possible to execute the join for some sample of records, and determine whether any records were found which did not conform to referential integrity constraints.

Abstract: A database application re-uses one or more query execution strategies for a given logical query, and saves historical data concerning query execution performance under differing execution parameters. The historical data is analyzed to identify environmental variables and/or imported variables which significantly affect execution performance. Preferably, an auxiliary data structure includes, for each of multiple execution strategies, a respective set of imported and environmental variables and respective average cost measure, such as execution time. An analytical tool compares multiple different strategies to identify imported and/or environmental variables which caused a different strategy to be used, and resultant average cost. Preferably, the tool can also compare variation within the same strategy.

Abstract: A database facility supports database join queries in a database environment having at least one database table divided into multiple partitions based on a partition key value. The facility determines whether the values in a table joined to the partitioned table place an implicit limitation on the partition key, and eliminates from query evaluation any partitions which do not satisfy the implicit limitation. Preferably, the database uses a star schema organization, in which implicit limitations in a relatively small dimension table are used to eliminate partitions in a relatively large fact table.

Abstract: A query facility for database queries dynamically determines whether selective portions of a database table are likely to benefit from separate query execution strategies, and constructs an appropriate separate execution strategies accordingly. Preferably, the database contains at least one relatively large table comprising multiple partitions, each sharing the definitional structure of the table and containing a different respective discrete subset of the table records. The query facility compares metadata for different partitions to determine whether sufficiently large differences exist among the partitions, and in appropriate cases selects one or more partitions for separate execution strategies. Preferably, partitions are ranked for separate evaluation using a weighting formula which takes into account: (a) the number of indexes for the partition, (b) recency of change activity, and (c) the size of the partition.

Abstract: A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data.

Abstract: A visual query explain mechanism displays a query to the user in a graphical tree format. A user may execute a query, and the actual performance from executing the query is imported into the visual query explain mechanism. The visual query explain mechanism adds the actual performance to the nodes in the query tree, and compares the actual performance to the estimates of performance that were generated prior to executing the query. The visual query explain mechanism then looks at a predefined threshold value, and determines whether actual performance exceeds the estimated performance by the predefined threshold value. If so, the corresponding node in the query graph is highlighted in some way, thereby providing a visual indication to the user of problem areas in the query.

Abstract: A database management system predicts a selectivity for database query conditions requiring a join of records from different tables. The system identifies at least one skewed value in a field specified in the join condition, and constructs, for each skewed value, a set of hypothetical query predicates in which the field specified in the join condition is replaced with a constant equal to the skewed value. The system then predicts the selectivity for the hypothetical predicates, using any appropriate prediction technique. The selectivities of the hypothetical predicates are used to predict a selectivity for the original query.

Abstract: Enabling a user to obtain and view graphic explain data with no need to activate a monitor and no need to wait for a job to complete before the explain data for a query may be viewed. Displaying explain data for an SQL query of a database that include executing an SQL query and displaying explain data for the SQL query while executing the SQL query. Displaying explain data includes identifying an access plan associated with the SQL query and generating explain data in dependence upon the access plan. Identifying an access plan may be carried out by retrieving an access plan identification from a cursor of a job control block and retrieving an access plan from an access plan cache in dependence upon the access plan identification.