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@xxxxxxxxx>
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(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$2" "ORDERS"@"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@xxxxxxxxx> 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.
