Forum Stats

  • 3,828,117 Users
  • 2,260,865 Discussions
  • 7,897,483 Comments

Discussions

To improve the Execution Plan

Ranagal
Ranagal Member Posts: 639 Bronze Badge
edited May 8, 2019 5:53AM in SQL & PL/SQL

Hello Experts,

I have the below plan:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                       | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                |                              |      1 |        |     50 |00:00:17.06 |     367K|     55 |     55 |       |       |          |

|   1 |  TEMP TABLE TRANSFORMATION                      |                              |      1 |        |     50 |00:00:17.06 |     367K|     55 |     55 |       |       |          |

|   2 |   LOAD AS SELECT                                |                              |      1 |        |      0 |00:00:00.54 |   56725 |      0 |     53 |  1040K|  1040K|          |

|   3 |    TABLE ACCESS BY INDEX ROWID                  | OBJECTS                      |   7785 |      1 |   7785 |00:00:00.03 |    8150 |      0 |      0 |       |       |          |

|*  4 |     INDEX UNIQUE SCAN                           | PK_OBJECTS                   |   7785 |      1 |   7785 |00:00:00.01 |     360 |      0 |      0 |       |       |          |

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   7785 |      1 |   5507 |00:00:00.05 |   12182 |      0 |      0 |       |       |          |

|*  6 |     INDEX RANGE SCAN                            | UK_ATTR                      |   7785 |      1 |   5507 |00:00:00.03 |    9621 |      0 |      0 |       |       |          |

|*  7 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

|   8 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   7785 |      1 |   5507 |00:00:00.03 |   12182 |      0 |      0 |       |       |          |

|*  9 |     INDEX RANGE SCAN                            | UK_ATTR                      |   7785 |      1 |   5507 |00:00:00.02 |    9621 |      0 |      0 |       |       |          |

|* 10 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

|  11 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   1366 |      1 |   1366 |00:00:00.02 |    4592 |      0 |      0 |       |       |          |

|* 12 |     INDEX RANGE SCAN                            | IDX_ATTR_NDC_OBJECT_VALUE    |   1366 |      1 |   1366 |00:00:00.01 |    3227 |      0 |      0 |       |       |          |

|* 13 |      INDEX RANGE SCAN                           | NCI_NODES_COVERING_IDX       |   1366 |      1 |   1366 |00:00:00.01 |     595 |      0 |      0 |       |       |          |

|* 14 |    VIEW                                         |                              |      1 |     12 |   7785 |00:00:00.37 |   24156 |      0 |      0 |       |       |          |

|* 15 |     FILTER                                      |                              |      1 |        |   7891 |00:00:00.36 |   19564 |      0 |      0 |       |       |          |

|* 16 |      CONNECT BY WITH FILTERING                  |                              |      1 |        |  66127 |00:00:00.38 |   19126 |      0 |      0 |  7069K|  1062K| 6283K (0)|

|  17 |       TABLE ACCESS BY INDEX ROWID               | NODES                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

|* 18 |        INDEX UNIQUE SCAN                        | PK_NODES                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|  19 |       NESTED LOOPS                              |                              |      9 |     11 |  66126 |00:00:00.14 |   19119 |      0 |      0 |       |       |          |

|  20 |        CONNECT BY PUMP                          |                              |      9 |        |  66127 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

|* 21 |        TABLE ACCESS BY INDEX ROWID BATCHED      | NODES                        |  66127 |     11 |  66126 |00:00:00.16 |   19119 |      0 |      0 |       |       |          |

|* 22 |         INDEX RANGE SCAN                        | NCI_NODES_PARENT_NODE_ID     |  66127 |     11 |  67800 |00:00:00.08 |   12128 |      0 |      0 |       |       |          |

|  23 |         TABLE ACCESS BY INDEX ROWID             | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|* 24 |          INDEX UNIQUE SCAN                      | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

|  25 |       TABLE ACCESS BY INDEX ROWID               | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|* 26 |        INDEX UNIQUE SCAN                        | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

|* 27 |      TABLE ACCESS BY INDEX ROWID                | OBJECT_TYPES                 |    219 |      1 |      1 |00:00:00.01 |     438 |      0 |      0 |       |       |          |

|* 28 |       INDEX UNIQUE SCAN                         | PK_OBJECT_TYPES              |    219 |      1 |    219 |00:00:00.01 |     219 |      0 |      0 |       |       |          |

|  29 |   LOAD AS SELECT                                |                              |      1 |        |      0 |00:00:02.14 |   37636 |     53 |      2 |  1040K|  1040K|          |

|  30 |    TABLE ACCESS BY INDEX ROWID                  | OBJECTS                      |    316 |      1 |    316 |00:00:00.01 |     603 |      0 |      0 |       |       |          |

|* 31 |     INDEX UNIQUE SCAN                           | PK_OBJECTS                   |    316 |      1 |    316 |00:00:00.01 |     287 |      0 |      0 |       |       |          |

|  32 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |    316 |      1 |    316 |00:00:00.01 |     950 |      0 |      0 |       |       |          |

|* 33 |     INDEX RANGE SCAN                            | UK_ATTR                      |    316 |      1 |    316 |00:00:00.01 |     666 |      0 |      0 |       |       |          |

|* 34 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

|  35 |    HASH UNIQUE                                  |                              |      1 |    148 |    316 |00:00:02.14 |   37632 |     53 |      0 |  1041K|  1041K| 1363K (0)|

|* 36 |     FILTER                                      |                              |      1 |        |   5500 |00:00:03.34 |   36079 |     53 |      0 |       |       |          |

|  37 |      MERGE JOIN CARTESIAN                       |                              |      1 |    148 |   5114K|00:00:02.11 |   34055 |     53 |      0 |       |       |          |

|* 38 |       HASH JOIN                                 |                              |      1 |     12 |    657 |00:00:00.93 |   33998 |      0 |      0 |  1003K|  1003K|  739K (0)|

