I received an email asking for my help with a production query that was performing badly on an AWS server. Other DBAs had apparently created a baseline for this statement in production, but it was not working. Initially I therefore thought this would be an investigation into why the baseline was not being picked up. I didn’t have access the database in OEM, so I used my SQL monitoring report mimicking ASH script to get an idea of where all the time was going. This immediately showed two things. Firstly, the query was effectively two queries, with a union joining them together, and the second half of the query was taking no time at all. Secondly, the vast majority of the time was being spent on one table.
As usual, I extracted the SQL from v$sql, as it was available there, and put it through a SQL formatter. I then created a little script around it to run an explain plan followed by running dbms_xplan.display(format=>’ALL’). I use the ALL format as this gives a report on hint usage. The SQL had bind variables so I needed to set these up and as some of them were dates and SQL*Plus can’t directly create date variables, I needed to replace these with calls to to_date and used varchar2 bind variables. I then went hunting in v$sql_bind_catpure and dba_hist_sqlbind for some examples of bind variable values. With all of that in place, I could run an explain.
This query ran in a reasonable amount of time even with the bad plan, so I had far more options than would be the case if it took hours to run. I could use things like the gather_plan_statistics hint to give me more information quickly without having to completely take the query apart and execute parts of it separately. It was however an insert into … select …, so I had to discard the insert section and reduce the query to just the select.
When tuning a query, I like to start by trying to nudge Oracle’s optimiser in the right direction by using the cardinality or opt_estimate hints so that the optimiser has better estimates for the number of rows it gets back from each table and hopefully will therefore have a better idea of the number of rows it gets back from each join. Cardinality feedback is intended to do something like this, but clearly it was not doing so in this case. From a run with “gather_plan_statistics” I knew the actual values for the rows returned from each table and each join and so I set to work nudging the optimiser with the relevant hints. I concentrated only on the first part of the union as this was the part taking all the time.
This nudging approach can work well when Oracle is getting things mostly right but has got its cardinality estimates wrong for a table or two or a join or two. However, it does become difficult when Oracle seems to have got things quite wrong and it can also be rather frustrating when you tell the optimiser that joining table A to table B will result in X rows and so the optimiser decides to join table A to table C first and then to table B, thus negating your hint. You can also tell it that index X returns Y rows and so Oracle chooses index Z instead. I therefore cut my losses if this approach seems to not be bearing fruit reasonably quickly, as in this case.
Next, I reviewed the tables, the join clauses and the where clause predicates to get an idea of what join orders were likely to be best. I then attempted to force the optimiser to use what I thought was the best order. This can be done with either the “ordered” hint or the “leading” hint. The “ordered” hint forces the tables to be join in the order they are specified in the from clause. If you want to change the order in which the tables are joined, then you need to change the order in the from clause. This makes things simple and explicit, but you can’t then create a baseline from the resultant SQL that can be transferred to the target SQL, or at least I have never managed it. With the “leading” hint you specify in the hint the order in which you want the tables to be joined and any remaining tables not specified in the hint will then be joined in whatever order the optimiser chooses. One complication with the “leading” hint is that it is dealing with tables and the table aliases directly. The query at issue had two “with” clauses and of course these were referenced in the relevant from clause by the alias given to the clauses. Whilst this is perfectly fine for the “ordered” hint, the “leading” hint requires the table name or table alias used in the with clause, not the with clause alias.
With my proposed order enforced by the leading hint I then ran the query with the “gather_plan_statistics” hint and reviewed the output. At this stage I am looking for the join order that will minimise the number of rows thrown away at each step. If you join table A to table B resulting in 1M rows and then join the result set to table C and get back 10 rows, you are throwing away almost 1M rows. If, however you joined table A to table C and get back 100 rows and then join the result set to table B and get back 10 rows, then you are only throwing away 90 rows. It would usually be better to join the tables in the order A => C => B. I therefore adjusted the join order where appropriate to reflect this strategy.
In addition to the join order, I was also looking at access paths and join types. Due to inaccurate cardinality estimates, the optimiser may choose an index range scan when a full table scan would be better. It can also decide on the wrong index, especially if the optimiser decides there are no rows returned from the previous step, meaning that accessing the table by any index would have the same cost, i.e. effectively 0. The access paths can be addressed with relevant hints, such as “full(<table alias>)”, “index(<table alias> <index name>)” or even “index_ffs(<table alias>)”. With join types, the optimiser may have underestimated the number of rows returned by the previous operation and so chosen a nested loops join over a hash join. This can be addressed with the hint “use_hash(<table alias>)”. There are however complications with a hash join which you can read about at https://use-the-index-luke.com/sql/join/hash-join-partial-objects and https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/.
After tweaking the join order and improving the access paths and join types, I was left with a rather annoying situation which was impervious to any tuning I could do through patches and the like. There were eight tables involved, and the joining of the first six resulted in about 31K rows returned at every stage. The query would take less than a second to get through the first six tables. It would then join to the seventh table and get back 41M rows followed by joining to the eighth and get back to approximately the previous 31K rows. No columns from these tables were output by the query, they were simply used to filter the results. Unfortunately, the join predicate for the eighth table included a predicate for the seventh table. This meant that the most obvious order was to join the result of the first six table joins to the seventh table and then to the eighth. Joining to the eighth and then the seventh resulted in far fewer rows being selected from the eight table, but a full table scan was then performed on the seventh table. This would not be ideal for a lot of bind variable combinations where far fewer rows were involved. Given that the query was running in just under two minutes and this was not only acceptable, but a vast improvement in the runtime, I decided to leave the issue of the joins to the seventh and eight tables to the development team.
With the query tuned, the next question was how to apply the relevant hints to the actual query. The code could be changed, but that is rarely an option palatable to development teams and hard coded hints are certainly not ideal. The question therefore boiled down to whether to use a SQL patch, baseline, or profile. I rarely used profiles, in fact the only time I recall using one is when I wanted to apply a baseline to a query with changing literals. A baseline only matches exact SQL and so it would not have been an option. As there were no literals in the current query, I considered using either a patch or a baseline. As I had no good information on the second half of the query, I didn’t want to force a plan which could prove suboptimal in an execution where the second half actually did matter. Consequently, I opted for a patch. A SQL patch inserts hints in the outer most select and the hints I had been using were in an inner select as this makes the process of determining the optimal hints easier. Also, the real query was an insert statement from which, for the purposes of my tuning, I had chopped off the insert part. I therefore needed to find the query block names used in the real insert statement so that I could convert my set of hints into hints that would work in the outer most select. As the real statement was being reasonably frequently run, it was easiest to get these from dbms_xplan.display_cursor used on an actual run of the insert statement. With this done, I created a SQL patch and applied it in a test environment which resulted in the expected improvement in performance.
There are two important things to be aware of when using SQL patches. Firstly, you can only create a patch using the SQL ID if that SQL ID is in the cursor cache. If it is not, then an “ORA-56975: invalid SQL_ID” error is generated. My guess is that this is because patches need to be created against SQL text, not SQL IDs. If you specify a SQL ID when creating a patch, Oracle replaces this with the SQL text extracted from v$sql. Obviously, if the SQL ID is not in v$sql, then this can’t happen. However, if you have the patch created on one database, then you can transfer it to any other database using a staging table and packing and unpacking it, all via dbms_sqldiag calls. Secondly, SQL baselines and SQL profiles can change the query block names which can nullify any SQL patch using them. Therefore, any SQL baselines or SQL profiles for the intended query should be disabled or dropped. Note that my work had not been on the production database.
With the patch created and tested, I informed the relevant team of this, and I also mentioned the issue with the seventh and eighth tables. The development team said they would investigate this.
A few days after the patch was implemented, the product reported that the query was still performing poorly, although this time for different bind variables. Analysis showed that this time the second half of the query was causing the problem. I went through my tuning process again and determined an appropriate set of hints for the second half of the query. Now that the whole query needed plan improvements, a baseline was the most appropriate approach and so I created one and after testing it too was applied to production. Again, I raised the issue of the seventh and eighth tables. This time I did some further analysis.
The relevant section of the query was something like the following:
inner join t_seven
on t_one.primary_key = t_seven.column_x
inner join t_eight
on t_two.some_id = t_eight.some_id
and t_seven.column_y = t_eight.primary_key
Joining the result of the first six tables to the seventh multiplied the rows over 1000 times, but joining to the eighth table first, even without the filter condition on the seventh table only resulted in the rows being multiplied about 20 times. Joining to the eighth table and then the seventh table would therefore seem like a better choice, but the one index on the seventh table only supported the join condition to the first six tables (t_one.primary_key = t_seven.column_x) and so it could not be used to apply the addition predicate from the eight table (t_seven.column_y = t_eight.primary_key). This meant that each row from the first six tables would become 20 after joining to the eighth table and then become 20,000 when joined to the index on the seventh table. Only on accessing the seventh table itself would the result set be brought back down to approximately the number of rows returned from the first six tables. The alternative would be for the seventh table to be accessed via a full table scan, but this is not a small table and there are a lot of executions of this query where the result set is much smaller. For them, a full tables scan would not be a good idea.
I reported this analysis back to the development team along with a recommendation for the creating of an index on the seventh table which would support the filtering of it on both the join predicate to the first six tables and the join predicate to the eighth. This not only meant that the index would return the minimum number of rows, but as all the columns specified in the query from the eighth table where in the index, it would be a covering index and therefore the eighth table itself would not need to be accessed. However, I was told that they didn’t like to create indexes between releases of the application, and the next release was three months away. Therefore, I create a baseline using the optimal join order in the absence of my recommended index. The baseline was satisfactorily tested and then applied to production.
A few days later, the product reported that for yet another set of bind variables, the query was still not performing satisfactorily. I analysed the performance for this set of bind variables and this time it was the joins to the seventh and eighth tables that were causing the problem. For this set the situation was so bad that we had over a billion index accesses of the eighth table. Given my previous analysis I could not understand how the query could ever have performed satisfactorily with this set of bind variables and so I thought there must be some other join order that would work. However, I could not find any execution plan in the AWR history that performed anywhere near to my version and so I concluded that either the query had never worked for this set of bind variables, or the data accessed had grown or changed significantly. I asked the product and they gave me a vague answer of it probably worked two months ago, which raised more questions than it answered. I therefore stated that the only option was to create my recommended index and create a new baseline to enforce its use. Unsurprisingly, faced with such a situation, the product decided to create the index. The new index and baseline were therefore tested and applied to production and the query is now performing very satisfactorily for all sets of bind variables.