Oracle Review
Review Oracle for explain plans, PL/SQL, partitioning, RAC, AWR/ASH, and licensing implications.
Key Checks
- DBMS_XPLAN.DISPLAY_CURSOR for actual plans — flag FULL TABLE SCAN on large tables
- Range/hash/list/composite partitioning with partition pruning verified in plan
- PL/SQL: FORALL over row-by-row DML, BULK COLLECT with LIMIT, bind variables (no EXECUTE IMMEDIATE with string concat)
- RAC: cache contention waits (gc buffer busy), sequence CACHE+NOORDER, SCAN listener
- AWR Top 5 events, ASH blocking sessions, SQL ordered by Elapsed Time
- SQL Plan Baselines over optimizer hints, hints documented when necessary
- Bind variables verified via V$SQL VERSION_COUNT
- Materialized views: REFRESH FAST eligibility, QUERY REWRITE enabled
- Licensing audit: flag Enterprise-only features (partitioning, RAC, Advanced Compression, TDE)
- SYS/SYSTEM not used by app, TDE for PII tablespaces, AUDIT policies active
Output
Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.
1---2name: oracle-review3description: Review Oracle Database schemas, queries, PL/SQL, and configuration for performance and production readiness4agent: database-reviewer5argument-hint: "<file-or-query-or-schema>"6tags: [database, oracle, sql, plsql, performance, indexing]7user-invocable: true8---910# Oracle Review1112Review Oracle for explain plans, PL/SQL, partitioning, RAC, AWR/ASH, and licensing implications.1314## Key Checks15- DBMS_XPLAN.DISPLAY_CURSOR for actual plans — flag FULL TABLE SCAN on large tables16- Range/hash/list/composite partitioning with partition pruning verified in plan17- PL/SQL: FORALL over row-by-row DML, BULK COLLECT with LIMIT, bind variables (no EXECUTE IMMEDIATE with string concat)18- RAC: cache contention waits (gc buffer busy), sequence CACHE+NOORDER, SCAN listener19- AWR Top 5 events, ASH blocking sessions, SQL ordered by Elapsed Time20- SQL Plan Baselines over optimizer hints, hints documented when necessary21- Bind variables verified via V$SQL VERSION_COUNT22- Materialized views: REFRESH FAST eligibility, QUERY REWRITE enabled23- Licensing audit: flag Enterprise-only features (partitioning, RAC, Advanced Compression, TDE)24- SYS/SYSTEM not used by app, TDE for PII tablespaces, AUDIT policies active2526## Output27Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.
ERP-CORE-DEV