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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

3 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

  2. Pingback: Optimizer bug fix makes a query run more than 3 times slower | Bobby Durrett's DBA Blog

Leave a Reply