Mauro Pagano's Blog


Quick and dirty Pathfinder (or SQLT XPLORE)

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 ( -> 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');

  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||''';');

   end loop;

spo off
spo result.txt
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 and 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 ūüėÄ


Introducing Pathfinder, is there a better plan for my SQL?

Pathfinder is a new free tool that provides an easy way to execute a SQL statement under multiple optimizer environments in order to generate different execution plans, potentially discovering better plans. The tool can also be used to quickly identify workarounds for wrong result bugs as well as slow parse issues.

Pathfinder uses the same brute-force approach of SQLT XPLORE, executing the SQL for every single CBO parameter and fix_control present in the database, with no installation required. This make Pathfinder easy to run in any environment, including a production one (assuming you understand *WELL* what the tool does, how it works and what it means for your database).

Each test adds¬†approximately 1 second overhead¬†to the time the SQL takes to complete and the amount of tests considered is pretty high, in it’s around 1100 and around 1500 in, thus I suggest to use Pathfinder on SQLs that take at most a few seconds to run (or just be ready to leave Pathfinder run for a loooong time).

The tool executes the SQL statement present in file script.sql (provided), just modify the script and replace the seeded SQL with the one you want to execute. In the same script you can also add ALTER SESSION commands that will be executed before the desired SQL, this is helpful in case you want to influence the analysis providing a different starting point.

To execute the tool just download it from the Download section on the right side of this page (or from here, also the tool will be released as standalone script in the same zip file as SQLd360) and follow these steps:

  1. Unzip, navigate to the root pathfinder directory, and connect as SYS to the database.
    $ unzip
    $ cd pathfinder-master
    $ sqlplus / as sysdba
  2. Open file script.sql and add your SQL in there. Make sure to add the mandatory comment /* ^^pathfinder_testid */. The file name must be script.sql, if you wish to change the name then just ping me.
  3. Execute pathfinder.sql and provide the connect string to connect as the user that is supposed to run the script.
  4. Unzip output file pathfinder_<dbname>_<date>.zip into a directory on your PC and review the results starting from file 00001_pathfinder_<dbname>_<date>_index.html

SQL> @pathfinder

Parameter 1:
Full connect string of the database to run the SQL into
If the database is remote or a PDB then you must include
the TNS alias i.e. scott/tiger@orcl

Enter value for 1: mpagano/mpagano@orcl
Building Pathfinder driver scripts

1) "pathfinder_{ 20151026_1906 (00001)" 19:06:40 BASELINE

2) "pathfinder_{ 20151026_1906 (00002)" 19:06:42 "_add_stale_mv_to_dependency_list" = FALSE

File created.

For each test Pathfinder will show the setting considered as well as some basic statistics like Plan Hash Value, Elapsed Time, CPU Time, Buffer Gets and Rows processed. Also two links are present, one points to the details of the execution plan generated while the other points to the details of V$SQL.

The main page will look something like this:

Screen Shot 2015-10-26 at 9.18.44 PM

Pathfinder also considers the effect of Cardinality Feedback executing the SQL multiple times until the plan stops changing (or CFB gives up after the fifth parse), for all those settings that lead to a first execution plan different than the baseline.
This is why for some settings you will see a Test# with an additional digit, the “reparse” number:

Screen Shot 2015-10-26 at 9.22.23 PM

In example for Test# 117 above the CBO generated a different plan (PHV 1837274416) than the baseline (PHV 1838229974) and Cardinality Feedback kicked in 3 times generating a different plan each time, until the 3rd parse when the CBO ended up with the same plan as the first execution (and no different plan was generated after).

This is the first release of the tool so I expect it to be far from perfect but I’ve already used it several times with success. Hopefully with time (and your feedbacks :D) the tool will get better and better.

I hope you enjoy it and please don’t hesitate to get in touch with me for feedbacks, suggestions and bugs!!!