Method and apparatus for fast and comprehensive DBMS analysis

Abstract

A fast and comprehensive analysis of a database table is peformed by reading a header block describing the location of blocks storing data information of a database table. The data blocks of the database table are read using direct asynchronous IO into memory. Data read in from the data blocks is analyzed to determine information regarding the health or condition of the database table. The analysis is performed by spawning separate processes, each process being assigned an approximately equivalent number of data blocks to analyze. Once each process completes analysis of the assigned data blocks, the analysis by each thread is summarized and averaged to provide information to the DBA relating to the condition of the database table. The information gathered regarding the database table is more comprehensive than that provided by currently available database management systems and tools.

Claims

What is claimed is:

1. A method of analysis of a database, comprising:

identifying a table in said database;

reading each of blocks associated with said table using direct asynchronous I/O; and

performing said analysis based on information contained in said blocks;

wherein reading includes:

creating plural block lists, each list identifying a set of blocks associated with said table;

spawning plural threads; and

performing the following for each respective thread,

assigning one of said plural block lists to the respective thread,

reading each block identified by the assigned block list using direct asynchronous I/O; and

gathering information for said analysis utilizing data contained in each block read.

2. The method according to claim 1, further comprising assigning each thread to a separate processor.

3. A method of analysis of a database, comprising:

identifying a table in said database;

reading each of blocks associated with said table using direct asynchronous I/O; and

performing said analysis based on information contained in said blocks;

wherein identifying comprises:

determining the location of an extent segment header block of a database table;

reading the extent segment header block; and

creating an extent list identifying each extent contained in the extent segment header block by file number, block number, and length.

4. The method according to claim 3, wherein:

reading each of blocks associated with said table includes:

dividing the extent list into at least two sublists each containing a number of blocks,

spawning a thread for working each of said sublists, and

gathering information related to said elements by each thread; and

performing includes summing and averaging the information gathered by each thread to perform said analysis.

5. The method according to claim 4, further comprising assigning each thread to a separate processor.

6. The method according to claim 4, wherein dividing comprises:

dividing the extent list into at least two sublists each containing an equal number of blocks.

7. A computer readable medium having computer instructions stored thereon that, when loaded into a computer, cause the computer to perform the steps of:

identifying a table in said database;

reading each of blocks associated with said table using direct asynchronous I/O; and

performing an analysis of said database based on information contained in said blocks;

wherein said step of reading includes the steps of:

creating plural block lists, each list identifying a set of blocks associated with said table;

spawning plural threads; and

performing the following steps for each respective thread,

assigning one of said plural block lists to the respective thread,

reading each block identified by the assigned block list using direct asynchronous IO; and

gathering information for said analysis utilizing data contained in each block read.

8. The computer readable medium according to claim 7, wherein said computer instructions further cause the computer to perform the step of assigning each thread to a separate processor.

9. A computer readable medium having computer instructions stored thereon that, when loaded into a computer, cause the computer to perform the steps of:

identifying a table in said database;

reading each of blocks associated with said table using direct asynchronous I/O; and

performing an analysis of said database based on information contained in said blocks;

wherein said step of identifying comprises the steps of:

determining the location of an extent segment header block of a database table;

reading the extent segment header block; and

creating an extent list identifying each extent contained in the extent segment header block by file number block number, and length.

10. The computer readable medium according to claim 9, wherein:

said step of reading each of blocks associated with said table includes the steps of,

dividing the extent list into at least two sublists each containing a number of blocks,

spawning a thread for working each of said sublists, and

gathering information related to said elements by each thread; and

said step of performing includes the step of summing and averaging the information gathered by each thread to perform said analysis.

11. The computer readable medium according to claim 10 wherein said computer instructions further cause the computer to perform the step of assigning each thread to a separate processor.

12. The computer readable medium according to claim 10, wherein said step of dividing comprises:

dividing the extent list into at least two sublists each containing an equal number of blocks.

13. An apparatus for analysis of a database, comprising:

means for identifying a table in said database;

means for reading each of blocks associated with said table using direct asynchronous I/O; and

