Oracle Scratchpad

March 6, 2008

dbms_xplan(3)

Filed under: dbms_xplan,Execution plans — Jonathan Lewis @ 8:01 am BST Mar 6,2008

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 parameter values 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 his 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 – I think it may have been Christian Antognini – 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.

15 Comments »

  1. 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 BST Mar 6,2008 | Reply

  2. 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 BST Mar 6,2008 | Reply

  3. 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 BST Mar 6,2008 | Reply

  4. 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 BST Mar 11,2008 | Reply

  5. 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 BST Mar 12,2008 | Reply

  6. 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 | Reply

    • 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 | Reply

  7. 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 | Reply

    • 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 | Reply

  8. 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 BST Jan 18,2012 | Reply

    • 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 BST Jan 19,2012 | Reply

      • 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 BST Jan 19,2012 | Reply

    • 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 BST Jan 26,2012 | Reply

  9. 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 BST Jan 28,2012 | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers