Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Can outlines be fully reverse engineered?

Can outlines be fully reverse engineered?

From: Williams, Trevor <Trevor.Williams_at_rac.com.au>
Date: Fri, 22 Oct 2004 12:45:26 +0800
Message-ID: <EBF0C84BC75F344883500C51712B20F442DAC7@mercury.rac.com.au>


Does anyone know whether outlines can, in fact, be fully/exactly reverse engineered into hints? If so, how? Is there some documentation anywhere on the topic of how/when outlines are processed by the optimiser? I sorted the hint_text by hint#, and placed the hints for each node# in the relevant section of the SQL. The hints were used; but for one node the execution plan was different from that of the original outlined plan. Similar. But the original performs marginally better.

 original outline hints                      outline for SQL containing original outline hints
 ----------------------                      -------------------------------------------------
 /*+ NO_EXPAND 	                       /*+ NO_EXPAND 
     PQ_DISTRIBUTE(T NONE NONE) 	         PQ_DISTRIBUTE(T NONE NONE) 
     PQ_DISTRIBUTE(P NONE NONE) 	         PQ_DISTRIBUTE(P NONE NONE) 
     PQ_DISTRIBUTE(A NONE NONE) 	         PQ_DISTRIBUTE(E NONE NONE) 
     PQ_DISTRIBUTE(E NONE NONE) 	         PQ_DISTRIBUTE(SYS_ALIAS_2 NONE NONE) 
     USE_NL(T) 	                           USE_NL(T) 
     USE_NL(P) 	                           USE_NL(P) 
     USE_NL(A) 	                           USE_NL(E) 
     USE_NL(E) 	                           USE_HASH(SYS_ALIAS_2) 
     ORDERED 	                           ORDERED 
     NO_FACT(T)                  	         NO_FACT(T) 
     NO_FACT(P) 	                           NO_FACT(P) 
     NO_FACT(A) 	                           NO_FACT(E) 
     NO_FACT(E) 	                           NO_FACT(SYS_ALIAS_2) 
     NO_FACT(SYS_ALIAS_2) 	               NO_FACT(A) 
     INDEX(T IPEGLTRAN_4) 	               INDEX(T IPEGLTRAN_4) 
     INDEX(P IPEHISTPAY_1) 	               INDEX(P IPEHISTPAY_1) 
     INDEX(A IOCCUP_ACCOUNT_1) 	         INDEX(E IEMPLOYEE_1) 
     INDEX(E IEMPLOYEE_1) 	               INDEX_FFS(SYS_ALIAS_2 SUBSTANTIVE_TMW) 
     INDEX_FFS(SYS_ALIAS_2 SUBSTANTIVE_TMW)  INDEX(A IOCCUP_ACCOUNT_1) 
     NOREWRITE 	                           NOREWRITE 
     NOREWRITE 	                           NOREWRITE 
     */ 	                                 */ 

If outlines work by dynamically inserting hints into the SQL then there should be a way to add the original outline hints in such a way that the original execution plan is generated. In which case, how should the outline hints be bundled for inclusion into the SQL? As you can see, I added them exactly as extracted from ol$hints and did not even remove duplicates.

But if the optimiser intercepts the outline and processes it internally somehow, then it may not be possible to hint for the exact plan. "These hints are applied at various stages of compilation" implies to me that the stored outline contains more information than a list of hints (and explains the stage and join_pos columns).

Why do I bother?
I generated statistics for the relevant objects and then created an outline for the SQL (all_rows). I then removed the statistics because the application is RULE based. The outline is being used and performance is fantastic. But then a developer made cosmetic changes and ignored my comments. Consequently plan B: add the hints to the actual SQL.

thanks
trevor

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 21 2004 - 23:40:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US