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;
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.
Pin the plan. Run the query again via an AutoTrace, or ask for a different plan.
Then compare the two.
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
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.
12 Comments
“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.
I’ll guess we’ll never know if you can’t show me your query.
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′ )
)
)
;
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!
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?
Indeed it does work. Thanks Vadim!
Nice!
Nice!
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