I just finished working with a coworker on speeding up a PS/Query. It was running well in a development environment but not in production. I tried to find a simple hint or two to make production run like development but didn’t have any luck. Then I remembered that my script to get a query’s plan prints out a full set of outline hints like this:
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$E903463E")
USE_NL(@"SEL$E903463E" "CLS"@"SEL$4")
USE_NL(@"SEL$E903463E" "SEC"@"SEL$3")
USE_NL(@"SEL$E903463E" "SOC"@"SEL$4")
... lines removed to make this post shorter ...
OUTLINE_LEAF(@"SEL$F5BB74E1")
PUSH_PRED(@"SEL$F5BB74E1" "A1"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$E903463E")
ALL_ROWS
OPT_PARAM('_unnest_subquery' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
A coworker edited the PS/Query and tried to add this hint but got errors because of the quotation marks. So, I simplified the hint by removing all the double quotes and taking out the lines with single quotes because I knew they weren’t needed. They only related to parameters that I knew were already set in production.
Here is the new quote-less hint:
/*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E903463E) USE_NL(@SEL$E903463E CLS@SEL$4) USE_NL(@SEL$E903463E SEC@SEL$3) USE_NL(@SEL$E903463E SOC@SEL$4) ... lines removed to make this post shorter ... OUTLINE_LEAF(@SEL$F5BB74E1) PUSH_PRED(@SEL$F5BB74E1 A1@SEL$1 1) OUTLINE_LEAF(@SEL$E903463E) ALL_ROWS IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */
We ran the modified PS/Query in production and I verified that the query was running the correct plan. The only weird thing was that because the query has a group by PS/Query stuck the hint in the group by clause and in the select clause.
SELECT /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E90346... ... middle of select statement ... GROUP BY /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E90346... ... end of select statement ...
At first this put us off, but really the hint in the group by clause is just a comment and syntactically is insignificant even though it is ugly.
By the way, here are the options I used to output the outline hint:
select * from
table(dbms_xplan.display('PLAN_TABLE',NULL,'ADVANCED'));
I get all my plans this way now. It generates a lot of output that I don’t usually use. But, seeing it reminds you that it is there if you need it.
– Bobby
Hi Bobby,
This is helpful. Can you please share the full SELECT statement that you put together after including the outline hints? I just want to see the full version of final select statement.
Thanks
Nitin
Nitin,
Thanks for your comment. Here is the full SQL with a fictional oprid:
SELECT /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E903463E) USE_NL(@SEL$E903463E CLS@SEL$4) USE_NL(@SEL$E903463E SEC@SEL$3) USE_NL(@SEL$E903463E SOC@SEL$4) LEADING(@SEL$E903463E OPR@SEL$3 SOC@SEL$4 SEC@SEL$3 CLS@SEL$4) INDEX(@SEL$E903463E CLS@SEL$4 (PS_SJT_CLASS_ALL.SCRTY_SET_CD PS_SJT_CLASS_ALL.SCRTY_TYPE_CD PS_SJT_CLASS_ALL.SCRTY_KEY1 PS_SJT_CLASS_ALL.SCRTY_KEY2 PS_SJT_CLASS_ALL.SCRTY_KEY3 PS_SJT_CLASS_ALL.CLASSID PS_SJT_CLASS_ALL.TREE)) INDEX_RS_ASC(@SEL$E903463E SEC@SEL$3 (PS_SJT_PERSON.EMPLID PS_SJT_PERSON.EMPL_RCD)) INDEX(@SEL$E903463E SOC@SEL$4 (PS_SJT_OPR_CLS.CLASSID PS_SJT_OPR_CLS.OPRID PS_SJT_OPR_CLS.SEC_RSC_FLG)) INDEX_RS_ASC(@SEL$E903463E OPR@SEL$3 (PSOPRDEFN.OPRID)) USE_HASH_AGGREGATION(@SEL$F5BB74E1) NLJ_BATCHING(@SEL$F5BB74E1 D@SEL$2) USE_NL(@SEL$F5BB74E1 D@SEL$2) USE_NL(@SEL$F5BB74E1 A1@SEL$1) USE_NL(@SEL$F5BB74E1 B@SEL$2) USE_NL(@SEL$F5BB74E1 C@SEL$2) USE_NL(@SEL$F5BB74E1 A@SEL$2) LEADING(@SEL$F5BB74E1 H@SEL$2 A@SEL$2 C@SEL$2 B@SEL$2 A1@SEL$1 D@SEL$2) INDEX(@SEL$F5BB74E1 D@SEL$2 (PS_CAL_DETP_TBL.SETID PS_CAL_DETP_TBL.CALENDAR_ID PS_CAL_DETP_TBL.FISCAL_YEAR PS_CAL_DETP_TBL.ACCOUNTING_PERIOD)) NO_ACCESS(@SEL$F5BB74E1 A1@SEL$1) INDEX_RS_ASC(@SEL$F5BB74E1 B@SEL$2 (PS_PAY_CALENDAR.COMPANY PS_PAY_CALENDAR.PAYGROUP PS_PAY_CALENDAR.PAY_END_DT)) INDEX_RS_ASC(@SEL$F5BB74E1 C@SEL$2 (PS_PAY_CHECK.PAYCHECK_NBR PS_PAY_CHECK.COMPANY PS_PAY_CHECK.PAYGROUP PS_PAY_CHECK.PAY_END_DT PS_PAY_CHECK.OFF_CYCLE PS_PAY_CHECK.PAGE_NUM PS_PAY_CHECK.LINE_NUM PS_PAY_CHECK.SEPCHK)) INDEX_RS_ASC(@SEL$F5BB74E1 A@SEL$2 (PS_HR_ACCTG_LINE.RUN_DT PS_HR_ACCTG_LINE.SEQNUM PS_HR_ACCTG_LINE.JRNL_LN_REF PS_HR_ACCTG_LINE.ACCOUNTING_DT)) FULL(@SEL$F5BB74E1 H@SEL$2) OUTLINE(@SEL$4) OUTLINE(@SEL$3) OUTLINE(@SEL$2) OUTLINE(@SEL$1) MERGE(@SEL$2) OUTLINE(@SEL$F5BB74E1) UNNEST(@SEL$4) OUTLINE(@SEL$F26D6FDE) MERGE(@SEL$2) OUTLINE_LEAF(@SEL$F5BB74E1) PUSH_PRED(@SEL$F5BB74E1 A1@SEL$1 1) OUTLINE_LEAF(@SEL$E903463E) ALL_ROWS IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */
‘With Paygroup’,
A.RUN_ID,
TO_CHAR (A.RUN_DT, ‘YYYY-MM-DD’),
A.PAYGROUP,
A.BUSINESS_UNIT_GL,
A.ACCOUNT,
A.DEPTID,
A.APPL_JRNL_ID,
SUM (A.MONETARY_AMOUNT)
FROM PS_UF_HR_ACCTG_VW A, PS_EMPLMT_SRCH_QRY A1
WHERE (A.EMPLID = A1.EMPLID AND A1.OPRID = ‘NOTREAL’ AND (A.RUN_ID = :1))
GROUP BY /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E903463E) USE_NL(@SEL$E903463E CLS@SEL$4) USE_NL(@SEL$E903463E SEC@SEL$3) USE_NL(@SEL$E903463E SOC@SEL$4) LEADING(@SEL$E903463E OPR@SEL$3 SOC@SEL$4 SEC@SEL$3 CLS@SEL$4) INDEX(@SEL$E903463E CLS@SEL$4 (PS_SJT_CLASS_ALL.SCRTY_SET_CD PS_SJT_CLASS_ALL.SCRTY_TYPE_CD PS_SJT_CLASS_ALL.SCRTY_KEY1 PS_SJT_CLASS_ALL.SCRTY_KEY2 PS_SJT_CLASS_ALL.SCRTY_KEY3 PS_SJT_CLASS_ALL.CLASSID PS_SJT_CLASS_ALL.TREE)) INDEX_RS_ASC(@SEL$E903463E SEC@SEL$3 (PS_SJT_PERSON.EMPLID PS_SJT_PERSON.EMPL_RCD)) INDEX(@SEL$E903463E SOC@SEL$4 (PS_SJT_OPR_CLS.CLASSID PS_SJT_OPR_CLS.OPRID PS_SJT_OPR_CLS.SEC_RSC_FLG)) INDEX_RS_ASC(@SEL$E903463E OPR@SEL$3 (PSOPRDEFN.OPRID)) USE_HASH_AGGREGATION(@SEL$F5BB74E1) NLJ_BATCHING(@SEL$F5BB74E1 D@SEL$2) USE_NL(@SEL$F5BB74E1 D@SEL$2) USE_NL(@SEL$F5BB74E1 A1@SEL$1) USE_NL(@SEL$F5BB74E1 B@SEL$2) USE_NL(@SEL$F5BB74E1 C@SEL$2) USE_NL(@SEL$F5BB74E1 A@SEL$2) LEADING(@SEL$F5BB74E1 H@SEL$2 A@SEL$2 C@SEL$2 B@SEL$2 A1@SEL$1 D@SEL$2) INDEX(@SEL$F5BB74E1 D@SEL$2 (PS_CAL_DETP_TBL.SETID PS_CAL_DETP_TBL.CALENDAR_ID PS_CAL_DETP_TBL.FISCAL_YEAR PS_CAL_DETP_TBL.ACCOUNTING_PERIOD)) NO_ACCESS(@SEL$F5BB74E1 A1@SEL$1) INDEX_RS_ASC(@SEL$F5BB74E1 B@SEL$2 (PS_PAY_CALENDAR.COMPANY PS_PAY_CALENDAR.PAYGROUP PS_PAY_CALENDAR.PAY_END_DT)) INDEX_RS_ASC(@SEL$F5BB74E1 C@SEL$2 (PS_PAY_CHECK.PAYCHECK_NBR PS_PAY_CHECK.COMPANY PS_PAY_CHECK.PAYGROUP PS_PAY_CHECK.PAY_END_DT PS_PAY_CHECK.OFF_CYCLE PS_PAY_CHECK.PAGE_NUM PS_PAY_CHECK.LINE_NUM PS_PAY_CHECK.SEPCHK)) INDEX_RS_ASC(@SEL$F5BB74E1 A@SEL$2 (PS_HR_ACCTG_LINE.RUN_DT PS_HR_ACCTG_LINE.SEQNUM PS_HR_ACCTG_LINE.JRNL_LN_REF PS_HR_ACCTG_LINE.ACCOUNTING_DT)) FULL(@SEL$F5BB74E1 H@SEL$2) OUTLINE(@SEL$4) OUTLINE(@SEL$3) OUTLINE(@SEL$2) OUTLINE(@SEL$1) MERGE(@SEL$2) OUTLINE(@SEL$F5BB74E1) UNNEST(@SEL$4) OUTLINE(@SEL$F26D6FDE) MERGE(@SEL$2) OUTLINE_LEAF(@SEL$F5BB74E1) PUSH_PRED(@SEL$F5BB74E1 A1@SEL$1 1) OUTLINE_LEAF(@SEL$E903463E) ALL_ROWS IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */
‘With Paygroup’,
A.RUN_ID,
TO_CHAR (A.RUN_DT, ‘YYYY-MM-DD’),
A.PAYGROUP,
A.BUSINESS_UNIT_GL,
A.ACCOUNT,
A.DEPTID,
A.APPL_JRNL_ID
The hint in the group by does nothing. It is just a comment which is ignored by the optimizer.
– Bobby
Pingback: Optimizer bug fix makes a query run more than 3 times slower | Bobby Durrett's DBA Blog