Re: Oracle Stored Outlines Interpretation

Thank you, Jonathan. I am studying about these, and below 3 questions pop up in my mind -

1. Oracle documentation suggests these are not of any benefit in new database versions, so I presume not to break my head interpreting these. But, what is your view here - can these benefit me as a developer to improve slow query performance.
2. As Oracle already gives HINTS to employ in queries like USE_HASH etc. then do they annul HINTS of outlines? If yes, then please help me envision why these are nevertheless relevant?
3. Is there any extra cost associated with query execution if I employ outlines to lift the slow query performance?

Best Regards,
Amit

On Fri, Jan 8, 2021 at 12:01 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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(*)
> ----------
> 0
>
> 1 row selected.
>
> SQL> select * from table(dbms_xplan.display_cursor(format=>'outline'));
>
> PLAN_TABLE_OUTPUT
>
> ------------------------------------------------------------------------------------------------------------------------------------
> SQL_ID d636m9npst6mx, child number 0
> -------------------------------------
> SELECT count(*) from dual where exists ( SELECT * FROM orders
> WHERE user_id = :user_id )
>
> Plan hash value: 772121486
>
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
>
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | 3 (100)|
> |
> | 1 | SORT AGGREGATE | | 1 | | |
> |
> |* 2 | FILTER | | | | |
> |
> | 3 | FAST DUAL | | 1 | | 2 (0)|
> 00:00:01 |
> |* 4 | INDEX UNIQUE SCAN| T1_PK | 1 | 5 | 1 (0)|
> 00:00:01 |
>
> -----------------------------------------------------------------------------
>
> Outline Data
> -------------
>
> /*+
> 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