Database performance analysis

• US 9,235,608 B2
• Filed: 03/13/2013
• Issued: 01/12/2016
• Est. Priority Date: 04/04/2012
• Status: Active Grant

• Current Assignee: Accenture Global Services Limited (Accenture PLC)
• Original Assignee: Accenture Global Services Limited (Accenture PLC)
• Inventors: Ferrigno, Francesco, Papili, Marina, Giustra, Domenico Antonio, Secco, Benedetto, Vassallo, Nico
• Primary Examiner(s): Yen, Syling
• Application Number:
  US13/800,660
• Publication Number:
  US 20130275372A1
• Time in Patent Office: 1,035 Days
• Field of Search: 707/688, 707/689, 707/690, 707/692, 707/693, 707/694, 707/696, 707/700, 707/701, 707/702, 707/704, 707/999.001, 717/127, 717/130
• US Class Current: 1/1
• CPC Class Codes: G06F 11/323 Visualisation of programs or trace data
• CPC Class Codes: G06F 11/3419 by assessing time
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 9/466 Transaction processing

Abstract

Stored duration information may be queried to determine duration of an action including a database operation. The querying may include specifying a subset of attributes of one or more relations. The relations may include a table with rows. The attributes may describe a subset of duration information including a start time of the operation. The querying may further include specifying conditions to be met, and retrieving, according to the conditions, the specified attributes of at least one row of the relations. The querying may include calculating a duration of the action based on the start time of the action.

Claims

1. A computer-implemented method for calculating a duration of processing an order, wherein the order comprises actions comprising at least one database operation,

wherein processing the order includes executing applications, each application of the applications having a corresponding application database of a plurality of application databases,

wherein executing the applications includes performing the actions on the corresponding application database and storing duration information for the actions,

wherein each application database has at least one data type and at least one key that differ from a data type and a key of any other of the application databases, the method comprising:

determining a primary order identifier of the actions;

deriving a secondary order identifier by querying a mapping table using the primary order identifier, wherein at least one of the following applies;

the primary order identifier is a key of at least one of the application databases;

the secondary order identifier is a key of at least one of the application databases; and

the primary order identifier is a key of only one of the application databases;

querying the stored duration information, the querying comprising;

specifying a subset of attributes of relations, the relations including at least one table, each of the relations including a plurality of rows, the subset of attributes describing;

the primary order identifier and the secondary order identifier, and

a subset of the stored duration information including a start time of the actions;

specifying conditions that must be met by at least one row of the relations; and

retrieving, according to the conditions, the specified subset of attributes of the at least one row of the relations;

determining, for each of the applications associated with the primary order identifier, which of the actions are performed during execution of each application;

calculating a duration of application execution for each application associated with the primary order identifier by at least one of;

identifying an earliest action of the determined actions and a latest action of the determined actions, and subtracting an earliest timestamp of the identified earliest action from a latest timestamp of the identified latest action; and

summing durations of the determined actions performed during execution of each application associated with the primary order identifier;

determining, for each of the applications associated with the secondary order identifier, which of the actions are performed during execution of the application;

calculating a duration of application execution for each application associated with the secondary order identifier; and

calculating the duration of the order by adding, across all of the applications associated with the primary and secondary orders, the durations of the actions associated with the primary order identifier and the durations of the actions associated with the secondary order identifier.

2. The method of claim 1, wherein determining the primary order identifier comprises either

receiving the primary order identifier;

or

receiving an account identifier, and

determining the primary order identifier based on the account identifier.

3. The method of claim 1, further comprising:

determining an average order duration by summing the durations of all orders in the application databases; and

determining an average duration for an application executed during processing of all the orders by summing the durations of the actions performed during the execution of the application.

4. The method of claim 3, comprising:

determining whether to carry out a performance improvement based on the average order duration; and

in response to determining to carry out the performance improvement;

identifying an application for the performance improvement based on the average execution duration of each application; and

identifying an action of the actions for the performance improvement based on the average execution duration of each application and/or the durations of the actions.

5. The method of claim 4, wherein the performance improvement comprises reducing number of database operations performed by the identified action.

6. The method of claim 1, wherein the subset of attributes further describe a key of the corresponding application database.

7. The method of claim 1, wherein each application database has a schema that differs from any other application database schema.

8. The method of claim 4, further comprising:

identifying each action invoked multiple times;

calculating a group duration of each identified action by aggregating invocations of the identified action and summing durations of the invocations; and

displaying each identified action followed by the group duration of the action.

9. A non-transitory computer readable program comprising instructions, which, when executed by a computer, cause the computer to perform the method of claim 1.

10. A computer system, comprising:

a hardware processor;

application databases, each application database of the application databases to store duration information and to receive at least one query from the hardware processor, wherein each application database has at least one data type and at least one key that differ from a data type and a key of any other of the application databases; and

a storage device comprising instructions that when executed by the hardware processor causes the hardware processor to;

calculate a duration of processing a requested order comprising actions, wherein the actions include executing applications, each application corresponding to the at least one application database, wherein to calculate a duration of each of the actions, the instructions are to cause the hardware processor to;

determine a primary order identifier of the actions;

derive a secondary order identifier by querying a mapping table using the primary order identifier, wherein at least one of the following applies;

the primary order identifier is a key of at least one of the application databases;

the secondary order identifier is a key of at least one of the application databases; and

the primary order identifier is a key of only one of the application databases;

query the stored duration information, wherein to query the stored duration information, the hardware processor is to;

specify a subset of attributes of relations, the relations including at least one table, each relation of the relations including a plurality of rows, the attributes describing;

the primary order identifier and the secondary order identifier, and

a subset of the duration information including a start time of the actions;

specify conditions to be met by at least one row of the plurality of rows of the relations;

retrieve, according to the conditions, the specified subset of attributes of the at least one row of the relations;

determine, for each of the applications associated with the primary order identifier, which of the actions are performed during execution of each application;

calculate a duration of application execution for each application associated with the primary order identifier by at least one of;

identifying an earliest action of the determined actions and a latest action of the determined actions, and subtracting an earliest timestamp of the identified earliest action from a latest timestamp of the identified latest action; and

summing durations of the determined actions performed during execution of each application associated with the primary order identifier;

determine, for each of the applications associated with the secondary order identifier, which of the actions are performed during execution of the application;

calculate a duration of application execution for each application associated with the secondary order identifier; and

calculate the duration of the order by adding, across all of the applications associated with the primary and secondary orders, the durations of the actions associated with the primary order identifier and the durations of the actions associated with the secondary order identifier.

11. The computer system of claim 10, wherein the hardware processor is to:

determine an average order duration of the applications by summing the durations of all orders in the application databases; and

determine an average duration that an application of the applications was executed during processing of all the orders by summing the durations of the actions performed during the execution of the application.

12. The computer system of claim 11, wherein the hardware processor is to:

determine whether to carry out a performance improvement based on the average order duration; and

in response to a determination to carry out the performance improvement;

identify an application of the applications for the performance improvement based on the average duration that each application was executed; and

identify an action for the performance improvement based on at least one of the average duration that each application was executed and the durations of the actions.

13. The computer system of claim 12, wherein the performance improvement comprises reducing number of database operations performed by the identified action.