Oracle Database Discussions

The application I support uses temporary tables for the majority of queries.. In looking at the SQL tuning advisor, and most other oracle tuning tools, they don't seem to handle temporary tables well.

This is what Happens (from what I can tell).

1) oracle identifies a SQL statement (or the DBA does), to be tuned.
2) The original runtime statistics are saved for comparison
3) Oracle tries to find a new plan (but at this point the temporary table is empty).
4) It tries to execute the new plan (again the temporary table is empty).
5) It comparies the execution time of the new plan against the original plan

The comparison , of course , is not a reasonable comparison.

Does anyone have any experience with this issue ?? My first thought is to "prime" the temporary table, and then execute the tuning advisor in the same session (hoping it will use the values in the temporary table).

other than that, Database Replay seems to be the best way to tune sql plan changes using baselines.