OraFAQ Forum: Performance Tuning » sql tuning for one query

Home » RDBMS Server » Performance Tuning » sql tuning for one query (11g 11.2.0,2,IBM aix 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql tuning for one query [message #534742]

Thu, 08 December 2011 15:43

pvgangadar
Messages: 3
Registered: December 2011
Location: vsp

Junior Member

Hi

Please can any body analyse this query and give recommendations

Select distinct inv_investments.id
FROM ((SELECT invest_id ID
FROM sortfolio_contents
WHERE sortfolio_id = 5115003
AND invest_type IN ('project')
AND is_Added_By_Expression = 1)) sortfolio_investments,
(SELECT c.currency_code code
FROM cmn_currencies c
WHERE c.is_active = 1) obj_currency,
inv_investments
WHERE inv_investments.is_active = 1
AND (inv_investments.purge_flag = 0 or
inv_investments.purge_flag is null)
AND obj_currency.code = inv_investments.currency_code
AND 0 =
(CASE WHEN inv_investments.odf_object_code = 'project' THEN
(SELECT is_template FROM inv_projects WHERE prid = inv_investments.ID) ELSE 0 END)
AND inv_investments.ID = sortfolio_investments.ID
AND inv_investments.status IN (1, 5, Cool

AND inv_investments.id IN
(SELECT ID
FROM ODF_CA_INV
WHERE ODF_OBJECT_CODE IN
(SELECT OE.OBJECT_CODE
FROM ODF_OBJECT_EXTENSIONS OE,
ODF_OBJECTS O,
CMN_SEC_CHK_USER_R_V S
WHERE S.USER_ID = 5545073
AND O.CODE = OE.OBJECT_CODE
AND OE.EXTENSION_CODE = 'inv'
AND S.OBJECT_TYPE = 'RECORD'
AND S.PERMISSION_CODE = 'READ'
AND S.OBJECT_CODE = O.RIGHT_CODE
UNION
SELECT 'project'
FROM CMN_SEC_CHK_USER_R_V0
WHERE USER_ID = 5545073
AND PERMISSION_CODE = 'prProjectViewer'
AND OBJECT_ID = 663)
UNION (SELECT OBJECT_INSTANCE_ID
FROM CMN_SEC_CHK_USER_V0
WHERE USER_ID = 5545073
AND PERMISSION_CODE = 'prProjectViewer'
AND OBJECT_ID = 663
UNION
SELECT OBJECT_INSTANCE_ID
FROM CMN_SEC_CHK_USER_V0
WHERE USER_ID = 5545073
AND PERMISSION_CODE = 'READ'
AND OBJECT_ID IN
(SELECT S.ID
FROM ODF_OBJECT_EXTENSIONS OE,
ODF_OBJECTS O,
CMN_SEC_OBJECTS S
WHERE O.CODE = OE.OBJECT_CODE
AND OE.EXTENSION_CODE = 'inv'
AND S.OBJECT_TYPE_CODE = 'RECORD'
AND S.OBJECT_CODE = O.RIGHT_CODE)))

I put this in oracle sql tuning advisor and does not get much recommendations.can any body tell some recommendations

Thanks
Ganga

Report message to a moderator

Re: sql tuning for one query [message #534743 is a reply to message #534742]

Thu, 08 December 2011 15:51

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Nobody can tune a SQL by just looking at it.

Information that is required to tune SQLs,

Report message to a moderator

Re: sql tuning for one query [message #534744 is a reply to message #534742]

Thu, 08 December 2011 15:55

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Ganga, your code is unreadable. I'll put it through a code formatter for you:

SELECT DISTINCT inv_investments.id
FROM   ((SELECT invest_id id
         FROM   sortfolio_contents
         WHERE  sortfolio_id = 5115003
                AND invest_type IN ( 'project' )
                AND is_added_by_expression = 1)) sortfolio_investments,
       (SELECT c.currency_code code
        FROM   cmn_currencies c
        WHERE  c.is_active = 1) obj_currency,
       inv_investments
WHERE  inv_investments.is_active = 1
       AND ( inv_investments.purge_flag = 0
              OR inv_investments.purge_flag IS NULL )
       AND obj_currency.code = inv_investments.currency_code
       AND 0 = ( CASE
                   WHEN inv_investments.odf_object_code = 'project' THEN
                   (SELECT is_template
                    FROM   inv_projects
                    WHERE  prid = inv_investments.id)
                   ELSE 0
                 END )
       AND inv_investments.id = sortfolio_investments.id
       AND inv_investments.status IN ( 1, 5, 8 )
       AND inv_investments.id IN (SELECT id
                                  FROM   odf_ca_inv
                                  WHERE
           odf_object_code IN (
           SELECT oe.object_code
           FROM
           odf_object_extensions oe,
           odf_objects o,
           cmn_sec_chk_user_r_v s
                               WHERE  s.user_id = 5545073
                                      AND
                               o.code = oe.object_code
                                      AND oe.extension_code =
                                          'inv'
                                      AND s.object_type =
                                          'RECORD'
                                      AND
           s.permission_code =
           'READ'
                                      AND s.object_code =
                                          o.right_code
                               UNION
                               SELECT 'project'
                               FROM   cmn_sec_chk_user_r_v0
                               WHERE  user_id = 5545073
                                      AND permission_code =
                                          'prProjectViewer'
                                      AND object_id = 663)
                                  UNION
                                  (SELECT object_instance_id
                                   FROM   cmn_sec_chk_user_v0
                                   WHERE  user_id = 5545073
                                          AND permission_code =
                                              'prProjectViewer'
                                          AND object_id = 663
                                   UNION
                                   SELECT object_instance_id
                                   FROM   cmn_sec_chk_user_v0
                                   WHERE  user_id = 5545073
                                          AND permission_code = 'READ'
                                          AND object_id IN (SELECT s.id
                                                            FROM
                                              odf_object_extensions oe
                                              ,
           odf_objects o,
           cmn_sec_objects s
           WHERE  o.code = oe.object_code
           AND oe.extension_code = 'inv'
           AND s.object_type_code = 'RECORD'
           AND s.object_code = o.right_code)))

You may get better responses now.

Report message to a moderator

Re: sql tuning for one query [message #534745 is a reply to message #534744]

Thu, 08 December 2011 17:09

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Looks like the formatter struggled with that. I can't tell what's supposed to go with what at the end there.
I suspect those unions can all be made union alls, which might help.

Report message to a moderator

Re: sql tuning for one query [message #534822 is a reply to message #534745]

Fri, 09 December 2011 07:39

pvgangadar
Messages: 3
Registered: December 2011
Location: vsp

Junior Member

Hi

Thanks for all your suggestions.Will provide you now with original plan(having high costs) and the oracle recommended plan (with low costs).So can any body check this and give some recommendations

original PLAN_TABLE_OUTPUT( with high cost)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 432557403

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                             |     1 |    56 |  4229   (2)| 00:02:01 |
|   1 |  SORT UNIQUE NOSORT                                |                             |     1 |    56 |  4229   (2)| 00:02:01 |
|   2 |   NESTED LOOPS                                     |                             |       |       |            |          |
|   3 |    NESTED LOOPS                                    |                             |     1 |    56 |    84   (0)| 00:00:03 |
|   4 |     NESTED LOOPS                                   |                             |     1 |    49 |    83   (0)| 00:00:03 |
|*  5 |      TABLE ACCESS BY INDEX ROWID                   | INV_INVESTMENTS             |    10 |   260 |    75   (0)| 00:00:03 |
|*  6 |       INDEX SKIP SCAN                              | INV_INVESTMENTS_N4          |     4 |       |    74   (0)| 00:00:03 |
|   7 |        SORT UNIQUE                                 |                             |    87 |  7095 |  4078  (94)| 00:01:56 |
|   8 |         UNION-ALL                                  |                             |       |       |            |          |
|*  9 |          INDEX RANGE SCAN                          | ODF_CA_INV_U1               |     1 |    14 |     2   (0)| 00:00:01 |
|  10 |           SORT UNIQUE                              |                             |     3 |   297 |   297  (12)| 00:00:09 |
|  11 |            UNION-ALL                               |                             |       |       |            |          |
|  12 |             NESTED LOOPS                           |                             |     1 |   131 |   273   (4)| 00:00:08 |
|  13 |              NESTED LOOPS                          |                             |     1 |    42 |     2   (0)| 00:00:01 |
|  14 |               TABLE ACCESS BY INDEX ROWID          | ODF_OBJECTS                 |     1 |    23 |     1   (0)| 00:00:01 |
|* 15 |                INDEX UNIQUE SCAN                   | ODF_OBJECTS_U1              |     1 |       |     1   (0)| 00:00:01 |
|* 16 |               INDEX UNIQUE SCAN                    | ODF_OBJECT_EXTENSIONS_U1    |     1 |    19 |     1   (0)| 00:00:01 |
|  17 |              VIEW                                  | CMN_SEC_CHK_USER_R_V        |     1 |    89 |   271   (4)| 00:00:08 |
|  18 |               SORT UNIQUE                          |                             |     3 |   387 |   271  (14)| 00:00:08 |
|  19 |                UNION ALL PUSHED PREDICATE          |                             |       |       |            |          |
|* 20 |                 HASH JOIN                          |                             |     1 |   118 |   239   (2)| 00:00:07 |
|* 21 |                  HASH JOIN                         |                             |     6 |   480 |   237   (2)| 00:00:07 |
|* 22 |                   INDEX FAST FULL SCAN             | CMN_SEC_GROUPS_N1           |   132 |  3168 |   108   (1)| 00:00:04 |
|* 23 |                   HASH JOIN                        |                             |   342 | 19152 |   129   (2)| 00:00:04 |
|* 24 |                    INDEX FAST FULL SCAN            | CMN_SEC_RIGHT_U1            |   899 | 30566 |     7   (0)| 00:00:01 |
|* 25 |                    HASH JOIN                       |                             | 14448 |   310K|   121   (1)| 00:00:04 |
|* 26 |                     INDEX RANGE SCAN               | CMN_SEC_USER_GROUPS_U1      |  8617 | 86170 |    29   (0)| 00:00:01 |
|  27 |                     INDEX FAST FULL SCAN           | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    92   (2)| 00:00:03 |
|* 28 |                  INDEX RANGE SCAN                  | CMN_SEC_OBJECTS_U2          |     1 |    38 |     2   (0)| 00:00:01 |
|* 29 |                 HASH JOIN                          |                             |     1 |    97 |    11  (10)| 00:00:01 |
|* 30 |                  HASH JOIN                         |                             |    12 |   708 |     9   (0)| 00:00:01 |
|* 31 |                   INDEX RANGE SCAN                 | CMN_SEC_ASSGND_RIGHT_U1     |    10 |   250 |     2   (0)| 00:00:01 |
|* 32 |                   INDEX FAST FULL SCAN             | CMN_SEC_RIGHT_U1            |   899 | 30566 |     7   (0)| 00:00:01 |
|* 33 |                  INDEX RANGE SCAN                  | CMN_SEC_OBJECTS_U2          |     1 |    38 |     2   (0)| 00:00:01 |
|* 34 |                 HASH JOIN                          |                             |     1 |   172 |    18   (6)| 00:00:01 |
|* 35 |                  INDEX RANGE SCAN                  | CMN_SEC_OBJECTS_U2          |     1 |    38 |     2   (0)| 00:00:01 |
|* 36 |                  HASH JOIN                         |                             |   303 | 40602 |    16   (7)| 00:00:01 |
|  37 |                   NESTED LOOPS                     |                             |   259 | 25900 |     8   (0)| 00:00:01 |
|  38 |                    NESTED LOOPS                    |                             |     5 |   375 |     4   (0)| 00:00:01 |
|  39 |                     NESTED LOOPS                   |                             |     1 |    38 |     3   (0)| 00:00:01 |
|* 40 |                      INDEX RANGE SCAN              | SRM_RESOURCES_N1            |     1 |    12 |     2   (0)| 00:00:01 |
|* 41 |                      INDEX RANGE SCAN              | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
|* 42 |                     INDEX RANGE SCAN               | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2   (0)| 00:00:01 |
|* 43 |                    INDEX RANGE SCAN                | CMN_SEC_ASSGND_RIGHT_U1     |    51 |  1275 |     1   (0)| 00:00:01 |
|* 44 |                   INDEX FAST FULL SCAN             | CMN_SEC_RIGHT_U1            |   899 | 30566 |     7   (0)| 00:00:01 |
|* 45 |             FILTER                                 |                             |       |       |            |          |
|  46 |              VIEW                                  | CMN_SEC_CHK_USER_R_V0       |     2 |   166 |    22  (14)| 00:00:01 |
|  47 |               SORT UNIQUE                          |                             |     2 |   140 |    22  (69)| 00:00:01 |
|  48 |                UNION-ALL                           |                             |       |       |            |          |
|  49 |                 NESTED LOOPS                       |                             |     1 |    80 |     7   (0)| 00:00:01 |
|  50 |                  NESTED LOOPS                      |                             |     1 |    56 |     6   (0)| 00:00:01 |
|  51 |                   NESTED LOOPS                     |                             |    10 |   460 |     5   (0)| 00:00:01 |
|  52 |                    TABLE ACCESS BY INDEX ROWID     | CMN_SEC_RIGHT               |     3 |   102 |     2   (0)| 00:00:01 |
|* 53 |                     INDEX RANGE SCAN               | CMN_SEC_RIGHT_N3            |     3 |       |     2   (0)| 00:00:01 |
|* 54 |                    INDEX RANGE SCAN                | CMN_SEC_GROUP_FLAT_HIERS_U1 |     4 |    48 |     1   (0)| 00:00:01 |
|* 55 |                   INDEX UNIQUE SCAN                | CMN_SEC_USER_GROUPS_U2      |     1 |    10 |     1   (0)| 00:00:01 |
|* 56 |                  INDEX RANGE SCAN                  | CMN_SEC_GROUPS_N1           |     1 |    24 |     1   (0)| 00:00:01 |
|* 57 |                 HASH JOIN                          |                             |     1 |    60 |    13   (8)| 00:00:01 |
|  58 |                  TABLE ACCESS BY INDEX ROWID       | CMN_SEC_RIGHT               |     3 |   102 |     2   (0)| 00:00:01 |
|* 59 |                   INDEX RANGE SCAN                 | CMN_SEC_RIGHT_N3            |     3 |       |     2   (0)| 00:00:01 |
|  60 |                  VIEW                              | VW_JF_SET$8FE1C7ED          |   269 |  6994 |    10   (0)| 00:00:01 |
|  61 |                   SORT UNIQUE                      |                             |   269 | 26668 |    10  (20)| 00:00:01 |
|  62 |                    UNION-ALL                       |                             |       |       |            |          |
|* 63 |                     INDEX RANGE SCAN               | CMN_SEC_ASSGND_RIGHT_U1     |    10 |   250 |     2   (0)| 00:00:01 |
|  64 |                     NESTED LOOPS                   |                             |   259 | 26418 |     8   (0)| 00:00:01 |
|  65 |                      NESTED LOOPS                  |                             |     5 |   375 |     4   (0)| 00:00:01 |
|  66 |                       NESTED LOOPS                 |                             |     1 |    38 |     3   (0)| 00:00:01 |
|* 67 |                        INDEX RANGE SCAN            | SRM_RESOURCES_N1            |     1 |    12 |     2   (0)| 00:00:01 |
|* 68 |                        INDEX RANGE SCAN            | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
|* 69 |                       INDEX RANGE SCAN             | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2   (0)| 00:00:01 |
|* 70 |                      INDEX RANGE SCAN              | CMN_SEC_ASSGND_RIGHT_U1     |    51 |  1377 |     1   (0)| 00:00:01 |
|  71 |          VIEW                                      | CMN_SEC_CHK_USER_V0         |     5 |   480 |   625   (2)| 00:00:18 |
|  72 |           SORT UNIQUE                              |                             |     5 |   592 |   625  (13)| 00:00:18 |
|  73 |            UNION-ALL                               |                             |       |       |            |          |
|  74 |             NESTED LOOPS                           |                             |     1 |   112 |   552   (1)| 00:00:16 |
|  75 |              NESTED LOOPS                          |                             |     1 |   103 |   551   (1)| 00:00:16 |
|* 76 |               HASH JOIN                            |                             |     1 |    57 |   550   (1)| 00:00:16 |
|* 77 |                INDEX RANGE SCAN                    | CMN_SEC_ASSGND_OBJ_PERM_N2  |     1 |    26 |     3   (0)| 00:00:01 |
|* 78 |                HASH JOIN                           |                             | 13592 |   411K|   546   (1)| 00:00:16 |
|* 79 |                 HASH JOIN                          |                             | 13558 |   291K|   121   (1)| 00:00:04 |
|* 80 |                  INDEX RANGE SCAN                  | CMN_SEC_USER_GROUPS_U1      |  8617 | 86170 |    29   (0)| 00:00:01 |
|  81 |                  INDEX FAST FULL SCAN              | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    92   (2)| 00:00:03 |
|* 82 |                 INDEX FULL SCAN                    | CMN_SEC_GROUPS_N1           |   104K|   917K|   424   (1)| 00:00:13 |
|* 83 |               INDEX RANGE SCAN                     | CMN_SEC_PERM_ELEMENTS_U1    |     1 |    46 |     2   (0)| 00:00:01 |
|* 84 |              INDEX RANGE SCAN                      | CMN_SEC_GROUPS_N1           |     1 |     9 |     1   (0)| 00:00:01 |
|  85 |             NESTED LOOPS                           |                             |     1 |    72 |     3   (0)| 00:00:01 |
|* 86 |              INDEX RANGE SCAN                      | CMN_SEC_PERM_ELEMENTS_U2    |     1 |    46 |     2   (0)| 00:00:01 |
|* 87 |              INDEX RANGE SCAN                      | CMN_SEC_ASSGND_OBJ_PERM_N9  |     1 |    26 |     1   (0)| 00:00:01 |
|  88 |             NESTED LOOPS                           |                             |     1 |   134 |     5   (0)| 00:00:01 |
|  89 |              NESTED LOOPS                          |                             |     1 |   108 |     4   (0)| 00:00:01 |
|  90 |               NESTED LOOPS                         |                             |     1 |    71 |     3   (0)| 00:00:01 |
|* 91 |                INDEX RANGE SCAN                    | CMN_SEC_ASSGND_RIGHT_U1     |     1 |    27 |     2   (0)| 00:00:01 |
|* 92 |                INDEX RANGE SCAN                    | CMN_SEC_RIGHT_U2            |     1 |    44 |     1   (0)| 00:00:01 |
|* 93 |               INDEX RANGE SCAN                     | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|* 94 |              INDEX RANGE SCAN                      | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
|  95 |             NESTED LOOPS                           |                             |     1 |   153 |    41   (3)| 00:00:02 |
|  96 |              NESTED LOOPS                          |                             |     1 |   127 |    40   (3)| 00:00:02 |
|  97 |               NESTED LOOPS                         |                             |     1 |    90 |    38   (0)| 00:00:02 |
|  98 |                NESTED LOOPS                        |                             |     1 |    46 |    37   (0)| 00:00:02 |
|* 99 |                 HASH JOIN                          |                             |     1 |    37 |    36   (0)| 00:00:02 |
|*100 |                  INDEX RANGE SCAN                  | CMN_SEC_USER_GROUPS_U1      |  8617 | 86170 |    29   (0)| 00:00:01 |
|*101 |                  INDEX FAST FULL SCAN              | CMN_SEC_ASSGND_RIGHT_U1     |     1 |    27 |     7   (0)| 00:00:01 |
|*102 |                 INDEX RANGE SCAN                   | CMN_SEC_GROUPS_N2           |     1 |     9 |     1   (0)| 00:00:01 |
|*103 |                INDEX RANGE SCAN                    | CMN_SEC_RIGHT_U2            |     1 |    44 |     1   (0)| 00:00:01 |
|*104 |               INDEX RANGE SCAN                     | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*105 |              INDEX RANGE SCAN                      | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 106 |             NESTED LOOPS                           |                             |     1 |   121 |    19   (0)| 00:00:01 |
| 107 |              VIEW                                  | VW_JF_SET$324750FD          |     2 |   218 |    18   (0)| 00:00:01 |
| 108 |               SORT UNIQUE                          |                             |     2 |   363 |    18  (45)| 00:00:01 |
| 109 |                UNION-ALL                           |                             |       |       |            |          |
| 110 |                 NESTED LOOPS                       |                             |       |       |            |          |
| 111 |                  NESTED LOOPS                      |                             |     1 |   145 |     8   (0)| 00:00:01 |
| 112 |                   NESTED LOOPS                     |                             |     2 |   272 |     7   (0)| 00:00:01 |
| 113 |                    NESTED LOOPS                    |                             |     2 |   248 |     5   (0)| 00:00:01 |
| 114 |                     NESTED LOOPS                   |                             |     1 |    98 |     4   (0)| 00:00:01 |
| 115 |                      NESTED LOOPS                  |                             |     1 |    61 |     3   (0)| 00:00:01 |
| 116 |                       TABLE ACCESS BY INDEX ROWID  | CMN_SEC_ASSGND_RIGHT        |     1 |    27 |     2   (0)| 00:00:01 |
|*117 |                        INDEX RANGE SCAN            | CMN_SEC_ASSGND_RIGHT_N2     |     1 |       |     1   (0)| 00:00:01 |
|*118 |                       INDEX RANGE SCAN             | CMN_SEC_RIGHT_U1            |     1 |    34 |     1   (0)| 00:00:01 |
|*119 |                      INDEX RANGE SCAN              | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*120 |                     INDEX RANGE SCAN               | PRJ_OBS_ASSOCIATIONS_N4     |     2 |    52 |     1   (0)| 00:00:01 |
| 121 |                    TABLE ACCESS BY INDEX ROWID     | PRJ_OBS_UNITS               |     1 |    12 |     1   (0)| 00:00:01 |
|*122 |                     INDEX UNIQUE SCAN              | PRJ_OBS_UNITS_U1            |     1 |       |     1   (0)| 00:00:01 |
|*123 |                   INDEX UNIQUE SCAN                | PRJ_OBS_TYPES_U1            |     1 |       |     1   (0)| 00:00:01 |
|*124 |                  TABLE ACCESS BY INDEX ROWID       | PRJ_OBS_TYPES               |     1 |     9 |     1   (0)| 00:00:01 |
| 125 |                 NESTED LOOPS                       |                             |       |       |            |          |
| 126 |                  NESTED LOOPS                      |                             |     1 |   218 |    10   (0)| 00:00:01 |
| 127 |                   NESTED LOOPS                     |                             |     1 |   209 |     9   (0)| 00:00:01 |
| 128 |                    NESTED LOOPS                    |                             |     1 |   197 |     8   (0)| 00:00:01 |
| 129 |                     NESTED LOOPS                   |                             |     1 |   171 |     7   (0)| 00:00:01 |
| 130 |                      NESTED LOOPS                  |                             |     1 |   134 |     5   (0)| 00:00:01 |
| 131 |                       NESTED LOOPS                 |                             |     1 |    97 |     4   (0)| 00:00:01 |
| 132 |                        NESTED LOOPS                |                             |     1 |    70 |     3   (0)| 00:00:01 |
|*133 |                         INDEX RANGE SCAN           | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     2   (0)| 00:00:01 |
| 134 |                         TABLE ACCESS BY INDEX ROWID| CMN_SEC_RIGHT               |     1 |    44 |     2   (0)| 00:00:01 |
|*135 |                          INDEX RANGE SCAN          | CMN_SEC_RIGHT_N3            |     1 |       |     1   (0)| 00:00:01 |
|*136 |                        INDEX RANGE SCAN            | CMN_SEC_ASSGND_RIGHT_N3     |     1 |    27 |     1   (0)| 00:00:01 |
|*137 |                       INDEX UNIQUE SCAN            | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     1   (0)| 00:00:01 |
|*138 |                      INDEX RANGE SCAN              | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*139 |                     INDEX RANGE SCAN               | PRJ_OBS_ASSOCIATIONS_N4     |     2 |    52 |     1   (0)| 00:00:01 |
| 140 |                    TABLE ACCESS BY INDEX ROWID     | PRJ_OBS_UNITS               |     1 |    12 |     1   (0)| 00:00:01 |
|*141 |                     INDEX UNIQUE SCAN              | PRJ_OBS_UNITS_U1            |     1 |       |     1   (0)| 00:00:01 |
|*142 |                   INDEX UNIQUE SCAN                | PRJ_OBS_TYPES_U1            |     1 |       |     1   (0)| 00:00:01 |
|*143 |                  TABLE ACCESS BY INDEX ROWID       | PRJ_OBS_TYPES               |     1 |     9 |     1   (0)| 00:00:01 |
|*144 |              INDEX RANGE SCAN                      | SRM_RESOURCES_N1            |     1 |    12 |     1   (0)| 00:00:01 |
| 145 |          NESTED LOOPS                              |                             |    71 |  6461 |  3152   (2)| 00:01:30 |
|*146 |           HASH JOIN                                |                             |     5 |   380 |     6  (17)| 00:00:01 |
| 147 |            NESTED LOOPS                            |                             |     5 |   210 |     3   (0)| 00:00:01 |
|*148 |             TABLE ACCESS FULL                      | ODF_OBJECTS                 |    95 |  2185 |     2   (0)| 00:00:01 |
|*149 |             INDEX UNIQUE SCAN                      | ODF_OBJECT_EXTENSIONS_U1    |     1 |    19 |     1   (0)| 00:00:01 |
|*150 |            INDEX FAST FULL SCAN                    | CMN_SEC_OBJECTS_U2          |   148 |  5032 |     2   (0)| 00:00:01 |
| 151 |           VIEW                                     | CMN_SEC_CHK_USER_V0         |     1 |    15 |   629   (2)| 00:00:18 |
| 152 |            SORT UNIQUE                             |                             |     6 |   858 |   629  (13)| 00:00:18 |
| 153 |             UNION ALL PUSHED PREDICATE             |                             |       |       |            |          |
| 154 |              NESTED LOOPS                          |                             |     1 |   112 |   552   (1)| 00:00:16 |
| 155 |               NESTED LOOPS                         |                             |     1 |   103 |   551   (1)| 00:00:16 |
|*156 |                HASH JOIN                           |                             |     1 |    57 |   550   (1)| 00:00:16 |
|*157 |                 INDEX RANGE SCAN                   | CMN_SEC_ASSGND_OBJ_PERM_N2  |     1 |    26 |     3   (0)| 00:00:01 |
|*158 |                 HASH JOIN                          |                             | 13592 |   411K|   546   (1)| 00:00:16 |
|*159 |                  HASH JOIN                         |                             | 13558 |   291K|   121   (1)| 00:00:04 |
|*160 |                   INDEX RANGE SCAN                 | CMN_SEC_USER_GROUPS_U1      |  8617 | 86170 |    29   (0)| 00:00:01 |
| 161 |                   INDEX FAST FULL SCAN             | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    92   (2)| 00:00:03 |
|*162 |                  INDEX FULL SCAN                   | CMN_SEC_GROUPS_N1           |   104K|   917K|   424   (1)| 00:00:13 |
|*163 |                INDEX RANGE SCAN                    | CMN_SEC_PERM_ELEMENTS_U1    |     1 |    46 |     2   (0)| 00:00:01 |
|*164 |               INDEX RANGE SCAN                     | CMN_SEC_GROUPS_N1           |     1 |     9 |     1   (0)| 00:00:01 |
| 165 |              NESTED LOOPS                          |                             |     1 |    72 |     3   (0)| 00:00:01 |
|*166 |               INDEX RANGE SCAN                     | CMN_SEC_PERM_ELEMENTS_U2    |     4 |   184 |     2   (0)| 00:00:01 |
|*167 |               INDEX RANGE SCAN                     | CMN_SEC_ASSGND_OBJ_PERM_N9  |     1 |    26 |     1   (0)| 00:00:01 |
| 168 |              NESTED LOOPS                          |                             |     1 |   134 |     5   (0)| 00:00:01 |
| 169 |               NESTED LOOPS                         |                             |     1 |   108 |     4   (0)| 00:00:01 |
| 170 |                NESTED LOOPS                        |                             |     1 |    71 |     3   (0)| 00:00:01 |
|*171 |                 INDEX RANGE SCAN                   | CMN_SEC_ASSGND_RIGHT_U1     |     1 |    27 |     2   (0)| 00:00:01 |
|*172 |                 INDEX RANGE SCAN                   | CMN_SEC_RIGHT_U2            |     1 |    44 |     1   (0)| 00:00:01 |
|*173 |                INDEX RANGE SCAN                    | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*174 |               INDEX RANGE SCAN                     | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 175 |              NESTED LOOPS                          |                             |     1 |   153 |    41   (3)| 00:00:02 |
| 176 |               NESTED LOOPS                         |                             |     1 |   127 |    40   (3)| 00:00:02 |
| 177 |                NESTED LOOPS                        |                             |     1 |    90 |    38   (0)| 00:00:02 |
| 178 |                 NESTED LOOPS                       |                             |     1 |    46 |    37   (0)| 00:00:02 |
|*179 |                  HASH JOIN                         |                             |     1 |    37 |    36   (0)| 00:00:02 |
|*180 |                   INDEX RANGE SCAN                 | CMN_SEC_USER_GROUPS_U1      |  8617 | 86170 |    29   (0)| 00:00:01 |
|*181 |                   INDEX FAST FULL SCAN             | CMN_SEC_ASSGND_RIGHT_U1     |     1 |    27 |     7   (0)| 00:00:01 |
|*182 |                  INDEX RANGE SCAN                  | CMN_SEC_GROUPS_N2           |     1 |     9 |     1   (0)| 00:00:01 |
|*183 |                 INDEX RANGE SCAN                   | CMN_SEC_RIGHT_U2            |     1 |    44 |     1   (0)| 00:00:01 |
|*184 |                INDEX RANGE SCAN                    | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*185 |               INDEX RANGE SCAN                     | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 186 |              NESTED LOOPS                          |                             |     1 |   157 |     9   (0)| 00:00:01 |
|*187 |               HASH JOIN                            |                             |     1 |   123 |     8   (0)| 00:00:01 |
| 188 |                NESTED LOOPS                        |                             |     4 |   384 |     6   (0)| 00:00:01 |
| 189 |                 NESTED LOOPS                       |                             |     1 |    59 |     5   (0)| 00:00:01 |
| 190 |                  NESTED LOOPS                      |                             |     1 |    50 |     4   (0)| 00:00:01 |
| 191 |                   NESTED LOOPS                     |                             |     1 |    38 |     3   (0)| 00:00:01 |
|*192 |                    INDEX RANGE SCAN                | SRM_RESOURCES_N1            |     1 |    12 |     2   (0)| 00:00:01 |
|*193 |                    INDEX RANGE SCAN                | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 194 |                   TABLE ACCESS BY INDEX ROWID      | PRJ_OBS_UNITS               |     1 |    12 |     1   (0)| 00:00:01 |
|*195 |                    INDEX UNIQUE SCAN               | PRJ_OBS_UNITS_U1            |     1 |       |     1   (0)| 00:00:01 |
|*196 |                  TABLE ACCESS BY INDEX ROWID       | PRJ_OBS_TYPES               |     1 |     9 |     1   (0)| 00:00:01 |
|*197 |                   INDEX UNIQUE SCAN                | PRJ_OBS_TYPES_U1            |     1 |       |     1   (0)| 00:00:01 |
|*198 |                 INDEX RANGE SCAN                   | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2   (0)| 00:00:01 |
| 199 |                TABLE ACCESS BY INDEX ROWID         | CMN_SEC_ASSGND_RIGHT        |     1 |    27 |     2   (0)| 00:00:01 |
|*200 |                 INDEX RANGE SCAN                   | CMN_SEC_ASSGND_RIGHT_N2     |     1 |       |     1   (0)| 00:00:01 |
|*201 |               INDEX RANGE SCAN                     | CMN_SEC_RIGHT_U1            |     1 |    34 |     1   (0)| 00:00:01 |
| 202 |              NESTED LOOPS                          |                             |     1 |   230 |    13   (0)| 00:00:01 |
| 203 |               NESTED LOOPS                         |                             |     1 |   204 |    12   (0)| 00:00:01 |
| 204 |                NESTED LOOPS                        |                             |     1 |   167 |    10   (0)| 00:00:01 |
| 205 |                 NESTED LOOPS                       |                             |     1 |   123 |     9   (0)| 00:00:01 |
| 206 |                  NESTED LOOPS                      |                             |     4 |   384 |     6   (0)| 00:00:01 |
| 207 |                   NESTED LOOPS                     |                             |     1 |    59 |     5   (0)| 00:00:01 |
| 208 |                    NESTED LOOPS                    |                             |     1 |    50 |     4   (0)| 00:00:01 |
| 209 |                     NESTED LOOPS                   |                             |     1 |    38 |     3   (0)| 00:00:01 |
|*210 |                      INDEX RANGE SCAN              | SRM_RESOURCES_N1            |     1 |    12 |     2   (0)| 00:00:01 |
|*211 |                      INDEX RANGE SCAN              | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 212 |                     TABLE ACCESS BY INDEX ROWID    | PRJ_OBS_UNITS               |     1 |    12 |     1   (0)| 00:00:01 |
|*213 |                      INDEX UNIQUE SCAN             | PRJ_OBS_UNITS_U1            |     1 |       |     1   (0)| 00:00:01 |
|*214 |                    TABLE ACCESS BY INDEX ROWID     | PRJ_OBS_TYPES               |     1 |     9 |     1   (0)| 00:00:01 |
|*215 |                     INDEX UNIQUE SCAN              | PRJ_OBS_TYPES_U1            |     1 |       |     1   (0)| 00:00:01 |
|*216 |                   INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2   (0)| 00:00:01 |
|*217 |                  INDEX RANGE SCAN                  | CMN_SEC_ASSGND_RIGHT_U1     |     1 |    27 |     1   (0)| 00:00:01 |
|*218 |                 INDEX RANGE SCAN                   | CMN_SEC_RIGHT_U2            |     1 |    44 |     1   (0)| 00:00:01 |
|*219 |                INDEX RANGE SCAN                    | OBS_UNITS_FLAT_BY_MODE_U1   |     4 |   148 |     2   (0)| 00:00:01 |
|*220 |               INDEX RANGE SCAN                     | PRJ_OBS_ASSOCIATIONS_N3     |     1 |    26 |     1   (0)| 00:00:01 |
| 221 |        TABLE ACCESS BY INDEX ROWID                 | INV_PROJECTS                |     1 |     9 |     2   (0)| 00:00:01 |
|*222 |         INDEX UNIQUE SCAN                          | INV_PROJECTS_U1             |     1 |       |     1   (0)| 00:00:01 |
|*223 |      TABLE ACCESS BY INDEX ROWID                   | PMA_SORTFOLIO_CONTENTS      |     1 |    23 |     1   (0)| 00:00:01 |
|*224 |       INDEX UNIQUE SCAN                            | PMA_SORTFOLIO_CONTENTS_N1   |     1 |       |     1   (0)| 00:00:01 |
|*225 |     INDEX UNIQUE SCAN                              | CMN_CURRENCIES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*226 |    TABLE ACCESS BY INDEX ROWID                     | CMN_CURRENCIES              |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------

Plan with oracle recommended (low cost)

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name   | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |   |     1 |    69 | 12190 |
|   1 |  SORT UNIQUE                                 |   |     1 |    69 | 12190 |
|*  2 |   FILTER                                     |   |       |       |       |
|*  3 |    HASH JOIN                                 |   |     1 |    69 | 12142 |
|   4 |     NESTED LOOPS                             |   |     1 |    56 |     7 |
|   5 |      NESTED LOOPS                            |   |     1 |    49 |     6 |
|*  6 |       TABLE ACCESS BY INDEX ROWID            | PMA_SORTFOLIO_CONTENTS   |     1 |    23 |     5 |
|*  7 |        INDEX RANGE SCAN                      | PMA_SORTFOLIO_CONTENTS_N1   |     3 |       |     3 |
|*  8 |       TABLE ACCESS BY INDEX ROWID            | INV_INVESTMENTS   |     1 |    26 |     1 |
|*  9 |        INDEX UNIQUE SCAN                     | INV_INVESTMENTS_PK   |     1 |       |     1 |
|* 10 |      TABLE ACCESS BY INDEX ROWID             | CMN_CURRENCIES   |     1 |     7 |     1 |
|* 11 |       INDEX UNIQUE SCAN                      | CMN_CURRENCIES_U1   |     1 |       |     1 |
|  12 |     VIEW                                     | VW_NSO_1   |   861 | 11193 | 12136 |
|  13 |      SORT UNIQUE                             |   |   861 | 29340 | 12136 |
|  14 |       UNION-ALL                              |   |       |       |       |
|* 15 |        HASH JOIN                             |   |   839 | 26848 |   577 |
|  16 |         VIEW                                 | VW_NSO_2   |     4 |    72 |   564 |
|  17 |          SORT UNIQUE                         |   |     4 |   444 |   564 |
|  18 |           UNION-ALL                          |   |       |       |       |
|  19 |            NESTED LOOPS                      |   |     1 |   195 |   316 |
|* 20 |             HASH JOIN                        |   |    27 |  4752 |   315 |
|  21 |              VIEW                            | CMN_SEC_CHK_USER_R_V   |    27 |  4131 |   313 |
|  22 |               SORT UNIQUE                    |   |    27 |  2865 |   313 |
|  23 |                UNION-ALL                     |   |       |       |       |
|* 24 |                 TABLE ACCESS BY INDEX ROWID  | CMN_SEC_OBJECTS   |     1 |    38 |     1 |
|  25 |                  NESTED LOOPS                |   |     1 |   118 |   139 |
|  26 |                   NESTED LOOPS               |   |     1 |    80 |   138 |
|* 27 |                    HASH JOIN                 |   |    32 |  1792 |   112 |
|* 28 |                     INDEX FAST FULL SCAN     | CMN_SEC_RIGHT_U1   |    23 |   782 |     6 |
|* 29 |                     HASH JOIN                |   | 14448 |   310K|   105 |
|* 30 |                      INDEX RANGE SCAN        | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
|  31 |                      INDEX FAST FULL SCAN    | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    69 |
|* 32 |                    INDEX RANGE SCAN          | CMN_SEC_GROUPS_N1   |     1 |    24 |     1 |
|* 33 |                   INDEX RANGE SCAN           | CMN_SEC_OBJECTS_N1   |     1 |       |     1 |
|  34 |                 NESTED LOOPS                 |   |    23 |  2231 |    21 |
|* 35 |                  HASH JOIN                   |   |    24 |  1416 |     9 |
|* 36 |                   INDEX RANGE SCAN           | CMN_SEC_ASSGND_RIGHT_U1   |   115 |  2875 |     2 |
|* 37 |                   INDEX FAST FULL SCAN       | CMN_SEC_RIGHT_U1   |    23 |   782 |     6 |
|* 38 |                  INDEX FAST FULL SCAN        | CMN_SEC_OBJECTS_U2   |     1 |    38 |     1 |
|* 39 |                 TABLE ACCESS BY INDEX ROWID  | CMN_SEC_OBJECTS   |     1 |    38 |     1 |
|  40 |                  NESTED LOOPS                |   |     3 |   516 |    13 |
|  41 |                   NESTED LOOPS               |   |     3 |   402 |    11 |
|  42 |                    NESTED LOOPS              |   |     3 |   300 |     9 |
|  43 |                     NESTED LOOPS             |   |     5 |   375 |     5 |
|  44 |                      NESTED LOOPS            |   |     1 |    38 |     3 |
|* 45 |                       INDEX RANGE SCAN       | SRM_RESOURCES_N1   |     1 |    12 |     2 |
|* 46 |                       INDEX RANGE SCAN       | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
|* 47 |                      INDEX RANGE SCAN        | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2 |
|* 48 |                     INDEX RANGE SCAN         | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    25 |     1 |
|* 49 |                    INDEX RANGE SCAN          | CMN_SEC_RIGHT_U1   |     1 |    34 |     1 |
|* 50 |                   INDEX RANGE SCAN           | CMN_SEC_OBJECTS_N1   |     1 |       |     1 |
|* 51 |              TABLE ACCESS FULL               | ODF_OBJECTS   |    95 |  2185 |     2 |
|* 52 |             INDEX UNIQUE SCAN                | ODF_OBJECT_EXTENSIONS_U1   |     1 |    19 |     1 |
|  53 |            VIEW                              | CMN_SEC_CHK_USER_R_V0   |     3 |   249 |   156 |
|  54 |             SORT UNIQUE                      |   |     3 |   275 |   156 |
|  55 |              UNION-ALL                       |   |       |       |       |
|  56 |               NESTED LOOPS                   |   |     1 |    80 |     6 |
|  57 |                NESTED LOOPS                  |   |     1 |    56 |     5 |
|  58 |                 NESTED LOOPS                 |   |     1 |    46 |     4 |
|  59 |                  TABLE ACCESS BY INDEX ROWID | CMN_SEC_RIGHT   |     1 |    34 |     3 |
|* 60 |                   INDEX RANGE SCAN           | CMN_SEC_RIGHT_N3   |     1 |       |     2 |
|* 61 |                  INDEX RANGE SCAN            | CMN_SEC_GROUP_FLAT_HIERS_U1 |    14 |   168 |     1 |
|* 62 |                 INDEX UNIQUE SCAN            | CMN_SEC_USER_GROUPS_U1   |     1 |    10 |     1 |
|* 63 |                INDEX RANGE SCAN              | CMN_SEC_GROUPS_N1   |     1 |    24 |     1 |
|  64 |               NESTED LOOPS                   |   |     1 |    59 |     4 |
|  65 |                TABLE ACCESS BY INDEX ROWID   | CMN_SEC_RIGHT   |     1 |    34 |     3 |
|* 66 |                 INDEX RANGE SCAN             | CMN_SEC_RIGHT_N3   |     1 |       |     2 |
|* 67 |                INDEX RANGE SCAN              | CMN_SEC_ASSGND_RIGHT_N3   |     1 |    25 |     1 |
|  68 |               NESTED LOOPS                   |   |     1 |   136 |     7 |
|  69 |                NESTED LOOPS                  |   |     1 |   124 |     6 |
|  70 |                 NESTED LOOPS                 |   |     1 |    98 |     5 |
|  71 |                  NESTED LOOPS                |   |     1 |    61 |     4 |
|  72 |                   TABLE ACCESS BY INDEX ROWID| CMN_SEC_RIGHT   |     1 |    34 |     3 |
|* 73 |                    INDEX RANGE SCAN          | CMN_SEC_RIGHT_N3   |     1 |       |     2 |
|* 74 |                   INDEX RANGE SCAN           | CMN_SEC_ASSGND_RIGHT_N5   |     5 |   135 |     1 |
|* 75 |                  INDEX RANGE SCAN            | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|* 76 |                 INDEX RANGE SCAN             | PRJ_OBS_ASSOCIATIONS_N4   |     2 |    52 |     1 |
|* 77 |                TABLE ACCESS BY INDEX ROWID   | SRM_RESOURCES   |     1 |    12 |     1 |
|* 78 |                 INDEX UNIQUE SCAN            | SRM_RESOURCES_PK   |     1 |       |     1 |
|  79 |         INDEX FAST FULL SCAN                 | ODF_CA_INV_U1   | 20987 |   286K|    12 |
|  80 |        VIEW                                  | CMN_SEC_CHK_USER_V0   |    17 |  1632 |   948 |
|  81 |         SORT UNIQUE                          |   |    17 |  2090 |   948 |
|  82 |          UNION-ALL                           |   |       |       |       |
|  83 |           NESTED LOOPS                       |   |    12 |  1344 |   593 |
|* 84 |            HASH JOIN                         |   |    12 |  1236 |   583 |
|* 85 |             INDEX RANGE SCAN                 | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|* 86 |             HASH JOIN                        |   |  1296 | 73872 |   580 |
|* 87 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_OBJ_PERM_N9  |  8196 |   208K|    36 |
|* 88 |              HASH JOIN                       |   | 13592 |   411K|   540 |
|* 89 |               HASH JOIN                      |   | 13558 |   291K|   105 |
|* 90 |                INDEX RANGE SCAN              | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
|  91 |                INDEX FAST FULL SCAN          | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    69 |
|* 92 |               INDEX FULL SCAN                | CMN_SEC_GROUPS_N1   |   104K|   917K|   424 |
|* 93 |            INDEX RANGE SCAN                  | CMN_SEC_GROUPS_N1   |     1 |     9 |     1 |
|  94 |           NESTED LOOPS                       |   |     1 |    72 |     4 |
|* 95 |            INDEX RANGE SCAN                  | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|* 96 |            INDEX RANGE SCAN                  | CMN_SEC_ASSGND_OBJ_PERM_N9  |     1 |    26 |     1 |
|  97 |           NESTED LOOPS                       |   |     1 |   134 |     6 |
|  98 |            NESTED LOOPS                      |   |     1 |   108 |     5 |
|  99 |             NESTED LOOPS                     |   |     1 |    71 |     3 |
|*100 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     2 |
|*101 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*102 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*103 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 104 |           NESTED LOOPS                       |   |     1 |   153 |    42 |
| 105 |            NESTED LOOPS                      |   |     1 |   127 |    41 |
| 106 |             NESTED LOOPS                     |   |     1 |    90 |    39 |
| 107 |              NESTED LOOPS                    |   |     1 |    46 |    38 |
|*108 |               HASH JOIN                      |   |     1 |    37 |    37 |
|*109 |                INDEX RANGE SCAN              | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
|*110 |                INDEX FAST FULL SCAN          | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     6 |
|*111 |               INDEX RANGE SCAN               | CMN_SEC_GROUPS_N1   |     1 |     9 |     1 |
|*112 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*113 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*114 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 115 |           NESTED LOOPS                       |   |     1 |   157 |    12 |
| 116 |            NESTED LOOPS                      |   |     1 |   123 |    11 |
| 117 |             NESTED LOOPS                     |   |     5 |   480 |     7 |
| 118 |              NESTED LOOPS                    |   |     1 |    59 |     5 |
| 119 |               NESTED LOOPS                   |   |     1 |    50 |     4 |
| 120 |                NESTED LOOPS                  |   |     1 |    38 |     3 |
|*121 |                 INDEX RANGE SCAN             | SRM_RESOURCES_N1   |     1 |    12 |     2 |
|*122 |                 INDEX RANGE SCAN             | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
| 123 |                TABLE ACCESS BY INDEX ROWID   | PRJ_OBS_UNITS   |     1 |    12 |     1 |
|*124 |                 INDEX UNIQUE SCAN            | PRJ_OBS_UNITS_U1   |     1 |       |     1 |
|*125 |               TABLE ACCESS BY INDEX ROWID    | PRJ_OBS_TYPES   |     1 |     9 |     1 |
|*126 |                INDEX UNIQUE SCAN             | PRJ_OBS_TYPES_U1   |     1 |       |     1 |
|*127 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2 |
|*128 |             INDEX RANGE SCAN                 | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     1 |
|*129 |            INDEX RANGE SCAN                  | CMN_SEC_RIGHT_U1   |     1 |    34 |     1 |
| 130 |           NESTED LOOPS                       |   |     1 |   230 |    14 |
| 131 |            NESTED LOOPS                      |   |     1 |   204 |    13 |
| 132 |             NESTED LOOPS                     |   |     1 |   167 |    12 |
| 133 |              NESTED LOOPS                    |   |     1 |   123 |    11 |
| 134 |               NESTED LOOPS                   |   |     5 |   480 |     7 |
| 135 |                NESTED LOOPS                  |   |     1 |    59 |     5 |
| 136 |                 NESTED LOOPS                 |   |     1 |    50 |     4 |
| 137 |                  NESTED LOOPS                |   |     1 |    38 |     3 |
|*138 |                   INDEX RANGE SCAN           | SRM_RESOURCES_N1   |     1 |    12 |     2 |
|*139 |                   INDEX RANGE SCAN           | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
| 140 |                  TABLE ACCESS BY INDEX ROWID | PRJ_OBS_UNITS   |     1 |    12 |     1 |
|*141 |                   INDEX UNIQUE SCAN          | PRJ_OBS_UNITS_U1   |     1 |       |     1 |
|*142 |                 TABLE ACCESS BY INDEX ROWID  | PRJ_OBS_TYPES   |     1 |     9 |     1 |
|*143 |                  INDEX UNIQUE SCAN           | PRJ_OBS_TYPES_U1   |     1 |       |     1 |
|*144 |                INDEX RANGE SCAN              | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2 |
|*145 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     1 |
|*146 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*147 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*148 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
|*149 |        HASH JOIN                             |   |     5 |   860 | 10468 |
| 150 |         NESTED LOOPS                         |   |     1 |    76 |     7 |
| 151 |          NESTED LOOPS                        |   |     2 |    84 |     3 |
|*152 |           TABLE ACCESS FULL                  | ODF_OBJECTS   |    95 |  2185 |     2 |
|*153 |           INDEX UNIQUE SCAN                  | ODF_OBJECT_EXTENSIONS_U1   |     1 |    19 |     1 |
|*154 |          INDEX RANGE SCAN                    | CMN_SEC_OBJECTS_U2   |     1 |    34 |     2 |
| 155 |         VIEW                                 | CMN_SEC_CHK_USER_V0   |  3150 |   295K| 10463 |
| 156 |          SORT UNIQUE                         |   |  3150 |   332K| 10463 |
| 157 |           UNION-ALL                          |   |       |       |       |
|*158 |            HASH JOIN                         |   |  2825 |   308K| 10058 |
|*159 |             HASH JOIN                        |   |  2818 |   283K|  9627 |
|*160 |              INDEX RANGE SCAN                | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|*161 |              HASH JOIN                       |   |   298K|    16M|  9623 |
|*162 |               HASH JOIN                      |   | 13592 |   411K|   540 |
|*163 |                HASH JOIN                     |   | 13558 |   291K|   105 |
|*164 |                 INDEX RANGE SCAN             | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
| 165 |                 INDEX FAST FULL SCAN         | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    69 |
|*166 |                INDEX FULL SCAN               | CMN_SEC_GROUPS_N1   |   104K|   917K|   424 |
|*167 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_OBJ_PERM_N9  |  2171K|    53M|  8725 |
|*168 |             INDEX FULL SCAN                  | CMN_SEC_GROUPS_N1   |   104K|   917K|   424 |
| 169 |            NESTED LOOPS                      |   |   321 | 23112 |     4 |
|*170 |             INDEX RANGE SCAN                 | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|*171 |             INDEX RANGE SCAN                 | CMN_SEC_ASSGND_OBJ_PERM_N1  |   177 |  4602 |     1 |
| 172 |            NESTED LOOPS                      |   |     1 |   134 |     6 |
| 173 |             NESTED LOOPS                     |   |     1 |   108 |     5 |
| 174 |              NESTED LOOPS                    |   |     1 |    71 |     3 |
|*175 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     2 |
|*176 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*177 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*178 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 179 |            NESTED LOOPS                      |   |     1 |   153 |    42 |
| 180 |             NESTED LOOPS                     |   |     1 |   127 |    41 |
| 181 |              NESTED LOOPS                    |   |     1 |    90 |    39 |
| 182 |               NESTED LOOPS                   |   |     1 |    46 |    38 |
|*183 |                HASH JOIN                     |   |     1 |    37 |    37 |
|*184 |                 INDEX RANGE SCAN             | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
|*185 |                 INDEX FAST FULL SCAN         | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     6 |
|*186 |                INDEX RANGE SCAN              | CMN_SEC_GROUPS_N1   |     1 |     9 |     1 |
|*187 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*188 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*189 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 190 |            NESTED LOOPS                      |   |     1 |   157 |    12 |
| 191 |             NESTED LOOPS                     |   |     1 |   123 |    11 |
| 192 |              NESTED LOOPS                    |   |     5 |   480 |     7 |
| 193 |               NESTED LOOPS                   |   |     1 |    59 |     5 |
| 194 |                NESTED LOOPS                  |   |     1 |    50 |     4 |
| 195 |                 NESTED LOOPS                 |   |     1 |    38 |     3 |
|*196 |                  INDEX RANGE SCAN            | SRM_RESOURCES_N1   |     1 |    12 |     2 |
|*197 |                  INDEX RANGE SCAN            | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
| 198 |                 TABLE ACCESS BY INDEX ROWID  | PRJ_OBS_UNITS   |     1 |    12 |     1 |
|*199 |                  INDEX UNIQUE SCAN           | PRJ_OBS_UNITS_U1   |     1 |       |     1 |
|*200 |                TABLE ACCESS BY INDEX ROWID   | PRJ_OBS_TYPES   |     1 |     9 |     1 |
|*201 |                 INDEX UNIQUE SCAN            | PRJ_OBS_TYPES_U1   |     1 |       |     1 |
|*202 |               INDEX RANGE SCAN               | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2 |
|*203 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     1 |
|*204 |             INDEX RANGE SCAN                 | CMN_SEC_RIGHT_U1   |     1 |    34 |     1 |
| 205 |            NESTED LOOPS                      |   |     1 |   230 |    14 |
| 206 |             NESTED LOOPS                     |   |     1 |   204 |    13 |
| 207 |              NESTED LOOPS                    |   |     1 |   167 |    12 |
| 208 |               NESTED LOOPS                   |   |     1 |   123 |    11 |
| 209 |                NESTED LOOPS                  |   |     5 |   480 |     7 |
| 210 |                 NESTED LOOPS                 |   |     1 |    59 |     5 |
| 211 |                  NESTED LOOPS                |   |     1 |    50 |     4 |
| 212 |                   NESTED LOOPS               |   |     1 |    38 |     3 |
|*213 |                    INDEX RANGE SCAN          | SRM_RESOURCES_N1   |     1 |    12 |     2 |
|*214 |                    INDEX RANGE SCAN          | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
| 215 |                   TABLE ACCESS BY INDEX ROWID| PRJ_OBS_UNITS   |     1 |    12 |     1 |
|*216 |                    INDEX UNIQUE SCAN         | PRJ_OBS_UNITS_U1   |     1 |       |     1 |
|*217 |                  TABLE ACCESS BY INDEX ROWID | PRJ_OBS_TYPES   |     1 |     9 |     1 |
|*218 |                   INDEX UNIQUE SCAN          | PRJ_OBS_TYPES_U1   |     1 |       |     1 |
|*219 |                 INDEX RANGE SCAN             | OBS_UNITS_FLAT_BY_MODE_U2   |     5 |   185 |     2 |
|*220 |                INDEX RANGE SCAN              | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    27 |     1 |
|*221 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2   |     1 |    44 |     1 |
|*222 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1   |     1 |    37 |     2 |
|*223 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 224 |    TABLE ACCESS BY INDEX ROWID               | INV_PROJECTS   |     1 |     9 |     2 |
|*225 |     INDEX UNIQUE SCAN                        | INV_PROJECTS_U1   |     1 |       |     1 |
--------------------------------------------------------------------------------
----------------------------

I am very much new to sqltuning.Please kindly check these plans and give some recommendations

Thanks in advance!

Thanks
Veera

Report message to a moderator

Re: sql tuning for one query [message #534824 is a reply to message #534822]

Fri, 09 December 2011 07:55

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

First of all you could format your orginal query so that's actually readable, since John's attempt didn't really suceed, and repost it here.
Secondly you could tell us what you did to get the second plan.
Thirdly you could format the second plan so that looks like the first plan where columns are actually lined up.

Report message to a moderator

Re: sql tuning for one query [message #534827 is a reply to message #534824]

Fri, 09 December 2011 08:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Worry less about cost and more about real world timings would be my advice. I've seen altered queries with costs 5 times higher than 'base', run 150+ times faster than 'base'.

Report message to a moderator

Re: sql tuning for one query [message #534835 is a reply to message #534827]

Fri, 09 December 2011 08:32

pvgangadar
Messages: 3
Registered: December 2011
Location: vsp

Junior Member

Hi

please find the formatted query

Select distinct inv_investments.id
  FROM ((SELECT invest_id ID
           FROM pma_sortfolio_contents
          WHERE sortfolio_id = 5115003
            AND invest_type IN ('project')
            AND is_Added_By_Expression = 1)) sortfolio_investments,
       (SELECT c.currency_code code
          FROM cmn_currencies c
         WHERE c.is_active = 1) obj_currency,
       inv_investments
 WHERE inv_investments.is_active = 1
   AND (inv_investments.purge_flag = 0 or
       inv_investments.purge_flag is null)
   AND obj_currency.code = inv_investments.currency_code
   AND 0 =
       (CASE WHEN inv_investments.odf_object_code = 'project' THEN
        (SELECT is_template FROM inv_projects WHERE prid = inv_investments.ID) ELSE 0 END)
   AND inv_investments.ID = sortfolio_investments.ID
   AND inv_investments.status IN (1, 5, 8)
   AND inv_investments.id IN
       (SELECT ID
          FROM ODF_CA_INV
         WHERE ODF_OBJECT_CODE IN
               (SELECT OE.OBJECT_CODE
                  FROM ODF_OBJECT_EXTENSIONS OE,
                       ODF_OBJECTS           O,
                       CMN_SEC_CHK_USER_R_V  S
                 WHERE S.USER_ID = 5545073
                   AND O.CODE = OE.OBJECT_CODE
                   AND OE.EXTENSION_CODE = 'inv'
                   AND S.OBJECT_TYPE = 'RECORD'
                   AND S.PERMISSION_CODE = 'READ'
                   AND S.OBJECT_CODE = O.RIGHT_CODE
                UNION
                SELECT 'project'
                  FROM CMN_SEC_CHK_USER_R_V0
                 WHERE USER_ID = 5545073
                   AND PERMISSION_CODE = 'prProjectViewer'
                   AND OBJECT_ID = 663)
        UNION (SELECT OBJECT_INSTANCE_ID
                FROM CMN_SEC_CHK_USER_V0
               WHERE USER_ID = 5545073
                 AND PERMISSION_CODE = 'prProjectViewer'
                 AND OBJECT_ID = 663
              UNION
              SELECT OBJECT_INSTANCE_ID
                FROM CMN_SEC_CHK_USER_V0
               WHERE USER_ID = 5545073
                 AND PERMISSION_CODE = 'READ'
                 AND OBJECT_ID IN
                     (SELECT S.ID
                        FROM ODF_OBJECT_EXTENSIONS OE,
                             ODF_OBJECTS           O,
                             CMN_SEC_OBJECTS       S
                       WHERE O.CODE = OE.OBJECT_CODE
                         AND OE.EXTENSION_CODE = 'inv'
                         AND S.OBJECT_TYPE_CODE = 'RECORD'
                         AND S.OBJECT_CODE = O.RIGHT_CODE)))

2)By applying sql tunining advisor ,it recommends this plan with alternate plan finding.

3)please find the formatted plan(which was recommended by oracle)

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name  			  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |   				  |     1 |    69 | 12190 |
|   1 |  SORT UNIQUE                                 |                          |     1 |    69 | 12190 |
|*  2 |   FILTER                                     |                          |       |       |       |
|*  3 |    HASH JOIN                                 |                          |     1 |    69 | 12142 |
|   4 |     NESTED LOOPS                             |                          |     1 |    56 |     7 |
|   5 |      NESTED LOOPS                            |                          |     1 |    49 |     6 |
|*  6 |       TABLE ACCESS BY INDEX ROWID            | PMA_SORTFOLIO_CONTENTS   |     1 |    23 |     5 |
|*  7 |        INDEX RANGE SCAN                      | PMA_SORTFOLIO_CONTENTS_N1|     3 |       |     3 |
|*  8 |       TABLE ACCESS BY INDEX ROWID            | INV_INVESTMENTS          |     1 |    26 |     1 |
|*  9 |        INDEX UNIQUE SCAN                     | INV_INVESTMENTS_PK       |     1 |       |     1 |
|* 10 |      TABLE ACCESS BY INDEX ROWID             | CMN_CURRENCIES           |     1 |     7 |     1 |
|* 11 |       INDEX UNIQUE SCAN                      | CMN_CURRENCIES_U1        |     1 |       |     1 |
|  12 |     VIEW                                     | VW_NSO_1                 |   861 | 11193 | 12136 |
|  13 |      SORT UNIQUE                             |                          |   861 | 29340 | 12136 |
|  14 |       UNION-ALL                              |                          |       |       |       |
|* 15 |        HASH JOIN                             |                          |   839 | 26848 |   577 |
|  16 |         VIEW                                 | VW_NSO_2                 |     4 |    72 |   564 |
|  17 |          SORT UNIQUE                         |                          |     4 |   444 |   564 |
|  18 |           UNION-ALL                          |                          |       |       |       |
|  19 |            NESTED LOOPS                      |                          |     1 |   195 |   316 |
|* 20 |             HASH JOIN                        |                          |    27 |  4752 |   315 |
|  21 |              VIEW                            | CMN_SEC_CHK_USER_R_V     |    27 |  4131 |   313 |
|  22 |               SORT UNIQUE                    |                          |    27 |  2865 |   313 |
|  23 |                UNION-ALL                     |                          |       |       |       |
|* 24 |                 TABLE ACCESS BY INDEX ROWID  | CMN_SEC_OBJECTS          |     1 |    38 |     1 |
|  25 |                  NESTED LOOPS                |                          |     1 |   118 |   139 |
|  26 |                   NESTED LOOPS               |                          |     1 |    80 |   138 |
|* 27 |                    HASH JOIN                 |                          |    32 |  1792 |   112 |
|* 28 |                     INDEX FAST FULL SCAN     | CMN_SEC_RIGHT_U1         |    23 |   782 |     6 |
|* 29 |                     HASH JOIN                |                          | 14448 |   310K|   105 |
|* 30 |                      INDEX RANGE SCAN        | CMN_SEC_USER_GROUPS_U1   |  8617 | 86170 |    29 |
|  31 |                      INDEX FAST FULL SCAN    | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    69 |
|* 32 |                    INDEX RANGE SCAN          | CMN_SEC_GROUPS_N1         |     1 |    24 |     1 |
|* 33 |                   INDEX RANGE SCAN           | CMN_SEC_OBJECTS_N1        |     1 |       |     1 |
|  34 |                 NESTED LOOPS                 |                           |    23 |  2231 |    21 |
|* 35 |                  HASH JOIN                   |                           |    24 |  1416 |     9 |
|* 36 |                   INDEX RANGE SCAN           | CMN_SEC_ASSGND_RIGHT_U1   |   115 |  2875 |     2 |
|* 37 |                   INDEX FAST FULL SCAN       | CMN_SEC_RIGHT_U1          |    23 |   782 |     6 |
|* 38 |                  INDEX FAST FULL SCAN        | CMN_SEC_OBJECTS_U2        |     1 |    38 |     1 |
|* 39 |                 TABLE ACCESS BY INDEX ROWID  | CMN_SEC_OBJECTS           |     1 |    38 |     1 |
|  40 |                  NESTED LOOPS                |                           |     3 |   516 |    13 |
|  41 |                   NESTED LOOPS               |                           |     3 |   402 |    11 |
|  42 |                    NESTED LOOPS              |                           |     3 |   300 |     9 |
|  43 |                     NESTED LOOPS             |                           |     5 |   375 |     5 |
|  44 |                      NESTED LOOPS            |                           |     1 |    38 |     3 |
|* 45 |                       INDEX RANGE SCAN       | SRM_RESOURCES_N1          |     1 |    12 |     2 |
|* 46 |                       INDEX RANGE SCAN       | PRJ_OBS_ASSOCIATIONS_N3   |     1 |    26 |     1 |
|* 47 |                      INDEX RANGE SCAN        | OBS_UNITS_FLAT_BY_MODE_U2 |     5 |   185 |     2 |
|* 48 |                     INDEX RANGE SCAN         | CMN_SEC_ASSGND_RIGHT_U1   |     1 |    25 |     1 |
|* 49 |                    INDEX RANGE SCAN          | CMN_SEC_RIGHT_U1          |     1 |    34 |     1 |
|* 50 |                   INDEX RANGE SCAN           | CMN_SEC_OBJECTS_N1        |     1 |       |     1 |
|* 51 |              TABLE ACCESS FULL               | ODF_OBJECTS               |    95 |  2185 |     2 |
|* 52 |             INDEX UNIQUE SCAN                | ODF_OBJECT_EXTENSIONS_U1  |     1 |    19 |     1 |
|  53 |            VIEW                              | CMN_SEC_CHK_USER_R_V0     |     3 |   249 |   156 |
|  54 |             SORT UNIQUE                      |                           |     3 |   275 |   156 |
|  55 |              UNION-ALL                       |                           |       |       |       |
|  56 |               NESTED LOOPS                   |                           |     1 |    80 |     6 |
|  57 |                NESTED LOOPS                  |                           |     1 |    56 |     5 |
|  58 |                 NESTED LOOPS                 |                           |     1 |    46 |     4 |
|  59 |                  TABLE ACCESS BY INDEX ROWID | CMN_SEC_RIGHT             |     1 |    34 |     3 |
|* 60 |                   INDEX RANGE SCAN           | CMN_SEC_RIGHT_N3          |     1 |       |     2 |
|* 61 |                  INDEX RANGE SCAN            | CMN_SEC_GROUP_FLAT_HIERS_U1 |    14 |   168 |     1 |
|* 62 |                 INDEX UNIQUE SCAN            | CMN_SEC_USER_GROUPS_U1    |     1 |    10 |     1 |
|* 63 |                INDEX RANGE SCAN              | CMN_SEC_GROUPS_N1         |     1 |    24 |     1 |
|  64 |               NESTED LOOPS                   |                           |     1 |    59 |     4 |
|  65 |                TABLE ACCESS BY INDEX ROWID   | CMN_SEC_RIGHT             |     1 |    34 |     3 |
|* 66 |                 INDEX RANGE SCAN             | CMN_SEC_RIGHT_N3          |     1 |       |     2 |
|* 67 |                INDEX RANGE SCAN              | CMN_SEC_ASSGND_RIGHT_N3   |     1 |    25 |     1 |
|  68 |               NESTED LOOPS                   |                           |     1 |   136 |     7 |
|  69 |                NESTED LOOPS                  |                           |     1 |   124 |     6 |
|  70 |                 NESTED LOOPS                 |                           |     1 |    98 |     5 |
|  71 |                  NESTED LOOPS                |                           |     1 |    61 |     4 |
|  72 |                   TABLE ACCESS BY INDEX ROWID| CMN_SEC_RIGHT             |     1 |    34 |     3 |
|* 73 |                    INDEX RANGE SCAN          | CMN_SEC_RIGHT_N3          |     1 |       |     2 |
|* 74 |                   INDEX RANGE SCAN           | CMN_SEC_ASSGND_RIGHT_N5   |     5 |   135 |     1 |
|* 75 |                  INDEX RANGE SCAN            | OBS_UNITS_FLAT_BY_MODE_U1 |     1 |    37 |     2 |
|* 76 |                 INDEX RANGE SCAN             | PRJ_OBS_ASSOCIATIONS_N4   |     2 |    52 |     1 |
|* 77 |                TABLE ACCESS BY INDEX ROWID   | SRM_RESOURCES             |     1 |    12 |     1 |
|* 78 |                 INDEX UNIQUE SCAN            | SRM_RESOURCES_PK          |     1 |       |     1 |
|  79 |         INDEX FAST FULL SCAN                 | ODF_CA_INV_U1             | 20987 |   286K|    12 |
|  80 |        VIEW                                  | CMN_SEC_CHK_USER_V0       |    17 |  1632 |   948 |
|  81 |         SORT UNIQUE                          |   				   |    17 |  2090 |   948 |
|  82 |          UNION-ALL                           |                           |       |       |       |
|  83 |           NESTED LOOPS                       |                           |    12 |  1344 |   593 |
|* 84 |            HASH JOIN                         |                           |    12 |  1236 |   583 |
|* 85 |             INDEX RANGE SCAN                 | CMN_SEC_PERM_ELEMENTS_U2  |     2 |    92 |     2 |
|* 86 |             HASH JOIN                        |                           |  1296 | 73872 |   580 |
|* 87 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_OBJ_PERM_N9|  8196 |   208K|    36 |
|* 88 |              HASH JOIN                       |                            | 13592 |   411K|   540 |
|* 89 |               HASH JOIN                      |                            | 13558 |   291K|   105 |
|* 90 |                INDEX RANGE SCAN              | CMN_SEC_USER_GROUPS_U1     |  8617 | 86170 |    29 |
|  91 |                INDEX FAST FULL SCAN          | CMN_SEC_GROUP_FLAT_HIERS_U1|   144K|  1689K|    69 |
|* 92 |               INDEX FULL SCAN                | CMN_SEC_GROUPS_N1         |   104K|   917K|   424 |
|* 93 |            INDEX RANGE SCAN                  | CMN_SEC_GROUPS_N1          |     1 |     9 |     1 |
|  94 |           NESTED LOOPS                       |                            |     1 |    72 |     4 |
|* 95 |            INDEX RANGE SCAN                  | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|* 96 |            INDEX RANGE SCAN                  | CMN_SEC_ASSGND_OBJ_PERM_N9 |     1 |    26 |     1 |
|  97 |           NESTED LOOPS                       |                            |     1 |   134 |     6 |
|  98 |            NESTED LOOPS                      |                            |     1 |   108 |     5 |
|  99 |             NESTED LOOPS                     |                            |     1 |    71 |     3 |
|*100 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     2 |
|*101 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*102 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*103 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4    |     4 |   104 |     1 |
| 104 |           NESTED LOOPS                       |                            |     1 |   153 |    42 |
| 105 |            NESTED LOOPS                      |                            |     1 |   127 |    41 |
| 106 |             NESTED LOOPS                     |                            |     1 |    90 |    39 |
| 107 |              NESTED LOOPS                    |                            |     1 |    46 |    38 |
|*108 |               HASH JOIN                      |                            |     1 |    37 |    37 |
|*109 |                INDEX RANGE SCAN              | CMN_SEC_USER_GROUPS_U1     |  8617 | 86170 |    29 |
|*110 |                INDEX FAST FULL SCAN          | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     6 |
|*111 |               INDEX RANGE SCAN               | CMN_SEC_GROUPS_N1          |     1 |     9 |     1 |
|*112 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*113 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*114 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4    |     4 |   104 |     1 |
| 115 |           NESTED LOOPS                       |                            |     1 |   157 |    12 |
| 116 |            NESTED LOOPS                      |                            |     1 |   123 |    11 |
| 117 |             NESTED LOOPS                     |                            |     5 |   480 |     7 |
| 118 |              NESTED LOOPS                    |                            |     1 |    59 |     5 |
| 119 |               NESTED LOOPS                   |                            |     1 |    50 |     4 |
| 120 |                NESTED LOOPS                  |                            |     1 |    38 |     3 |
|*121 |                 INDEX RANGE SCAN             |  SRM_RESOURCES_N1          |     1 |    12 |     2 |
|*122 |                 INDEX RANGE SCAN             | PRJ_OBS_ASSOCIATIONS_N3    |     1 |    26 |     1 |
| 123 |                TABLE ACCESS BY INDEX ROWID   | PRJ_OBS_UNITS              |     1 |    12 |     1 |
|*124 |                 INDEX UNIQUE SCAN            | PRJ_OBS_UNITS_U1           |     1 |       |     1 |
|*125 |               TABLE ACCESS BY INDEX ROWID    | PRJ_OBS_TYPES              |     1 |     9 |     1 |
|*126 |                INDEX UNIQUE SCAN             | PRJ_OBS_TYPES_U1           |     1 |       |     1 |
|*127 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U2  |     5 |   185 |     2 |
|*128 |             INDEX RANGE SCAN                 | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     1 |
|*129 |            INDEX RANGE SCAN                  | CMN_SEC_RIGHT_U1           |     1 |    34 |     1 |
| 130 |           NESTED LOOPS                       |                            |     1 |   230 |    14 |
| 131 |            NESTED LOOPS                      |                            |     1 |   204 |    13 |
| 132 |             NESTED LOOPS                     |                            |     1 |   167 |    12 |
| 133 |              NESTED LOOPS                    |                            |     1 |   123 |    11 |
| 134 |               NESTED LOOPS                   |                            |     5 |   480 |     7 |
| 135 |                NESTED LOOPS                  |                            |     1 |    59 |     5 |
| 136 |                 NESTED LOOPS                 |                            |     1 |    50 |     4 |
| 137 |                  NESTED LOOPS                |                            |     1 |    38 |     3 |
|*138 |                   INDEX RANGE SCAN           | SRM_RESOURCES_N1           |     1 |    12 |     2 |
|*139 |                   INDEX RANGE SCAN           | PRJ_OBS_ASSOCIATIONS_N3    |     1 |    26 |     1 |
| 140 |                  TABLE ACCESS BY INDEX ROWID | PRJ_OBS_UNITS              |     1 |    12 |     1 |
|*141 |                   INDEX UNIQUE SCAN          | PRJ_OBS_UNITS_U1           |     1 |       |     1 |
|*142 |                 TABLE ACCESS BY INDEX ROWID  | PRJ_OBS_TYPES              |     1 |     9 |     1 |
|*143 |                  INDEX UNIQUE SCAN           | PRJ_OBS_TYPES_U1           |     1 |       |     1 |
|*144 |                INDEX RANGE SCAN              | OBS_UNITS_FLAT_BY_MODE_U2  |     5 |   185 |     2 |
|*145 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     1 |
|*146 |              INDEX RANGE SCAN                | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*147 |             INDEX RANGE SCAN                 | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*148 |            INDEX RANGE SCAN                  | PRJ_OBS_ASSOCIATIONS_N4    |     4 |   104 |     1 |
|*149 |        HASH JOIN                             |                            |     5 |   860 | 10468 |
| 150 |         NESTED LOOPS                         |                            |     1 |    76 |     7 |
| 151 |          NESTED LOOPS                        |                            |     2 |    84 |     3 |
|*152 |           TABLE ACCESS FULL                  | ODF_OBJECTS                |    95 |  2185 |     2 |
|*153 |           INDEX UNIQUE SCAN                  | ODF_OBJECT_EXTENSIONS_U1   |     1 |    19 |     1 |
|*154 |          INDEX RANGE SCAN                    | CMN_SEC_OBJECTS_U2         |     1 |    34 |     2 |
| 155 |         VIEW                                 | CMN_SEC_CHK_USER_V0        |  3150 |   295K| 10463 |
| 156 |          SORT UNIQUE                         |                            |  3150 |   332K| 10463 |
| 157 |           UNION-ALL                          |                            |       |       |       |
|*158 |            HASH JOIN                         |                            |  2825 |   308K| 10058 |
|*159 |             HASH JOIN                        |                            |  2818 |   283K|  9627 |
|*160 |              INDEX RANGE SCAN                | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|*161 |              HASH JOIN                       |                            |   298K|    16M|  9623 |
|*162 |               HASH JOIN                      |                            | 13592 |   411K|   540 |
|*163 |                HASH JOIN                     |                            | 13558 |   291K|   105 |
|*164 |                 INDEX RANGE SCAN             | CMN_SEC_USER_GROUPS_U1     |  8617 | 86170 |    29 |
| 165 |                 INDEX FAST FULL SCAN         | CMN_SEC_GROUP_FLAT_HIERS_U1 |   144K|  1689K|    69 |
|*166 |                INDEX FULL SCAN               | CMN_SEC_GROUPS_N1           |   104K|   917K|   424 |
|*167 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_OBJ_PERM_N9  |  2171K|    53M|  8725 |
|*168 |             INDEX FULL SCAN                  | CMN_SEC_GROUPS_N1           |   104K|   917K|   424 |
| 169 |            NESTED LOOPS                      |                             |   321 | 23112 |     4 |
|*170 |             INDEX RANGE SCAN                 | CMN_SEC_PERM_ELEMENTS_U2   |     2 |    92 |     2 |
|*171 |             INDEX RANGE SCAN                 | CMN_SEC_ASSGND_OBJ_PERM_N1 |   177 |  4602 |     1 |
| 172 |            NESTED LOOPS                      |                            |     1 |   134 |     6 |
| 173 |             NESTED LOOPS                     |                            |     1 |   108 |     5 |
| 174 |              NESTED LOOPS                    |                            |     1 |    71 |     3 |
|*175 |               INDEX RANGE SCAN               | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     2 |
|*176 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*177 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*178 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4   |     4 |   104 |     1 |
| 179 |            NESTED LOOPS                      |                            |     1 |   153 |    42 |
| 180 |             NESTED LOOPS                     |                            |     1 |   127 |    41 |
| 181 |              NESTED LOOPS                    |                            |     1 |    90 |    39 |
| 182 |               NESTED LOOPS                   |                            |     1 |    46 |    38 |
|*183 |                HASH JOIN                     |                            |     1 |    37 |    37 |
|*184 |                 INDEX RANGE SCAN             | CMN_SEC_USER_GROUPS_U1     |  8617 | 86170 |    29 |
|*185 |                 INDEX FAST FULL SCAN         | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     6 |
|*186 |                INDEX RANGE SCAN              | CMN_SEC_GROUPS_N1          |     1 |     9 |     1 |
|*187 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*188 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*189 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4    |     4 |   104 |     1 |
| 190 |            NESTED LOOPS                      |                            |     1 |   157 |    12 |
| 191 |             NESTED LOOPS                     |                            |     1 |   123 |    11 |
| 192 |              NESTED LOOPS                    |                            |     5 |   480 |     7 |
| 193 |               NESTED LOOPS                   |                            |     1 |    59 |     5 |
| 194 |                NESTED LOOPS                  |                            |     1 |    50 |     4 |
| 195 |                 NESTED LOOPS                 |                            |     1 |    38 |     3 |
|*196 |                  INDEX RANGE SCAN            | SRM_RESOURCES_N1           |     1 |    12 |     2 |
|*197 |                  INDEX RANGE SCAN            | PRJ_OBS_ASSOCIATIONS_N3    |     1 |    26 |     1 |
| 198 |                 TABLE ACCESS BY INDEX ROWID  | PRJ_OBS_UNITS              |     1 |    12 |     1 |
|*199 |                  INDEX UNIQUE SCAN           | PRJ_OBS_UNITS_U1           |     1 |       |     1 |
|*200 |                TABLE ACCESS BY INDEX ROWID   | PRJ_OBS_TYPES              |     1 |     9 |     1 |
|*201 |                 INDEX UNIQUE SCAN            | PRJ_OBS_TYPES_U1           |     1 |       |     1 |
|*202 |               INDEX RANGE SCAN               | OBS_UNITS_FLAT_BY_MODE_U2  |     5 |   185 |     2 |
|*203 |              INDEX RANGE SCAN                | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     1 |
|*204 |             INDEX RANGE SCAN                 | CMN_SEC_RIGHT_U1           |     1 |    34 |     1 |
| 205 |            NESTED LOOPS                      |                            |     1 |   230 |    14 |
| 206 |             NESTED LOOPS                     |                            |     1 |   204 |    13 |
| 207 |              NESTED LOOPS                    |                            |     1 |   167 |    12 |
| 208 |               NESTED LOOPS                   |                            |     1 |   123 |    11 |
| 209 |                NESTED LOOPS                  |                            |     5 |   480 |     7 |
| 210 |                 NESTED LOOPS                 |                            |     1 |    59 |     5 |
| 211 |                  NESTED LOOPS                |                            |     1 |    50 |     4 |
| 212 |                   NESTED LOOPS               |                            |     1 |    38 |     3 |
|*213 |                    INDEX RANGE SCAN          | SRM_RESOURCES_N1           |     1 |    12 |     2 |
|*214 |                    INDEX RANGE SCAN          | PRJ_OBS_ASSOCIATIONS_N3    |     1 |    26 |     1 |
| 215 |                   TABLE ACCESS BY INDEX ROWID| PRJ_OBS_UNITS              |     1 |    12 |     1 |
|*216 |                    INDEX UNIQUE SCAN         | PRJ_OBS_UNITS_U1           |     1 |       |     1 |
|*217 |                  TABLE ACCESS BY INDEX ROWID | PRJ_OBS_TYPES              |     1 |     9 |     1 |
|*218 |                   INDEX UNIQUE SCAN          | PRJ_OBS_TYPES_U1           |     1 |       |     1 |
|*219 |                 INDEX RANGE SCAN             | OBS_UNITS_FLAT_BY_MODE_U2  |     5 |   185 |     2 |
|*220 |                INDEX RANGE SCAN              | CMN_SEC_ASSGND_RIGHT_U1    |     1 |    27 |     1 |
|*221 |               INDEX RANGE SCAN               | CMN_SEC_RIGHT_U2           |     1 |    44 |     1 |
|*222 |              INDEX RANGE SCAN                | OBS_UNITS_FLAT_BY_MODE_U1  |     1 |    37 |     2 |
|*223 |             INDEX RANGE SCAN                 | PRJ_OBS_ASSOCIATIONS_N4    |     4 |   104 |     1 |
| 224 |    TABLE ACCESS BY INDEX ROWID               | INV_PROJECTS               |     1 |     9 |     2 |
|*225 |     INDEX UNIQUE SCAN                        | INV_PROJECTS_U1            |     1 |       |     1 |
-----------------------------------------------------------------------------------------------------------

Thanks
Ganga

Report message to a moderator

Re: sql tuning for one query [message #534836 is a reply to message #534835]

Fri, 09 December 2011 08:41

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Without more detail what I can suggest/ask so far is:

How many rows are returned?
How long is the execution time currently?
What is the target execution time?
Have you tried breaking it apart to see if one part of it is the obvious performance hole?

Report message to a moderator

Re: sql tuning for one query [message #534838 is a reply to message #534835]

Fri, 09 December 2011 08:48

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Hello again. Ganga, I don't understand the problem. If I understand you correctly, the SQL Tuning Advisor
has come up with some advice. What is the issue? Does it actually run more slowly when you accept it?
Of course, you must not be put off by the values in the "cost" column of the plans: they have no absolute meaning at all,
they are only the relative cost of each step.

Report message to a moderator

Previous Topic:	SQL Profiles
Next Topic:	Difficulty in using bind variable to check explain plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 15:55:43 CST 2025