Generate the SQL's Explain Plan

  • Let's assume this is the SQL to be checked

    Example of SQL

    SQL To Check

  • Adjust the SQL Statement:
    • Extract the tested SQL into the SQL Developer editor.
    • Replace the COBOL name of each Host Variable with the equivalent database identifier :b<n> where n is a unique number identifying that host variable. If the same variable appears more than one in the SQL use the same database host variable id in all occurrences.
    • Force the database to analyze the SQL in Rule Base mode by introducing the RULE database hint phrase.

    Worksheet showing SQL statement with Rule.

    Adjust the SQL Statement

  • Generate the Explain Plan:

    • Position the cursor on the SQL statement.

    • Run the Explain Plan by hitting F10(or right-click anywhere on the SQL statement and click Explain Plan... from the context menu).

    • The generated plan will appear in the output tab.

      Explain Plan tab.

      Explain Plan