Database Code-Module Performance and Reliability Metrics Instrumentation

Patent metadata

Field	Value
Publication number	US-2016232200-A1
Application number	US-201615010234-A
Country	US
Kind code	A1
Filing date	Jan 29, 2016
Priority date	Feb 11, 2015
Publication date	Aug 11, 2016
Grant date	—

Abstract

Novel tools and techniques for instrumenting code-module execution within a database (or within a plurality of databases). In one aspect, various embodiments can instrument (e.g., measure and/or monitor) database applications, jobs, or other coded modules, execution steps, and/or any other type of executable code within (or even outside) of a database to accurately track execution and call lineage, timings, and/or errors within database code modules, long-running SQL statements, or other large database calls/callouts initiating from within one or more databases, across one or more platforms (e.g., Oracle, SQL Server, etc.). In another aspect, certain embodiments can perform such instrumentation through a variety of interfaces (intra-database calls, database links, linked servers, external application code, etc.).

First claim

What is claimed is:

1 . A method for measuring database code-module performance and reliability, the method comprising:
receiving, in a database management system, a call to a set of executable code within a database managed by the database management system; determining, in the database management system, that the set of executable code should be instrumented to measure performance of the set of executable code; invoking, with an executing instance of the set of executable code in the database management system, the measurement function, based at least in part on a determination that the set of executable code should be instrumented; collecting, with the measurement function, metric data about execution of the set of executable code, the metric data including a start time of the executing instance of the set of executable code; storing, with the measurement function, the metric data in a table in the database; associating, in the database, a unique identifier with the executing instance of the set of executable code and the metric data; returning, with the measurement function, an identifier string to the executing instance of the set of executable code, the identifier string corresponding to the unique identifier; passing, with the executing instance of the set of executable code, the identifier string to the measurement function at completion of execution of the set of executable code; adding, with the measurement function, a stop time to the metric data in the database, based on receipt of the identifier string from the executing instance of the set of executable code.

2 . The method of claim 1 , wherein the metric data is stored in a dedicated table in the database, the dedicated table being separate from any tables on which the executing instance of the set of executable code operates.

3 . The method of claim 2 , further comprising:
performing a roll-back operation in the database to revert transactions performed by the executing instance of the set of executable code, wherein the metric data persists in the dedicated table even after the roll-back operation has been performed.

4 . The method of claim 1 , further comprising:
asynchronously pushing the metric data and associated unique identifier to a repository server for further analysis.

5 . The method of claim 4 , further comprising:
analyzing the metric data and associated unique identifier in the repository server to identify an execution chain involving the executing instance of the set of executable code.

6 . The method of claim 5 , wherein the execution chain comprises executing instances of multiple sets of executable code, each set comprising different executable code.

7 . The method of claim 6 , wherein at least some of the multiple sets of executable code executes outside of the database.

8 . The method of claim 6 , further comprising:
analyzing the execution chain to identify performance issues relating to execution of at least some of the multiple sets of executable code.

9 . The method of claim 4 , further comprising:
asynchronously purging the metric data from the dedicated table.

10 . The method of claim 9 , wherein the metric data is purged without regard to a success or failure in pushing the metric data to the repository server.

11 . The method of claim 1 , wherein the measurement function employs autonomous transactions to store the metric data and stop time.

12 . The method of claim 1 , wherein the database management system does not support autonomous transactions, and wherein the measurement function simulates an autonomous transaction to store the metric data and stop time.

13 . The method of claim 12 , wherein simulating an autonomous transaction comprises:
creating a loopback linked server; disabling remote transaction promotion in the loopback linked server; creating a stored procedure to store the metric data and stop time; and calling the stored procedure.

14 . The method of claim 13 , wherein simulating an autonomous transaction further comprises:
autonomously determining whether the database management system is in an auto-commit mode; if the database management system is in an auto-commit mode, calling the stored procedure without using the loopback linked server; and if the database management system is not in an auto-commit mode, calling the stored procedure over the loopback linked server.

15 . The method of claim 14 , wherein autonomously determining whether the database management system is in an auto-commit mode comprises:
determining whether implicit transactions are enabled in the database management system; determining whether a current transaction count in the database management system is greater than zero; and based on a determination that implicit transactions are not enabled and the current transaction count is not greater than zero, determining that the database management system is in an auto-commit mode.

16 . The method of claim 1 , wherein the set of executable code is a first set of executable code, the metric data is first metric data, the unique identifier is a first unique identifier, and the identifier string is a first identifier string, the method further comprising:
prior to initiating a callout to a second set of executable code, passing the unique identifier from the executing instance of the first set of executable code to the measurement function; creating, with the measurement function, a second unique identifier, the second unique identifier being associated with execution of the second set of executable code and with the first unique identifier; storing, with the measurement function, second metric data in the database; associating, in the database, the second metric data with the second unique identifier; returning, with the measurement function, a second identifier string to the executing instance of the first set of executable code, the second identifier string corresponding to the second unique identifier; upon completion of execution of the second set of executable code, passing, with the executing instance of the first set of executable code, the second identifier string to the measurement function; and storing, with the measurement function, a second stop time with the second metric data in the database, based on receipt of the second identifier string from the executing instance of the first set of executable code.

17 . The method of claim 16 , further comprising:
initiating, with the executing instance of the first set of executable code, the callout to the second set of executable code; determining that the second set of executable code should be instrumented to measure performance of the second set of executable code; invoking, with an executing instance of the second set of executable code, the measurement function, based at least in part on a determination that the second set of executable code should be instrumented; collecting, with the measurement function, additional second metric data about execution of the second set of executable code; storing, with the measurement function, the additional second metric data in the database; and associating, in the database, the additional second metric data with the second unique identifier.

18 . The method of claim 16 , wherein the second set of executable code executes in a different database than the first set of executable code.

19 . The method of claim 16 , wherein the second set of executable code executes outside of any database.

20 . The m

Assignees

• Centurylink Ip Llc

Inventors

• Sherman Roby

Classifications

• G06F16/217

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• G06F16/2365

  Ensuring data consistency and integrity · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F17/30371

  Physics · mapped topic

• G06F17/30377 Primary

  Physics · mapped topic