|  39 |        NESTED LOOPS                             |                              |      1 |     69 |    969 |00:00:00.31 |   20127 |      0 |      0 |       |       |          |

|  40 |         NESTED LOOPS                            |                              |      1 |    132 |    970 |00:00:00.31 |   19957 |      0 |      0 |       |       |          |

|  41 |          VIEW                                   |                              |      1 |     12 |    312 |00:00:00.32 |   19564 |      0 |      0 |       |       |          |

|* 42 |           FILTER                                |                              |      1 |        |    312 |00:00:00.32 |   19564 |      0 |      0 |       |       |          |

|* 43 |            CONNECT BY WITH FILTERING            |                              |      1 |        |  66127 |00:00:00.32 |   19126 |      0 |      0 |  6219K|  1010K| 5527K (0)|

|  44 |             TABLE ACCESS BY INDEX ROWID         | NODES                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

|* 45 |              INDEX UNIQUE SCAN                  | PK_NODES                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|  46 |             NESTED LOOPS                        |                              |      9 |     11 |  66126 |00:00:00.13 |   19119 |      0 |      0 |       |       |          |

|  47 |              CONNECT BY PUMP                    |                              |      9 |        |  66127 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

|* 48 |              TABLE ACCESS BY INDEX ROWID BATCHED| NODES                        |  66127 |     11 |  66126 |00:00:00.15 |   19119 |      0 |      0 |       |       |          |

|* 49 |               INDEX RANGE SCAN                  | NCI_NODES_PARENT_NODE_ID     |  66127 |     11 |  67800 |00:00:00.08 |   12128 |      0 |      0 |       |       |          |

|  50 |               TABLE ACCESS BY INDEX ROWID       | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|* 51 |                INDEX UNIQUE SCAN                | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

|  52 |             TABLE ACCESS BY INDEX ROWID         | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|* 53 |              INDEX UNIQUE SCAN                  | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

|* 54 |            TABLE ACCESS BY INDEX ROWID          | OBJECT_TYPES                 |    219 |      1 |      1 |00:00:00.01 |     438 |      0 |      0 |       |       |          |

|* 55 |             INDEX UNIQUE SCAN                   | PK_OBJECT_TYPES              |    219 |      1 |    219 |00:00:00.01 |     219 |      0 |      0 |       |       |          |

|* 56 |          INDEX RANGE SCAN                       | NCI_NODES_PARENT_NODE_ID     |    312 |     11 |    970 |00:00:00.01 |     393 |      0 |      0 |       |       |          |

|* 57 |         TABLE ACCESS BY INDEX ROWID             | NODES                        |    970 |      6 |    969 |00:00:00.01 |     170 |      0 |      0 |       |       |          |

|* 58 |        VIEW                                     | index$_join$_065             |      1 |     42 |      4 |00:00:00.34 |   13871 |      0 |      0 |       |       |          |

|* 59 |         HASH JOIN                               |                              |      1 |        |    434 |00:00:00.01 |      12 |      0 |      0 |  1519K|  1519K| 1591K (0)|

|  60 |          INDEX FAST FULL SCAN                   | PK_OBJECT_TYPES              |      1 |     42 |    434 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

|  61 |          INDEX FAST FULL SCAN                   | UK_IDX_OBJECT_TYPE_NDC       |      1 |     42 |    434 |00:00:00.01 |       8 |      0 |      0 |       |       |          |

|  62 |       BUFFER SORT                               |                              |    657 |     12 |   5114K|00:00:00.62 |      57 |     53 |      0 |   372K|   372K|  330K (0)|

|  63 |        VIEW                                     |                              |      1 |     12 |   7785 |00:00:00.01 |      57 |     53 |      0 |       |       |          |

|  64 |         TABLE ACCESS FULL                       | SYS_TEMP_0FD9D74CF_1445481D  |      1 |     12 |   7785 |00:00:00.01 |      57 |     53 |      0 |       |       |          |

|  65 |      TABLE ACCESS BY INDEX ROWID                | OBJECTS                      |    657 |      1 |    657 |00:00:00.01 |    1068 |      0 |      0 |       |       |          |

|* 66 |       INDEX UNIQUE SCAN                         | PK_OBJECTS                   |    657 |      1 |    657 |00:00:00.01 |     410 |      0 |      0 |       |       |          |

|  67 |      TABLE ACCESS BY INDEX ROWID BATCHED        | ATTRIBUTES                   |    318 |      1 |    318 |00:00:00.01 |     956 |      0 |      0 |       |       |          |

|* 68 |       INDEX RANGE SCAN                          | UK_ATTR                      |    318 |      1 |    318 |00:00:00.01 |     670 |      0 |      0 |       |       |          |

|* 69 |        TABLE ACCESS FULL                        | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

|  70 |   SORT GROUP BY                                 |                              |      1 |      1 |     50 |00:00:14.38 |     273K|      2 |      0 |   619K|   471K|  550K (0)|

|  71 |    VIEW                                         |                              |      1 |      1 |   4375 |00:00:13.12 |     273K|      2 |      0 |       |       |          |

|  72 |     HASH UNIQUE                                 |                              |      1 |      1 |   4375 |00:00:13.12 |     273K|      2 |      0 |  1186K|  1186K| 1395K (0)|

|  73 |      TABLE ACCESS BY INDEX ROWID                | OBJECTS                      |   4606 |      1 |   4606 |00:00:05.61 |   37088 |      0 |      0 |       |       |          |

|* 74 |       INDEX UNIQUE SCAN                         | PK_OBJECTS                   |   4606 |      1 |   4606 |00:00:05.59 |   32472 |      0 |      0 |       |       |          |

|* 75 |      HASH JOIN                                  |                              |      1 |      1 |   4375 |00:00:25.14 |     273K|      2 |      0 |  1410K|  1075K| 1420K (0)|

|  76 |       NESTED LOOPS                              |                              |      1 |      1 |   4375 |00:00:00.07 |   12952 |      2 |      0 |       |       |          |

