Whilst I was away on holiday, the primary query in a particular batch process changed plan. An appropriate baseline was create for it but this was not used by the query. I was asked to look at it on my return. My first step was to generate a 10053 trace of the query and this showed that the baseline was being identified as being associated with this query:
... SPM: statement found in SMB ...
However it also showed that after applying the baseline hints the plan generated was not that associated with the baseline and so the baseline was discarded:
... SPM: planId in plan baseline = 4112141197, planId of reproduced plan = 1268833870 ------- START SPM Plan Dump ------- SPM: failed to reproduce the plan using the following info: <baseline hints>
The trace file does not tell you why the generated plan using the hints was not the same as that associated with the baseline, only that it was different.
In such a situation the baseline is completely ignored and in a previous similar situation I had used the hints in the baseline to produce a reasonable plan. I therefore did this, taking the hints from the 10053 trace, but the execution plan generated did not perform well. I then started looking for the reason that the baseline was not used. Previously I have found that this was because an object has changed and so I looked at DBA_OBJECTS and the last DDL timestamps. Importantly this showed that the views in the query had not changed for several weeks and I new that the last time the good plan was used was two days before the unused baseline was created. However it did show that a number of the objects had changed on the same day the good plan stopped being used. Further digging however suggested that this was due to partition addition, as all the relevant objects where partitioned. The product also stated that no DDL changes had been made since a release several weeks earlier.
After failing to determine the cause of the plan change and why the baseline was not being used I started looking at hinting the query to generate the good plan. It did not take long to determine a set of hints which generate the original good plan, however these hints were applied to an inner select and so could not be directly used in a SQL patch. Using the query block names in the 10053 trace file I tried to convert these hints into hints on the outermost select but did not succeed. Whilst investigating this using 10053 traces I noted the following type of statements at the bottom of the trace file:
Dumping Hints ============= atom_hint=(@=0x2aed25a214b8 err=0 resol=0 used=0 token=83 org=1 lvl=3 txt=INDEX (<object> <index>) ) atom_hint=(@=0x2aed25a225a8 err=0 resol=0 used=0 token=83 org=1 lvl=3 txt=INDEX (<object> <index>) ) atom_hint=(@=0x2aed25a234d8 err=0 resol=0 used=0 token=83 org=1 lvl=3 txt=INDEX (<object> <index>) ) atom_hint=(@=0x2aed25a21200 err=0 resol=0 used=0 token=83 org=1 lvl=3 txt=INDEX (<object> <index>) ) ====================== END SQL Statement Dump ======================
A little web searching suggested that “err” meant that there was an error with the hint and “used” meant that the hint was actually used. I could not find a definition of the rest, but I suspect that “resol” is resolved meaning that the optimiser found what you were trying to hint. The text of the hint is a parsed version as things like query block names are removed. One further thing to note about this is that if you have used the “BEGIN_OUTLINE_DATA” and “END_OUTLINE_DATA” statements in the hint block, which you see in all baselines, then no hints are dumped.
This suggested that I was somehow not being able to address the correct query blocks with the hints, although I was sure I was using the query block names given in the 10053 trace. I generated a baseline from the SQL with the inner select hinted and a good plan, but again the resultant baseline was also not used.
Having failed again I decided to start from scratch and attempt to add some hints to optimise the query rather than trying to reproduce the original query. After successfully doing this I generated a baseline from it and everything was looking rosy. However the baseline was also not used by the query. The 10053 trace showed it being picked up, but the baseline hints did not generate the plan associated with the baseline. Later analysis suggested that this was because I was being a little sloppy and using the “ordered” hint instead of the “leading” hint. This made it easier to get to the good plan, but I had to change the order in which the tables were listed in the SQL so that the “ordered” hint had the desired effect. This probably resulted in a change in query block names and so the baseline would not have been relevant.
Having failed yet again, but at least suspecting that the issue was the “ordered” hint, I set out to use the “leading” hint along with all the other hints from the “ordered” attempt. Initial attempts failed and I suspected that this was down to the views which of course I could not add hints to to ensure the table ordering. It seems that the “ORDERED” hint works for views but the “LEADING” hint does not. Looking at the baseline hints from the previous attempt I did notice a leading hint of the following form:
LEADING(@"SEL$5067E4BC" "<table alias>"@"SEL$2" "<table alias>"@"SEL$3" "<table alias>"@"SEL$3" "<table alias>"@"SEL$4" "<table alias>"@"SEL$5" "<table alias>"@"SEL$6" "<table alias>"@"SEL$7" "<table alias>"@"SEL$10" "<table alias>"@"SEL$11" "<table alias>"@"SEL$12" "<table alias>"@"SEL$13" "<table alias>"@"SEL$14" "<table alias>"@"SEL$15" "<table alias>"@"SEL$26" "<table alias>"@"SEL$30" "<table alias>"@"SEL$31" "<table alias>"@"SEL$32")
This did not seem to work in the outermost select statement, but by removing the query block name (@”SEL$5067E4BC”) and placing it in the appropriate inner select it seemed to work. However whilst most of the tables were being accessed in the right order, not all were. This was especially baffling as the 10053 trace file suggested that the hint was being used:
Dumping Hints ============= ... atom_hint=(@=0x2b7363b51478 err=0 resol=1 used=1 token=501 org=1 lvl=4 txt=LEADING (<string of table aliases matching the LEADING hint) ) ...
This was rather difficult to understand, but could the hint be partially used? After a lot of head scratching I noticed that the tables were being joined in the correct order up to a certain point and after that everything was in the wrong order. Digging into the 10053 trace file I noted that the query block aliases was wrong at the point the hint stopped being used. I realised that this was because it was taken from the baseline generated on the “ordered” version and this had a different order of tables in the join clause. After correcting all the query block aliases, the “LEADING” hint correctly forced the optimiser to join the tables in the order I had judged best. After the addition of a “NO_PUSH_PRED” hint to an “EXISTS” predicate, I managed to generate the same execution plan as from the “ordered” statement and the performance was acceptable. I generated a baseline from this and finally I had succeeded in creating a baseline that caused the SQL to execute in an acceptable time frame.
Lessons
- The “ordered” hint is quick to use and even works when a table is actually a view. However the “leading” hint is the one to use. It may be more complicated and require query block aliases, but it saves time in the long run.
- The 10053 trace file contains some very useful statements on whether hints are used or not.