Access path optimization through system statistics

摘要

In an approach for calculating one or more access paths during bind time, a computer receives a query. The computer identifies one or more access paths for processing the received query, wherein the one or more access paths include steps associated with retrieving data from a database based on the received query. The computer calculates resource costs associated with processing the received query on the one or more identified access paths based on one of more of: resources utilized to perform steps associated with processing the received query, and system statistics associated with the one or more identified access paths.

主权项

1. A method for calculating one or more access paths during bind time, the method comprising:
receiving, by one or more computer processors, a query; identifying, by one or more computer processors, one or more access paths for processing the received query, wherein the one or more access paths include steps associated with retrieving data from a database based on the received query; calculating, by one or more computer processors, resource costs associated with processing the received query on the one or more identified access paths based on one or more of: resources utilized to perform steps associated with processing the received query, and system statistics associated with the one or more identified access paths, wherein the resource costs include: central processing unit (CPU) cost, input/output (I/O) costs, buffer pool usage, record identifier (RID) usage, and work file usage; determining, by one or more computer processors, whether the received query exits bind time, wherein the bind time includes preparation to execute the received query, establish a relationship with the database, and validate the received query to detect and correct potential errors; responsive to determining the received query does not exit bind time, retrieving system statistics associated with the one or more identified access paths based on one or more of: a time-based interval, short-period statistics, long-period statistics, and real-time statistics; identifying, by one or more computer processors, resource usage changes associated with the one or more identified access paths; determining, by one or more computer processors, resource usage weight changes associated with the identified resource usage changes; recalculating, by one or more computer processors, the resource costs associated with the one or more identified access paths based on the retrieved system statistics and the determined resource usage weight changes associated with the identified resource usage changes; and storing, by one or more computer processors, the recalculated resource costs associated with the one or more identified access paths during bind time.