The other day I was working on a SQL with an odd plan (JPPD with pushed predicate not on the driver table inside the view) when as a test I flipped OFE back one version and got the plan I was expecting, this is (one of) the typical use case(s) for Pathfinder or SQLT XPLORE.
I didn’t have a reproducible testcase and while creating it is always a good thing (IMHO), I was working in a lower environment that gave me a degree of freedom in testing things. I knew exactly which release to go from (12.1.0.1 -> 12.1.0.2) so I wrote a few lines of PL/SQL to implement a smaller Pathfinder. The idea was to let it run while I was working on a testcase to emulate the problem (and maybe run full blown Pathfinder on it).
The goal of this post isn’t really to provide a working set of files but rather to give you an idea of what those tools do so that you have a better understanding of what to expect from them 🙂
Here is the code, comments later
set serveroutput on timing off feed off veri off
spo driver.sql
exec dbms_output.put_line('set echo on');
begin
for i in (select bugno fix_control, CASE WHEN value = 0 THEN 1 ELSE 0 END new_value
from v$system_fix_control
where optimizer_feature_enable = '&&1.'
and value in (0,1)) LOOP
dbms_output.put_line('CONN <>/<>@<>');
dbms_output.put_line(q'[ALTER SESSION SET "_fix_control" = ']'||i.fix_control||':'||i.new_value||''';');
dbms_output.put_line('@q');
dbms_output.put_line('@x');
end loop;
end;
/
spo off
spo result.txt
@driver.sql
spo off
The idea is simple (and partially flawed), iterate over the fixes that have been introduced in the version you are testing on and write a “test” for each of them (to be tested individually), one at a time. Two support scripts are q.sql that includes SQL to test (with binds if needed) and x.sql to extract the plan from DBMS_XPLAN.DISPLAY_CURSOR.
The flawed idea raises from the fact the SQL does not consider changes delivered under parameters (usually features rather than fixes) as well as it ignores those fixes that are configurable more than [ON/OFF]. Also there are some fixes with questionable versions tracked under OFE, this screws up the test as well.
Both of those conditions explained above make up the dirty part 🙂
The block above creates another script, driver.sql, that is than executed. Between 12.1.0.1 and 12.1.0.2 there were 107 fixes.
The CONNECT over and over is an overkill in this case, but experience on XPLORE and Pathfinder taught me there are some parameter changes that don’t trigger a new parse (even though they should) or they don’t get completely “cleaned” when setting them back (what just described isn’t very accurate though so I wouldn’t mind somebody scolding me for it!).
Pathfinder does something very similar, except it iterates on every parameter related to CBO plus all fix_controls, thus executing a much larger number of tests. On top of that it takes into account Cardinality Feedback, executing the SQL multiple times until the plan stop changing (assuming it changes).
Feedbacks, correction, etc welcome as usual.
Oh btw, those few lines of code helped me find out the fix I was after 😀
Mauro Pagano's Blog 