Materialized views assistant
The present disclosure relates to a method comprising incorporating a utilization level field for a set of materialized views of database system in a database catalog of the database system. A computer process may be generated to run during a defined time period on a spare host of the database system. The computer process is configured to monitor utilization of the set of materialized views and to collect data of utilized materialized views in a dedicated registry. A check if each materialized view of the set of materialized views is present in the registry may be periodically be performed. The utilization level of a materialized view may be adapted based on its collected data in the registry. A check in catalog utilization levels of the set of materialized views may periodically be performed and based on the utilization levels utilization of the set of materialized views may be adapted.
1. A computer-implemented method for a database system comprising a set of materialized views, the method comprising:
incorporating, by one or more processors, a utilization level field for the set of materialized views in a database catalog of the database system;
generating, by the one or more processors, a computer process to run during a defined time period on a spare host of the database system, the computer process being configured to monitor utilization of the set of materialized views;
executing, by the one or more processors, the computer process comprising:
detecting, by the computer process, utilization of at least one materialized view of the set of materialized views; and
collecting, by the computer process, data of the detected materialized view in a dedicated registry, the data indicating the detected materialized view and a time of utilization of the materialization view;
periodically checking, by the one or more processors, if each materialized view of the set of materialized views is present in the registry;
adapting, by the one or more processors, in the utilization level field, the utilization level of a materialized view based on its collected data in the registry; and
periodically checking, by the one or more processors, in catalog utilization levels of the set of materialized views, and based on the utilization levels adapting the utilization of the set of materialized views,
wherein adapting the utilization of the set of materialized views comprises:
in a case where the utilization level of a materialized view is smaller than a threshold, disabling the materialized view; and
in a case where the utilization level of a materialized view is higher than a threshold, maintaining the materialized view.
2. The computer-implemented method of claim 1 , the utilization level being a number of utilizations.
3. The computer-implemented method of claim 1 , wherein the materialized view includes a table that contains a subset of columns of a base table.
4. The computer-implemented method of claim 1 , the utilization level being a frequency of utilizations, wherein adapting the utilization of the set of materialized views comprises: determining based on frequency time periods during which the materialized view is to be enabled, and enabling the materialized view only for the time periods.
5. The computer-implemented method of claim 1 , wherein adapting the utilization level comprises: increasing the utilization level of a materialized view upon determining that it exists in the registry, otherwise decreasing the utilization level of the materialized view.
6. The computer-implemented method of claim 1 , collected data of a detected materialized view further indicating a SQL statement that triggered the utilization of the detected materialized view.
7. The computer-implemented method of claim 6 , the collected data comprising a hash generated from the SQL statement.
8. The computer-implemented method of claim 1 , wherein each materialized view the set of materialized views is represented by a record in the database catalog, the record comprising values of attributes of the materialized view, the attributes comprising an additional utilization level field.
9. The computer-implemented method of claim 1 , wherein the computer process is a daemon process.
10. The computer-implemented method of claim 1 , wherein the registry is checked more frequently than the catalog.
11. A 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, the program instructions executable by one or more processors to cause the one or more processors to perform actions comprising:
incorporating, by the one or more processors, a utilization level field for a set of materialized views in a database catalog of a database system;
generating, by the one or more processors, a computer process to run during a defined time period on a spare host of the database system, the computer process being configured to monitor utilization of the set of materialized views;
executing, by the one or more processors, the computer process comprising:
detecting, by the computer process, utilization of at least one materialized view of the set of materialized views; and
collecting, by the computer process, data of the detected materialized view in a dedicated registry, the data indicating the detected materialized view and a time of utilization of the materialization view;
periodically checking, by the one or more processors, if each materialized view of the set of materialized views is present in the registry;
adapting, by the one or more processors, in the utilization level field, the utilization level of a materialized view based on its collected data in the registry; and
periodically checking, by the one or more processors, in catalog utilization levels of the set of materialized views, and based on the utilization levels adapting the utilization of the set of materialized views,
wherein adapting the utilization of the set of materialized views comprises:
in a case where the utilization level of a materialized view is smaller than a threshold, disabling the materialized view; and
in a case where the utilization level of a materialized view is higher than a threshold, maintaining the materialized view.
12. The computer program product of claim 11 , the utilization level being a number of utilizations.
13. The computer program product of claim 11 , wherein the materialized view includes a table that contains a subset of columns of a base table.
14. The computer program product of claim 11 , the utilization level being a frequency of utilizations, wherein adapting the utilization of the set of materialized views comprises: determining based on frequency time periods during which the materialized view is to be enabled, and enabling the materialized view only for the time periods.
15. The computer program product of claim 11 , wherein adapting the utilization level comprises: increasing the utilization level of a materialized view upon determining that it exists in the registry, otherwise decreasing the utilization level of the materialized view.
16. The computer program product of claim 11 , collected data of a detected materialized view further indicating a SQL statement that triggered the utilization of the detected materialized view.
17. The computer program product of claim 16 , the collected data comprising a hash generated from the SQL statement.
18. The computer program product of claim 11 , wherein each materialized view the set of materialized views is represented by a record in the database catalog, the record comprising values of attributes of the materialized view, the attributes comprising an additional utilization level field.
19. The computer program product of claim 11 , wherein the program instructions are provided as a service in a cloud environment.
20. A system comprising:
one or more computer readable storage media with program instructions collectively stored on the one or more computer readable storage media; and
one or more processors configured to execute the program instructions to perform a method comprising:
incorporating a utilization level field for a set of materialized views of database system in a database catalog of the database system;
generating a computer process to run during a defined time period on a spare host of the database system, the computer process being configured to monitor utilization of the set of materialized views, detect utilization of at least one materialized view of the set of materialized views; and collect data of the detected materialized view in a dedicated registry, the data indicating the detected materialized view and a time of utilization of the materialized view;
periodically checking if each materialized view of the set of materialized views is present in the registry;
adapting, in the utilization level field, a utilization level of a materialized view based on its collected data in the registry; and
periodically checking in catalog utilization levels of the set of materialized views, and based on the utilization levels adapting the utilization of the set of materialized views,
wherein adapting the utilization of the set of materialized views comprises:
in a case where the utilization level of a materialized view is smaller than a threshold, disabling the materialized view; and
in a case where the utilization level of a materialized view is higher than a threshold, maintaining the materialized view.