System and method for analyzing sequential data access efficiency
A system and method determines sequential access efficiency for a database table. A number of data block changes that occur during a sequential access of a plurality of rows in a database table are determined. The sequential access efficiency is calculated based on the determined number of data block changes.
1. A computer-implemented method of determining sequential access efficiency for a database table, the method executed by a processor to perform a plurality of operations, the operations comprising:
determining a sequential access of a plurality of rows of a database table, wherein the plurality of rows are stored using a plurality of data blocks, and wherein the sequential access occurs when a portion of the database table is accessed;
determining a number of data block changes that occur during the sequential access of the plurality of rows in the database table, wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block, different from the first data block, such that accessing the next row after accessing the current row results in the data block change; and
calculating a sequential access efficiency based on the determined number of data block changes, wherein calculating the sequential access efficiency is performed using the following equation:
( N B −1)/ N C
wherein N B is the determined number of data blocks and N c the determined number of data block changes.
2. The computer-implemented method of claim 1 , wherein determining the sequential access comprises using a least recently used (LRU) algorithm to determine how the data blocks have been accessed.
3. The computer-implemented method of claim 1 , wherein determining the sequential access comprises accessing the plurality of rows of the database table sequentially according to a native key sequence.
4. The computer-implemented method of claim 1 , wherein the operations further comprise:
determining a number of data blocks that contain data, wherein calculating the sequential access efficiency further comprises:
calculating the sequential access efficiency based on the determined number of data block changes and the determined number of data blocks.
5. The computer-implemented method of claim 1 , wherein the operations further comprise:
determining a block count, wherein the block count is based on a maximum number of rows per data block and a number of the plurality of rows; and
wherein calculating the sequential access efficiency further comprises calculating the sequential access efficiency based on the determined number of data block changes and the determined block count.
6. The computer-implemented method of claim 1 , wherein the operations further comprise:
determining a first buffered sequential access of the plurality of rows of the database table based on a first number of buffers used to buffer the first buffered sequential access;
determining a first number of first data block changes that occur during the first buffered sequential access; and
calculating a first buffered sequential access efficiency value based on the determined first number of data block changes and the first number of data buffers utilized to buffer the first buffered sequential access.
7. The computer-implemented method of claim 6 , wherein the operations further comprise:
determining a second buffered sequential access of the plurality of rows of the database table based on a second number of buffers used to buffer the second buffered sequential access;
determining a second number of first data block changes that occur during the second buffered sequential access; and
calculating a second buffered sequential access efficiency value based on the determined second number of data block changes and the second number of data buffers utilized to buffer the first buffered sequential access.
8. The computer-implemented method of claim 7 , wherein the operations further comprise:
generating a report that comprises the calculated sequential access efficiency, the calculated first buffered sequential access efficiency value, and the calculated second buffered sequential access efficiency value.
9. The computer-implemented method of claim 1 , wherein determining the sequential access comprises determining the sequential access while the database is online.
10. The computer-implemented method of claim 1 , wherein determining the sequential access comprises determining the sequential access while the database is offline.
11. A computer-implemented system of determining sequential access efficiency fora database table, the system comprising:
a processor to:
determine a sequential access of a plurality of rows of a database table, wherein the plurality of rows are stored using a plurality of data blocks, wherein the sequential access occurs when a portion of the database table is accessed;
determine a number of data block changes that occur during the sequential access of the plurality of rows in the database table, wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block, different from the first data block, such that accessing the next row after accessing the current row results in the data block change; and
calculate a sequential access efficiency based on the determined number of data block changes, wherein calculating the sequential access efficiency is performed using the following equation:
( N B −1)/ N C
wherein N B is the determined number of data blocks and N c the determined number of data block changes.
12. The computer-implemented system of claim 11 , wherein the processor to determine the sequential access is further to use a least recently used (LRU) algorithm to determine how the data blocks have been accessed.
13. The computer-implemented system of claim 11 , wherein the processor to determine the sequential access is further to access the plurality of rows of the database table sequentially according to a native key sequence.
14. The computer-implemented system of claim 11 , wherein the processor is further to:
determine a number of data blocks that contain data, wherein the processor to determine the sequential access efficiency is further to calculate the sequential access efficiency based on the determined number of data block changes and the determined number of data blocks.
15. The computer-implemented system of claim 11 , wherein the processor is further to:
determine a block count, wherein the block count is based on a maximum number of rows per data block and a number of the plurality of rows, wherein the processor to determine the sequential access efficiency is further to calculate the sequential access efficiency based on the determined number of data block changes and the determined block count.
16. The computer-implemented system of claim 11 , wherein the processor is further to:
determine a first buffered sequential access of the plurality of rows of the database table based on a first number of buffers used to buffer the first buffered sequential access;
determine a first number of first data block changes that occur during the first buffered sequential access; and
calculate a first buffered sequential access efficiency value based on the determined first number of data block changes and the first number of data buffers utilized to buffer the first buffered sequential access.
17. The computer-implemented system of claim 16 , wherein the processor is further to:
determine a second buffered sequential access of the plurality of rows of the database table based on a second number of buffers used to buffer the second buffered sequential access;
determine a second number of first data block changes that occur during the second buffered sequential access; and
calculate a second buffered sequential access efficiency value based on the determined second number of data block changes and the second number of data buffers utilized to buffer the first buffered sequential access.
18. The computer-implemented system of claim 17 , wherein the processor is further to:
generate a report that comprises the calculated sequential access efficiency, the calculated first buffered sequential access efficiency, and the calculated second buffered sequential access efficiency value.
19. A non-transitory, tangible computer-readable storage medium having computer-readable instructions thereon which when executed by a processor cause the processor to:
determine a sequential access of a plurality of rows of a database table, wherein the plurality of rows are stored using a plurality of data blocks, and wherein the sequential access occurs when a portion of the database table is accessed;
determine a number of data block changes that occur during the sequential access of the plurality of rows in the database table, wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block, different from the first data block, such that accessing the next row after accessing the current row results in the data block change; and
calculate a sequential access efficiency based on the determined number of data block changes, wherein calculating the sequential access efficiency is performed using the following equation:
( N B −1)/ N C
wherein N B is the determined number of data blocks and N c the determined number of data block changes.
20. The non-transitory, tangible computer-readable storage medium of claim 19 , wherein the computer-readable instructions further cause the processor to:
determine a first buffered sequential access of the plurality of rows of the database table based on a first number of buffers used to buffer the first buffered sequential access;
determine a first number of first data block changes that occur during the first buffered sequential access; and
calculate a first buffered sequential access efficiency value based on the determined first number of data block changes and the first number of data buffers utilized to buffer the first buffered sequential access.
21. The non-transitory, tangible computer-readable storage medium of claim 20 , wherein computer-readable instructions further cause the processor to:
determine a second buffered sequential access of the plurality of rows of the database table based on a second number of buffers used to buffer the second buffered sequential access;
determine a second number of first data block changes that occur during the second buffered sequential access; and
calculate a second buffered sequential access efficiency value based on the determined second number of data block changes and the second number of data buffers utilized to buffer the first buffered sequential access.
22. The non-transitory, tangible computer-readable storage medium of claim 21 , wherein the computer-readable instructions further cause the processor to:
generate a report that comprises the calculated sequential access efficiency, the calculated first buffered sequential access efficiency value, and the calculated second buffered sequential access efficiency value.