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 23ai 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;
/📚 Official Documentation & Technical References
- Oracle Database Database Performance Tuning Guide 19c - Optimizer Hints — Official documentation on optimizer hints and why hardcoding hints in application code is discouraged.
- Oracle Database Database Performance Tuning Guide 19c - Managing SQL Plan Baselines — Technical guide on fixing execution plans without modifying SQL text.
- ORACLE-BASE: SQL Plan Management (SPM) in Oracle Database — Tim Hall’s tutorial on managing execution plans dynamically.
- Jonathan Lewis: Optimizer Hints - Myths and Realities — Jonathan Lewis’s analysis of optimizer hints, side effects, and maintenance overhead in upgraded database releases.
Need assistance auditing custom SQL hints across PeopleSoft or Banner? Schedule an Async ERP Database Health Audit.