Query optimization and distribution

Abstract

Computer implemented methods, systems, and computer program products include program code executing on a processor(s) monitors query execution within the database system within the multiple databases and by the multiple query optimization engines. The program code retains statistical information from the monitoring in a centralized database. The program code obtains, at a query optimization engine of the database system, a new query. The program code predicts, based on accessing the statistical information in the centralized database, that executing the new query with the query optimization engine at a designated execution time comprises a suboptimal execution of the new query. The program code alters a planned execution of the new query at the designated execution time with the query optimization engine to optimize execution of the query within the database system.

What is claimed is: 
     
         1 . A computer-implemented method for distributing query optimization activities across a database system comprising multiple databases and multiple query optimization engines, comprising:
 monitoring, by one or more processors, query execution within the database system within the multiple databases and by the multiple query optimization engines;   retaining, by the one or more processors, statistical information from the monitoring in a centralized database;   obtaining, by the one or more processors, at a query optimization engine of the database system, a new query;   predicting, by the one or more processors, based on accessing the statistical information in the centralized database, that executing the new query with the query optimization engine at a designated execution time comprises a suboptimal execution of the new query; and   altering, by the one or more processors, a planned execution of the new query at the designated execution time with the query optimization engine to optimize execution of the query within the database system.   
     
     
         2 . The computer-implemented method of  claim 1 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution;   identifying, by the one or more processors, a similar optimization engine with capacity to execute the new query;   transmitting, by the one or more processors, the new query to the similar optimization engine; and   executing, by the one or more processors, the new query with the similar optimization engine.   
     
     
         3 . The computer-implemented method of  claim 1 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution, wherein the determining comprises:
 determining, by the one or more processors, based on the statistical information that historical queries similar to the new query executed suboptimally when executed by query optimization engines of a given types, and where the query optimization engine is of the given type; 
   identifying, by the one or more processors, an optimization engine of a type different than the given type, wherein the optimization engine of the type different comprises capacity and functionality to execute the new query;   transmitting, by the one or more processors, the new query to the optimization engine of the different type; and   executing, by the one or more processors, the new query with the optimization engine of the different type.   
     
     
         4 . The computer-implemented method of  claim 1 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that optimizing the query in advance of the designated execution time will improve performance by an amount of time about a pre-defined threshold;   pre-emptively optimizing, by the one or more processors, with the query optimization engine, the new query in advance of the designated execution time;   caching, by the one or more processors, the new query in a memory; and   executing, by the one or more processors, the new query at the designated execution time, based on obtaining the pre-emptively optimized query from the memory.   
     
     
         5 . The computer-implemented method of  claim 1 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 commencing, by the one or more processors, execution of the new query by the query optimization engine;   in advance of completing of the execution, determining, by the one or more processors, that the executing is longer than an anticipated threshold amount of time;   determining, by the one or more processors, that other query optimization engines of the multiple query optimization engines are unavailable to execute the new query; and   pausing, by the one or more processors, the execution of the new query by the query optimization engine.   
     
     
         6 . The computer-implemented method of  claim 5 , further comprising:
 determining, by the one or more processors, that a given query optimization engine of the other query optimization engines is available to accept the new query;   transmitting, by the one or more processors, the new query to the given query optimization engine; and   executing, by the one or more processors, the new query with the given optimization engine.   
     
     
         7 . The computer-implemented method of  claim 6 , wherein the executing the new query with the given optimization engine comprises re-optimizing the query. 
     
     
         8 . The computer-implemented method of  claim 1 , wherein the statistical information comprises query log events and details relevant to various stages of query execution. 
     
     
         9 . The computer-implemented method of  claim 1 , wherein the database system comprises a data lakehouse. 
     
     
         10 . A computer system for distributing query optimization activities across a database system comprising multiple databases and multiple query optimization engines, comprising:
 a memory; and   one or more processors in communication with the memory, wherein the computer system is configured to perform a method, said method comprising:
 monitoring, by one or more processors, query execution within the database system within the multiple databases and by the multiple query optimization engines; 
 retaining, by the one or more processors, statistical information from the monitoring in a centralized database; 
 obtaining, by the one or more processors, at a query optimization engine of the database system, a new query; 
 predicting, by the one or more processors, based on accessing the statistical information in the centralized database, that executing the new query with the query optimization engine at a designated execution time comprises a suboptimal execution of the new query; and 
 altering, by the one or more processors, a planned execution of the new query at the designated execution time with the query optimization engine to optimize execution of the query within the database system. 
   
     
     
         11 . The computer system of  claim 10 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution;   identifying, by the one or more processors, a similar optimization engine with capacity to execute the new query;   transmitting, by the one or more processors, the new query to the similar optimization engine; and   executing, by the one or more processors, the new query with the similar optimization engine.   
     
     
         12 . The computer system of  claim 10 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution, wherein the determining comprises:
 determining, by the one or more processors, based on the statistical information that historical queries similar to the new query executed suboptimally when executed by query optimization engines of a given types, and where the query optimization engine is of the given type; 
   identifying, by the one or more processors, an optimization engine of a type different than the given type, wherein the optimization engine of the type different comprises capacity and functionality to execute the new query;   transmitting, by the one or more processors, the new query to the optimization engine of the different type; and   executing, by the one or more processors, the new query with the optimization engine of the different type.   
     
     
         13 . The computer system of  claim 10 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that optimizing the query in advance of the designated execution time will improve performance by an amount of time about a pre-defined threshold;   pre-emptively optimizing, by the one or more processors, with the query optimization engine, the new query in advance of the designated execution time;   caching, by the one or more processors, the new query in a memory; and   executing, by the one or more processors, the new query at the designated execution time, based on obtaining the pre-emptively optimized query from the memory.   
     
     
         14 . The computer system of  claim 10 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 commencing, by the one or more processors, execution of the new query by the query optimization engine;   in advance of completing of the execution, determining, by the one or more processors, that the executing is longer than an anticipated threshold amount of time;   determining, by the one or more processors, that other query optimization engines of the multiple query optimization engines are unavailable to execute the new query; and   pausing, by the one or more processors, the execution of the new query by the query optimization engine.   
     
     
         15 . The computer system of  claim 14 , further comprising:
 determining, by the one or more processors, that a given query optimization engine of the other query optimization engines is available to accept the new query;   transmitting, by the one or more processors, the new query to the given query optimization engine; and   executing, by the one or more processors, the new query with the given optimization engine.   
     
     
         16 . The computer system of  claim 15 , wherein the executing the new query with the given optimization engine comprises re-optimizing the query. 
     
     
         17 . The computer system of  claim 10 , wherein the statistical information comprises query log events and details relevant to various stages of query execution. 
     
     
         18 . A computer program product for distributing query optimization activities across a database system comprising multiple databases and multiple query optimization engines, the computer program product comprising:
 one or more computer readable storage media and program instructions collectively stored on the one or more computer readable storage media readable by at least one processing circuit to:
 monitor query execution within the database system within the multiple databases and by the multiple query optimization engines; 
 retain statistical information from the monitoring in a centralized database; 
 obtain, at a query optimization engine of the database system, a new query; 
 predict, based on accessing the statistical information in the centralized database, that executing the new query with the query optimization engine at a designated execution time comprises a suboptimal execution of the new query; and 
 alter a planned execution of the new query at the designated execution time with the query optimization engine to optimize execution of the query within the database system. 
   
     
     
         19 . The computer program product of  claim 18 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution;   identifying, by the one or more processors, a similar optimization engine with capacity to execute the new query;   transmitting, by the one or more processors, the new query to the similar optimization engine; and   executing, by the one or more processors, the new query with the similar optimization engine.   
     
     
         20 . The computer program product of  claim 18 , wherein altering the planned execution of the new query at the designated execution time with the query optimization engine comprises:
 determining, by the one or more processors, that an existing workload of the query optimization engine will cause the suboptimal execution, wherein the determining comprises:
 determining, by the one or more processors, based on the statistical information that historical queries similar to the new query executed suboptimally when executed by query optimization engines of a given types, and where the query optimization engine is of the given type; 
   identifying, by the one or more processors, an optimization engine of a type different than the given type, wherein the optimization engine of the type different comprises capacity and functionality to execute the new query;   transmitting, by the one or more processors, the new query to the optimization engine of the different type; and   executing, by the one or more processors, the new query with the optimization engine of the different type.