How do you do the Toad SQL Optimizer for Oracle at the highest depth? I was thinking this would work. I did this on two similar queries. There are a couple of small differences. I compared the data that both queries generated and the data was identical. One query ran for 6 hours and the other ran for 8 hours. Neither of these generated a single alternative. Both said they checked 24500 alternatives.
In Toad for Oracle, the Auto Optimize SQL → Search depth slider is essentially a front-end for the SQL Optimizer “Intelligence Level”.
-
Low search depth ≈ lower Intelligence Level (fewer hint types/permutations, lower quotas) → faster, fewer SQLs
-
Max search depth ≈ highest Intelligence Level (most hint types/permutations, highest quotas) → slowest, most SQLs
If you open the Optimizer itself, the closest “ground truth” for what each level is doing is the Options / Hints Quota area: it shows which hint families are enabled at each level and how many permutations (quota) it will attempt. That’s why you can see a large, explored count (like ~24,500) at high depth.
Why you can see “checked 24,000+ alternatives” but get zero alternatives / no “better SQL”
“Alternatives checked” means the Optimizer generated and considered many SQL variants (hinted plan permutations, rewrites, join/order variations, etc.). But a SQL only becomes a recommended alternate if it survives the tool’s validation rules.
-
No improvement found: none of the SQLs were faster/lower cost than the original.
-
Mode/settings effect: if you’re in a rewrite-only / do-not-execute style run, many SQL's can be generated but fewer can be promoted because runtime benefit isn’t confirmed.
-
Execution/validation filtered them out: SQL's may be dropped due to errors/timeouts/locks or termination rules during benchmarking.
-
Tiny SQL differences can still change plans (bind handling, join order, adaptive choices), so two “same result” queries can still run 6 vs 8 hours.
2 Likes