Force a Different Path with Automatic Plan Hints

thatjeffsmith SQL Developer 12 Comments

Tell Others About This Story:

Are you smarter than the optimizer?

Are you hiding critical information from it? Perhaps your stats are missing, stale, or a bit misleading?

I don’t want to get into whether hints are good or bad – they can only be used to accomplish good or bad things. They are often absused – so please don’t take this post as carte blanche to go crazy with hints.

But.

What if you could easily tell the Optimizer to take a different path with your query. And then test it, to see if you were ‘smarter?’

In v4.2, you can do that.

Let’s look at the explain plan for

SELECT * FROM DBA_DATA_FILES;
I'm right clicking on the line with the red boxed text...the NESTED LOOPS.

I’m right clicking on the line with the red boxed text…the NESTED LOOPS.

Now, there’s more than one way for the optimizer to bring data together via JOINs. I’m not the person to explain the differences, although Tanel has a nice post here.

And I’d be remiss if I didn’t mention our own Oracle Docs – they even have nice pictures 🙂

So, the optimizer has more than one way to get our data together, and it almost always picks the most efficient way. But in this case, I want to ask it to try a different path.

Right click on the join method in the plan, pick your poison by clicking on the hint. We’ll inject the optimizer hint to your plan.

plan_hint2

Pin the plan. Run the query again via an AutoTrace, or ask for a different plan.

Then compare the two.

Is this good, or bad?

Is this good, or bad?

The session stats reflecting the work performed for the 2 queries certainly are different.

The session stats reflecting the work performed for the 2 queries certainly are different.

Maybe it is significantly better. Maybe you need to create some histograms so the optimizer KNOWS it needs to change up its join method. At the end of the day, you now have another tool in your toolbox – just don’t abuse it, or the optimizer.

#TEASE One last tweak – we have also opened up the plan table columns when viewing plans/traces in SQL Developer for our next drop of v4.2. We’ve added Bytes, TEMP_SPACE, TIME, and a few others. Just check your preferences. You’ll see these when 4.2 EA2 is available – so something to look forward to. #TEASE

You have a few more to choose from now in v4.2

You have a few more to choose from now in v4.2

A Nice Video on Comparing Plans using SQL Developer

Chris Saxon has a very nice video on comparing plans. Give it a look-see why don’t you.

Tell Others About This Story:

Comments 12

  1. “Right click on the join method in the plan, pick your poison by clicking on the hint.” I’m not seeing this — New feature? I’m on build 16.260.1303

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. Sorry, I did not intend that to sound like a challenge. If you’re interested, here’s my query:

            SELECT
            CL.CLAIMTYPE, CL.CLAIM_STATUS, CL.TRUE_CLAIM_ID, CL.PROVIDER_SPEC_CODE,
            CL.DCN, CL.RECIPIENT_MCARE_A_IND, CL.RECIPIENT_MCARE_B_IND, CL.RECIPIENT_MCARE_D_IND,
            CL.PAID_DATE, CL.FIRST_SERVICE_DATE, CL.LAST_SERVICE_DATE, CL.REIMBURSEMENT_AMT,
            CL.ADJUST_REASON, CL.CLAIM_CREDIT_IND, CL.NPI_PROVIDER_NUM, CL.ALLOWED_CHARGE_SOURCE,
            CL.DIAGNOSIS_CODE_1, CL.DIAGNOSIS_CODE_2, CL.DIAGNOSIS_CODE_3, CL.DIAGNOSIS_CODE_4,
            CL.DIAGNOSIS_CODE_5, CL.SURG_DATE_1, CL.SURG_DATE_2, CL.SURG_DATE_3, CL.SURG_DATE_4,
            CL.SURG_DATE_5, CL.SURG_DATE_6, CL.SURG_MOD_INFO_1, CL.SURG_MOD_INFO_2,
            CL.SURG_MOD_INFO_3, CL.SURG_MOD_INFO_4, CL.SURG_MOD_INFO_5, CL.SURG_MOD_INFO_6, CL.ER_VISIT,
            CL.RX_DAYS_SUPPLY, CL.RX_QTY_DISPENSED, CL.RX_DISPENSE_FEE, CL.RX_PROVIDER_NUMBER,
            CL.DRUG_CODE, CL.ADMIT_SOURCE, CL.ADMIT_TYPE, CL.BILL_FACILITY_TYPE, CL.REVENUE_CODE,
            CL.DETAIL_NUM, CL.DTL_FIRST_SERVICE_DATE, CL.DTL_LAST_SERVICE_DATE, CL.DTL_PROC_CODE,
            CL.DTL_SERVICE_UNITS, CL.DTL_OTHER_INS, CL.DTL_ALLOWED, CL.PAYMENT_DATE,
            PR.PROVIDER_NAME, PR.ZIP5, CL.ICN, CL.RECIPIENT_COUNTY_CODE
            FROM MEDICAID.CLAIMS_2013 CL
            LEFT JOIN PROVIDERS PR ON CL.PROVIDER_NUMBER = PR.LONG_PROVIDER_NUMBER
            WHERE
            CL.DCN IN (SELECT DCN FROM MERCY_DCNS)
            AND SUBSTR(CL.ICN, 1, 1) != ‘7’
            AND CL.CLAIM_STATUS != ‘V’
            AND (
            (
            DTL_FIRST_SERVICE_DATE BETWEEN ’01-JAN-2013′ AND ’31-MAY-2016′
            OR DTL_LAST_SERVICE_DATE BETWEEN ’01-JAN-2013′ AND ’31-MAY-2016′
            OR ( DTL_FIRST_SERVICE_DATE ’31-MAY-2016′ )
            ) OR (
            FIRST_SERVICE_DATE BETWEEN ’01-JAN-2013′ AND ’31-MAY-2016′
            OR LAST_SERVICE_DATE BETWEEN ’01-JAN-2013′ AND ’31-MAY-2016′
            OR ( FIRST_SERVICE_DATE ’31-MAY-2016′ )
            )
            )
            ;

        2. Reproduced for simpler query:

          select e.*, d.* from DEPARTMENTS D
          left join
          EMPLOYEES E
          on E.DEPARTMENT_ID = D.DEPARTMENT_ID;

          Context menu for join operation missing. However, for table access plan nodes there are options “INDEX” and “LEADING” — this way you will at least witness that the feature is there. You can also click on any outline hint in other_xml branch and copy it or suggested alternative.

          1. Also, it seems that clicking on join method in the outline does work. In my case it is the very first outline directive:
            /*+ USE_MERGE(@”SEL$0E991E55″ “E”@”SEL$1”) */
            In the context menu I get all three join methods. Does this trick work for you?

  2. Thanks for that great article, knowing these features may have saved me lot of time !

    Note that a plain plan export can also be useful to exchange with partners on performance issues. I didnt find it in sqldev and i usually use another tools just for that (sqltools ++).

    Ali

Leave a Reply

Your email address will not be published. Required fields are marked *