Can outlines be fully reverse engineered?
- From: "Williams, Trevor" <Trevor.Williams@xxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Oct 2004 12:45:26 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
