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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

12 Comments

  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

    • Nope, it’s in there.

      So in Build 16.260.1303 –

      run autotrace on
      select * from dba_data_files

      right click on the first NESTED_LOOPS plan step

    • Hmm, yes I see that now. I guess it doesn’t work for more complex queries, like the one I first tried it with.

    • 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′ )
      )
      )
      ;

    • Vadim Tropashko

      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.

    • Yes, I did see that the hints are listed in other_xml, so there’s that option. Thanks!

    • Vadim Tropashko

      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

Write A Comment