Query Workload Statistics Collection in a Database Management System

Abstract

In various embodiments, a method, apparatus, and article of manufacture collect statistics in a database management system. A workload comprising a plurality of database queries is received. Query statistics for the database queries are identified. The query statistics for the database queries are consolidated to provide consolidated statistics. One or more statistics collection requests are generated based on the consolidated statistics.

1 . A computer-implemented method of collecting statistics in a database management system comprising a plurality of collection objects having a plurality of tables, comprising: 
 receiving a workload comprising a plurality of database queries referencing said plurality of tables, said plurality of database queries comprising a plurality of predicates, each predicate of said plurality of predicates comprising a column of a table of said plurality of tables, an operator, and a comparison value;    for each database query of said plurality of database queries of said workload: 
 identifying query statistics for said each database query of said plurality of database queries, said query statistics comprising a number of occurrences of a predicate in said each database query and a number of occurrences of said each database query; and  
 consolidating, for said each database query, said query statistics to provide consolidated statistics comprising a cumulative predicate score for said column of said table of said predicate based on a previous cumulative predicate score for said column of said table, if any, and a product of said number of occurrences of said predicate in said each database query and said number of occurrences of said each database query; and  
   generating a plurality of statistics collection tasks to collect database statistics to be used by an optimizer based on said consolidated statistics, said plurality of statistics collection tasks to collect said database statistics on said plurality of tables referenced by said plurality of database queries of said workload, said plurality of statistics collection tasks being based on a plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, a plurality of collection object scores are determined for said plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, at least one collection object score of said plurality of collection object scores is based on, at least in part, said cumulative predicate score for at least one column of at least one table of at least one collection object of said plurality of collection objects, wherein said plurality of statistics collection tasks are ordered based on said plurality of collection object scores.    
   
   
       2 . The method of  claim 1  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a literal.  
   
   
       3 . The method of  claim 1  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       4 . The method of  claim 1  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       5 . The method of  claim 1  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a blank.  
   
   
       6 . The method of  claim 1  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a NULL.  
   
   
       7 . A computer program product comprising a computer usable storage medium having computer usable program code for collecting statistics in a database management system comprising a plurality of collection objects having a plurality of tables, said computer program product comprising: 
 computer usable program code for receiving a workload comprising a plurality of database queries referencing said plurality of tables, said plurality of database queries comprising a plurality of predicates, each predicate of said plurality of predicates comprising a column of a table of said plurality of tables, an operator, and a comparison value;    computer usable program code for: 
 for each database query of said plurality of database queries of said workload: 
 identifying query statistics for said each database query of said plurality of database queries, said query statistics comprising a number of occurrences of a predicate in said each database query and a number of occurrences of said each database query; and  
 consolidating, for said each database query, said query statistics to provide consolidated statistics comprising a cumulative predicate score for said column of said table of said predicate based on a previous cumulative predicate score for said column of said table, if any, and a product of said number of occurrences of said predicate in said each database query and said number of occurrences of said each database query; and  
 
   computer usable program code for generating a plurality of statistics collection tasks to collect database statistics to be used by an optimizer based on said consolidated statistics, said plurality of statistics collection tasks to collect said database statistics on said plurality of tables referenced by said plurality of database queries of said workload, said plurality of statistics collection tasks being based on a plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, a plurality of collection object scores are determined for said plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, at least one collection object score of said plurality of collection object scores is based on, at least in part, said cumulative predicate score for at least one column of at least one table of at least one collection object of said plurality of collection objects, wherein said plurality of statistics collection tasks are ordered based on said plurality of collection object scores.    
   
   
       8 . The computer program product of  claim 7  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a literal.  
   
   
       9 . The computer program product of  claim 7  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       10 . The computer program product of  claim 7  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       11 . The computer program product of  claim 7  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a blank.  
   
   
       12 . The computer program product of  claim 7  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a NULL.  
   
   
       13 . An apparatus for collecting statistics in a database management system comprising a plurality of collection objects having a plurality of tables, comprising: 
 a processor; and    a memory storing one or more instructions that, when executed, cause said processor to:    receive a workload comprising a plurality of database queries referencing said plurality of tables, said plurality of database queries comprising a plurality of predicates, each predicate of said plurality of predicates comprising a column of a table of said plurality of tables, an operator, and a comparison value;    for each database query of said plurality of database queries of said workload: 
 identify query statistics for said each database query of said plurality of database queries, said query statistics comprising a number of occurrences of a predicate in said each database query and a number of occurrences of said each database query; and  
 consolidate, for said each database query, said query statistics to provide consolidated statistics comprising a cumulative predicate score for said column of said table of said predicate based on a previous cumulative predicate score for said column of said table, if any, and a product of said number of occurrences of said predicate in said each database query and said number of occurrences of said each database query; and  
   generate a plurality of statistics collection tasks to collect database statistics to be used by an optimizer based on said consolidated statistics, said plurality of statistics collection tasks to collect said database statistics on said plurality of tables referenced by said plurality of database queries of said workload, said plurality of statistics collection tasks being based on a plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, a plurality of collection object scores are determined for said plurality of collection objects comprising said plurality of tables referenced by said plurality of database queries of said workload, at least one collection object score of said plurality of collection object scores is based on, at least in part, said cumulative predicate score for at least one column of at least one table of at least one collection object of said plurality of collection objects, wherein said plurality of statistics collection tasks are ordered based on said plurality of collection object scores.    
   
   
       14 . The apparatus of  claim 13  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a literal.  
   
   
       15 . The apparatus of  claim 13  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       16 . The apparatus of  claim 13  wherein said operator of at least two predicates of said plurality of predicates is a comparison operator, and said comparison value of said at least two predicates of said plurality of predicates is a parameter marker that represents a position in a structured query language statement where an application will provide a value.  
   
   
       17 . The apparatus of  claim 13  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a blank.  
   
   
       18 . The apparatus of  claim 13  wherein said operator of at least two predicates of said plurality of predicates is an equal operator, and said comparison value of said at least two predicates of said plurality of predicates is a NULL.