CMN_AUDITS_TSV needs index on AUDIT_ID and COLUMN_NAME

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Performance issue is observed on the below audit statement:

select value from cmn_audits_tsv where audit_id = :1 and column_name = :2

STEPS TO REPRODUCE:

1. Set up multiple TSV fields for Audit: prestsum, prestcurve, avail_curve, and allocation_curve
2. On a busy PROD environment, have hundreds of users update the TSV fields

Expected Results: Audit to perform fast

Actual Results: Audit creates bottlenecks due to the statement select value from cmn_audits_tsv where audit_id = :1 and column_name = :2

Environment

Clarity 16.3.3, 16.4.0, 16.4.1

Cause

DE189230

Resolution

Fixed in 16.4.3

Workaround: Create an index:

CREATE INDEX Z_CMN_AUDITS_TSV ON CMN_AUDITS_TSV (AUDIT_ID, COLUMN_NAME);

Remember to remove this index before upgrade to the latest release