Oracle 23ai Performance Tips for SQL Developers

As we close out the year, here’s a practical performance guide focused specifically on Oracle 23ai SQL features and how to get the most out of them.

GROUP BY ALL performance: GROUP BY ALL generates the same execution plan as an explicit GROUP BY list. There’s no performance penalty, the optimizer resolves the column list at parse time. Use it freely.

Vector index accuracy vs. speed tradeoff:

CREATE VECTOR INDEX docs_idx ON docs(embedding)
WITH TARGET ACCURACY 90;  -- faster, approximate results
-- vs.
WITH TARGET ACCURACY 99;  -- slower, more precise

For most RAG use cases, 90-95% accuracy is acceptable and significantly faster.

Schema privileges and parse overhead: Schema-level privileges (GRANT SELECT ANY TABLE ON SCHEMA) don’t affect query parse time. The privilege check at parse time is O(1) — same as object-level privileges.

Domains and constraint checking: Domain constraints are enforced at INSERT/UPDATE time, exactly like column-level CHECK constraints. There’s no additional overhead for using domains vs. inline constraints.

JSON Duality Views: Duality views over wide joins (5+ tables) can have higher write overhead because each JSON mutation must be decomposed into relational DML across multiple tables. Profile with realistic payloads before deploying to production.

MEMOPTIMIZE FOR WRITE throughput: To maximize Fast Ingest throughput, ensure the LARGE_POOL_SIZE is sufficient. Oracle recommends allocating at least 3x the expected peak buffer size:

ALTER SYSTEM SET LARGE_POOL_SIZE = 1G;

SQL Firewall learning period: Run the learning phase for at least 2 weeks to capture all application code paths (batch jobs, end-of-month processes, etc.) before enabling enforcement. An incomplete learning phase leads to false positives.