Multi-user database system and method for resource usage tracking

• US 20050010572A1
• Filed: 07/11/2003
• Published: 01/13/2005
• Est. Priority Date: 07/11/2003
• Status: Active Grant

• Current Assignee: AT&T Intellectual Property I LP (AT&T, Inc.)
• Original Assignee: SBC Knowledge Ventures LP (AT&T, Inc.)
• Inventors: Clark, Mary B., Stanek, Thomas F., Lancour, Phillip E., Bal, Satwant S.
• Granted Patent: US 7,526,521 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

The disclosure is directed to a multi-user database system. The multi-user database system includes at least one processor, at least one network interface coupled to the processor, an event table, an accounting table, and a session table. The network interface is configured to receive transactions from a plurality of users. The transactions include session maintenance transactions and data requests. The event table stores a data log of the session maintenance transactions. The accounting table stores data associated with the data requests. The session table is derived from the event table and the accounting table. The session table stores resource usage data associated with at least one user session.

Claims

1. A multi-user database system comprising:

at least one processor;

at least one network interface coupled to the at least one processor, the at least one network interface configured to receive transactions from a plurality of users, the transactions including session maintenance transactions and data requests;

an event table to store an event log of the session maintenance transactions;

an accounting table to store data associated with the data requests; and

a session table derived from the event table and the accounting table, the session table to store resource usage data associated with at least one user session.

2. The multi-user database system of claim 1, wherein the resource usage data includes CPU usage.

3. The multi-user database system of claim 1, wherein the resource usage data includes input/output usage.

4. The multi-user database system of claim 1, wherein the at least one processor comprises more than one processor in a parallel processing environment.

5. The multi-user database system of claim 4, wherein the parallel processing environment is associated with an enterprise data warehouse.

6. The multi-user database system of claim 1, further comprising:

a request table derived from the event table and the accounting table, the request table to store resource usage data associated with the data requests.

7. The multi-user database system of claim 6, wherein the request table is accessible to identify data requests that utilize a selected level of computing resources.

8. The multi-user database system of claim 1, wherein the session table is accessible to identify sessions that utilize a selected level of computing resources.

9. The multi-user database system of claim 1, wherein the session table is accessible to identify usage trends for resource utilization forecasting.

10. A multi-user database system comprising:

a processor;

a network interface coupled to the processor, the network interface configured to receive transactions from a plurality of users, the transactions including session maintenance transactions and data requests;

an event table to store an event log of the session maintenance transactions;

an accounting table to store data associated with the data requests; and

a request table derived from the event table and the accounting table, the request table to store resource usage data associated with the transactions.

11. The multi-user database system of claim 10, wherein the resource usage data includes CPU usage.

12. The multi-user database system of claim 10, wherein the resource usage data includes input/output usage.

13. The multi-user database system of claim 10, wherein the request table is accessible to identify data requests that utilize a selected level of computing resources.

14. The multi-user database system of claim 10, further comprising more than one processor in a parallel processing environment.

15. The multi-user database system of claim 14, wherein the parallel processing environment is associated with an enterprise data warehouse.

16. The multi-user database system of claim 10, further comprising:

a session table derived from the event table and the accounting table, the session table to store resource usage data associated with at least one user session.

17. The multi-user database system of claim 16, wherein the session table is accessible to identify high resource utilization sessions.

18. The multi-user database system of claim 16, wherein the session table is accessible to identify usage trends for resource utilization forecasting.

19. A method of tracking database system usage, the method comprising:

determining a set of new sessions from an event log data table to form a temporary session data table;

matching entries in the temporary sessions data table with a set of request transactions to form a matched data table;

preparing a sessions level summary from the matched data table;

updating a session table, the session table to store resource usage data associated with the set of new sessions; and

querying the sessions table to track database system usage.

20. The method of claim 19, wherein the resource usage data includes CPU usage.

21. The method of claim 19, wherein the resource usage data includes input/output usage.

22. The method of claim 19, further comprising:

determining a set of open sessions; and

associating the set of open sessions with logoff events stored in the event log data table.

23. The method of claim 19, further comprising:

determining a set of open sessions;

associating running sessions with open sessions in the set of open sessions; and

closing open sessions not associated with running sessions.

24. The method of claim 19, further comprising:

preparing a request level summary from the matched data table;

updating a request table, the request table to store resource usage data associated with the set of request transactions; and

querying the request table to track resource usage.

25. The method of claim 24, wherein querying the request table includes providing data associated with resource inefficient transaction requests.

26. The method of claim 25, further comprising:

modifying the resource inefficient transaction requests whereby database performance is enhanced.

27. The method of claim 19, wherein querying the sessions table yields data associated with usage trends.

28. The method of claim 27, further comprising:

allocating database resources based on the data associated with usage trends.

29. The method of claim 19, wherein matching entries in the temporary session data table is performed using a user identifier and a session identifier.

30. The method of claim 19, wherein matching entries in the temporary session data table is performed using a user identifier and an account string.