Processing dynamic statistics for expensive queries

Abstract

Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

What is claimed is: 
     
         1 . A method comprising:
 receiving a query that references one or more database objects;   generating an execution plan for the query;   based on executing the execution plan, determining that the query is an expensive query;   generating, for the query, a budget that indicates an amount of resources to devote to processing dynamic statistics for the query;   wherein the method is performed by one or more computing devices.   
     
     
         2 . The method of  claim 1 , wherein determining that the query is an expensive query is based on a time that lapsed to execute the execution plan. 
     
     
         3 . The method of  claim 1 , wherein determining that the query is an expensive query is based on a number of rows processed, CPU utilization, memory utilization, or disk I/O utilization during execution of the execution plan. 
     
     
         4 . The method of  claim 1 , wherein the amount of resources is an amount of time to devote to processing the dynamic statistics for the query. 
     
     
         5 . The method of  claim 4 , wherein the amount of time is based on a time that lapsed to execute the execution plan. 
     
     
         6 . The method of  claim 4 , further comprising calculating the amount of time, wherein the amount of time is calculated (a) based on a median of past execution times of the execution plan or an average of the past execution times or (b) as a percentage of (i) execution time data associated with the execution plan, (ii) the median, or (iii) the average. 
     
     
         7 . The method of  claim 1 , wherein:
 executing the execution plan comprises generating first statistics about an operation and a database object in the execution plan;   the method further comprising storing, in association with the database object and the operation, the first statistics that indicate information about the operation; and   the dynamic statistics include the first statistics.   
     
     
         8 . The method of  claim 1 , wherein processing the dynamic statistics comprises gathering the dynamic statistics. 
     
     
         9 . One or more storage media storing instructions which, when executed by one or more processors, cause:
 receiving a query that references one or more database objects;   generating an execution plan for the query;   based on executing the execution plan, determining that the query is an expensive query;   generating, for the query, a budget that indicates an amount of resources to devote to processing dynamic statistics for the query;   wherein the method is performed by one or more computing devices.   
     
     
         10 . The one or more storage media of  claim 9 , wherein determining that the query is an expensive query is based on a time that lapsed to execute the execution plan. 
     
     
         11 . The one or more storage media of  claim 9 , wherein determining that the query is an expensive query is based on a number of rows processed, CPU utilization, memory utilization, or disk I/O utilization during execution of the execution plan. 
     
     
         12 . The one or more storage media of  claim 9 , wherein the amount of resources is an amount of time to devote to processing the dynamic statistics for the query. 
     
     
         13 . The one or more storage media of  claim 12 , wherein the amount of time is based on a time that lapsed to execute the execution plan. 
     
     
         14 . The one or more storage media of  claim 12 , further comprising calculating the amount of time, wherein the amount of time is calculated (a) based on a median of past execution times of the execution plan or an average of the past execution times or (b) as a percentage of (i) execution time data associated with the execution plan, (ii) the median, or (iii) the average. 
     
     
         15 . The one or more storage media of  claim 9 , wherein:
 executing the execution plan comprises generating first statistics about an operation and a database object in the execution plan;   the instructions, when executed by the one or more processors, further cause storing, in association with the database object and the operation, the first statistics that indicate information about the operation; and   the dynamic statistics include the first statistics.   
     
     
         16 . The one or more storage media of  claim 9 , wherein processing the dynamic statistics comprises gathering the dynamic statistics. 
     
     
         17 . A system comprising:
 one or more processors;   one or more storage media storing instructions which, when executed by the one or more processors, cause:
 receiving a query that references one or more database objects; 
 generating an execution plan for the query; 
 based on executing the execution plan, determining that the query is an expensive query; 
 generating, for the query, a budget that indicates an amount of resources to devote to processing dynamic statistics for the query. 
   
     
     
         18 . The system of  claim 17 , wherein determining that the query is an expensive query is based on a time that lapsed to execute the execution plan. 
     
     
         19 . The system of  claim 17 , wherein determining that the query is an expensive query is based on a number of rows processed, CPU utilization, memory utilization, or disk I/O utilization during execution of the execution plan. 
     
     
         20 . The system of  claim 17 , further comprising, wherein the amount of resources is an amount of time to devote to processing the dynamic statistics for the query.