Multi-user database system and method for resource usage tracking
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.
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;
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, wherein the resource usage data includes processor usage of the at least one processor and an input/output usage; and
a request table derived from the event table and the accounting table, to store resource usage data associated with the data requests, wherein the request table is accessible to identify data requests that utilize a selected level of computing resources.
2. The multi-user database system of claim 1 , wherein the at least one processor is configured to determine from the session table a historical trend of the processor usage of the at least one user session.
3. The multi-user database system of claim 1 , wherein the at least one processor is configured to determine from the session table a historical trend of the input/output usage of the at least one user session.
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 , wherein the session table is accessible to identify sessions that utilize a selected level of computing resources.
7. The multi-user database system of claim 1 , wherein the session table is accessible to identify usage trends for resource utilization forecasting.
8. 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;
a request table derived from the event table and the accounting table, the request table to store resource usage data associated with the transactions, wherein the resource usage data includes at least one of a processor usage and an input/output usage; and
a session table derived from the event table and the accounting table, to store resource usage data associated with at least one user session, wherein the session table is accessible to identify high resource utilization sessions and usage trends for resource utilization forecasting.
9. The multi-user database system of claim 8 , wherein the processor is configured to determine from the request table a historical trend of the processor usage of at least one user session.
10. The multi-user database system of claim 8 , wherein the processor is configured to determine from the request table a historical trend of the input/output usage of at least one user session.
11. The multi-user database system of claim 8 , wherein the request table is accessible to identify data requests that utilize a selected level of computing resources.
12. The multi-user database system of claim 8 , further comprising more than one processor in a parallel processing environment.
13. The multi-user database system of claim 12 , wherein the parallel processing environment is associated with an enterprise data warehouse.
14. 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 sessions table, the sessions table to store resource usage data associated with the set of new sessions, wherein the resource usage includes at least one of a usage of a processor and an input/output usage;
querying the sessions table to track database system usage;
preparing a request level summary from the matched data table;
updating a request table to store resource usage data associated with the set of request transactions; and
querying the request table to track resource usage, wherein querying the request table comprises providing data associated with resource inefficient transaction requests and modifying the resource inefficient transaction requests to enhance database performance.
15. The method of claim 14 , wherein the processor is configured to determine from the sessions table a historical trend of the processor usage of at least one user session.
16. The method of claim 14 , wherein the processor is configured to determine from the sessions table a historical trend of the input/output usage of at least one user session.
17. The method of claim 14 , 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.
18. The method of claim 14 , 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.
19. The method of claim 14 , wherein querying the sessions table yields data associated with usage trends.
20. The method of claim 19 , further comprising:
allocating database resources based on the data associated with usage trends.
21. The method of claim 14 , wherein matching entries in the temporary session data table is performed using a user identifier and a session identifier.
22. The method of claim 14 , wherein the matching entries in the temporary session data table is performed using a user identifier and an account string.