means for performing said analysis based on information contained in said blocks;

wherein said means for reading includes:

means for creating plural block lists, each list identifying a set of blocks associated with said table;

means for spawning plural threads; and

means for performing the following steps for each respective thread,

assigning one of said plural block lists to the respective thread,

reading each block identified by the assigned block list using direct asynchronous I/O; and

gathering information for said analysis utilizing data contained in each block read.

14. An apparatus for analysis of a database, comprising:

means for identifying a table in said database;

means for reading each of blocks associated with said table using direct asynchronous I/O; and

means for performing said analysis based on information contained in said blocks;

wherein said means for identifying comprises:

means for determining the location of an extent segment header block of a database table;

means for reading the extent segment header block; and

means for creating an extent list identifying each extent contained in the extent segment header block by file number, block number, and length.

15. An apparatus for analysis of a database, comprising:

means for identifying a table in said database;

means for reading each of blocks associated with said table using direct asynchronous I/O; and

means for performing said analysis based on information contained in said blocks;

wherein said means for reading each of blocks associated with said table includes,

means for dividing the extent list into at least two sublists each containing a number of blocks,

means for spawning a thread for working each of said sublists, and

means for gathering information related to said elements by each thread; and

said means for performing includes means for summing and averaging the information gathered by each thread to perform said analysis.

16. The apparatus according to claim 15, wherein said means for dividing comprises:

means for dividing the extent list into at least two sublists each containing an equal number of blocks.

17. An apparatus for analysis of a database, comprising:

an identifying device configured to identify a table in said database;

a reader configured to read each of blocks associated with said table using direct asynchronous I/O; and

an analyzer configured to perform said analysis based on information contained in said blocks;

wherein said reader comprises:

a list device configured to create plural block lists, each list identifying a set of blocks associated with said table; and

a process spawner configured to spawn threads for processing and assign one of said block lists to each thread spawned; and

wherein the following steps are performed by each respective thread,

reading each block identified by the assigned block lists using direct asynchronous I/O; and

gathering information for said analysis utilizing data contained in each block read.

18. An apparatus for analysis of a database, comprising:

an identifying device configured to identify a table in said database;

a reader configured to read each of blocks associated with said table using direct asynchronous I/O; and

an analyzer configured to perform said analysis based on inforination contained in said blocks;

wherein said identifying device comprises:

a locator configured to determine a location of an extent segment header block of the database table and read the extent segment header block; and

a list device configured to create an extent list identifying each extent contained in the extent segment header block.

19. An apparatus for analysis of a database, comprising:

an identifying device configured to identify a table in said database;

a reader configured to read each of blocks associated with said table using direct asynchronous I/O; and

an analyzer configured to perform said analysis based on information contained in said blocks;

wherein said reader comprises,

a divider configured to divide the extent list into at least two sublists each containing a number of blocks,

a process spawner configured to spawn a thread for working each of said sublists, and

an information gatherer configured to gather information related to said elements by each thread; and

said analyzer comprises a summing device configured to sum and average the information gathered by each thread to perform said analysis.

Description

BACKGROUND OF THE INVENTION

1. Field of the Invention

This invention relates to a method of analysis and of increasing the speed at which the condition of a database is determined. The invention is more particularly related to the determination of appropriate data needed for a comprehensive analysis of specific tables in the database, and to increase the speed at which the analysis for determining the condition of a database table is performed.

2. Discussion of the Background

