Oracle 12cR2含Cross Join的查询性能进阶优化问询及执行计划一致性下的性能差异疑问

Hi Roberto, great job already making significant progress on optimizing that query—cutting runtime from 23.7s down to 7.93s is solid progress! Let's tackle your two questions based on the details you shared:

Environment Recap #

• Database: Oracle Database 12cR2
• Host: Red Hat 7 Linux VM with 8 CPUs, 32GB RAM
• Stats: Up-to-date via AUTO SAMPLE and FOR ALL COLUMNS SIZE AUTO
• Optimizer: Default 12.2 parameters

Your Optimization Journey So Far #

• Initial state: Third-party query using CROSS JOIN to force a HASH JOIN, full table scans, runtime 23.70s
• First tweak: Created two invisible indexes:
  • alfats.FloatingRateRollover_PRF1: Covers adjustmentProcessedIndicator, agreementNumber, scheduleNumber, terminationNumber, id
  • alfats.FloatingRateInterestCalendar_PRF2: Covers floatingRateRolloverId, processingDate
  • Result: Runtime dropped to 10.95s, all tables use INDEX FAST FULL SCAN
• Second tweak: Rewrote some CROSS JOIN to INNER JOIN (with ON clause)
  • Result: Runtime fell to 7.93s, but execution plan hash, cost, access paths, and predicates matched the prior version

Answers to Your Questions #

1. Are there simpler ways to further optimize this query? #

Absolutely—here are a few low-effort, high-impact checks you can run:

• Validate invisible index utility: Even though you're using INDEX FAST FULL SCAN, consider testing if making these indexes visible changes anything. Invisible indexes can sometimes have subtle cost differences; making them visible might let the optimizer refine the plan further. Just remember to test in a non-production environment first!
• Check index compression: Since you're using index full scans, enabling index compression (either basic or advanced) can reduce I/O and memory usage. Run ALTER INDEX alfats.FloatingRateRollover_PRF1 COMPRESS ADVANCED; (and the same for the other index) and re-run the query to see if runtime drops.
• Verify PGA allocation: Hash joins rely heavily on PGA memory. If your pga_aggregate_target is set too low, the join might spill to disk, slowing things down. Check the execution plan's runtime stats (look for temp space used in DBMS_XPLAN.DISPLAY_CURSOR)—if you see significant temp usage, bumping pga_aggregate_target (within reason for your 32GB RAM) could help.
• Double-check predicate filtering: Even though you have indexes, make sure all filter predicates are being applied as early as possible. Run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')); to see the actual number of rows processed at each step—if a step is handling more rows than expected, there might be a predicate that's not being pushed down properly.

2. Why does the INNER JOIN (ON syntax) perform better with an identical execution plan? #

This is a tricky one, but there are a few likely explanations:

• Runtime predicate application timing: Even if the execution plan looks identical, the ON clause in an INNER JOIN might cause the optimizer to apply filter predicates earlier in the execution flow (before the hash join) compared to a CROSS JOIN where predicates might be applied after the join. This reduces the number of rows fed into the hash join, cutting down on memory usage and processing time—even if the plan doesn't explicitly show this difference.
• Cache behavior: While you might have run tests back-to-back, it's possible that the first run (with CROSS JOIN) primed the buffer cache, but the INNER JOIN run benefited from more efficient cache utilization. To rule this out, flush the buffer cache (again, in non-prod!) and re-run both queries in reverse order to see if the performance swap persists.
• Subtle execution plan differences: Execution plan hashes can be identical even if there are minor under-the-hood differences (like parallel execution thread allocation, or the order in which rows are processed). Use DBMS_XPLAN.DISPLAY_CURSOR with the ADVANCED option to look for hidden details—you might see differences in Predicate Information (even if it looks similar at first glance) or Runtime Statistics that explain the performance gap.
• Optimizer "hidden" logic: Oracle's CBO sometimes treats CROSS JOIN syntax differently internally, even if it generates the same plan hash. The INNER JOIN with ON clause might signal to the optimizer that the join is more "intentional," leading to minor adjustments in how it allocates resources or processes data during runtime.

内容的提问来源于stack exchange，提问作者Roberto Hernandez