Service level goal processing for multi-environment data engine requests

Patent metadata

Field	Value
Publication number	US-12001430-B2
Application number	US-201916296732-A
Country	US
Kind code	B2
Filing date	Mar 8, 2019
Priority date	Dec 28, 2018
Publication date	Jun 4, 2024
Grant date	Jun 4, 2024

Abstract

A data engine request is received on a local data system. The data engine request includes a portion of the request that is to be processed on an external data engine system. The portion is forwarded to the external data engine system and statistics for accessing external objects of the external data engine system is acquired. The statistics are evaluated for compliance with a Service Level Goal (SLG) associated with the request. Rules-based processing permits optimization and planning of the request on the local data engine system to be modified in view of the statistics received from the external data engine system to comply with the SLG. In an embodiment, actual resource utilization metrics noted during execution of the portion on the external data engine system is provided as feedback to the local data engine system for re-planning and re-optimizing the request with a modified execution plan.

First claim

The invention claimed is:

1. A method, comprising:
receiving, on a local data engine system, a data engine request, the local data engine system comprising a local database management system (DBMS);
identifying, on the local data engine system, a portion of the data engine request that is to be processed by an external data engine system, the external data engine system comprising an external DBMS different from the local DBMS, and a local portion of the data engine request that is to be processed by the local data engine system;
obtaining, on the local data engine system, external statistics from an external resource of the external data engine system to be used in fulfilling the portion of the data engine request that is to be processed by the external data engine system;
evaluating, on the local data engine system, the external statistics from the external resource of the external data engine system for compliance with Contract Negotiated Service Level Goal (CNSLG) classification criteria of a Service Level Goal (SLG) associated with the data engine request;
determining whether the SLG can be satisfied using the external data source based on the CNSLG classification criteria, wherein the CNSLG classification criteria provides automated actions that can be taken based on the external statistics from the external resource of the external data engine system;
when the CNSLG classification criteria determines that the SLG cannot be satisfied using the external data source:
providing a local database administrator (DBA) with automated alternative processing for the data engine request;

when the CNSLG classification criteria determines that the SLG can be satisfied using the external data source:
modifying, on the local data engine system, an optimization and an execution plan for processing the portion of the data engine request that is to be processed by an external data engine system and the local portion of the data engine request that is to be processed by the local data engine system, based on the automated actions of the CNSLG classification criteria associated with the data engine request;
obtaining, on the local data engine system, actual resource utilization metrics from the external data engine system for the portion of the data engine request that was processed by the external data engine system;
evaluating, on the local data engine system, the actual resource utilization metrics from the external data engine system for compliance with the SLG associated with the data engine request; and
performing, on the local data engine system, further adjustments of the modified optimization and the modified execution plan, or a determination that the SLG associated with the data engine request cannot be satisfied, based on the evaluation of the actual resource utilization metrics.

2. The method of claim 1 , wherein receiving further includes receiving the data engine request as a data engine query.

3. The method of claim 2 , wherein identifying further includes identifying the portion that is to be processed by the external data engine system as access to a remote function located on the external data engine system.

4. The method of claim 3 , wherein identifying further includes identifying an Application Programming Interface (API) for accessing the remote function and the external data engine system from an external query grid mapping.

5. The method of claim 4 , wherein obtaining further includes obtaining the external statistics as a total number of rows in the remote function and a data size of the rows in the remote function.

6. The method of claim 4 , wherein obtaining further includes obtaining the external statistics as a histogram for column groupings associated with the remote function.

7. The method of claim 1 , wherein modifying further includes processing the automated actions to create the modified optimization and the modified execution plan based on the external statistics and the SLG.

8. The method of claim 7 , wherein processing further includes evaluating rules to select the automated actions.

9. The method of claim 1 further comprising, notifying a local data engine administrator when the external statistics or the actual resource utilization metrics are unable to satisfy the CNSLG classification criteria or SLG for the data engine request.

10. A method comprising:
obtaining, on a local database management system, external system costs from an external database management system different from the local database management system to fulfill a portion of a query that is to be processed by the external database management system;
evaluating, on the local database management system, the external system costs from the external database management system for compliance with a Contract Negotiated Service Level Goal (CNSLG) classification criteria of a Service Level Goal (SLG) associated with the query;
determining whether the SLG can be satisfied using the external database management system based on the CNSLG classification criteria, wherein the CNSLG classification criteria provides automated actions that can be taken based on the external system costs from the external database management system;
when the CNSLG classification criteria determines that the SLG cannot be satisfied using the external database management system:
providing a local database administrator (DBA) with automated alternative processing for the query;

when the CNSLG classification criteria determines that the SLG can be satisfied using the external database management system:
altering a query execution plan for the query on the local database management system based on the external system costs obtained from the external database management system to fulfill the portion of the query that is to be processed on the external database management system and the CNSLG classification criteria of the SLG for the query, wherein the altering further includes altering both the portion of the query to be processed on the external database management system and a local portion of the query to be processed on the local database management system;
obtaining actual resource utilization metrics from the external database management system when the portion of the query is processed on the external database management system;
evaluating the actual resource utilization metrics as the external system costs for compliance with the SLG for the query; and
adjusting the query execution plan for the query based on the evaluation of the actual resource utilization metrics and the SLG.

11. The method of claim 10 , wherein altering further includes altering the query execution plan by evaluating rules and applying the automated actions to a local optimization associated with the local portion of the query processed on the local database management system.

12. The method of claim 11 , wherein applying the automated actions comprises selecting at least one of the automated actions that alters a local resource priority associated with processing the local portion or that changes the local resource to a different local resource.

13. The method of claim 10 , wherein altering further includes determining the external system costs based on metadata statistics obtained from the external database management system that estimates a cardinality associated with processing the portion on the external database management system.

14. The method of claim 13 , wherein determining further includes estimating the cardinality based on a histogram for indices associated with a table used by the external database management system when processing the portion on the external database

Assignees

• Teradata Corp
• Teradata Us Inc

Inventors

• Brown Douglas Paul
• Mcintire Michael Sean
• Agarwal Prama

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F9/547 Primary

  Remote procedure calls [RPC]; Web services · CPC title

• G06F2209/549

  Remote execution · CPC title