With the increasing use of computer databases in each of the home, office, and scientific environments, computer users and Database Administrators (DBA's) are increasingly confronted with the tasks of determining the condition or health of databases and performing various operations to maintain the database, including reorganization or rebuilding of tables stored in the database. Before rebuilding a database table(s), the DBA must first determine whether the condition of the database table(s) requires rebuilding.

Typically, commercial database products include tools to perform analysis to determine the condition of a database. For example, Oracle database management systems include an analyze function that performs a predetermined function including an SQL call UPDATE_STATISTICS_FOR_TABLE. The analyze function reads a selected database table and generates a set of statistics that reveal the condition of the table.

FIG. 1 is a flowchart illustrating the basic steps performed by a conventional Database Management System (DBMS) for determining the condition of a database table. At step 10, a Database Administrator (DBA) provides the name of a table to be analyzed. The table name is provided to a DBMS function that returns data about the named table (step 20). The returned data is analyzed resulting in statistical information (step 30) which is then placed (stored) in a location for retrieval for by DBA (step 40).

The DBMS function that returns data (DBMS analysis function) is shown in FIG. 2 and includes the steps of building an SQL call (step 50), invoking the SQL call (step 60), performing SQL overhead and administrative functions (step 70), and returning the SQL results (step 80). The SQL results are provided as the return values (statistical information) in step 30 of FIG. 1.

Once the SQL results are analyzed, the displayed (or stored) information is utilized by the DBA to determine appropriate action, if needed, to properly maintain the database (rebuild tables, for example). In the example function, UPDATE_STATISTICS_FOR_TABLE, the Oracle DBMS provides table information including No. of Rows, Chained Rows, Blocks allocated, Blocks allocated but free, Blocks on freelist, Average available space in Freelist, Average free space in a Block, and Min, Max, Average Row Length.

However, the provided table information does not provide enough information to give the DBA the most accurate picture of the DBMS table condition. In addition, current methods for retrieving the DBMS table information are slow, being encumbered by overhead associated with standard DBMS procedures and function calls (file reading techniques, and SQL, for example).

Modern database tables contain enormous amounts of data. If a DBA needs to determine the condition of a large database table, analysis techniques based on current methods can take unacceptably large amounts of time during which a database table may not be available to a user. Furthermore, current methods do not provide the DBA with the exact information needed for a proper analysis. Therefore, the DBA may rebuild tables when it is not necessary or where other action may have been more appropriate. All of the above leads to increased down time and maintenance efforts.

SUMMARY OF THE INVENTION

The present inventor has realized the need for increasing the amount of data gathered to determine the condition of tables within a database and to increase the speed at which the condition is determined. Accordingly, it is an object of the present invention to provide a comprehensive analysis of at least one database table to determine an information set relating to the health of the tables analyzed.

It is another object of this invention to provide method to increase the speed at which database tables are analyzed.

These and other objects are accomplished by a method for analyzing database tables including the steps of identifying a database table, determining elements corresponding to the condition of the database table based on data contained in the database tables and providing said elements to one of a display for viewing by a user, a storage location, and a mechanism for using said elements. The elements determined include at least one of number of simple rows, number of migrated rows, number of deleted rows, number of blocks initialized but empty, average free space in a block unused, average free released free space, average row length of migrated rows, average row length of chained rows, number of blocks to be read for normal, chained, and migrated rows, measure of density of blocks used, number of blocks exceeding a percentage used threshold, number of blocks less a percentage free threshold, number of chained rows spanning data files, and min max average of chained rows spanning blocks.

In addition, a method to increase the speed of analysis is provided including the steps of identifying a table in said database, reading each of blocks associated with said table using direct asynchronous I/O, and performing said analysis based on information contained in said blocks.

Additional speed of analysis is provided by parallel processing and bypassing SQL overhead associated with regular RDBMS architectures. The parallel processing includes the steps of dividing the blocks read into plural sets, and providing each set to separate processes to determine information needed for the analysis.

BRIEF DESCRIPTION OF THE DRAWINGS

A more complete appreciation of the invention and many of the attendant advantages thereof will be readily obtained as the same becomes better understood by reference to the following detailed description when considered in connection with the accompanying drawings, wherein:

FIG. 1 is a flow chart of a conventional routine for determining status of a database table;

FIG. 2 is a flow chart of a conventional database management system (DBMS) analysis function for retrieving information needed to determine the status of a database table;

FIG. 3 is an illustration of processing of a data block; and

FIG. 4 is a flowchart of the Fast Analysis method for accessing a database table and analyzing table contents according to the present invention.

DETAILED DESCRIPTION OF THE INVENTION

Conventional Database Management Systems (DBMS) utilize tables to store related data in a database. Once a table is defined, the DBMS will allocate blocks for storage of the related data. For example, if a table is to be built to contain 100,000 rows, and the rows of the table were defined such that 100 rows could fit per block, the DBMS would allocate 1,000 blocks to build the table.

Generally, DBMS systems allocate blocks in sets of contiguous blocks. A contiguous set of allocated blocks is commonly referred to as an extent. As a general rule, extents vary in size. Using the above example, the DBMS may utilize a single extent of 1,000 blocks, 2 extents of 500 blocks, or any other combination of extent sites to allocate the required 1, 000 blocks to build the table. Once the required blocks are allocated, the data is then stored in rows in the table utilizing the blocks allocated.

Over the course of time, additional data may be added to the table and the DBMS will allocate additional blocks as required. For example, if a user adds 250 rows to the table, using the above parameters, an additional 3 blocks would need to be allocated. Also over the course of time, information in the database may be deleted. In this case, deletion of rows occurs through the use of SQL to delete rows from the table. For example, a user may delete fifty rows from block 1, 40 rows from block 20, and 30 rows from block 60. When this occurs, it causes there to be more blocks in the table than required to maintain the data present in the table.

From time to time, the Database Administrator (DBA) will perform an analysis on each table that provides information regarding the condition of the database table. For example, the database administrator may look at information regarding the number of deleted rows to ascertain efficiency information with respect to how many blocks are holding deleted rows.

As another example, the Database Administrator may look to see whether a table has become fragmented. Row migration is one type of fragmentation. Row migration occurs when a row gets updated and the original space allocated for the row in the block is insufficient to hold all of the updated information. In this case, the row is moved to a location with more space, and a pointer is placed in the block where the original row resided, the pointer now pointing to the location of the moved (migrated) row. A large amount of row migration would tell the DBA that perhaps an inadequate amount of space was allocated for the original rows.

More often than not, there is insufficient space in a block to hold an updated row. In this case, the row is migrated to an entirely different block than where the original row resided, and the pointer is placed in the original row position points to the updated row in the different block.

Row migration causes increased retrieval time of database information because a pointer must be read and interpreted, and possibly two or more blocks (the block containing the pointer, and the block containing the actual row data) must be read to retrieve row specific information, as compared to a single block read for tables having no row migration.

It is useful for the database administrator to be able to retrieve information on row migration within a database table. If a lot of row migration has occurred, the database administrator may likely decide to rebuild the tables to more efficiently allocate row space.

In another example, a DBA makes a decision regarding the management of a row allocation within blocks of a table by setting a percentage of blocks free (PCTFREE) or percentage of blocks used (PCTUSED). For example, the DBMS allows the DBA to set PCTFREE when a database table is created. Then, as the DBMS fills up each block with table information, it will keep a percentage of the block free at least equal to the percentage PCTFREE for row updates.

The DBA sets the PCTFREE variable depending on how the database table is to be used. For example, if a table is to have frequent updates, additional PCTFREE would be established so that enough space is available to allow any necessary row migration to occur within the same block. As discussed above, row migration within the same block causes a table to become fragmented. However, rows migrated within the same block are retrieved with a single block read rather than the cumbersome process of reading a block, interpreting a pointer, and reading a second block (or more) as occurs when rows are migrated to different blocks. Therefore, appropriate PCTFREE settings allow DBMS performance to be maintained although the database tables may be modified.

PCTUSED is another parameter that allows the DBA to control the row allocation of a DBMS table. The DBMS prevents additional rows to be placed in a block unless the percentage of that block has fallen below PCTUSED. PCTUSED is different from PCTFREE in the sense that although a block may be used if there is an update, it will not be used to insert a new row unless the percentage used in the block is below PCTUSED. Therefore, PCTFREE is used to maintain free space when the table is initially built, and PCTUSED determines whether a new row may be inserted in a block. The PCTUSED parameter comes into effect when used space becomes free due to deletes.

In analyzing the database table, a DBA will look to see how closely the use of blocks of a table correspond to PCTUSED and PCTFREE set for that table.

If the average free space for a table is high, the DBA may decide to rebuild the table with a lower PCTFREE because space is being wasted. Conversely, if the hereinbelow described parameter BOCKS_LESS_THAN_PCTUSED is sufficiently high, it tells the DBA that too much migration is taking place and that it may be preferable to rebuild the tables with optimal values for PCTFREE and/or PCTUSED.

Thus, the value of knowledge regarding the condition of database tables is firmly established. However, conventional database management systems do not provide enough information for a DBA to make the most intelligent and efficient decisions regarding how and when tables within a database should be altered, modified, or rebuilt.

The present invention, hereinafter referred to as Fast Analysis, provides a detailed view of the condition, or health, of the database table allowing the DBA to make well informed decisions as to how to maintain the DBMS tables.

Referring to Table 1, a chart is provided comparing information gathered by Fast Analysis in comparison to a conventional DBMS system (Oracle, in this example). As shown in Table 1, Fast Analysis provides information regarding the condition of database tables in greater depth and quantity as compared to the conventional system.

                             TABLE 1
    Comparison of Fast Analysis and Oracle Information Gathered
                         Fast
    Name               Analysis  Oracle  Description
    ROW COUNT              X        X    No. of Rows
    NORMAL ROW             X             No. of Simple Rows
    COUNT
    MIGRATE ROW            X             No. of Migrated Rows
    COUNT
    CHAINED ROWS           X        X    No. of Chained Rows
    DELETED ROWS           X             No. of Deleted Rows
    BLOCK COUNT            X        X    No. of Blocks allocated
                                         for table
    USED BLOCK             X        X    No. of Blocks used by a
    COUNT                                table
    EMPTY BLOCK            X             No. of Blocks initialized
    COUNT                                but empty
    FREE BLOCK             X        X    No. of Blocks allocated
    COUNT                                but free
    BLOCKS ON              X        X    No. of Blocks on the
    FREELIST                             freelist
    AVG SPACE IN           X        X    Average available space in
    FREELIST BLKS                        freelist blocks
    AVG FREE SPACE         X        X    Average free space [across
                                         blocks in a table] in a
                                         block
    AVG UNUSED FREE        X             Average free space in a
    SPACE                                block never used
    AVG RELEASED           X             Average free released free
    FREE SPC                             space in box
    ROW LENGTH             X        X    Min, Max, Average Row
                                         length
    AVG MIGRATED           X             Average row length of
    ROW LEN                              chained rows
    BLOCK READS            X             No. of Blocks to be read
                                         for normal, chained and
                                         migrated rows.
    BLOCK FILL             X             Measure of Density of the
    PERCENTAGE                           blocks used
    BLKS MORE THAN         X             No. of Blks exceeded
    PCTFREE                              PCTFREE
    BLOCKS LESS            X             No. of Blks less than
    THAN PCTUSED                         PCTUSED
    FILESPAN COUNT         X             No. of chained rows
                                         spanning datafiles
    BLOCKSPAN              X             min, max average of
    OFFSET                               chained rows spanning
                                         blocks
    AVG BLOCK              X             Avg. Block header size
    HEADER
    AVG BLOCK DATA         X             Avg. space available in a
                                         block
    MIGRATED AND           X             No. of rows migrated and
    CHAINED CNT                          chained
    MIGRATED OR            X             No. of rows either
    CHAINED CNT                          migrated or chained
    AVG ROW DIR            X             Reusable offsets to row
    ENTRIES                              data
    TOTAL ROW DIR          X             No. of row directory
    ENTRIES                              entries
    AVG NORMAL             X             Avg. length of a normal
    ROW LEN                              row
    AVG CHAINED            X             Avg. length of migrated
    ROW LEN                              and chained rows
    AVG MIGRATED           X             Avg. length of migrated
    AND CHAINED                          and chained rows
    ROW LEN
    HOME ROW READS         X             No of initial blocks to
                                         read all rows of a table
    MIGRATED ROW           X             No of additional blocks to
    READS                                read for migrated rows
    CHAINED ROW            X             No of additional blocks to
    READS                                read for chained rows

                             TABLE 2
                 DDL Definition of DAO Repository
    /
    CREATE TABLE DAO.COMMON_DICT
        OBJECT_ID         NUMBER
            CONSTRAINT         TABLE_DICT_ID
            PRIMARY KEY
            USING INDEX        TABLESPACE DA02,
        COLLECT ID             NUMBER
            CONSTRAINT         TABLE_DICT_COLLECT_ID
            REFERENCES         DAO.COLLECT,
        PCT FREE               NUMBER,
        PCT USED                                NUMBER,
        CACHE                                   NUMBER(1),
        TABLE LOCK                         VARCHAR2(8),
        NESTED                                  NUMBER(1),
        NUM ROWS                           NUMBER,
        BLOCKS                             NUMBER,
        EMPTY BLOCKS                       NUMBER,
        AVG SPACE                          NUMBER,
        CHAIN CNT                          NUMBER,
        AVG ROW LEN                        NUMBER,
        AVG SPACE FREELIST BLOCKS          NUMBER,
        NUM FREELIST BLOCKS                NUMBER,
        SAMPLE_SIZE                        NUMBER,
        IOT NAME                           VARCHAR2(30),
        IOT TYPE                           NUMBER(1),
        TABLE TYPE OWNER                   VARCHAR2(30),
        TABLE TYPE                         VARCHAR2(30),
        PACKED                             NUMBER(1),
        MIN EXTENT SIZE                    NUMBER,
        MAX EXTENT SIZE                    NUMBER,
        AVG EXTENT SIZE                    NUMBER,
        STD EXTENT_SIZE                    NUMBER
    /
    CREATE TABLE DAO.TABLE FAST_ANAL
        TABLE_ID          NUMBER
            CONSTRAINT TABLE_FAST_ANAL_ID
            PRIMARY KEY
            USING INDEX TABLESPACE DAO2,
        TRUE CHAIN CNT                          NUMBER,
        TRUE MIGRATE CNT                        NUMBER,
        FREE LIST_LENGTH                        NUMBER,
        EMB CNT                                 NUMBER,
        SEGMENT _ HEADER _ HIGH_WATER           NUMBER,
        BLOCK IiEADER SIZE                      NUMBER,
        BLOCK DATA BYTES                        NUMBER,
        MIN_N7ORMAL_ROWS_BYTES                  NUMBER,
        MAX _ NORMAL_ROWS_BYTES                 NUMBER,
        AVG _ NORMAL_ROWS_BYTES                 NUMBER,
        STI) NORMAL ROWS BYTES                  NUMBER,
        MIN DELETE ROWS BYTES                   NUMBER,
        MAX_DELETE ROWS BYTES                   NUMBER,
        AVG DELETE ROWS BYTES                   NUMBER,
        STD DELETE_ROWS_BYTES                   NUMBER,
        MIN MIGRATED ROWS BYTES                 NUMBER,
        MAX MIGRATED ROWS_BYTES                 NUMBER,
        AVG MIGRATED ROWS BYTES                 NUMBER,
        STD _ MIGRATED ROWS_BYTES               NUMBER,
        MIN CHAINED ROW PIECE BYTES             NUMBER,
        AVG CHAINED ROW PIECE BYTES             NUMBER,
        STD CHAINED ROW PIECE BYTES             NUMBER,
        MIN NEW ROW PIECE BYTES                 NUMBER,
        MAX NEW ROW PIECE BYTES                 NUMBER,
        AVG_NEW_ROW_PIECE_BYTES                 NUMBER,
        REFERENCES             DAO.COLLECT
    /

• Inventors
  Pereira, Hilton M.;
• Assignee
  Computer Associates Think, Inc. (Islandia, NY)
• Published
  Jun-24-2003
• Current US Classes:
  707/104.1
  707/200
• Application #
  496975
• International Classes
  G06F 017/30
• Field of Search
  707/200 707/3 707/103 707/104 709/224
• Examiner
  Shah; Sanjiv
• Agent
  Calfee, Halter & Griswold LLP
• US Patent References:
  4506326
  5261093
  5806060
  5864856
  5873091
  5974407
  6081809
  6094650
  6112238
  6122640
  6185555