The Danger of Hardcoded SQL Hints in PeopleSoft & Ellucian Banner Codebases

Introduction: Why Developers Add Hardcoded SQL Hints

When an ERP batch process (such as PeopleSoft Payroll calculation or Ellucian Banner Student Registration) experiences a sudden slowdown, developers often patch the query by embedding a hardcoded optimizer hint:

-- Legacy Developer Patch
SELECT /*+ INDEX(A PS_JOB_RUN_CNTL) FULL(B) */ 
    A.EMPLID, B.DEPTID 
FROM PS_JOB A, PS_DEPT_TBL B 
WHERE A.JOB_REQ_ID = B.JOB_REQ_ID;

While hints provide a quick quick-fix in the moment, they create permanent technical debt.

---

3 Reasons Hardcoded Hints Break ERP Applications

1. Plan Freezing Across Database Version Upgrades

• The Problem: A hint designed for Oracle 11g forces Oracle 19c or 23c to use obsolete join methods (e.g., forcing a Nested Loop join when a Hash Join is 100x faster).

2. Inability to Adapt to Data Scale Growth

• The Problem: A /*+ INDEX */ hint forced when a table has 10,000 rows becomes a disaster when the table grows to 10,000,000 rows.

3. Application Code Pollution & Loss of Maintainability

• The Problem: Modifying vendor SQR, COBOL, or PL/SQL packages requires re-applying custom patches during every PeopleTools or Banner application release upgrade.

---

🔍 Check Your Environment Now (Diagnostic CTA)

Run the following SQL query against your database cursor cache to identify all active application queries that contain hardcoded optimizer hints:

-- Diagnostic: Search Cursor Cache for Hardcoded Application SQL Hints
SELECT 
    sql_id,
    parsing_schema_name,
    executions,
    elapsed_time / 1000000 AS total_elapsed_sec,
    sql_text
FROM v$sql
WHERE (sql_text LIKE '%/*+%' OR sql_text LIKE '%--+%' )
  AND parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'AUDSYS')
ORDER BY elapsed_time DESC;

What to Look For:

• Review queries returning from application schemas (SYSADM, SATURN, BANINST1).
• Look for hardcoded /*+ FULL(...) */, /*+ INDEX(...) */, or /*+ FIRST_ROWS */ hints.

---

The Correct Modern Fix: SQL Plan Baselines (DBMS_SPM)

Instead of modifying ERP application source code, pin the optimal execution plan at the database layer using Oracle SQL Plan Management:

-- Create SQL Plan Baseline without modifying application code
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '7f3k8m9pq2z1a',
    plan_hash_value => 3819204912,
    fixed           => 'YES'
  );
END;
/

Need assistance auditing custom SQL hints across PeopleSoft or Banner? Schedule an Async ERP Database Health Audit.