Adding an outline hint to a PS/Query

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Adding an outline hint to a PS/Query

  1. nitin says:

    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

    • Bobby says:

      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

Leave a Reply