Can outlines be fully reverse engineered?

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

Other related posts: