How does the STA work in 11gR2 with the query from “Oracle’s SQL Tuning Pack – part II″ ?
In Part II, the STA in 10g proposed a new profile for the query but that profile actually caused the query to run slower. Quetion is, in 11gR2 does the STA do better?
Below I ran the query load, identied the query, submitted to STA and STA spent 30 minutes burning CPU trying to tune the query and finally ended with an error that a better plan could not be found.
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. Yet in DB Optimizer a new plan is found in minutes that runs faster than the original. So a better plan was out there, relatively easy to get yet STA in 11gR2 could’t find it in a half an hour. I guess that’s better though than suggesting a worse profile as it did in 10gR2.
Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:
All opinions expressed here are my own and do not represent those of my employer.
If you'd like to find out more detail about my work at Amazon check out my blog postings at https://aws.amazon.com/blogs/aws/