Cost based analysis of direct I/O access

• US 20080098169A1
• Filed: 10/20/2006
• Published: 04/24/2008
• Est. Priority Date: 10/20/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Malhotra, Varun, Lahiri, Tirthankar, Loaiza, Juan, Potapov, Dmitry, Chatterjee, Sumanta, Susairaj, Margaret, Kaluskar, Sanjay, Jakobsson, Hakan
• Granted Patent: US 8,224,813 B2
• Time in Patent Office: Days
• US Class Current: 711/113
• CPC Class Codes: G06F 12/0866 for peripheral storage systems, e.g. disk cache
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

A method, system, and computer program product forcost based analysis for data access in a database management system. In one approach, the method, system, and computer program productperforms identifying data to access, determining a first cost for direct I/O storage access and a second cost for cache access, performing a comparison between the first cost and the second cost, and accessing a first portion of identified data based upon the comparison.

Claims

1. A method of cost based analysis for data access in a database management system, comprising:

identifying data to access;

determining a first cost for direct I/O storage access and a second cost for cache access;

performing a comparison between the first cost and the second cost; and

accessing a first portion of identified data based upon the comparison.

2. The method of claim 1, wherein the comparison determines whether the first portion of identified data is accessed using a direct I/O storage access.

3. The method of claim 1, wherein the second cost for cache access is a cost for a database buffer cache access.

4. The method of claim 1, wherein the first cost for direct I/O storage access further comprises determining a cost of writing uncommitted database changes to a disk.

5. The method of claim 1, further comprising:

determining one or more cost factors for the first cost and the second cost; and

determining a weight to be applied to at least one of the one or more cost factors.

6. The method of claim 5, wherein the weight to be applied is dependent on whether the database management systems uses a smart storage device.

7. The method of claim 5, wherein the one or more cost factors further comprises a cost to access a storage unit of data directly from I/O storage and a number of storage units of data to access from I/O storage.

8. The method of claim 5, wherein the one or more cost factors further comprises a maximum number of uncommitted storage units of data to write to a disk and a ratio of a cost for access of a cache to a cost for a direct I/O storage access.

9. The method of claim 5, wherein the one or more cost factors further comprises a number of storage units of data distributed on remote cache instances, a ratio of a cost for transfer of data between cache instances to a cost for direct I/O access, a ratio of a cost for access of a cache to cost for direct I/O access, a number of storage units of data, a number of cached storage units of data on a local instance, and an I/O time per storage unit of data for direct I/O.

10. The method of claim 5, wherein the weight to be applied is dependent on a projection capability of the smart storage device.

11. The method of claim 5, wherein the weight to be applied is dependent on a predicate pushing capability of the smart storage device.

12. The method of claim 1, further comprising:

determining whether a database request involves a direct I/O storage access candidate;

determining whether a set of uncommitted changes relevant to the database request are written to disk; and

accessing a second portion of the identified data based upon the database request and the set of uncommitted changes.

13. The method of claim 1, further comprising:

determining a plan to fetch one or more data blocks in response to a database request, wherein the first portion of identified data is a first data block in the plan.

14. The method of claim 13, further comprising:

comparing the first cost and the second cost for a second portion of identified data, wherein the second portion of identified data is a second data block in the plan.

15. The method of claim 1, wherein the database management system manages a clustered database.

16. A method for cost based analysis for data access in a computing system with a smart storage device, comprising:

identifying data to access;

determining a first cost of direct I/O storage access with the smart storage;

performing a comparison between a second cost for a cache access and the first cost; and

accessing data based on the comparison.

17. The method of claim 16, further comprising:

determining one or more cost factors for the first cost and the second cost; and

determining a weight to be applied to at least one of the one or more cost factors.

18. The method of claim 17, wherein the one or more cost factors further comprises a cost to access a storage unit of data directly from I/O storage and a number of storage units of data to access from I/O storage.

19. The method of claim 17, wherein the weight to be applied is dependent on a projection capability of the smart storage device.

20. The method of claim 17, wherein the weight to be applied is dependent on a predicate pushing capability of the smart storage device.

21. A computer program product comprising a computer usable medium having executable code to execute a process for implementing cost based analysis for data access in a computing system, the process comprising:

identifying data to access;

determining a first cost for direct I/O storage access and a second cost for cache access;

performing a comparison between the first cost and the second cost; and

accessing a first portion of identified data based upon the comparison.

22. The system for cost based analysis for comprising:

a database server for identifying data to access;

a database cache for the database server to access data and a database I/O storage for the database server to access data, wherein the database server determines a first cost for direct I/O storage access and a second cost for cache access; and

a direct access I/O decision module for performing a comparison between the first cost and the second cost, wherein the database server accesses a first portion of identified data based upon the comparison.

23. The system of claim 22, wherein the comparison determines whether the first portion of identified data is accessed using a direct I/O storage access.

24. The system of claim 22, wherein the second cost for cache access is a cost for a database buffer cache access.

25. The system of claim 22, wherein the first cost for direct I/O storage access determines a cost of writing uncommitted database changes to a disk.

26. The system of claim 22, wherein a direct access I/O decision module determines one or more cost factors for the first cost and the second cost, and a weight to be applied to at least one of the one or more cost factors.

27. The system of claim 26, wherein the weight to be applied is dependent on whether the database management systems uses a smart storage device.

28. A computer program product comprising a computer usable medium having executable code to execute a process for implementing cost based analysis for data access in a computing system, the process comprising:

identifying data to access;

determining a first cost of direct I/O storage access with the smart storage;

performing a comparison between a second cost for a cache access and the first cost; and

accessing data based on the comparison.

29. The system for cost based analysis for comprising:

a database server for identifying data to access;

a database cache for the database server to access data and a database I/O storage for the database server to access data, wherein the database server determines a first cost of direct I/O storage access with the smart storage; and

a direct access I/O decision module for performing a comparison between a second cost for a cache access and the first cost, wherein the database server accessing data based on the comparison.