|  77 |        NESTED LOOPS                             |                              |      1 |      2 |   4375 |00:00:00.07 |   12593 |      2 |      0 |       |       |          |

|  78 |         NESTED LOOPS                            |                              |      1 |      1 |   4375 |00:00:00.06 |   11761 |      2 |      0 |       |       |          |

|* 79 |          HASH JOIN                              |                              |      1 |      1 |   5500 |00:00:00.01 |      60 |      2 |      0 |  1321K|  1321K| 1840K (0)|

|  80 |           VIEW                                  |                              |      1 |     12 |   7785 |00:00:00.01 |      54 |      0 |      0 |       |       |          |

|  81 |            TABLE ACCESS FULL                    | SYS_TEMP_0FD9D74CF_1445481D  |      1 |     12 |   7785 |00:00:00.01 |      54 |      0 |      0 |       |       |          |

|  82 |           VIEW                                  |                              |      1 |    148 |    316 |00:00:00.01 |       6 |      2 |      0 |       |       |          |

|  83 |            TABLE ACCESS FULL                    | SYS_TEMP_0FD9D74D0_1445481D  |      1 |    148 |    316 |00:00:00.01 |       6 |      2 |      0 |       |       |          |

|  84 |          TABLE ACCESS BY INDEX ROWID BATCHED    | ATTRIBUTES                   |   5500 |      1 |   4375 |00:00:00.03 |   11701 |      0 |      0 |       |       |          |

|* 85 |           INDEX RANGE SCAN                      | IDX_ATTR_NDC_OBJECT_VALUE    |   5500 |      1 |   4375 |00:00:00.02 |    7353 |      0 |      0 |       |       |          |

|* 86 |         INDEX RANGE SCAN                        | NCI_ATTRIBUTE_VALUES_ATTR_ID |   4375 |      2 |   4375 |00:00:00.01 |     832 |      0 |      0 |       |       |          |

|  87 |        TABLE ACCESS BY INDEX ROWID              | ATTRIBUTE_VALUES             |   4375 |      2 |   4375 |00:00:00.01 |     359 |      0 |      0 |       |       |          |

|  88 |       VIEW                                      |                              |      1 |   1730 |   4606 |00:00:12.58 |     260K|      0 |      0 |       |       |          |

|* 89 |        FILTER                                   |                              |      1 |        |   4606 |00:00:00.05 |    2094 |      0 |      0 |       |       |          |

|* 90 |         CONNECT BY WITH FILTERING               |                              |      1 |        |   4922 |00:00:00.05 |    2037 |      0 |      0 |   478K|   448K|  424K (0)|

|  91 |          NESTED LOOPS                           |                              |      1 |    148 |    316 |00:00:00.01 |     953 |      0 |      0 |       |       |          |

|  92 |           NESTED LOOPS                          |                              |      1 |    148 |    316 |00:00:00.01 |     637 |      0 |      0 |       |       |          |

|  93 |            VIEW                                 | VW_NSO_1                     |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|  94 |             HASH UNIQUE                         |                              |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |  2170K|  2170K| 2528K (0)|

|  95 |              VIEW                               |                              |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|  96 |               TABLE ACCESS FULL                 | SYS_TEMP_0FD9D74D0_1445481D  |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|* 97 |            INDEX UNIQUE SCAN                    | PK_NODES                     |    316 |      1 |    316 |00:00:00.01 |     634 |      0 |      0 |       |       |          |

|  98 |           TABLE ACCESS BY INDEX ROWID           | NODES                        |    316 |      1 |    316 |00:00:00.01 |     316 |      0 |      0 |       |       |          |

|  99 |          NESTED LOOPS                           |                              |      2 |   1582 |   4606 |00:00:00.01 |    1081 |      0 |      0 |       |       |          |

| 100 |           CONNECT BY PUMP                       |                              |      2 |        |   4922 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

|*101 |           TABLE ACCESS BY INDEX ROWID BATCHED   | NODES                        |   4922 |     11 |   4606 |00:00:00.01 |    1081 |      0 |      0 |       |       |          |

|*102 |            INDEX RANGE SCAN                     | NCI_NODES_PARENT_NODE_ID     |   4922 |     11 |   4608 |00:00:00.01 |     950 |      0 |      0 |       |       |          |

| 103 |            TABLE ACCESS BY INDEX ROWID          | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|*104 |             INDEX UNIQUE SCAN                   | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

| 105 |          TABLE ACCESS BY INDEX ROWID            | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

|*106 |           INDEX UNIQUE SCAN                     | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

|*107 |         TABLE ACCESS BY INDEX ROWID             | OBJECT_TYPES                 |      3 |      1 |      1 |00:00:00.01 |      57 |      0 |      0 |       |       |          |

|*108 |          INDEX UNIQUE SCAN                      | PK_OBJECT_TYPES              |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I wanted to know what is wrong in here and how it can be improved ? Because I see a lot of difference in E - Rows and A - Rows

And please copy paste the plan to Notepad ++ for a better view. Formatting is not proper in the above one.

Thanks in advance,

Ranagal

