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 computer program product for calculating one or more access paths during bind time, the computer program product comprising:
one or more non-transitory computer readable storage media and program instructions stored on the one or more computer readable storage media, the program instructions comprising:
program instructions to receive a query;
program instructions to identify 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;
program instructions to calculate 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;
program instructions to determine 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, program instructions to retrieve 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;
program instructions to identify resource usage changes associated with the one or more identified access paths;
program instructions to determine resource usage weight changes associated with the identified resource usage changes;
program instructions to recalculate 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
program instructions to store the recalculated resource costs associated with the one or more identified access paths during bind time.