Re: Oracle Stored Outlines Interpretation

A.S.
If you're going to reference something you've read online then it's worth giving a URL so that we can see the context. I'll guess from the phrase "stored outlines in the execution plan" that it's something I said relating to the "Outline Data" section you can get from a call to dbms_xplan.display_xxx()

e.g - cut and paste session:

SQL> l
  1 SELECT count(*) from dual where exists (   2 SELECT *
  3 FROM orders
  4 WHERE user_id = :user_id
  5* )
SQL> /   COUNT(*)

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$2")
      OUTLINE_LEAF(_at_"SEL$1")
      INDEX(_at_"SEL$2" "ORDERS"_at_"SEL$2" ("ORDERS"."USER_ID"))
      END_OUTLINE_DATA

Predicate Information (identified by operation id):

   2 - filter( IS NOT NULL)
   4 - access("USER_ID"=:USER_ID)

With the 'oultine' format the execution plan includes the list of hints that ought to recreate the execution plan. The label "outline" is still used here, despite the fact that officially this would now be saved as an SQL Plan Baseline (or an SQL Profile or SQL Patch as alternatives that can be achieved with a little hacking).

Regards
Jonathan Lewis

On Fri, 8 Jan 2021 at 15:57, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi All,
>
> A recent thread on OTN caught my thought where Mr. Jonathan Lewis is
> describing query performance issues by referring to stored outlines in the
> execution plan. I tried going through the web but couldn’t spot any
> satisfying information to figure out these.
>
> Could you please help me know what these do?, and request you to give some
> clues, how I can read and value the pieces of information they have in
> query performance tuning and direct me to some blog posts, books, or any
> article to know these in a better way.
>
>
> Thank you for all your help and support in this regard.
>
>
> Best Regards,
> A.S.
>

--
http://www.freelists.org/webpage/oracle-l