Access path optimization through system statistics

Patent metadata

Field	Value
Publication number	US-9436735-B1
Application number	US-201615016461-A
Country	US
Kind code	B1
Filing date	Feb 5, 2016
Priority date	Jul 14, 2015
Publication date	Sep 6, 2016
Grant date	Sep 6, 2016

Abstract

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.

First claim

What is claimed is:

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.

Assignees

• IBM

Inventors

• Feng Hao
• Li Shuo
• Sun Shengyan
• Yang xin ying

Classifications

• G06F17/30474 Primary

  Physics · mapped topic

• G06F16/24534

  Query rewriting; Transformation · CPC title

• G06F16/24545 Primary

  Selectivity estimation or determination · CPC title

• G06F16/245

  Query processing · CPC title

• G06F16/24549 Primary

  Run-time optimisation · CPC title