techblog 

We have been having some issues with the database performance of our whois query. It was taking far too much resources, in particular many, many buffer gets. We are running against 10.1.0.3 here so I thought I would look at the explain plan of the query, I provide the final few lines below:

This is the 10.1.0.3 plan fragment. I did not like the look of that full table scan, but could see no way of hinting it out. I then happened to look at explain plan on a 10.1.0.4 instance, and the plan looked a bit more efficient:

So the 10.1.0.4 explain plan is around 100 times more efficient, it is the view pushed predicate that allows the pk_members index to be used. You can read a little about this from here.

The question then became why the 10.1.0.3 was not working and then how to get it to do the predicate pushing. I think I must by now have encountered around 10 bugs in Oracle version 10g and this was yet another: Bug 3723007 - Predicate push down may not occur in 10g.

There is a patch for this bug, but we wanted a quick workaround that we could apply immediately. There is a workaround that involves changing the parameter _optimizer_cost_based_transformation to off. Now traditionally you are not supposed to go mucking around with underscore parameter but as oracle support were giving the go ahead we were not too worried. So we actually wanted this to just come into effect for the user that performs the whois queries so we decided to put a logon trigger for that user:

SQL> CREATE OR REPLACE TRIGGER logon_trigger
after logon on USER.schema
begin
execute immediate
'alter session set "_optimizer_cost_based_transformation"=off';
end;
/

We now have this running and the whois queries are taking around two orders of magnitude less database resources. Just a pity oracle introduced the problem in the first place.