Method and apparatus for fast and comprehensive DBMS analysis

• US 6,584,474 B1
• Filed: 02/02/2000
• Issued: 06/24/2003
• Est. Priority Date: 08/31/1998
• Status: Expired due to Term

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Pereira, Hilton M.
• Primary Examiner(s): Shah, Sanjiv
• Application Number:
  US09/496,975
• Time in Patent Office: 1,238 Days
• Field of Search: 707/200, 707/3, 707/103, 707/104, 709/224
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

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

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.