📋

Description

This error indicates a conflict when SAP S/4HANA attempts to store or update an SQL execution plan, specifically when an identical record for that plan already exists in the system's plan stability mechanism. The 'DEPRECATED' suffix suggests this issue might arise from using older or non-recommended plan stability functionalities.

💬

Error Message

ERR_SQL_PLAN_STABILITY_RECORD_ALREADY_EXISTS_DEPRECATED

🔍

Known Causes

3 known causes

⚠️

Duplicate Plan Storage Attempt

An operation attempted to save an SQL execution plan that already exists within the system's plan stability store.

⚠️

Concurrent Plan Creation

Multiple processes or users tried to create or store the same SQL plan record simultaneously, leading to a race condition and conflict.

⚠️

Legacy Plan Stability Mechanism

The error is triggered by the use of a deprecated or outdated SQL plan stability feature, which might not handle existing records as gracefully as newer methods.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate SQL Plan Stability Records medium

Manually remove redundant entries from the SQL plan stability table.

1

Connect to your SAP HANA database as a user with the necessary privileges (e.g., SYSTEM or a user with `_SYS_STATISTICS` schema access and `ALTER SYSTEM` privilege).

2

Query the `_SYS_STATISTICS`.`PLAN_STABILITY_RECORDS` table to identify duplicate entries. Look for records with the same `SQL_TEXT`, `SCHEMA_NAME`, and `PLAN_HASH` but potentially different `RECORD_ID`s or creation timestamps.

SELECT RECORD_ID, SQL_TEXT, SCHEMA_NAME, PLAN_HASH, CREATED_BY, CREATED_AT FROM "_SYS_STATISTICS"."PLAN_STABILITY_RECORDS" WHERE SQL_TEXT = '<problematic_sql_text>' AND SCHEMA_NAME = '<problematic_schema_name>' ORDER BY CREATED_AT;

3

Based on the query results, determine which records are duplicates. Typically, you'll want to keep the most recent or the one marked as the preferred plan if applicable. Then, delete the redundant records. **Caution:** This is a critical operation. Ensure you have a backup or are absolutely certain about the records you are deleting.

DELETE FROM "_SYS_STATISTICS"."PLAN_STABILITY_RECORDS" WHERE RECORD_ID = <record_id_to_delete>;

4

After deleting, re-execute the SQL statement that was causing the error. The error should now be resolved.

2. Clear All SQL Plan Stability Records easy

Remove all existing plan stability records to start fresh.

1

Connect to your SAP HANA database as a user with the `ALTER SYSTEM` privilege.

2

Execute the following SQL command to clear all records from the plan stability table. This will essentially reset the plan stability configuration.

DELETE FROM "_SYS_STATISTICS"."PLAN_STABILITY_RECORDS";

3

Re-execute the problematic SQL statement. The system will generate a new plan stability record for it.

3. Re-create the SQL Plan Stability Record medium

Explicitly define the desired SQL plan if the automatic creation is failing.

1

Identify the SQL statement that is causing the error. You might find this in the SAP HANA trace files or by monitoring SQL statements being executed.

2

Execute the problematic SQL statement and capture its execution plan using `EXPLAIN PLAN FOR <your_sql_statement>;` and then `GET EXPLAIN PLAN;`.

EXPLAIN PLAN FOR SELECT * FROM "MY_SCHEMA"."MY_TABLE" WHERE MY_COLUMN = 'some_value';
GET EXPLAIN PLAN;

3

Use the `ALTER SYSTEM ALTER CONFIGURATION` statement to explicitly set the desired plan for the SQL statement. This overrides any conflicting automatic entries.

ALTER SYSTEM ALTER CONFIGURATION ('plan_stability.ini', 'SYSTEM') SET ('plan_stability.ini', 'SYSTEM') = 'ADD "<schema_name>" "<sql_text>" "<plan_hash>"';
-- Example:
-- ALTER SYSTEM ALTER CONFIGURATION ('plan_stability.ini', 'SYSTEM') SET ('plan_stability.ini', 'SYSTEM') = 'ADD "MY_SCHEMA" "SELECT * FROM \"MY_TABLE\" WHERE MY_COLUMN = ''some_value''" "<captured_plan_hash>"';
-- Note: The SQL text needs to be properly escaped, and the plan_hash is crucial.

4

The `plan_hash` can be obtained from the `EXPLAIN PLAN` output. You will need to carefully construct the `ADD` statement with the correct SQL text and plan hash, ensuring proper escaping of quotes and special characters within the SQL text.

🔗

Related Errors

5 related errors

SAP S/4HANA Error 694: Duplicate SQL Plan Record