Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor().
I’ve just seen a note on Rob van Wijk’s blog where he investigates many more format options that can be used with the function.
When I tried to add a comment to the blog listing a couple more of the options that I knew, I found that I couldn’t do so without creating a google or blogger account. So I’ve posted them here, and I’d be happy for someone with a suitable acccount to add them to Rob’s blog so that they’re all in the same place.
‘All’: Prints the Query block/Object Alias section, the Predicate information, and the Column Projection information after the basic plan.
‘Advanced’: as for ‘All’, but also include the Outline information (the set of hints that will reproduce the plan) and the peeked bind variables used to optimise the query.
‘Outline’: prints just the Outline information and the Predicate information after the basic plan.
I got the ‘Advanced’ one from one of the Oak Table members and guessed the ‘Outline’ one when I saw that it was possible to get the outline from the advanced output.
Update [Sept 2009] – I’ve just discovered another option (which should have been obvious really – especially given my comment about how I discovered the “outline” option)
‘Projection’: Print the “column projection” information. It’s a little obscure in places, but it tells you which columns have been passed to their parent by each line – and the size of those columns.
Update [Jan 2021] – A couple more options that are relevant to more recent versions of Oracle.
‘metrics’: Reports the directive id for any SQL Plan Directives that have been used to generate the plan. (See also MOS note 2390551.1
‘hint_report’, ‘hint_report_used’, ‘hint_report_unused’: the first reports the fate of all the hints you included in the SQL, the latter report only the used or unused (respectively) hint. Unused hints may also show the error that made a hint unusable. (See also Franck Pachot’s blog note on the hint report)
‘qbregistry’: reports the query block registry information – but it’s basically unreadable in 19c. Franck Pachot (again) has a not on an improvement in 20c which is the qbregistry_graph
‘adaptive’: reports the whole of an adaptive plan, marking inactive operations with a ‘-‘ and showing some “STATISTICS_COLLECTOR” operations
‘all_shards’: presumably supplies information about sharded objects or the handling of shards.
Oracle Scratchpad 
Oracle documentation explain about ‘ALL’ parameter, but not mentioned any thing about ‘Advanced’ and ‘Outline’. ORACLE_HOME/rdbms/admin/dbmsxpln.sql file has many good example for different procedure/ function and there parameters.
Nice to know 2 more parameter of display_cursor(), Thanks
Comment by Virag Sharma — March 6, 2008 @ 10:41 am GMT Mar 6,2008 |
Why do you suppose Oracle are Parameterising their functions in such an unusual way?
Are they deliberately attempting to make performance tuning the obfuscated art of a closed society?
Why not add these options as pseudo-boolean type parameters and atleast allow them to be revealed by “describe” – if keeping the documentation up to date is too onerous?
Comment by Basil — March 6, 2008 @ 2:56 pm GMT Mar 6,2008 |
Thanks Jonathan for letting me know and for making the list more complete.
I’ve added a small update section because of it.
Regards,
Rob.
Comment by Rob van Wijk — March 6, 2008 @ 10:36 pm GMT Mar 6,2008 |
Basil,
I don’t think they’re trying to make life difficult. It’s probably more a case of “and here’s another good idea” and a quick bolt-on. Once you start doing it one way, it’s hard to re-engineer.
Personally I’d love it if the Statspack code allowed you to select which bit of functionality went into the snapshot by setting boolean flags – as it is I have to hack the code to get just the bits I want. (But at least the source is there for hacking).
Comment by Jonathan Lewis — March 11, 2008 @ 6:34 am GMT Mar 11,2008 |
At least we have access to the spreport.sql script and can query the PERFSTAT schema
so that we can design modified versions of spreport.sql
Can’t do the same with awrrpt.sql at all ! It just calls a stored procedure which
“automagically” generates the report for us.
(BTW, have you used the “NO_OPTIONS=8” to “ENABLE_ADDM”, modifying awrrpti.sql ?
Hemant
Comment by Hemant K Chitale — March 12, 2008 @ 7:20 am GMT Mar 12,2008 |
Hi Jonathan,
Is there a way to check which step of a SQL execution plan currently being executed?
If we can get timing prediction on it that will be even better.
This will be very helpful for long running sqls with big execution plan and multi table joins etc.
Regards,
Darshan
Comment by Darshan — June 13, 2009 @ 7:27 pm BST Jun 13,2009 |
Darshan,
There are “approximations” that you can try.
If you know the steps of the plan you could keep checking V$session_wait for the session to check for calls related to disk I/O – this may give you some clues.
If you keep an eye on v$session_longops this may tell you about things like tablescans, sorts and other long operations – but again it’s just clues, not firm information. Gints Plivna has a good article on v$session_longops, and Hemant Chitale has a nice example of the potential traps.
But if you’re on 11g, there’s an option to do real-time monitoring of SQL statements. Greg Rahn wrote up an example of this last year.
Comment by Jonathan Lewis — June 14, 2009 @ 6:36 pm BST Jun 14,2009 |
Hi Jonathan,
Thanks for the knowledge and links you shared.
Do we have any information documented somewhere with examples on V$SQL_WORKAREA views? How one can leverage information in these.
I have seen these views are populated with some steps of the execution plan underway for a session id. Again it looks like it covers only few cases.
Regards,
Darshan
Comment by Darshan — June 16, 2009 @ 9:56 pm BST Jun 16,2009 |
Darshan,
The view v$sql_workarea is populated only for lines of execution plans (v$sql_plan) that relate to workarea usage, such as hash joins and sorts. If you’re running 9i and query v$sql_plan, you can do an outer join to v$sql_workarea to get information the most recent and cumulative use made of work areas. But in 10g it’s much easier to get this information with a call to dbms_xplan.display_cursor(). I’ve described this function in a few previous posts, commenting particularly on the hint /*+ gather_plan_statistics */ – but the work area information is always reported by this function – even if you haven’t used the hint.
Comment by Jonathan Lewis — June 20, 2009 @ 5:34 pm BST Jun 20,2009 |
outline is very useful, problem is how to make sense of the output produced by outline for a very complex query – the problem remains same , for example (allow me to post a long explain plan output I got for a complex query with outline option ).
Plan hash value: 2973119928 ------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 583 | 27814 (1)| 00:01:32 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 81 | 15 (0)| 00:00:01 | | 3 | MERGE JOIN CARTESIAN | | 1 | 55 | 13 (0)| 00:00:01 | | 4 | MERGE JOIN CARTESIAN | | 1 | 47 | 6 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 18 | 4 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 18 | 1 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 11 | 0 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | 4 | 0 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 7 | 0 (0)| 00:00:01 | |* 10 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 7 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | FND_PRODUCT_GROUPS | 1 | | 3 (0)| 00:00:01 | | 13 | BUFFER SORT | | 1 | 29 | 3 (0)| 00:00:01 | |* 14 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 29 | 2 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 2 | | 1 (0)| 00:00:01 | | 16 | BUFFER SORT | | 144 | 1152 | 11 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | GL_SETS_OF_BOOKS | 144 | 1152 | 7 (0)| 00:00:01 | |* 18 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)| 00:00:01 | |* 19 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 26 | 2 (0)| 00:00:01 | |* 20 | FILTER | | | | | | | 21 | MERGE JOIN CARTESIAN | | 1 | 583 | 27809 (1)| 00:01:32 | | 22 | NESTED LOOPS | | | | | | | 23 | NESTED LOOPS | | 1 | 581 | 27806 (1)| 00:01:32 | | 24 | NESTED LOOPS | | 1 | 566 | 27805 (1)| 00:01:32 | | 25 | NESTED LOOPS | | 1 | 544 | 27800 (1)| 00:01:32 | | 26 | NESTED LOOPS | | 1 | 538 | 27799 (1)| 00:01:32 | | 27 | NESTED LOOPS | | 1 | 528 | 27798 (1)| 00:01:32 | | 28 | NESTED LOOPS | | 1 | 522 | 27797 (1)| 00:01:32 | | 29 | NESTED LOOPS | | 1 | 512 | 27796 (1)| 00:01:32 | | 30 | NESTED LOOPS | | 1 | 479 | 27794 (1)| 00:01:32 | | 31 | NESTED LOOPS | | 1 | 468 | 27792 (1)| 00:01:32 | | 32 | NESTED LOOPS | | 1 | 452 | 27790 (1)| 00:01:32 | | 33 | NESTED LOOPS | | 1 | 417 | 27785 (1)| 00:01:32 | | 34 | NESTED LOOPS | | 1 | 413 | 27783 (1)| 00:01:32 | | 35 | NESTED LOOPS | | 1 | 405 | 27782 (1)| 00:01:32 | | 36 | NESTED LOOPS | | 1 | 377 | 27780 (1)| 00:01:32 | | 37 | NESTED LOOPS | | 1 | 370 | 27780 (1)| 00:01:32 | | 38 | NESTED LOOPS | | 1 | 341 | 27778 (1)| 00:01:32 | | 39 | NESTED LOOPS | | 1 | 334 | 27777 (1)| 00:01:32 | | 40 | NESTED LOOPS | | 1 | 268 | 27771 (1)| 00:01:32 | | 41 | NESTED LOOPS | | 1 | 250 | 27770 (1)| 00:01:32 | |* 42 | HASH JOIN | | 1 | 218 | 27769 (1)| 00:01:32 | |* 43 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 29 | 10 (0)| 00:00:01 | |* 44 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_IX1 | 43 | | 1 (0)| 00:00:01 | | 45 | NESTED LOOPS | | | | | | | 46 | NESTED LOOPS | | 112 | 21168 | 27759 (1)| 00:01:32 | | 47 | NESTED LOOPS | | 3042 | 383K| 12506 (1)| 00:00:42 | | 48 | NESTED LOOPS | | 1 | 79 | 12 (0)| 00:00:01 | | 49 | NESTED LOOPS | | 1 | 61 | 11 (0)| 00:00:01 | |* 50 | TABLE ACCESS BY INDEX ROWID| HR_ORGANIZATION_INFORMATION | 1 | 29 | 10 (0)| 00:00:01 | |* 51 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_IX1 | 43 | | 1 (0)| 00:00:01 | | 52 | TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS_TL | 1 | 32 | 1 (0)| 00:00:01 | |* 53 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 18 | 1 (0)| 00:00:01 | | 55 | TABLE ACCESS BY INDEX ROWID | PO_HEADERS_ALL | 15151 | 739K| 12494 (1)| 00:00:42 | |* 56 | INDEX RANGE SCAN | GEMS_PO_HEADERS_ALL_N99 | 15151 | | 97 (2)| 00:00:01 | |* 57 | INDEX RANGE SCAN | OE_ORDER_HEADERS_N5 | 3 | | 2 (0)| 00:00:01 | |* 58 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 60 | 5 (0)| 00:00:01 | | 59 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 32 | 1 (0)| 00:00:01 | |* 60 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| 00:00:01 | |* 61 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 18 | 1 (0)| 00:00:01 | |* 62 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 66 | 6 (0)| 00:00:01 | |* 63 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 8 | | 2 (0)| 00:00:01 | |* 64 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 7 | 1 (0)| 00:00:01 | |* 65 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 | |* 66 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 29 | 2 (0)| 00:00:01 | |* 67 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 2 | | 1 (0)| 00:00:01 | |* 68 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 7 | 0 (0)| 00:00:01 | | 69 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 28 | 2 (0)| 00:00:01 | |* 70 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)| 00:00:01 | | 71 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 8 | 1 (0)| 00:00:01 | |* 72 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)| 00:00:01 | |* 73 | INDEX FULL SCAN | GL_SETS_OF_BOOKS_U2 | 1 | 4 | 1 (0)| 00:00:01 | |* 74 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 1 | 35 | 5 (0)| 00:00:01 | |* 75 | INDEX RANGE SCAN | PO_LINES_U2 | 6 | | 2 (0)| 00:00:01 | | 76 | TABLE ACCESS BY INDEX ROWID | PO_VENDOR_SITES_ALL | 1 | 16 | 2 (0)| 00:00:01 | |* 77 | INDEX UNIQUE SCAN | PO_VENDOR_SITES_U1 | 1 | | 1 (0)| 00:00:01 | | 78 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 11 | 2 (0)| 00:00:01 | |* 79 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 1 (0)| 00:00:01 | | 80 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 33 | 2 (0)| 00:00:01 | |* 81 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 1 (0)| 00:00:01 | | 82 | TABLE ACCESS BY INDEX ROWID | PO_SYSTEM_PARAMETERS_ALL | 1 | 10 | 1 (0)| 00:00:01 | |* 83 | INDEX UNIQUE SCAN | PO_SYSTEM_PARAMETERS_U1 | 1 | | 0 (0)| 00:00:01 | |* 84 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | 6 | 1 (0)| 00:00:01 | | 85 | TABLE ACCESS BY INDEX ROWID | PO_SYSTEM_PARAMETERS_ALL | 1 | 10 | 1 (0)| 00:00:01 | |* 86 | INDEX UNIQUE SCAN | PO_SYSTEM_PARAMETERS_U1 | 1 | | 0 (0)| 00:00:01 | |* 87 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | 6 | 1 (0)| 00:00:01 | |* 88 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL | 5 | 110 | 5 (0)| 00:00:01 | |* 89 | INDEX RANGE SCAN | PO_LINE_LOCATIONS_N1 | 5 | | 2 (0)| 00:00:01 | |* 90 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)| 00:00:01 | |* 91 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 15 | 1 (0)| 00:00:01 | | 92 | BUFFER SORT | | 1 | 2 | 27808 (1)| 00:01:32 | | 93 | TABLE ACCESS FULL | FND_PRODUCT_GROUPS | 1 | 2 | 3 (0)| 00:00:01 | | 94 | NESTED LOOPS | | 1 | 46 | 5 (0)| 00:00:01 | | 95 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUE_SETS | 1 | 28 | 2 (0)| 00:00:01 | |* 96 | INDEX UNIQUE SCAN | FND_FLEX_VALUE_SETS_U2 | 1 | | 1 (0)| 00:00:01 | |* 97 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 18 | 3 (0)| 00:00:01 | |* 98 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$FC4A3BA3 8 - SEL$FC4A3BA3 / MP@SEL$3 9 - SEL$FC4A3BA3 / HAOTL@SEL$4 10 - SEL$FC4A3BA3 / HAO@SEL$4 11 - SEL$FC4A3BA3 / HAO@SEL$4 12 - SEL$FC4A3BA3 / FPG@SEL$3 14 - SEL$FC4A3BA3 / HOI1@SEL$3 15 - SEL$FC4A3BA3 / HOI1@SEL$3 17 - SEL$FC4A3BA3 / GSOB@SEL$3 18 - SEL$FC4A3BA3 / HOI2@SEL$3 19 - SEL$FC4A3BA3 / HOI2@SEL$3 20 - SEL$426BD430 43 - SEL$426BD430 / O2@SEL$5 44 - SEL$426BD430 / O2@SEL$5 50 - SEL$426BD430 / O2@SEL$8 51 - SEL$426BD430 / O2@SEL$8 52 - SEL$426BD430 / OTL@SEL$8 53 - SEL$426BD430 / OTL@SEL$8 54 - SEL$426BD430 / O3@SEL$8 55 - SEL$426BD430 / POH@SEL$1 56 - SEL$426BD430 / POH@SEL$1 57 - SEL$426BD430 / OEH@SEL$1 58 - SEL$426BD430 / OEH@SEL$1 59 - SEL$426BD430 / OTL@SEL$5 60 - SEL$426BD430 / OTL@SEL$5 61 - SEL$426BD430 / O3@SEL$5 62 - SEL$426BD430 / OEL@SEL$1 63 - SEL$426BD430 / OEL@SEL$1 64 - SEL$426BD430 / HAO@SEL$7 65 - SEL$426BD430 / HAO@SEL$7 66 - SEL$426BD430 / HOI1@SEL$6 67 - SEL$426BD430 / HOI1@SEL$6 68 - SEL$426BD430 / HAOTL@SEL$7 69 - SEL$426BD430 / HOI2@SEL$6 70 - SEL$426BD430 / HOI2@SEL$6 71 - SEL$426BD430 / MP@SEL$6 72 - SEL$426BD430 / MP@SEL$6 73 - SEL$426BD430 / GSOB@SEL$6 74 - SEL$426BD430 / POL@SEL$1 75 - SEL$426BD430 / POL@SEL$1 76 - SEL$426BD430 / POV@SEL$1 77 - SEL$426BD430 / POV@SEL$1 78 - SEL$426BD430 / CUS@SEL$1 79 - SEL$426BD430 / CUS@SEL$1 80 - SEL$426BD430 / PRT@SEL$1 81 - SEL$426BD430 / PRT@SEL$1 82 - SEL$426BD430 / PSP2@SEL$1 83 - SEL$426BD430 / PSP2@SEL$1 84 - SEL$426BD430 / CCID2@SEL$1 85 - SEL$426BD430 / PSP1@SEL$1 86 - SEL$426BD430 / PSP1@SEL$1 87 - SEL$426BD430 / CCID1@SEL$1 88 - SEL$426BD430 / POLL@SEL$1 89 - SEL$426BD430 / POLL@SEL$1 90 - SEL$426BD430 / PAR2@SEL$1 91 - SEL$426BD430 / PAR2@SEL$1 93 - SEL$426BD430 / FPG@SEL$6 94 - SEL$9 95 - SEL$9 / A@SEL$9 96 - SEL$9 / A@SEL$9 97 - SEL$9 / B@SEL$9 98 - SEL$9 / B@SEL$9 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA NLJ_BATCHING(@"SEL$FC4A3BA3" "HOI2"@"SEL$3") USE_NL(@"SEL$FC4A3BA3" "HOI2"@"SEL$3") USE_MERGE_CARTESIAN(@"SEL$FC4A3BA3" "GSOB"@"SEL$3") USE_MERGE_CARTESIAN(@"SEL$FC4A3BA3" "HOI1"@"SEL$3") USE_NL(@"SEL$FC4A3BA3" "FPG"@"SEL$3") USE_NL(@"SEL$FC4A3BA3" "HAO"@"SEL$4") USE_NL(@"SEL$FC4A3BA3" "HAOTL"@"SEL$4") LEADING(@"SEL$FC4A3BA3" "MP"@"SEL$3" "HAOTL"@"SEL$4" "HAO"@"SEL$4" "FPG"@"SEL$3" "HOI1"@"SEL$3" "GSOB"@"SEL$3" "HOI2"@"SEL$3") INDEX(@"SEL$FC4A3BA3" "HOI2"@"SEL$3" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) FULL(@"SEL$FC4A3BA3" "GSOB"@"SEL$3") INDEX_RS_ASC(@"SEL$FC4A3BA3" "HOI1"@"SEL$3" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) FULL(@"SEL$FC4A3BA3" "FPG"@"SEL$3") INDEX_RS_ASC(@"SEL$FC4A3BA3" "HAO"@"SEL$4" ("HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")) INDEX(@"SEL$FC4A3BA3" "HAOTL"@"SEL$4" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE")) INDEX(@"SEL$FC4A3BA3" "MP"@"SEL$3" ("MTL_PARAMETERS"."ORGANIZATION_ID")) USE_NL(@"SEL$9" "B"@"SEL$9") LEADING(@"SEL$9" "A"@"SEL$9" "B"@"SEL$9") INDEX_RS_ASC(@"SEL$9" "B"@"SEL$9" ("FND_FLEX_VALUES"."FLEX_VALUE_SET_ID" "FND_FLEX_VALUES"."FLEX_VALUE")) INDEX_RS_ASC(@"SEL$9" "A"@"SEL$9" ("FND_FLEX_VALUE_SETS"."FLEX_VALUE_SET_NAME")) SWAP_JOIN_INPUTS(@"SEL$426BD430" "O2"@"SEL$5") USE_MERGE_CARTESIAN(@"SEL$426BD430" "FPG"@"SEL$6") NLJ_BATCHING(@"SEL$426BD430" "PAR2"@"SEL$1") USE_NL(@"SEL$426BD430" "PAR2"@"SEL$1") USE_NL(@"SEL$426BD430" "POLL"@"SEL$1") USE_NL(@"SEL$426BD430" "CCID1"@"SEL$1") USE_NL(@"SEL$426BD430" "PSP1"@"SEL$1") USE_NL(@"SEL$426BD430" "CCID2"@"SEL$1") USE_NL(@"SEL$426BD430" "PSP2"@"SEL$1") USE_NL(@"SEL$426BD430" "PRT"@"SEL$1") USE_NL(@"SEL$426BD430" "CUS"@"SEL$1") USE_NL(@"SEL$426BD430" "POV"@"SEL$1") USE_NL(@"SEL$426BD430" "POL"@"SEL$1") USE_NL(@"SEL$426BD430" "GSOB"@"SEL$6") USE_NL(@"SEL$426BD430" "MP"@"SEL$6") USE_NL(@"SEL$426BD430" "HOI2"@"SEL$6") USE_NL(@"SEL$426BD430" "HAOTL"@"SEL$7") USE_NL(@"SEL$426BD430" "HOI1"@"SEL$6") USE_NL(@"SEL$426BD430" "HAO"@"SEL$7") USE_NL(@"SEL$426BD430" "OEL"@"SEL$1") USE_NL(@"SEL$426BD430" "O3"@"SEL$5") USE_NL(@"SEL$426BD430" "OTL"@"SEL$5") USE_HASH(@"SEL$426BD430" "O2"@"SEL$5") NLJ_BATCHING(@"SEL$426BD430" "OEH"@"SEL$1") USE_NL(@"SEL$426BD430" "OEH"@"SEL$1") USE_NL(@"SEL$426BD430" "POH"@"SEL$1") USE_NL(@"SEL$426BD430" "O3"@"SEL$8") USE_NL(@"SEL$426BD430" "OTL"@"SEL$8") LEADING(@"SEL$426BD430" "O2"@"SEL$8" "OTL"@"SEL$8" "O3"@"SEL$8" "POH"@"SEL$1" "OEH"@"SEL$1" "O2"@"SEL$5" "OTL"@"SEL$5" "O3"@"SEL$5" "OEL"@"SEL$1" "HAO"@"SEL$7" "HOI1"@"SEL$6" "HAOTL"@"SEL$7" "HOI2"@"SEL$6" "MP"@"SEL$6" "GSOB"@"SEL$6" "POL"@"SEL$1" "POV"@"SEL$1" "CUS"@"SEL$1" "PRT"@"SEL$1" "PSP2"@"SEL$1" "CCID2"@"SEL$1" "PSP1"@"SEL$1" "CCID1"@"SEL$1" "POLL"@"SEL$1" "PAR2"@"SEL$1" "FPG"@"SEL$6") FULL(@"SEL$426BD430" "FPG"@"SEL$6") INDEX(@"SEL$426BD430" "PAR2"@"SEL$1" ("MTL_PARAMETERS"."ORGANIZATION_ID")) INDEX_RS_ASC(@"SEL$426BD430" "POLL"@"SEL$1" ("PO_LINE_LOCATIONS_ALL"."PO_LINE_ID" "PO_LINE_LOCATIONS_ALL"."SHIPMENT_TYPE")) INDEX(@"SEL$426BD430" "CCID1"@"SEL$1" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID")) INDEX_RS_ASC(@"SEL$426BD430" "PSP1"@"SEL$1" ("PO_SYSTEM_PARAMETERS_ALL"."ORG_ID")) INDEX(@"SEL$426BD430" "CCID2"@"SEL$1" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID")) INDEX_RS_ASC(@"SEL$426BD430" "PSP2"@"SEL$1" ("PO_SYSTEM_PARAMETERS_ALL"."ORG_ID")) INDEX_RS_ASC(@"SEL$426BD430" "PRT"@"SEL$1" ("HZ_PARTIES"."PARTY_ID")) INDEX_RS_ASC(@"SEL$426BD430" "CUS"@"SEL$1" ("HZ_CUST_ACCOUNTS"."CUST_ACCOUNT_ID")) INDEX_RS_ASC(@"SEL$426BD430" "POV"@"SEL$1" ("PO_VENDOR_SITES_ALL"."VENDOR_SITE_ID")) INDEX_RS_ASC(@"SEL$426BD430" "POL"@"SEL$1" ("PO_LINES_ALL"."PO_HEADER_ID" "PO_LINES_ALL"."LINE_NUM")) INDEX(@"SEL$426BD430" "GSOB"@"SEL$6" ("GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")) INDEX_RS_ASC(@"SEL$426BD430" "MP"@"SEL$6" ("MTL_PARAMETERS"."ORGANIZATION_ID")) INDEX_RS_ASC(@"SEL$426BD430" "HOI2"@"SEL$6" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) INDEX(@"SEL$426BD430" "HAOTL"@"SEL$7" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE")) INDEX_RS_ASC(@"SEL$426BD430" "HOI1"@"SEL$6" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) INDEX_RS_ASC(@"SEL$426BD430" "HAO"@"SEL$7" ("HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")) INDEX_RS_ASC(@"SEL$426BD430" "OEL"@"SEL$1" ("OE_ORDER_LINES_ALL"."HEADER_ID")) INDEX(@"SEL$426BD430" "O3"@"SEL$5" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) INDEX_RS_ASC(@"SEL$426BD430" "OTL"@"SEL$5" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE")) INDEX_RS_ASC(@"SEL$426BD430" "O2"@"SEL$5" ("HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION1")) INDEX(@"SEL$426BD430" "OEH"@"SEL$1" ("OE_ORDER_HEADERS_ALL"."CUST_PO_NUMBER")) INDEX_RS_ASC(@"SEL$426BD430" "POH"@"SEL$1" ("PO_HEADERS_ALL"."ORG_ID" "PO_HEADERS_ALL"."TYPE_LOOKUP_CODE" "PO_HEADERS_ALL"."LAST_UPDATE_DATE")) INDEX(@"SEL$426BD430" "O3"@"SEL$8" ("HR_ORGANIZATION_INFORMATION"."ORGANIZATION_ID" "HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION_CONTEXT")) INDEX_RS_ASC(@"SEL$426BD430" "OTL"@"SEL$8" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE")) INDEX_RS_ASC(@"SEL$426BD430" "O2"@"SEL$8" ("HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION1")) OUTLINE(@"SEL$7") OUTLINE(@"SEL$6") OUTLINE(@"SEL$5") OUTLINE(@"SEL$8") OUTLINE(@"SEL$4") OUTLINE(@"SEL$3") MERGE(@"SEL$7") OUTLINE(@"SEL$68B588A0") ELIMINATE_JOIN(@"SEL$5" "O"@"SEL$5") OUTLINE(@"SEL$5E93DB58") ELIMINATE_JOIN(@"SEL$8" "O"@"SEL$8") OUTLINE(@"SEL$5C8CD9A3") OUTLINE(@"SEL$1") MERGE(@"SEL$4") OUTLINE(@"SEL$07BDC5B4") OUTLINE(@"SEL$2") MERGE(@"SEL$68B588A0") MERGE(@"SEL$5E93DB58") MERGE(@"SEL$5C8CD9A3") OUTLINE(@"SEL$14CFE8FF") MERGE(@"SEL$07BDC5B4") OUTLINE(@"SEL$641071AC") ELIMINATE_JOIN(@"SEL$14CFE8FF" "PAR1"@"SEL$1") OUTLINE_LEAF(@"SEL$426BD430") OUTLINE_LEAF(@"SEL$9") ELIMINATE_JOIN(@"SEL$641071AC" "SOB"@"SEL$2") OUTLINE_LEAF(@"SEL$FC4A3BA3") ALL_ROWS OPT_PARAM('_b_tree_bitmap_plans' 'false') DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Comment by Ajeet — January 18, 2012 @ 2:58 pm GMT Jan 18,2012 |
Ajeet,
I tried to make your output readable, but lost the predicate section while doing so.
If you want to try posting the example again, make sure you do “set trimspool on” before generating the output. Then start the output with “sourcecode” and end it with “/sourcecode” (but using square brackets instead of quote marks).
Comment by Jonathan Lewis — January 19, 2012 @ 8:28 am GMT Jan 19,2012 |
Jonathan
Thanks for your help, I am posting the pedicate information (after taking the output again using set trimspool on), as all other infomration as posted above are good.so I am posting only the predicate information. what i want to understand is the join order of the different tables, and anything which suggest that this plan is not optimal and how to really fix that. this will be a great help to everyone who has to read thru large plan outputs and then make sense of it.
Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("MP"."ORGANIZATION_ID"=:B1) 9 - access("HAOTL"."ORGANIZATION_ID"=:B1 AND "HAOTL"."LANGUAGE"=USERENV('LANG')) filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANI ZATION_ID"))='TRUE') 10 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_BU SINESS_GROUP_ID"())) 11 - access("HAO"."ORGANIZATION_ID"=:B1) 14 - filter("HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFORMATION1"='INV') 15 - access("HOI1"."ORGANIZATION_ID"=:B1) filter("HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS') 18 - access("HOI2"."ORGANIZATION_ID"=:B1) filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information') 19 - filter("HOI2"."ORG_INFORMATION1"=TO_CHAR("GSOB"."SET_OF_BOOKS_ID")) 20 - filter( NOT EXISTS (SELECT 0 FROM "APPS"."FND_FLEX_VALUES" "B","APPS"."FND_FLEX_VALUE_SETS" "A" WHERE "A"."FLEX_VALUE_SET_NAME"='GEMS_GPO_EXCLUDED_ORGS' AND "B"."FLEX_VALUE"=:B1 AND "A"."FLEX_VALUE_SET_ID"="B"."FLEX_VALUE_SET_ID" AND "B"."ENABLED_FLAG"='Y' AND NVL("B"."END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!)) 42 - access("OEH"."SOLD_FROM_ORG_ID"="O2"."ORGANIZATION_ID") 43 - filter("O2"."ORG_INFORMATION2"='Y' AND "O2"."ORG_INFORMATION_CONTEXT"||''='CLASS') 44 - access("O2"."ORG_INFORMATION1"='OPERATING_UNIT') 50 - filter("O2"."ORG_INFORMATION2"='Y' AND "O2"."ORG_INFORMATION_CONTEXT"||''='CLASS') 51 - access("O2"."ORG_INFORMATION1"='OPERATING_UNIT') 53 - access("O2"."ORGANIZATION_ID"="OTL"."ORGANIZATION_ID" AND "OTL"."LANGUAGE"=USERENV('LANG')) 54 - access("O3"."ORGANIZATION_ID"="O2"."ORGANIZATION_ID" AND "O3"."ORG_INFORMATION_CONTEXT"='Operating Unit Information') 56 - access("O2"."ORGANIZATION_ID"="POH"."ORG_ID" AND "POH"."TYPE_LOOKUP_CODE"='STANDARD') 57 - access("POH"."SEGMENT1"="OEH"."CUST_PO_NUMBER") filter("OEH"."CUST_PO_NUMBER" IS NOT NULL) 58 - filter(TO_CHAR("POH"."ORG_ID")=SUBSTR("OEH"."ORIG_SYS_DOCUMENT_REF",INSTR("OEH"."ORIG_SYS_DOCUMENT_REF",'.',1)+1,INSTR("OEH "."ORIG_SYS_DOCUMENT_REF",'.',1,2)-1-INSTR("OEH"."ORIG_SYS_DOCUMENT_REF",'.',1)) AND "POH"."ORG_ID"<>"OEH"."ORG_ID") 60 - access("O2"."ORGANIZATION_ID"="OTL"."ORGANIZATION_ID" AND "OTL"."LANGUAGE"=USERENV('LANG')) 61 - access("O3"."ORGANIZATION_ID"="O2"."ORGANIZATION_ID" AND "O3"."ORG_INFORMATION_CONTEXT"='Operating Unit Information') 62 - filter("OEL"."SHIPPED_QUANTITY" IS NOT NULL AND TRUNC(NVL("OEL"."ACTUAL_SHIPMENT_DATE",TO_DATE(' 2011-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))>=TO_DATE(' 2011-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 63 - access("OEH"."HEADER_ID"="OEL"."HEADER_ID") 64 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_BU SINESS_GROUP_ID"())) 65 - access("HAO"."ORGANIZATION_ID"="OEL"."SHIP_FROM_ORG_ID") 66 - filter("HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFORMATION1"='INV') 67 - access("HAO"."ORGANIZATION_ID"="HOI1"."ORGANIZATION_ID") filter("HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS') 68 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID" AND "HAOTL"."LANGUAGE"=USERENV('LANG')) filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANI ZATION_ID"))='TRUE') 70 - access("HAO"."ORGANIZATION_ID"="HOI2"."ORGANIZATION_ID") filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information') 72 - access("HAO"."ORGANIZATION_ID"="MP"."ORGANIZATION_ID") filter("ORGANIZATION_ID"="OEL"."SHIP_FROM_ORG_ID") 73 - filter("HOI2"."ORG_INFORMATION1"=TO_CHAR("GSOB"."SET_OF_BOOKS_ID")) 74 - filter("POL"."ITEM_ID" IS NOT NULL AND "OEL"."INVENTORY_ITEM_ID"="POL"."ITEM_ID" AND ("POL"."VENDOR_PRODUCT_NUM"="OEL"."ORIG_SYS_LINE_REF" AND "POL"."VENDOR_PRODUCT_NUM" IS NOT NULL OR "POL"."VENDOR_PRODUCT_NUM"=TO_CHAR("OEH"."HEADER_ID"))) 75 - access("POH"."PO_HEADER_ID"="POL"."PO_HEADER_ID") 77 - access("POH"."VENDOR_SITE_ID"="POV"."VENDOR_SITE_ID") 79 - access("OEH"."SOLD_TO_ORG_ID"="CUS"."CUST_ACCOUNT_ID") 81 - access("CUS"."PARTY_ID"="PRT"."PARTY_ID") 83 - access("POH"."ORG_ID"="PSP2"."ORG_ID") 84 - access("PSP2"."ACCRUED_CODE_COMBINATION_ID"="CCID2"."CODE_COMBINATION_ID") 86 - access("OEH"."ORG_ID"="PSP1"."ORG_ID") 87 - access("PSP1"."ACCRUED_CODE_COMBINATION_ID"="CCID1"."CODE_COMBINATION_ID") 88 - filter("POLL"."QUANTITY_RECEIVED" IS NOT NULL) 89 - access("POLL"."PO_LINE_ID"="POL"."PO_LINE_ID") 90 - access("POLL"."SHIP_TO_ORGANIZATION_ID"="PAR2"."ORGANIZATION_ID") 91 - filter("PAR2"."ATTRIBUTE7"='PARTS') 96 - access("A"."FLEX_VALUE_SET_NAME"='GEMS_GPO_EXCLUDED_ORGS') 97 - filter("B"."ENABLED_FLAG"='Y' AND NVL("B"."END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!) 98 - access("A"."FLEX_VALUE_SET_ID"="B"."FLEX_VALUE_SET_ID" AND "B"."FLEX_VALUE"=:B1)Comment by Ajeet — January 19, 2012 @ 8:49 am GMT Jan 19,2012 |
The standard approach to a long plan is to think of it in pieces so, for example, the long chain of nested loop can be ignored to start with and your basic plan is this – which is much easier to understand.
------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 583 | 27814 (1)| 00:01:32 | |* 20 | FILTER | | | | | | | 21 | MERGE JOIN CARTESIAN | | 1 | 583 | 27809 (1)| 00:01:32 | | 22 | {BIG THING} | | | | | | | 92 | BUFFER SORT | | 1 | 2 | 27808 (1)| 00:01:32 | | 93 | TABLE ACCESS FULL | FND_PRODUCT_GROUPS | 1 | 2 | 3 (0)| 00:00:01 | | 94 | NESTED LOOPS | | 1 | 46 | 5 (0)| 00:00:01 | | 95 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUE_SETS | 1 | 28 | 2 (0)| 00:00:01 | |* 96 | INDEX UNIQUE SCAN | FND_FLEX_VALUE_SETS_U2 | 1 | | 1 (0)| 00:00:01 | |* 97 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 18 | 3 (0)| 00:00:01 | |* 98 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------------------Now we can go back to the cascading list of nested loops, and perhaps viewing it in isolation we decide that the hash join at line 42 should have been a nested loop into HR_ORGANIZATION_INFORMATION, So we search for the use_hash() hint that must be in the outline, and this is what we find:
USE_HASH(@"SEL$426BD430" "O2"@"SEL$5") SWAP_JOIN_INPUTS(@"SEL$426BD430" "O2"@"SEL$5") INDEX_RS_ASC(@"SEL$426BD430" "O2"@"SEL$5" ("HR_ORGANIZATION_INFORMATION"."ORG_INFORMATION1"))Without the original SQL I was a little lucky that there was only one hash join, it made it easy to see that the table had an alias of O2, which allowed me to find the index hint on the table. The fact that there is a swap_join_inputs() hint tells me that the position of the table in the plan output will drop just below oe_order_headers_all (it looks like this plan came from 11g with the NLJ_Batching features showing up at this point), and all I need do is change the hint from use_hash to use_nl, and change the index definition to one that would allow me to do an efficient nested loop into the table.
It’s not difficult (usually – but ANSI can make it much harder), it just takes patience and practice.
Comment by Jonathan Lewis — January 26, 2012 @ 10:23 pm GMT Jan 26,2012 |
Jonathan,
Thanks for wonderful explanaation as always. I was trying to replace the
USE_HASH(@”SEL$426BD430″ “O2″@”SEL$5”)
with
USE_NL(USE_HASH(“O2″@”SEL$5”)
and I observed that I have to give the alias name as appeared in outline , If I give just use_nl(O2) , this hint was ingored.
O2 table is part of a view..is that the reason for this.
Kindly elobarate a bit on this.
Regards
Ajeet
Comment by Ajeet — January 28, 2012 @ 5:06 pm GMT Jan 28,2012 |
Ajeet,
See https://jonathanlewis.wordpress.com/2007/06/25/qb_name/
Comment by Jonathan Lewis — January 28, 2012 @ 8:13 pm GMT Jan 28,2012 |