How does the STA work in 11gR2 with the query from “Oracle’s SQL Tuning Pack – part 2” ?
Here is the view on the Top Activity Page:
The first spike is when I ran the query. I pass the gray window over the spike and selected the first query and submitted it to the tuner. The rest of the load, the next 30 minutes is the STA running trying to find a better plan. After 30 minutes the STA times out saying not better plan found.
On 10gR2 the STA finishes in less than 2 minutes and finds a profile though the profile puts a higher load on the database for this query. On 11gR2, the STA runs for 30 and times out without finding any suggestions.
Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:
Comments