Query optimizer cost model
An embodiment of the invention optimizes a DBMS operation based upon multiple criteria, such as input/outputs and CPU time. Multiple optimization criteria are normalized to a common unit of measurement. Normalized optimization criteria are used in a cost function. Estimated execution times are calculated under a variety of different workload environments. Cost values are scaled to a variety of computer architectures.
1. A computer implemented optimizer cost function to estimate an execution cost of an execution plan, comprising:
a first set of cost values configured to estimate a plan's first consumption amount of a first resource, at a plurality of database workloads; and
a second set of cost values configured to estimate a plan's second consumption amount of a second resource, at the plurality of database workloads,
wherein the first cost value set and the second cost value set comprise cost values normalized to a common unit of measurement.
2. The optimizer cost function of claim 1 , wherein the first resource comprises a plurality of input/outputs (I/Os).
3. The optimizer cost function of claim 2 , wherein the plurality of I/Os includes one or more from the group of single block reads, multi-block reads, direct reads, direct writes, and CPU cycles.
4. The optimizer cost function of claim 1 , wherein the second resource comprises CPU cycles.
5. The optimizer cost function of claim 1 , wherein the common unit of measurement comprises execution time.
6. The optimizer cost function of claim 1 , wherein the database workloads comprise workloads in which the demand on the CPU is greater than the demand on the I/O.
7. The optimizer cost function of claim 1 , wherein the database workloads comprise workloads in which the demand on the CPU is less than the demand on the I/O.
8. The optimizer cost function of claim 1 , wherein the first cost value set or the second cost value set is tuned at one or more of the plurality of workloads.
9. The optimizer cost function of claim 1 , in which the first cost value and the second cost value for each of the plurality of workloads are combined to generate a set of workload-based execution plan costs.
10. A computer implemented method of estimating an execution cost of an execution plan, comprising:
computing a first cost value of a first consumption amount of a first resource, the first resource including one or more of a plurality of input/output (I/O) types;
computing a second cost value of a second consumption amount of a second resource, the second resource comprising CPU cycles;
normalizing the first cost value and the second cost value to a common unit of measurement; and
combining the first cost value and the second cost value to generate the execution cost.
11. The method of claim 10 , wherein the common unit of measurement comprises execution time.
12. The method of claim 10 , wherein the plurality of I/O types includes single block reads, multi-block reads, direct reads, direct writes, and CPU cycles.
13. The method of claim 10 , wherein the first cost value of the execution plan is computed at a plurality of database workloads.
14. The method of claim 10 , wherein the second cost value of the execution plan is computed at a plurality of database workloads.
15. The method of claim 10 , further comprising tuning the first cost value or the second cost values based on a plurality of database workloads.
16. The method of claim 15 , wherein the plurality of database workloads comprise batch processing and transaction processing.
17. A computer-useable medium, the medium comprising a sequence of instructions which, when executed by a processor, causes the processor to execute a computer implemented method of estimating an execution cost of an execution plan, comprising:
computing a first cost value of a first consumption amount of a first resource, the first resource including one or more of a plurality of input/output (I/O) types;
computing a second cost value of a second consumption amount of a second resource, the second resource comprising CPU cycles;
normalizing the first cost value and the second cost value to a common unit of measurement; and
combining the first cost value and the second cost value to generate the estimated execution cost.
18. The computer-useable medium of claim 17 , wherein the common unit of measurement comprises execution time.
19. The computer-useable medium of claim 17 , wherein the plurality of I/O types includes single block reads, multi-block reds, direct reads, direct writes, and CPU cycles.
20. The computer-useable medium of claim 17 , wherein the first cost value of the execution plan is computed and a plurality of database workloads.
21. The computer-useable medium of claim 17 , wherein the second cost value of the execution plan is computed at a plurality of database workloads.
22. The computer-useable medium of claim 17 , further comprising tuning the first cost value or the second cost value based on a plurality of database workloads.
23. The computer-useable medium of claim 22 , wherein the plurality of database workloads comprises batch processing and transaction processing.
24. A computer implemented optimizer cost function to estimate an execution cost of an execution plan, comprising:
a cost value to estimate a plan's consumption amount of a resource;
wherein the cost value is tuned at a plurality of database workloads.
25. The optimizer cost function of claim 24 , wherein the workload comprises a user-selected workload.
26. The optimizer cost function of claim 24 , wherein the workload comprises a workload selected by application of a heuristic.
27. A computer implemented method for estimating execution cost of an execution plan, comprising:
estimating a first set of cost values for the execution plan, the first set of cost values including a first consumption amount of a first resource at a plurality of database workloads; and
estimating a second set of cost values for the execution plan, the second set of cost values including a second consumption amount of a second resource at the plurality of database workloads,
wherein the first cost value set and the second cost value set comprise cost values normalized to a common unit of measurement.
28. The method of claim 27 , wherein the first resource comprises a plurality of input/outputs (I/Os).
29. The method of claim 27 , further comprising:
combining the first cost value and the second cost value for each of the plurality of workloads to generate a set of workload-based execution plan costs.
30. A computer program product embodied on computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a computer implemented method for estimating execution cost of an execution plan, the method comprising:
estimating a first set of cost values for the execution plan, the first set of cost values including a first consumption amount of a first resource at a plurality of database workloads; and
estimating a second set of cost values for the execution plan, the second set of cost values including a second consumption amount of a second resource at the plurality of database workloads,
wherein the first cost value set and the second cost value set comprise cost values normalized to a common unit of measurement.
31. The computer program product of claim 30 , wherein the first resource comprises a plurality of input/outputs (I/Os).
32. The computer program of claim 31 , further comprising:
combining the first cost value and the second cost value for each of the plurality of workloads to generate a set of workload-based execution plan costs.
33. A computer implemented system of estimating an execution cost of an execution plan, comprising:
means for computing a first cost value of a first consumption amount of a first resource, the first resource including one or more of a plurality of input/output (I/O) types;
means for computing a second cost value of a second consumption amount of a second resource, the second resource comprising CPU cycles;
means for normalizing the first cost value and the second cost value to a common unit of measurement; and
means for combining the first cost value and the second cost value to generate the execution cost.
34. The system of claim 33 , further comprising:
means for tuning the first cost value or the second cost value based on a plurality of database workloads.
35. A computer implemented method to estimate an execution cost of an execution plan, comprising:
estimating a cost value of the execution plan's consumption amount of a resource;
wherein the cost value is tuned at a plurality of database workloads.
36. The method of claim 35 , wherein the workload comprises a user-selected workload.
37. The method of claim 35 , wherein the workload comprises a workload selected by application of a heuristic.
38. A computer program product embodied on computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a computer implemented method to estimate an execution cost of an execution plan, the method comprising:
estimating a cost value of the execution plan's consumption amount of a resource;
wherein the cost value is tuned at a plurality of database workloads.
39. The method of claim 38 , wherein the workload comprises a user-selected workload.
40. The method of claim 38 , wherein the workload comprises a workload selected by application of a heuristic.