John Thorton
«13

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 6, 2019 7:59AM
    Ranagal wrote:Hello Experts,I have the below plan:...I wanted to know what is wrong in here and how it can be improved ? Because I see a lot of difference in E - Rows and A - RowsAnd please copy paste the plan to Notepad ++ for a better view. Formatting is not proper in the above one.Thanks in advance,Ranagal

    You can fix the formatting yourself by using the advanced editor and changing the font to be a fixed width one (courier new is fine).

    You're missing the important predicate section from your plan and the query.

    Judging by the plan, you're using scalar subqueries all over the shop. But the main source of wrong cardinality estimations is by your recursive subqueries. To see what's important, you would look at where the time is going but due to the nesty loopy scalar subquery nature of your plan, there's been a lot of rounding error that makes it very difficult to use. I would guess this is because you used the gather_plan_statistics hint rather than setting your session statistics_level parameter to all. I would suggest you re-run the statement without the hint but with the altered session parameter, then grab the plan with execution statistics and copy and paste it here (in its entirety).

    PLEASE include the SQL.

    Freebie advice: it looks like you're using an Entity Attributes Value data model, this will never scale. I recommend you ditch it as soon as you can and use a proper data model.

    John Thorton
  • Ranagal
    Ranagal Member Posts: 639 Bronze Badge
    edited May 6, 2019 8:58AM

    Hi,

    Thanks for your initial comments. It's true that I used gather_plan_statistics hint to get the info about E-Rows and A-Rows. I'm quite not sure what you asked me to do as a next step. Can you please give me the steps so that I'll follow the same.

    Regards,

    Ranagal

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 6, 2019 9:50AM
    Ranagal wrote:Hi,Thanks for your initial comments. It's true that I used gather_plan_statistics hint to get the info about E-Rows and A-Rows. I'm quite not sure what you asked me to do as a next step. Can you please give me the steps so that I'll follow the same.Regards,Ranagal

    Alter session set statistics_level=all;

    Run query without the gather_plan_statistics hint.

    Grab the plan with execution statistics exactly how you did before. The statistics_level=all means Oracle will collect the row source execution statistics as before but it is less prone to rounding error.

  • Ranagal
    Ranagal Member Posts: 639 Bronze Badge
    edited May 6, 2019 10:51AM

    Ok. Thanks for the steps and info. I'll post it taking some time as my office hours are over.

    And can't access it now.

    Regards,

    Ranagal

  • Ranagal
    Ranagal Member Posts: 639 Bronze Badge
    edited May 7, 2019 4:58AM

    Hi,

    Followed the steps and got the below one:

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                                       | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                                |                              |      1 |        |     50 |00:00:18.00 |     367K|     55 |     55 |       |       |          |

    |   1 |  TEMP TABLE TRANSFORMATION                      |                              |      1 |        |     50 |00:00:18.00 |     367K|     55 |     55 |       |       |          |

    |   2 |   LOAD AS SELECT                                |                              |      1 |        |      0 |00:00:00.55 |   56743 |      0 |     53 |  1040K|  1040K|          |

    |   3 |    TABLE ACCESS BY INDEX ROWID                  | OBJECTS                      |   7785 |      1 |   7785 |00:00:00.03 |    8150 |      0 |      0 |       |       |          |

    |*  4 |     INDEX UNIQUE SCAN                           | PK_OBJECTS                   |   7785 |      1 |   7785 |00:00:00.01 |     360 |      0 |      0 |       |       |          |

    |   5 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   7785 |      1 |   5507 |00:00:00.05 |   12182 |      0 |      0 |       |       |          |

    |*  6 |     INDEX RANGE SCAN                            | UK_ATTR                      |   7785 |      1 |   5507 |00:00:00.03 |    9621 |      0 |      0 |       |       |          |

    |*  7 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

    |   8 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   7785 |      1 |   5507 |00:00:00.03 |   12182 |      0 |      0 |       |       |          |

    |*  9 |     INDEX RANGE SCAN                            | UK_ATTR                      |   7785 |      1 |   5507 |00:00:00.02 |    9621 |      0 |      0 |       |       |          |

    |* 10 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

    |  11 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |   1366 |      1 |   1366 |00:00:00.02 |    4592 |      0 |      0 |       |       |          |

    |* 12 |     INDEX RANGE SCAN                            | IDX_ATTR_NDC_OBJECT_VALUE    |   1366 |      1 |   1366 |00:00:00.01 |    3227 |      0 |      0 |       |       |          |

    |* 13 |      INDEX RANGE SCAN                           | NCI_NODES_COVERING_IDX       |   1366 |      1 |   1366 |00:00:00.01 |     595 |      0 |      0 |       |       |          |

    |* 14 |    VIEW                                         |                              |      1 |     12 |   7785 |00:00:00.41 |   24174 |      0 |      0 |       |       |          |

    |* 15 |     FILTER                                      |                              |      1 |        |   7891 |00:00:00.39 |   19582 |      0 |      0 |       |       |          |

    |* 16 |      CONNECT BY WITH FILTERING                  |                              |      1 |        |  66134 |00:00:00.37 |   19144 |      0 |      0 |  7069K|  1062K| 6283K (0)|

    |  17 |       TABLE ACCESS BY INDEX ROWID               | NODES                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

    |* 18 |        INDEX UNIQUE SCAN                        | PK_NODES                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |  19 |       NESTED LOOPS                              |                              |      9 |     11 |  66133 |00:00:00.19 |   19137 |      0 |      0 |       |       |          |

    |  20 |        CONNECT BY PUMP                          |                              |      9 |        |  66134 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

    |* 21 |        TABLE ACCESS BY INDEX ROWID BATCHED      | NODES                        |  66134 |     11 |  66133 |00:00:00.15 |   19137 |      0 |      0 |       |       |          |

    |* 22 |         INDEX RANGE SCAN                        | NCI_NODES_PARENT_NODE_ID     |  66134 |     11 |  67807 |00:00:00.08 |   12139 |      0 |      0 |       |       |          |

    |  23 |         TABLE ACCESS BY INDEX ROWID             | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |* 24 |          INDEX UNIQUE SCAN                      | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    |  25 |       TABLE ACCESS BY INDEX ROWID               | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |* 26 |        INDEX UNIQUE SCAN                        | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    |* 27 |      TABLE ACCESS BY INDEX ROWID                | OBJECT_TYPES                 |    219 |      1 |      1 |00:00:00.01 |     438 |      0 |      0 |       |       |          |

    |* 28 |       INDEX UNIQUE SCAN                         | PK_OBJECT_TYPES              |    219 |      1 |    219 |00:00:00.01 |     219 |      0 |      0 |       |       |          |

    |  29 |   LOAD AS SELECT                                |                              |      1 |        |      0 |00:00:02.86 |   37654 |     53 |      2 |  1040K|  1040K|          |

    |  30 |    TABLE ACCESS BY INDEX ROWID                  | OBJECTS                      |    316 |      1 |    316 |00:00:00.01 |     603 |      0 |      0 |       |       |          |

    |* 31 |     INDEX UNIQUE SCAN                           | PK_OBJECTS                   |    316 |      1 |    316 |00:00:00.01 |     287 |      0 |      0 |       |       |          |

    |  32 |    TABLE ACCESS BY INDEX ROWID BATCHED          | ATTRIBUTES                   |    316 |      1 |    316 |00:00:00.01 |     950 |      0 |      0 |       |       |          |

    |* 33 |     INDEX RANGE SCAN                            | UK_ATTR                      |    316 |      1 |    316 |00:00:00.01 |     666 |      0 |      0 |       |       |          |

    |* 34 |      TABLE ACCESS FULL                          | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

    |  35 |    HASH UNIQUE                                  |                              |      1 |    148 |    316 |00:00:02.86 |   37650 |     53 |      0 |  1041K|  1041K| 1371K (0)|

    |* 36 |     FILTER                                      |                              |      1 |        |   5500 |00:00:02.85 |   36097 |     53 |      0 |       |       |          |

    |  37 |      MERGE JOIN CARTESIAN                       |                              |      1 |    148 |   5114K|00:00:02.23 |   34073 |     53 |      0 |       |       |          |

    |* 38 |       HASH JOIN                                 |                              |      1 |     12 |    657 |00:00:01.05 |   34016 |      0 |      0 |  1003K|  1003K|  728K (0)|

    |  39 |        NESTED LOOPS                             |                              |      1 |     69 |    969 |00:00:00.36 |   20145 |      0 |      0 |       |       |          |

    |  40 |         NESTED LOOPS                            |                              |      1 |    132 |    970 |00:00:00.36 |   19975 |      0 |      0 |       |       |          |

    |  41 |          VIEW                                   |                              |      1 |     12 |    312 |00:00:00.35 |   19582 |      0 |      0 |       |       |          |

    |* 42 |           FILTER                                |                              |      1 |        |    312 |00:00:00.35 |   19582 |      0 |      0 |       |       |          |

    |* 43 |            CONNECT BY WITH FILTERING            |                              |      1 |        |  66134 |00:00:00.34 |   19144 |      0 |      0 |  6219K|  1010K| 5527K (0)|

    |  44 |             TABLE ACCESS BY INDEX ROWID         | NODES                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

    |* 45 |              INDEX UNIQUE SCAN                  | PK_NODES                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |  46 |             NESTED LOOPS                        |                              |      9 |     11 |  66133 |00:00:00.18 |   19137 |      0 |      0 |       |       |          |

    |  47 |              CONNECT BY PUMP                    |                              |      9 |        |  66134 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

    |* 48 |              TABLE ACCESS BY INDEX ROWID BATCHED| NODES                        |  66134 |     11 |  66133 |00:00:00.15 |   19137 |      0 |      0 |       |       |          |

    |* 49 |               INDEX RANGE SCAN                  | NCI_NODES_PARENT_NODE_ID     |  66134 |     11 |  67807 |00:00:00.08 |   12139 |      0 |      0 |       |       |          |

    |  50 |               TABLE ACCESS BY INDEX ROWID       | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |* 51 |                INDEX UNIQUE SCAN                | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    |  52 |             TABLE ACCESS BY INDEX ROWID         | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |* 53 |              INDEX UNIQUE SCAN                  | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    |* 54 |            TABLE ACCESS BY INDEX ROWID          | OBJECT_TYPES                 |    219 |      1 |      1 |00:00:00.01 |     438 |      0 |      0 |       |       |          |

    |* 55 |             INDEX UNIQUE SCAN                   | PK_OBJECT_TYPES              |    219 |      1 |    219 |00:00:00.01 |     219 |      0 |      0 |       |       |          |

    |* 56 |          INDEX RANGE SCAN                       | NCI_NODES_PARENT_NODE_ID     |    312 |     11 |    970 |00:00:00.01 |     393 |      0 |      0 |       |       |          |

    |* 57 |         TABLE ACCESS BY INDEX ROWID             | NODES                        |    970 |      6 |    969 |00:00:00.01 |     170 |      0 |      0 |       |       |          |

    |* 58 |        VIEW                                     | index$_join$_065             |      1 |     42 |      4 |00:00:00.69 |   13871 |      0 |      0 |       |       |          |

    |* 59 |         HASH JOIN                               |                              |      1 |        |    434 |00:00:00.01 |      12 |      0 |      0 |  1519K|  1519K| 1491K (0)|

    |  60 |          INDEX FAST FULL SCAN                   | PK_OBJECT_TYPES              |      1 |     42 |    434 |00:00:00.01 |       4 |      0 |      0 |       |       |          |

    |  61 |          INDEX FAST FULL SCAN                   | UK_IDX_OBJECT_TYPE_NDC       |      1 |     42 |    434 |00:00:00.01 |       8 |      0 |      0 |       |       |          |

    |  62 |       BUFFER SORT                               |                              |    657 |     12 |   5114K|00:00:00.63 |      57 |     53 |      0 |   372K|   372K|  330K (0)|

    |  63 |        VIEW                                     |                              |      1 |     12 |   7785 |00:00:00.02 |      57 |     53 |      0 |       |       |          |

    |  64 |         TABLE ACCESS FULL                       | SYS_TEMP_0FD9D761B_1445481D  |      1 |     12 |   7785 |00:00:00.02 |      57 |     53 |      0 |       |       |          |

    |  65 |      TABLE ACCESS BY INDEX ROWID                | OBJECTS                      |    657 |      1 |    657 |00:00:00.01 |    1068 |      0 |      0 |       |       |          |

    |* 66 |       INDEX UNIQUE SCAN                         | PK_OBJECTS                   |    657 |      1 |    657 |00:00:00.01 |     410 |      0 |      0 |       |       |          |

    |  67 |      TABLE ACCESS BY INDEX ROWID BATCHED        | ATTRIBUTES                   |    318 |      1 |    318 |00:00:00.01 |     956 |      0 |      0 |       |       |          |

    |* 68 |       INDEX RANGE SCAN                          | UK_ATTR                      |    318 |      1 |    318 |00:00:00.01 |     670 |      0 |      0 |       |       |          |

    |* 69 |        TABLE ACCESS FULL                        | ATTRIBUTE_TYPES              |      1 |      1 |      1 |00:00:00.01 |      38 |      0 |      0 |       |       |          |

    |  70 |   SORT GROUP BY                                 |                              |      1 |      1 |     50 |00:00:14.59 |     273K|      2 |      0 |   619K|   471K|  550K (0)|

    |  71 |    VIEW                                         |                              |      1 |      1 |   4375 |00:00:13.31 |     273K|      2 |      0 |       |       |          |

    |  72 |     HASH UNIQUE                                 |                              |      1 |      1 |   4375 |00:00:13.31 |     273K|      2 |      0 |  1186K|  1186K| 1400K (0)|

    |  73 |      TABLE ACCESS BY INDEX ROWID                | OBJECTS                      |   4606 |      1 |   4606 |00:00:05.59 |   37088 |      0 |      0 |       |       |          |

    |* 74 |       INDEX UNIQUE SCAN                         | PK_OBJECTS                   |   4606 |      1 |   4606 |00:00:05.56 |   32472 |      0 |      0 |       |       |          |

    |* 75 |      HASH JOIN                                  |                              |      1 |      1 |   4375 |00:00:13.29 |     273K|      2 |      0 |  1410K|  1075K| 1423K (0)|

    |  76 |       NESTED LOOPS                              |                              |      1 |      1 |   4375 |00:00:00.07 |   12952 |      2 |      0 |       |       |          |

    |  77 |        NESTED LOOPS                             |                              |      1 |      2 |   4375 |00:00:00.06 |   12593 |      2 |      0 |       |       |          |

    |  78 |         NESTED LOOPS                            |                              |      1 |      1 |   4375 |00:00:00.05 |   11761 |      2 |      0 |       |       |          |

    |* 79 |          HASH JOIN                              |                              |      1 |      1 |   5500 |00:00:00.01 |      60 |      2 |      0 |  1321K|  1321K| 1775K (0)|

    |  80 |           VIEW                                  |                              |      1 |     12 |   7785 |00:00:00.01 |      54 |      0 |      0 |       |       |          |

    |  81 |            TABLE ACCESS FULL                    | SYS_TEMP_0FD9D761B_1445481D  |      1 |     12 |   7785 |00:00:00.01 |      54 |      0 |      0 |       |       |          |

    |  82 |           VIEW                                  |                              |      1 |    148 |    316 |00:00:00.01 |       6 |      2 |      0 |       |       |          |

    |  83 |            TABLE ACCESS FULL                    | SYS_TEMP_0FD9D761C_1445481D  |      1 |    148 |    316 |00:00:00.01 |       6 |      2 |      0 |       |       |          |

    |  84 |          TABLE ACCESS BY INDEX ROWID BATCHED    | ATTRIBUTES                   |   5500 |      1 |   4375 |00:00:00.04 |   11701 |      0 |      0 |       |       |          |

    |* 85 |           INDEX RANGE SCAN                      | IDX_ATTR_NDC_OBJECT_VALUE    |   5500 |      1 |   4375 |00:00:00.02 |    7353 |      0 |      0 |       |       |          |

    |* 86 |         INDEX RANGE SCAN                        | NCI_ATTRIBUTE_VALUES_ATTR_ID |   4375 |      2 |   4375 |00:00:00.01 |     832 |      0 |      0 |       |       |          |

    |  87 |        TABLE ACCESS BY INDEX ROWID              | ATTRIBUTE_VALUES             |   4375 |      2 |   4375 |00:00:00.01 |     359 |      0 |      0 |       |       |          |

    |  88 |       VIEW                                      |                              |      1 |   1730 |   4606 |00:00:13.21 |     260K|      0 |      0 |       |       |          |

    |* 89 |        FILTER                                   |                              |      1 |        |   4606 |00:00:00.06 |    2094 |      0 |      0 |       |       |          |

    |* 90 |         CONNECT BY WITH FILTERING               |                              |      1 |        |   4922 |00:00:00.05 |    2037 |      0 |      0 |   478K|   448K|  424K (0)|

    |  91 |          NESTED LOOPS                           |                              |      1 |    148 |    316 |00:00:00.01 |     953 |      0 |      0 |       |       |          |

    |  92 |           NESTED LOOPS                          |                              |      1 |    148 |    316 |00:00:00.01 |     637 |      0 |      0 |       |       |          |

    |  93 |            VIEW                                 | VW_NSO_1                     |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |  94 |             HASH UNIQUE                         |                              |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |  2170K|  2170K| 2517K (0)|

    |  95 |              VIEW                               |                              |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |  96 |               TABLE ACCESS FULL                 | SYS_TEMP_0FD9D761C_1445481D  |      1 |    148 |    316 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |* 97 |            INDEX UNIQUE SCAN                    | PK_NODES                     |    316 |      1 |    316 |00:00:00.01 |     634 |      0 |      0 |       |       |          |

    |  98 |           TABLE ACCESS BY INDEX ROWID           | NODES                        |    316 |      1 |    316 |00:00:00.01 |     316 |      0 |      0 |       |       |          |

    |  99 |          NESTED LOOPS                           |                              |      2 |   1582 |   4606 |00:00:00.01 |    1081 |      0 |      0 |       |       |          |

    | 100 |           CONNECT BY PUMP                       |                              |      2 |        |   4922 |00:00:00.01 |       0 |      0 |      0 |       |       |          |

    |*101 |           TABLE ACCESS BY INDEX ROWID BATCHED   | NODES                        |   4922 |     11 |   4606 |00:00:00.01 |    1081 |      0 |      0 |       |       |          |

    |*102 |            INDEX RANGE SCAN                     | NCI_NODES_PARENT_NODE_ID     |   4922 |     11 |   4608 |00:00:00.01 |     950 |      0 |      0 |       |       |          |

    | 103 |            TABLE ACCESS BY INDEX ROWID          | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |*104 |             INDEX UNIQUE SCAN                   | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    | 105 |          TABLE ACCESS BY INDEX ROWID            | OBJECT_TYPES                 |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    |*106 |           INDEX UNIQUE SCAN                     | UK_IDX_OBJECT_TYPE_NDC       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |

    |*107 |         TABLE ACCESS BY INDEX ROWID             | OBJECT_TYPES                 |      3 |      1 |      1 |00:00:00.01 |      57 |      0 |      0 |       |       |          |

    |*108 |          INDEX UNIQUE SCAN                      | PK_OBJECT_TYPES              |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       4 - access("O"."OBJECT_ID"=:B1)

       6 - access("A"."OBJECT_ID"=:B1 AND "A"."ATTRIBUTE_TYPE_ID"=)

       7 - filter("NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_CODELIST')

       9 - access("A"."OBJECT_ID"=:B1 AND "A"."ATTRIBUTE_TYPE_ID"=)

      10 - filter("NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_CODELIST')

      12 - access("A"."NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_ACTIVE_STATUS' AND "A"."OBJECT_ID"=)

      13 - access("NOD"."NODE_ID"=:B1)

      14 - filter("GROUP_ACTIVE_STATUS"='LOOKUP_VALUE.ACTIVE_STATUS_A')

      15 - filter(("DELETION_DATE"='01-Jan-1900' AND  IS NOT NULL AND "OBJECT_ID" IS NOT NULL))

      16 - access("N"."PARENT_NODE_ID"=PRIOR NULL)

      18 - access("N"."NODE_ID"=TO_NUMBER(:I_NODE_ID))

      21 - filter("N"."OBJECT_TYPE_ID"<>)

      22 - access("connect$_by$_pump$_029"."PRIOR n.node_id"="N"."PARENT_NODE_ID")

      24 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

      26 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

      27 - filter("NAME"=:I_SEARCH_OBJ_TYPE)

      28 - access("OBJECT_TYPE_ID"=:B1)

      31 - access("O"."OBJECT_ID"=:B1)

      33 - access("A"."OBJECT_ID"=:B1 AND "A"."ATTRIBUTE_TYPE_ID"=)

      34 - filter("AT"."NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_MASTER_UNIQUE_ITEM_ID')

      36 - filter(("CN"."CODELIST"= AND "CN"."CODELIST_MUI"=))

      38 - access("N"."OBJECT_TYPE_ID"="OBJECT_TYPE_ID")

      42 - filter(("DELETION_DATE"='01-Jan-1900' AND  IS NOT NULL))

      43 - access("PARENT_NODE_ID"=PRIOR NULL)

      45 - access("NODE_ID"=TO_NUMBER(:I_NODE_ID))

      48 - filter("OBJECT_TYPE_ID"<>)

      49 - access("connect$_by$_pump$_049"."PRIOR node_id "="PARENT_NODE_ID")

      51 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

      53 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

      54 - filter(("NAME_DISPLAY_CODE"='OBJECT_TYPE.MDR_CL_CONTAINER' OR "NAME_DISPLAY_CODE"='OBJECT_TYPE.MDR_STUDY_CL_PARENT_CONTAINER'))

      55 - access("OBJECT_TYPE_ID"=:B1)

      56 - access("N"."PARENT_NODE_ID"="NODE_ID")

      57 - filter("N"."DELETION_DATE"='01-Jan-1900')

      58 - filter(("NAME_DISPLAY_CODE"MEMBER OF"PKG_MDR_COMP_RPT_MGR"."F_GET_LOV_MAPPED_OBJTYPES"("VP40"."VARCHAR_TBL"('OBJECT_TYPE.MDR_CL_CODELIST')) OR

                  "NAME_DISPLAY_CODE"MEMBER OF"PKG_MDR_COMP_RPT_MGR"."F_GET_LOV_MAPPED_OBJTYPES"("VP40"."VARCHAR_TBL"('OBJECT_TYPE.MDR_CL_SUBSET'))))

      59 - access(ROWID=ROWID)

      66 - access("O"."OBJECT_ID"=:B1)

      68 - access("A"."OBJECT_ID"=:B1 AND "A"."ATTRIBUTE_TYPE_ID"=)

      69 - filter("AT"."NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_MASTER_UNIQUE_ITEM_ID')

      74 - access("O"."OBJECT_ID"=NVL(:B1,"PKG_MDR_UTIL"."F_GET_REF_NODE_OBJECT_ID"(:B2,"REQ_INFO_TYPE"("USER_CREDENTIALS_TYPE"(1,NULL,'en-US'),"AUDIT_INFO_TYPE"(NULL,NULL,NULL),N

                  ULL,NULL,NULL))))

      75 - access("COD"."CL_NODE_ID"="CL"."NODE_ID" AND "AV"."ATTRIBUTE_VALUE"="COD"."OBJ_NAME")

      79 - access("CN"."CODELIST_MUI"="CL"."MUI_VALUE")

      85 - access("A"."NAME_DISPLAY_CODE"='ATTRIBUTE_TYPE.MDR_CODELIST_VALUE' AND "CN"."OBJECT_ID"="A"."OBJECT_ID")

      86 - access("A"."ATTRIBUTE_ID"="AV"."ATTRIBUTE_ID")

      89 - filter(("DELETION_DATE"='01-Jan-1900' AND  IS NOT NULL))

      90 - access("N"."PARENT_NODE_ID"=PRIOR NULL)

      97 - access("N"."NODE_ID"="NODE_ID")

    101 - filter("N"."OBJECT_TYPE_ID"<>)

    102 - access("connect$_by$_pump$_082"."PRIOR n.node_id "="N"."PARENT_NODE_ID")

    104 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

    106 - access("NAME_DISPLAY_CODE"='OBJECT_TYPE.ARCHIVE_CONTAINER')

    107 - filter("NAME_DISPLAY_CODE"MEMBER OF"PKG_MDR_COMP_RPT_MGR"."F_GET_LOV_MAPPED_OBJTYPES"("VP40"."VARCHAR_TBL"('OBJECT_TYPE.MDR_CL_CODE')))

    108 - access("OBJECT_TYPE_ID"=:B1)

    Note

    -----

       - dynamic statistics used: dynamic sampling (level=2)

       - 3 Sql Plan Directives used for this statement

    Sorry about the formatting. I did try it but turned out to be worse.

    Regards,

    Ranagal

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 7, 2019 3:17AM

    Try the formatting again:

    from the main thread page (not your inbox), go to actions>edit post.

    Highlight your plan

    Change the font at the top to courier new

    Save the update

    So long as you copied the plan with the right whitespace included (I don’t see how you wouldn’t have) then this should work fine

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,982 Blue Diamond
    edited May 7, 2019 3:43AM

    It's a little hard to interpret because the information supplied by Oracle is not entirely self-consistent, but I think the following is probably correct:

    The main body of your query starts at operation 70

    |  70 |   SORT GROUP BY                                 |                              |      1 |      1 |     50 |00:00:14.59 |     273K|      2 |      0 |   619K|   471K|  550K (0)|

    |  71 |    VIEW                                         |                              |      1 |      1 |   4375 |00:00:13.31 |     273K|      2 |      0 |       |       |          |

    |  72 |     HASH UNIQUE                                 |                              |      1 |      1 |   4375 |00:00:13.31 |     273K|      2 |      0 |  1186K|  1186K| 1400K (0)|

    |  73 |      TABLE ACCESS BY INDEX ROWID                | OBJECTS                      |   4606 |      1 |   4606 |00:00:05.59 |   37088 |      0 |      0 |       |       |          |

    |* 74 |       INDEX UNIQUE SCAN                         | PK_OBJECTS                   |   4606 |      1 |   4606 |00:00:05.56 |   32472 |      0 |      0 |       |       |          |

    |* 75 |      HASH JOIN                                  |                              |      1 |      1 |   4375 |00:00:13.29 |     273K|      2 |      0 |  1410K|  1075K| 1423K (0)|

    The driving step for collecting the initial data set is line 75, there's an inline scalar subquery shown at operations 73/74 that gets adds a column value to the data, then operation 72 does a hash unique and opteration 70 does a sort group by

    The first big time component appears in operation 75, but I think this is misleading reporting - the time USAGE is in operation 74 where the scalar subquery executes 4606 times. There's a strange change from 5.58 seconds to 13.31 seconds which I will ascribe to granularity errors in Oracle and loss of pl/sql time.

    The big time in operation 74 is the complex PL/SQL function calling that takes place - visible in the predicate:

      74 - access("O"."OBJECT_ID"=NVL(:B1,"PKG_MDR_UTIL"."F_GET_REF_NODE_OBJECT_ID"(:B2,"REQ_INFO_TYPE"("USER_CREDENTIALS_TYPE"(1,NULL,'en-US'),"AUDIT_INFO_TYPE"(NULL,NULL,NULL),NULL,NULL,NULL))))

    You need to find a way to minimise the work done in this step.  Since it seems that 4606 calls result in 4375 unique rows, but the sort group by reduces that to 50 (unless that's somehow reporting just the first 50 rows returned) then maybe the number of distinct values supplied to the functions is small and the functions need to be declared as deterministic.

    Test - can you run a test against modified code that doesn't call that function / scalar subquery to see if the performance of the modified query is significantly better.

    Regards

    Jonathan Lewis

  • Ranagal
    Ranagal Member Posts: 639 Bronze Badge
    edited May 7, 2019 4:59AM

    Done. Please check now.

  • Ranagal
    Ranagal Member Posts: 639 Bronze Badge
    edited May 7, 2019 5:13AM

    I can not remove that function which is very important. I mean I am running this query to get the value from that function itself. If I remove it and try running the query then the whole point of running the query becomes useless.

    Regards,

    Ranagal

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,982 Blue Diamond
    edited May 7, 2019 6:11AM
    Ranagal wrote:I can not remove that function which is very important. I mean I am running this query to get the value from that function itself. If I remove it and try running the query then the whole point of running the query becomes useless.Regards,Ranagal

    This may suprise you, but I had assumed that removing the function call from the query would affect the result set.

    If you re-read my response you will note it starts with a comment that (emphasis added):

    • "It's a little hard to interpret because the information supplied by Oracle is not entirely self-consistent, but I think the following is probably correct"

    and ends with the advice

    • Test - can you run a test ... that doesn't call that function .. to see if the performance ... is significantly better.

    Note the word "test"; I did not suggest that this was a solution to your problem, it was a test of a hypothesis - if the execution time drops from 18 seconds to subsecond for the same plan then that tells us that the function is a major resource threat and must either be made more efficient or must be called much less frequently. If the time doesn't change significantly then we need to look elsewhere. The first step to solving a problem is to find out where it is.

    I am slightly surprised that no-one has pointed out that I seem to have missed the 13.21 seconds that appears out of nowhere in operation 88. (But the people give up their time to offer help on the forum do have their own jobs and don't get paid to monitor the forum all day long.) It's possible that this anomaly is the one that's introducing most of the time, and it might be something to do with bad accounting for the time in the filter operation 89 where the filter predicate shows:

    89 - filter(("DELETION_DATE"='01-Jan-1900' AND  {missing subquery information} IS NOT NULL))

    So if a suitable test eliminates the packaged function calls at operation 74 as a source of time spent then operation 89 is probably the next piece of the puzzle to examine.

    Regards

    Jonathan Lewis