Mauro Pagano's Blog

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

6 Comments

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 11.2.0.4 it’s around 1100 and around 1500 in 12.1.0.2, 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 pathfinder-master.zip, navigate to the root pathfinder directory, and connect as SYS to the database.
    $ unzip pathfinder-master.zip
    $ 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
mpagano/mpagano@orcl
Building Pathfinder driver scripts
Connected.

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 pathfinder_orcl_20151023_1256.zip 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!!!

6 thoughts on “Introducing Pathfinder, is there a better plan for my SQL?

  1. Good day Mauro,
    I really look forward to all your articles because like you performance tuning always brings out the best in me. I have downloaded the pathfinder utility but I am experiencing errors similar to the following when I execute it from a DOS shell

    91) “pathfinder_{ 20151109_1156 (00091)” 12:37:43 “_optimizer_coalesce_subqueries” = FALSE
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.

    Kind regards
    Ravin Maharaj
    ravin.b.maharaj@gmail.com

    Like

    • Hi Ravin,

      thanks for the nice words 🙂

      About Pathfinder, since you are running it from Windows the command should be DEL (not RM) but I haven’t implemented such change yet (but it’s on the list! 🙂
      Could you please test it from a *nix machine and confirm there is no such error?

      Thanks,
      Mauro

      Like

  2. Good day Mauro,
    Is this normal ? The index html file contains only the below information after running for almost 6 hours !!!

    Pathfinder v1501 (2015-10-02): Plan Finder
    dbname:ssport connect string:sgy_ss_prod0813/@ startime:2015/11/09 11:56:11

    Test#

    CFB
    Reparse#

    Setting

    Plan
    Hash Value

    Elapsed
    Time

    CPU
    Time

    Buffer
    Gets

    Rows
    Processed

    Execution
    Plan

    V$SQL
    Details

    endtime:2015/11/09 17:45:50

    SQL Statement :

    SELECT /* ^^pathfinder_testid */ * FROM (SELECT DISTINCT SLG.SLUG_GENREFNO
    “slug_genrefno”, SLG.SLUG_CHA_NUMBER “slug_cha_number”,
    SLG.SLUG_NUMBER “slug_number”, SLG.SLUG_TOTALFRAMES
    “slug_totalframes”, SLG.SLUG_UID “slug_uid”,
    SLG.SLUG_VIDEO_ASPECT “slug_video_aspect”, SLG.SLUG_VIDEO_X
    “slug_video_x”, SLG.SLUG_VIDEO_Y “slug_video_y”,
    SLG.SLUG_ISUPDATEREQUIRED “slug_isupdaterequired”,
    SLG.SLUG_PART_NUMBER, SLG.SLUG_VERSION_CODE, LEM.LEM_EPIUID
    “LEM_EPIUID”, FG.GEN_TITLE_WORKING “GEN_TITLE”,
    LVM.LVM_VERSION_UID, LVM.LVM_VERSION_CODE FROM FID_SCHEDULE FS
    INNER JOIN X_SLUGDATA SLG ON FS.SCH_GEN_REFNO =
    SLG.SLUG_GENREFNO INNER JOIN LMKRS_EPI_MAPPING LEM ON
    SLG.SLUG_GENREFNO =LEM.LEM_GEN_REF_NUMBER INNER JOIN FID_GENERAL
    FG ON SLG.SLUG_GENREFNO = FG.GEN_REFNO LEFT JOIN
    LMKRS_VERSION_MAPPING LVM ON SLG.SLUG_GENREFNO =
    LVM.LVM_GENREF_NUMBER AND SLG.SLUG_CHA_NUMBER =
    LVM.LVM_CHA_NUMBER WHERE FS.SCH_ACTUAL_START_DATE
    +(FS.SCH_TIME/86400) BETWEEN :B2 AND :B1 AND FS.SCH_CHA_NUMBER
    IN (SELECT TO_NUMBER(XT.COLUMN_VALUE) FROM
    XMLTABLE(GETONAIRCHANNELS()) XT ) AND (LVM.LVM_VERSION_CODE IS
    NULL OR SLG.SLUG_VERSION_CODE LVM.LVM_VERSION_CODE OR
    LVM.LVM_IS_GENREF_UPD_REQ = ‘Y’) AND(LVM.LVM_NUMBER IS NULL OR
    LVM.LVM_IS_SYNC_REQ = ‘Y’ OR LVM.LVM_IS_GENREF_UPD_REQ = ‘Y’)
    AND SLG.SLUG_ISUPDATEREQUIRED = ‘N’ OR LVM.LVM_IS_GENREF_UPD_REQ
    = ‘Y’) WHERE ROWNUM < 1001;

    Regards
    Ravin Maharaj
    ravin.b.maharaj@gmail.com

    Like

  3. Hi Mauro,
    This is a fantastic brute force script. 🙂

    Could you please hide the password in connect string. Is it possible? Every html page is spooling the password out. I am not if if I am doing right?

    Thank you,
    Anjul

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s