Home » RDBMS Server » Performance Tuning » Ugly View Not Cooperating
Ugly View Not Cooperating [message #548216] Tue, 20 March 2012 19:19 Go to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
Hi all,

I have an ugly view containing over a dozen tables that we have to run queries against. There are about 10 million rows in each of four of the tables contained in the view. The column that we always drive off, net_device_id, is the primary key of one of the 10 million row tables. When I use a simple IN clause, it yields the desired explain plan. (It's desired because it has proven to be fast).
SELECT * FROM network_device_all nda WHERE 1 = 1 AND net_device_id IN (1)
 
Plan hash value: 2893850283
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |       |       |    58 (100)|          |       |       |
|   1 |  SORT AGGREGATE                                              |                              |     1 |    14 |            |          |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | BILLING_CYCLE                |     1 |    14 |     4   (0)| 00:00:01 |       |       |
|*  3 |    INDEX SKIP SCAN                                           | BILLING_CYCLE_UK1            |    24 |       |     3   (0)| 00:00:01 |       |       |
|   4 |  SORT AGGREGATE                                              |                              |     1 |    14 |            |          |       |       |
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | BILLING_CYCLE                |     1 |    14 |     4   (0)| 00:00:01 |       |       |
|*  6 |    INDEX SKIP SCAN                                           | BILLING_CYCLE_UK1            |    24 |       |     3   (0)| 00:00:01 |       |       |
|   7 |  NESTED LOOPS OUTER                                          |                              |     3 |   134K|    58   (0)| 00:00:01 |       |       |
|   8 |   NESTED LOOPS OUTER                                         |                              |     3 |   134K|    55   (0)| 00:00:01 |       |       |
|   9 |    NESTED LOOPS OUTER                                        |                              |     3 |   134K|    52   (0)| 00:00:01 |       |       |
|  10 |     NESTED LOOPS OUTER                                       |                              |     3 |   134K|    49   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS OUTER                                      |                              |     2 | 91550 |    47   (0)| 00:00:01 |       |       |
|  12 |       NESTED LOOPS OUTER                                     |                              |     2 | 91498 |    45   (0)| 00:00:01 |       |       |
|  13 |        NESTED LOOPS OUTER                                    |                              |     1 | 45731 |    44   (0)| 00:00:01 |       |       |
|  14 |         NESTED LOOPS OUTER                                   |                              |     1 | 45705 |    43   (0)| 00:00:01 |       |       |
|  15 |          NESTED LOOPS OUTER                                  |                              |     1 | 45687 |    42   (0)| 00:00:01 |       |       |
|  16 |           NESTED LOOPS OUTER                                 |                              |     1 | 45661 |    41   (0)| 00:00:01 |       |       |
|  17 |            NESTED LOOPS OUTER                                |                              |     1 | 45643 |    40   (0)| 00:00:01 |       |       |
|  18 |             NESTED LOOPS OUTER                               |                              |     1 | 45617 |    39   (0)| 00:00:01 |       |       |
|  19 |              NESTED LOOPS OUTER                              |                              |     1 | 45591 |    38   (0)| 00:00:01 |       |       |
|  20 |               NESTED LOOPS OUTER                             |                              |     1 | 45565 |    37   (0)| 00:00:01 |       |       |
|  21 |                NESTED LOOPS OUTER                            |                              |     1 | 45539 |    36   (0)| 00:00:01 |       |       |
|  22 |                 NESTED LOOPS OUTER                           |                              |     1 | 45521 |    35   (0)| 00:00:01 |       |       |
|  23 |                  NESTED LOOPS OUTER                          |                              |     1 | 45503 |    34   (0)| 00:00:01 |       |       |
|  24 |                   NESTED LOOPS OUTER                         |                              |     1 | 45485 |    33   (0)| 00:00:01 |       |       |
|  25 |                    NESTED LOOPS OUTER                        |                              |     1 | 45467 |    32   (0)| 00:00:01 |       |       |
|  26 |                     NESTED LOOPS OUTER                       |                              |     1 | 45415 |    31   (0)| 00:00:01 |       |       |
|  27 |                      NESTED LOOPS OUTER                      |                              |     1 | 45407 |    30   (0)| 00:00:01 |       |       |
|  28 |                       NESTED LOOPS OUTER                     |                              |     1 | 45399 |    29   (0)| 00:00:01 |       |       |
|  29 |                        VIEW                                  |                              |     1 | 45373 |    28   (0)| 00:00:01 |       |       |
|  30 |                         NESTED LOOPS OUTER                   |                              |     1 |  1598 |    28   (0)| 00:00:01 |       |       |
|  31 |                          NESTED LOOPS OUTER                  |                              |     1 |  1572 |    27   (0)| 00:00:01 |       |       |
|  32 |                           NESTED LOOPS OUTER                 |                              |     1 |  1523 |    25   (0)| 00:00:01 |       |       |
|  33 |                            NESTED LOOPS OUTER                |                              |     1 |  1470 |    24   (0)| 00:00:01 |       |       |
|  34 |                             NESTED LOOPS OUTER               |                              |     1 |  1449 |    23   (0)| 00:00:01 |       |       |
|  35 |                              NESTED LOOPS OUTER              |                              |     1 |  1428 |    22   (0)| 00:00:01 |       |       |
|  36 |                               NESTED LOOPS OUTER             |                              |     1 |  1407 |    21   (0)| 00:00:01 |       |       |
|  37 |                                NESTED LOOPS OUTER            |                              |     1 |  1386 |    20   (0)| 00:00:01 |       |       |
|  38 |                                 NESTED LOOPS OUTER           |                              |     1 |  1365 |    19   (0)| 00:00:01 |       |       |
|  39 |                                  NESTED LOOPS OUTER          |                              |     1 |  1296 |    18   (0)| 00:00:01 |       |       |
|  40 |                                   NESTED LOOPS OUTER         |                              |     1 |  1227 |    17   (0)| 00:00:01 |       |       |
|  41 |                                    NESTED LOOPS OUTER        |                              |     1 |  1168 |    16   (0)| 00:00:01 |       |       |
|  42 |                                     NESTED LOOPS             |                              |     1 |  1090 |    15   (0)| 00:00:01 |       |       |
|  43 |                                      NESTED LOOPS            |                              |     1 |   737 |    13   (0)| 00:00:01 |       |       |
|  44 |                                       NESTED LOOPS           |                              |     1 |   683 |    11   (0)| 00:00:01 |       |       |
|  45 |                                        NESTED LOOPS          |                              |     1 |   670 |    10   (0)| 00:00:01 |       |       |
|  46 |                                         NESTED LOOPS         |                              |     1 |   641 |     9   (0)| 00:00:01 |       |       |
|  47 |                                          NESTED LOOPS        |                              |     1 |   612 |     8   (0)| 00:00:01 |       |       |
|  48 |                                           NESTED LOOPS       |                              |     1 |   591 |     7   (0)| 00:00:01 |       |       |
|  49 | R                                          NESTED LOOPS OUTE |                              |     1 |   570 |     6   (0)| 00:00:01 |       |       |
|  50 | ER                                          NESTED LOOPS OUT |                              |     1 |   549 |     5   (0)| 00:00:01 |       |       |
|  51 |                                              NESTED LOOPS    |                              |     1 |   423 |     4   (0)| 00:00:01 |       |       |
|  52 | Y INDEX ROWID                                 TABLE ACCESS B | UIQ_DB_SWITCH                |     1 |    43 |     2   (0)| 00:00:01 |       |       |
|* 53 | SCAN                                           INDEX UNIQUE  | UIQ_DB_SWITCH_UX1            |     1 |       |     1   (0)| 00:00:01 |       |       |
|  54 | Y GLOBAL INDEX ROWID                          TABLE ACCESS B | DEVICE                       |     1 |   380 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 55 | SCAN                                           INDEX UNIQUE  | DEVICE_PK                    |     1 |       |     1   (0)| 00:00:01 |       |       |
|  56 |  INDEX ROWID                                 TABLE ACCESS BY | NETWORK_DEVICE_TYPE          |    17 |  2142 |     1   (0)| 00:00:01 |       |       |
|* 57 | CAN                                           INDEX UNIQUE S | NETWORK_DEVICE_TYPE_PK       |     1 |       |     0   (0)|          |       |       |
|  58 | INDEX ROWID                                 TABLE ACCESS BY  | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 59 | AN                                           INDEX UNIQUE SC | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  60 | NDEX ROWID                                 TABLE ACCESS BY I | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 61 | N                                           INDEX UNIQUE SCA | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  62 | DEX ROWID                                 TABLE ACCESS BY IN | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 63 |                                            INDEX UNIQUE SCAN | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  64 | EX ROWID                                 TABLE ACCESS BY IND | MASTER_TYPE                  |  2048 | 59392 |     1   (0)| 00:00:01 |       |       |
|* 65 |                                           INDEX UNIQUE SCAN  | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  66 | X ROWID                                 TABLE ACCESS BY INDE | MASTER_TYPE                  |  2048 | 59392 |     1   (0)| 00:00:01 |       |       |
|* 67 |                                          INDEX UNIQUE SCAN   | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  68 |  ROWID                                 TABLE ACCESS BY INDEX | MASTER_TYPE                  |  2048 | 26624 |     1   (0)| 00:00:01 |       |       |
|* 69 |                                         INDEX UNIQUE SCAN    | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  70 | R                                     PARTITION LIST ITERATO |                              |     1 |    54 |     2   (0)| 00:00:01 |   KEY |   KEY |
|  71 |  INDEX ROWID                           TABLE ACCESS BY LOCAL | DEVICE_DATA_TIMESTAMPS       |     1 |    54 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 72 |                                         INDEX UNIQUE SCAN    | DEVICE_DATA_TIMESTAMPS_PK    |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  73 | INDEX ROWID                          TABLE ACCESS BY GLOBAL  | NIC                          |  9391K|  3161M|     2   (0)| 00:00:01 | ROWID | ROWID |
|* 74 |                                       INDEX UNIQUE SCAN      | NIC_PK                       |     1 |       |     1   (0)| 00:00:01 |       |       |
|  75 | WID                                 TABLE ACCESS BY INDEX RO | UTILOS_SW                    |    11 |   858 |     1   (0)| 00:00:01 |       |       |
|* 76 |                                      INDEX UNIQUE SCAN       | UTILOS_SW_PK                 |     1 |       |     0   (0)|          |       |       |
|  77 | ID                                 TABLE ACCESS BY INDEX ROW | NIC_TYPE                     |     9 |   531 |     1   (0)| 00:00:01 |       |       |
|* 78 |                                     INDEX UNIQUE SCAN        | NIC_TYPE_PK                  |     1 |       |     0   (0)|          |       |       |
|  79 | D                                 TABLE ACCESS BY INDEX ROWI | IMU_TYPE                     | 20000 |  1347K|     1   (0)| 00:00:01 |       |       |
|* 80 |                                    INDEX UNIQUE SCAN         | IMU_TYPE_PK                  |     1 |       |     0   (0)|          |       |       |
|  81 |                                  TABLE ACCESS BY INDEX ROWID | WAN_TYPE                     |     1 |    69 |     1   (0)| 00:00:01 |       |       |
|* 82 |                                   INDEX UNIQUE SCAN          | WAN_TYPE_PK                  |     1 |       |     0   (0)|          |       |       |
|  83 |                                 TABLE ACCESS BY INDEX ROWID  | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 84 |                                  INDEX UNIQUE SCAN           | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  85 |                                TABLE ACCESS BY INDEX ROWID   | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 86 |                                 INDEX UNIQUE SCAN            | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  87 |                               TABLE ACCESS BY INDEX ROWID    | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 88 |                                INDEX UNIQUE SCAN             | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  89 |                              TABLE ACCESS BY INDEX ROWID     | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 90 |                               INDEX UNIQUE SCAN              | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  91 |                             TABLE ACCESS BY INDEX ROWID      | MASTER_TYPE                  |  2048 | 43008 |     1   (0)| 00:00:01 |       |       |
|* 92 |                              INDEX UNIQUE SCAN               | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  93 | D                          TABLE ACCESS BY GLOBAL INDEX ROWI | DEVICE                       |  9391K|   474M|     1   (0)| 00:00:01 | ROWID | ROWID |
|* 94 |                             INDEX UNIQUE SCAN                | DEVICE_PK                    |     1 |       |     1   (0)| 00:00:01 |       |       |
|  95 |                           TABLE ACCESS BY GLOBAL INDEX ROWID | DEVICE                       |  9391K|   438M|     2   (0)| 00:00:01 | ROWID | ROWID |
|* 96 |                            INDEX UNIQUE SCAN                 | DEVICE_PK                    |     1 |       |     1   (0)| 00:00:01 |       |       |
|  97 |                          TABLE ACCESS BY INDEX ROWID         | MASTER_TYPE                  |  2048 | 53248 |     1   (0)| 00:00:01 |       |       |
|* 98 |                           INDEX UNIQUE SCAN                  | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
|  99 |                        TABLE ACCESS BY INDEX ROWID           | UIQ_METER_PROGRAM_CAL        |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*100 |                         INDEX UNIQUE SCAN                    | UIQ_METER_PROGRAM_CAL_PK     |     1 |       |     0   (0)|          |       |       |
| 101 |                       TABLE ACCESS BY INDEX ROWID            | UIQ_METER_PROGRAM_TOU        |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|*102 |                        INDEX UNIQUE SCAN                     | UIQ_METER_PROGRAM_TOU_PK     |     1 |       |     0   (0)|          |       |       |
| 103 |                      TABLE ACCESS BY INDEX ROWID             | UIQ_METER_PROGRAM_LP         |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|*104 |                       INDEX UNIQUE SCAN                      | UIQ_METER_PROGRAM_LP_PK      |     1 |       |     0   (0)|          |       |       |
| 105 |                     TABLE ACCESS BY INDEX ROWID              | UIQ_METER_PROGRAM            |     1 |    52 |     1   (0)| 00:00:01 |       |       |
|*106 |                      INDEX UNIQUE SCAN                       | UIQ_METER_PROGRAM_PK         |     1 |       |     0   (0)|          |       |       |
| 107 |                    TABLE ACCESS BY INDEX ROWID               | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*108 |                     INDEX UNIQUE SCAN                        | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |     0   (0)|          |       |       |
| 109 |                   TABLE ACCESS BY INDEX ROWID                | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*110 |                    INDEX UNIQUE SCAN                         | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |     0   (0)|          |       |       |
| 111 |                  TABLE ACCESS BY INDEX ROWID                 | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*112 |                   INDEX UNIQUE SCAN                          | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |     0   (0)|          |       |       |
| 113 |                 TABLE ACCESS BY INDEX ROWID                  | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*114 |                  INDEX UNIQUE SCAN                           | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |     0   (0)|          |       |       |
| 115 |                TABLE ACCESS BY INDEX ROWID                   | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*116 |                 INDEX UNIQUE SCAN                            | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 117 |               TABLE ACCESS BY INDEX ROWID                    | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*118 |                INDEX UNIQUE SCAN                             | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 119 |              TABLE ACCESS BY INDEX ROWID                     | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*120 |               INDEX UNIQUE SCAN                              | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 121 |             TABLE ACCESS BY INDEX ROWID                      | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*122 |              INDEX UNIQUE SCAN                               | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 123 |            TABLE ACCESS BY INDEX ROWID                       | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*124 |             INDEX UNIQUE SCAN                                | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |     0   (0)|          |       |       |
| 125 |           TABLE ACCESS BY INDEX ROWID                        | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*126 |            INDEX UNIQUE SCAN                                 | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 127 |          TABLE ACCESS BY INDEX ROWID                         | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*128 |           INDEX UNIQUE SCAN                                  | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |     0   (0)|          |       |       |
| 129 |         TABLE ACCESS BY INDEX ROWID                          | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*130 |          INDEX UNIQUE SCAN                                   | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 131 |        TABLE ACCESS BY INDEX ROWID                           | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*132 |         INDEX UNIQUE SCAN                                    | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |     0   (0)|          |       |       |
| 133 |       TABLE ACCESS BY INDEX ROWID                            | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*134 |        INDEX UNIQUE SCAN                                     | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 135 |      TABLE ACCESS BY INDEX ROWID                             | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|*136 |       INDEX UNIQUE SCAN                                      | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |     0   (0)|          |       |       |
| 137 |     TABLE ACCESS BY INDEX ROWID                              | MASTER_TYPE                  |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|*138 |      INDEX UNIQUE SCAN                                       | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
| 139 |    TABLE ACCESS BY INDEX ROWID                               | BATTERY                      |     1 |    90 |     1   (0)| 00:00:01 |       |       |
|*140 |     INDEX UNIQUE SCAN                                        | BATTERY_PK                   |     1 |       |     0   (0)|          |       |       |
| 141 |   TABLE ACCESS BY INDEX ROWID                                | MASTER_TYPE                  |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|*142 |    INDEX UNIQUE SCAN                                         | MASTER_TYPE_PK               |     1 |       |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------------------------------------------------------------


If, however, the IN clause is a subquery, the plan sucks. (It sucks because it takes a very long time to run.) The subquery has only one record that matchees
SQL> SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009;
 
                          NET_DEVICE_ID
---------------------------------------
                                9170100
So I would really like it to use the same execution plan. It, instead, generates this ugliness.
SQL_ID  anqu9t32u3jg4, child number 0
-------------------------------------
SELECT * FROM network_device_all nda WHERE 1 = 1 AND net_device_id IN 
(SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
 
Plan hash value: 1841300360
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |       |       |       |  1604K(100)|          |       |       |
|   1 |  SORT AGGREGATE                                              |                              |     1 |    14 |       |            |          |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | BILLING_CYCLE                |     1 |    14 |       |     4   (0)| 00:00:01 |       |       |
|*  3 |    INDEX SKIP SCAN                                           | BILLING_CYCLE_UK1            |    24 |       |       |     3   (0)| 00:00:01 |       |       |
|   4 |  SORT AGGREGATE                                              |                              |     1 |    14 |       |            |          |       |       |
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | BILLING_CYCLE                |     1 |    14 |       |     4   (0)| 00:00:01 |       |       |
|*  6 |    INDEX SKIP SCAN                                           | BILLING_CYCLE_UK1            |    24 |       |       |     3   (0)| 00:00:01 |       |       |
|   7 |  NESTED LOOPS OUTER                                          |                              |     3 |   134K|       |  1604K  (1)| 05:20:56 |       |       |
|   8 |   NESTED LOOPS OUTER                                         |                              |     3 |   134K|       |  1604K  (1)| 05:20:56 |       |       |
|   9 |    NESTED LOOPS OUTER                                        |                              |     2 | 91808 |       |  1604K  (1)| 05:20:56 |       |       |
|  10 |     NESTED LOOPS OUTER                                       |                              |     2 | 91628 |       |  1604K  (1)| 05:20:56 |       |       |
|  11 |      NESTED LOOPS OUTER                                      |                              |     2 | 91576 |       |  1604K  (1)| 05:20:56 |       |       |
|  12 |       NESTED LOOPS OUTER                                     |                              |     2 | 91524 |       |  1604K  (1)| 05:20:56 |       |       |
|  13 |        NESTED LOOPS OUTER                                    |                              |     2 | 91472 |       |  1604K  (1)| 05:20:56 |       |       |
|  14 |         NESTED LOOPS OUTER                                   |                              |     2 | 91420 |       |  1604K  (1)| 05:20:56 |       |       |
|  15 |          NESTED LOOPS OUTER                                  |                              |     2 | 91384 |       |  1604K  (1)| 05:20:55 |       |       |
|  16 |           NESTED LOOPS OUTER                                 |                              |     2 | 91348 |       |  1604K  (1)| 05:20:55 |       |       |
|  17 |            NESTED LOOPS OUTER                                |                              |     1 | 45656 |       |  1604K  (1)| 05:20:55 |       |       |
|  18 |             NESTED LOOPS OUTER                               |                              |     1 | 45630 |       |  1604K  (1)| 05:20:55 |       |       |
|  19 |              NESTED LOOPS OUTER                              |                              |     1 | 45604 |       |  1604K  (1)| 05:20:55 |       |       |
|  20 |               NESTED LOOPS OUTER                             |                              |     1 | 45578 |       |  1604K  (1)| 05:20:55 |       |       |
|  21 |                NESTED LOOPS OUTER                            |                              |     1 | 45560 |       |  1604K  (1)| 05:20:55 |       |       |
|  22 |                 NESTED LOOPS OUTER                           |                              |     1 | 45542 |       |  1604K  (1)| 05:20:55 |       |       |
|  23 |                  NESTED LOOPS OUTER                          |                              |     1 | 45524 |       |  1604K  (1)| 05:20:55 |       |       |
|  24 |                   NESTED LOOPS OUTER                         |                              |     1 | 45506 |       |  1604K  (1)| 05:20:55 |       |       |
|  25 |                    NESTED LOOPS OUTER                        |                              |     1 | 45454 |       |  1604K  (1)| 05:20:55 |       |       |
|  26 |                     NESTED LOOPS OUTER                       |                              |     1 | 45446 |       |  1604K  (1)| 05:20:55 |       |       |
|  27 |                      NESTED LOOPS OUTER                      |                              |     1 | 45438 |       |  1604K  (1)| 05:20:55 |       |       |
|  28 |                       NESTED LOOPS OUTER                     |                              |     1 | 45425 |       |  1604K  (1)| 05:20:55 |       |       |
|* 29 |                        HASH JOIN                             |                              |     1 | 45399 |       |  1604K  (1)| 05:20:55 |       |       |
|  30 |                         SORT UNIQUE                          |                              |     1 |    26 |       |    47   (0)| 00:00:01 |       |       |
|  31 |                          PARTITION RANGE ALL                 |                              |     1 |    26 |       |    47   (0)| 00:00:01 |     1 |    45 |
|  32 |                           TABLE ACCESS BY LOCAL INDEX ROWID  | TMP_DEVICE_SEARCH            |     1 |    26 |       |    47   (0)| 00:00:01 |     1 |    45 |
|* 33 |                            INDEX RANGE SCAN                  | DEVICE_SEARCH_KEY_IDX        |     1 |       |       |    46   (0)| 00:00:01 |     1 |    45 |
|  34 |                         VIEW                                 |                              |  9391K|   396G|       |  1604K  (1)| 05:20:54 |       |       |
|* 35 |                          HASH JOIN RIGHT OUTER               |                              |  9391K|    13G|       |  1604K  (1)| 05:20:54 |       |       |
|  36 |                           TABLE ACCESS FULL                  | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 37 |                           HASH JOIN RIGHT OUTER              |                              |  9391K|    13G|       |  1604K  (1)| 05:20:54 |       |       |
|  38 |                            TABLE ACCESS FULL                 | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 39 |                            HASH JOIN RIGHT OUTER             |                              |  9391K|    13G|       |  1604K  (1)| 05:20:53 |       |       |
|  40 |                             TABLE ACCESS FULL                | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 41 |                             HASH JOIN RIGHT OUTER            |                              |  9391K|    13G|       |  1604K  (1)| 05:20:52 |       |       |
|  42 |                              TABLE ACCESS FULL               | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 43 |                              HASH JOIN RIGHT OUTER           |                              |  9391K|    13G|       |  1604K  (1)| 05:20:52 |       |       |
|  44 |                               TABLE ACCESS FULL              | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|  45 |                               NESTED LOOPS OUTER             |                              |  9391K|    13G|       |  1604K  (1)| 05:20:51 |       |       |
|* 46 |                                HASH JOIN RIGHT OUTER         |                              |  9391K|    12G|       |  1604K  (1)| 05:20:51 |       |       |
|  47 |                                 TABLE ACCESS FULL            | MASTER_TYPE                  |  2048 | 53248 |       |     8   (0)| 00:00:01 |       |       |
|* 48 |                                 HASH JOIN                    |                              |  9391K|    12G|       |  1604K  (1)| 05:20:51 |       |       |
|  49 |                                  TABLE ACCESS FULL           | MASTER_TYPE                  |  2048 | 26624 |       |     8   (0)| 00:00:01 |       |       |
|* 50 |                                  HASH JOIN                   |                              |  9391K|    12G|       |  1604K  (1)| 05:20:50 |       |       |
|  51 |                                   PART JOIN FILTER CREATE    | :BF0000                      |  2048 | 59392 |       |     8   (0)| 00:00:01 |       |       |
|  52 |                                    TABLE ACCESS FULL         | MASTER_TYPE                  |  2048 | 59392 |       |     8   (0)| 00:00:01 |       |       |
|* 53 |                                   HASH JOIN                  |                              |  9391K|    12G|       |  1604K  (1)| 05:20:49 |       |       |
|  54 |                                    TABLE ACCESS FULL         | MASTER_TYPE                  |  2048 | 59392 |       |     8   (0)| 00:00:01 |       |       |
|* 55 |                                    HASH JOIN                 |                              |  9391K|    12G|       |  1604K  (1)| 05:20:49 |       |       |
|  56 |                                     TABLE ACCESS FULL        | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 57 |                                     HASH JOIN                |                              |  9391K|    11G|       |  1603K  (1)| 05:20:48 |       |       |
|  58 |                                      TABLE ACCESS FULL       | MASTER_TYPE                  |  2048 | 43008 |       |     8   (0)| 00:00:01 |       |       |
|* 59 |                                      HASH JOIN RIGHT OUTER   |                              |  9391K|    11G|   546M|  1603K  (1)| 05:20:48 |       |       |
|  60 |                                       PARTITION LIST ALL     |                              |  9391K|   438M|       |   109K  (1)| 00:21:56 |     1 |     7 |
|  61 |                                        TABLE ACCESS FULL     | DEVICE                       |  9391K|   438M|       |   109K  (1)| 00:21:56 |     1 |     7 |
|  62 |                                       NESTED LOOPS OUTER     |                              |  9391K|    11G|       |   891K  (1)| 02:58:21 |       |       |
|* 63 |                                        HASH JOIN RIGHT OUTER |                              |  9391K|    10G|       |   891K  (1)| 02:58:21 |       |       |
|  64 |                                         TABLE ACCESS FULL    | IMU_TYPE                     | 20000 |  1347K|       |    60   (0)| 00:00:01 |       |       |
|* 65 | R                                       HASH JOIN RIGHT OUTE |                              |  9391K|    10G|       |   891K  (1)| 02:58:19 |       |       |
|  66 |                                          TABLE ACCESS FULL   | UTILOS_SW                    |    11 |   858 |       |     8   (0)| 00:00:01 |       |       |
|* 67 | ER                                       HASH JOIN RIGHT OUT |                              |  9391K|  9708M|       |   891K  (1)| 02:58:19 |       |       |
|  68 |                                           TABLE ACCESS FULL  | NIC_TYPE                     |     9 |   531 |       |     8   (0)| 00:00:01 |       |       |
|* 69 | TER                                       HASH JOIN RIGHT OU |                              |  9391K|  9179M|       |   891K  (1)| 02:58:18 |       |       |
|  70 |                                            TABLE ACCESS FULL | WAN_TYPE                     |     1 |    69 |       |     8   (0)| 00:00:01 |       |       |
|* 71 |                                            HASH JOIN         |                              |  9391K|  8561M|  3268M|   891K  (1)| 02:58:18 |       |       |
|  72 | LL                                          PARTITION LIST A |                              |  9391K|  3161M|       | 93481   (1)| 00:18:42 |     1 |     7 |
|  73 | LL                                           TABLE ACCESS FU | NIC                          |  9391K|  3161M|       | 93481   (1)| 00:18:42 |     1 |     7 |
|* 74 | OUTER                                       HASH JOIN RIGHT  |                              |  9391K|  5400M|       |   362K  (1)| 01:12:33 |       |       |
|  75 | LL                                           TABLE ACCESS FU | NETWORK_DEVICE_TYPE          |    17 |  2142 |       |     8   (0)| 00:00:01 |       |       |
|* 76 |                                              HASH JOIN       |                              |  9391K|  4271M|   924M|   362K  (1)| 01:12:32 |       |       |
|  77 |                                               NESTED LOOPS   |                              |  8891K|   822M|       | 33022   (1)| 00:06:37 |       |       |
|  78 | BY INDEX ROWID                                 TABLE ACCESS  | UIQ_DB_SWITCH                |     1 |    43 |       |     2   (0)| 00:00:01 |       |       |
|* 79 |  SCAN                                           INDEX UNIQUE | UIQ_DB_SWITCH_UX1            |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  80 | T ALL                                          PARTITION LIS |                              |  8891K|   457M|       | 33020   (1)| 00:06:37 |     1 |    12 |
|  81 |  FULL                                           TABLE ACCESS | DEVICE_DATA_TIMESTAMPS       |  8891K|   457M|       | 33020   (1)| 00:06:37 |     1 |    12 |
|  82 |  JOIN-FILTER                                  PARTITION LIST |                              |  9391K|  3403M|       |   109K  (1)| 00:21:56 |:BF0000|:BF0000|
|  83 | FULL                                           TABLE ACCESS  | DEVICE                       |  9391K|  3403M|       |   109K  (1)| 00:21:56 |:BF0000|:BF0000|
|  84 | L INDEX ROWID                          TABLE ACCESS BY GLOBA | DEVICE                       |     1 |    53 |       |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 85 |                                         INDEX UNIQUE SCAN    | DEVICE_PK                    |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  86 |                                TABLE ACCESS BY INDEX ROWID   | MASTER_TYPE                  |     1 |    21 |       |     1   (0)| 00:00:01 |       |       |
|* 87 |                                 INDEX UNIQUE SCAN            | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
|  88 |                        TABLE ACCESS BY INDEX ROWID           | UIQ_METER_PROGRAM_CAL        |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|* 89 |                         INDEX UNIQUE SCAN                    | UIQ_METER_PROGRAM_CAL_PK     |     1 |       |       |     0   (0)|          |       |       |
|  90 |                       TABLE ACCESS BY INDEX ROWID            | MASTER_TYPE                  |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |
|* 91 |                        INDEX UNIQUE SCAN                     | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
|  92 |                      TABLE ACCESS BY INDEX ROWID             | UIQ_METER_PROGRAM_TOU        |     1 |     8 |       |     1   (0)| 00:00:01 |       |       |
|* 93 |                       INDEX UNIQUE SCAN                      | UIQ_METER_PROGRAM_TOU_PK     |     1 |       |       |     0   (0)|          |       |       |
|  94 |                     TABLE ACCESS BY INDEX ROWID              | UIQ_METER_PROGRAM_LP         |     1 |     8 |       |     1   (0)| 00:00:01 |       |       |
|* 95 |                      INDEX UNIQUE SCAN                       | UIQ_METER_PROGRAM_LP_PK      |     1 |       |       |     0   (0)|          |       |       |
|  96 |                    TABLE ACCESS BY INDEX ROWID               | UIQ_METER_PROGRAM            |     1 |    52 |       |     1   (0)| 00:00:01 |       |       |
|* 97 |                     INDEX UNIQUE SCAN                        | UIQ_METER_PROGRAM_PK         |     1 |       |       |     0   (0)|          |       |       |
|  98 |                   TABLE ACCESS BY INDEX ROWID                | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|* 99 |                    INDEX UNIQUE SCAN                         | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |       |     0   (0)|          |       |       |
| 100 |                  TABLE ACCESS BY INDEX ROWID                 | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*101 |                   INDEX UNIQUE SCAN                          | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |       |     0   (0)|          |       |       |
| 102 |                 TABLE ACCESS BY INDEX ROWID                  | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*103 |                  INDEX UNIQUE SCAN                           | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |       |     0   (0)|          |       |       |
| 104 |                TABLE ACCESS BY INDEX ROWID                   | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*105 |                 INDEX UNIQUE SCAN                            | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |       |     0   (0)|          |       |       |
| 106 |               TABLE ACCESS BY INDEX ROWID                    | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*107 |                INDEX UNIQUE SCAN                             | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 108 |              TABLE ACCESS BY INDEX ROWID                     | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*109 |               INDEX UNIQUE SCAN                              | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 110 |             TABLE ACCESS BY INDEX ROWID                      | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*111 |              INDEX UNIQUE SCAN                               | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 112 |            TABLE ACCESS BY INDEX ROWID                       | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*113 |             INDEX UNIQUE SCAN                                | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |       |     0   (0)|          |       |       |
| 114 |           TABLE ACCESS BY INDEX ROWID                        | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*115 |            INDEX UNIQUE SCAN                                 | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |       |     0   (0)|          |       |       |
| 116 |          TABLE ACCESS BY INDEX ROWID                         | UIQ_METER_PROGRAM_TOU_SUM    |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*117 |           INDEX UNIQUE SCAN                                  | UIQ_METER_PROGRAM_TOU_SUM_PK |     1 |       |       |     0   (0)|          |       |       |
| 118 |         TABLE ACCESS BY INDEX ROWID                          | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*119 |          INDEX UNIQUE SCAN                                   | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 120 |        TABLE ACCESS BY INDEX ROWID                           | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*121 |         INDEX UNIQUE SCAN                                    | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 122 |       TABLE ACCESS BY INDEX ROWID                            | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*123 |        INDEX UNIQUE SCAN                                     | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 124 |      TABLE ACCESS BY INDEX ROWID                             | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*125 |       INDEX UNIQUE SCAN                                      | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
| 126 |     TABLE ACCESS BY INDEX ROWID                              | BATTERY                      |     1 |    90 |       |     1   (0)| 00:00:01 |       |       |
|*127 |      INDEX UNIQUE SCAN                                       | BATTERY_PK                   |     1 |       |       |     0   (0)|          |       |       |
| 128 |    TABLE ACCESS BY INDEX ROWID                               | UIQ_METER_PROGRAM_LP_CHANNEL |     1 |    18 |       |     1   (0)| 00:00:01 |       |       |
|*129 |     INDEX UNIQUE SCAN                                        | UIQ_METER_PROGRAM_LP_CHN_PK  |     1 |       |       |     0   (0)|          |       |       |
| 130 |   TABLE ACCESS BY INDEX ROWID                                | MASTER_TYPE                  |     1 |    26 |       |     1   (0)| 00:00:01 |       |       |
|*131 |    INDEX UNIQUE SCAN                                         | MASTER_TYPE_PK               |     1 |       |       |     0   (0)|          |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

I understand why it does this. The CBO doesn't know how many rows are in the subquery, so it makes its best guess. I, however, do know how many rows will be in the subquery. It's never more than a couple of thousand and usually not more than a hundred. Either way, the first execution plan will be significantly faster than the second.

How do I convince the CBO that the subquery is not returning enough rows to necessitate full table scans on my 10 million row tables? I tried a CARDINALITY hint, but it didn't do anything. I can't see how gathering stats could do anything because the WHERE clause on the subquery is never the same. I'm at a loss. I fear I am going to have to resort to executing the subquery and creating a dynamic IN clause if it returns less than 1000 rows. Please save me from coding such an abomination.
Re: Ugly View Not Cooperating [message #548219 is a reply to message #548216] Tue, 20 March 2012 19:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
do current statistics exist for TMP_DEVICE_SEARCH table?
is DEVICE_SEARCH_KEY indexed?
do current statistics exist for DEVICE_SEARCH_KEY?

is TMP_DEVICE_SEARCH a "temp" table that is populated soon before doing more complex SELECT?

[Updated on: Tue, 20 March 2012 19:28]

Report message to a moderator

Re: Ugly View Not Cooperating [message #548243 is a reply to message #548219] Wed, 21 March 2012 01:16 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
No, we do not have current stats on TMP_DEVICE_SEARCH.

DEVICE_SEARCH_KEY is indexed.

Current statistics do not exist for DEVICE_SEARCH_KEY.

TMP_DEVICE_SEARCH is a "temp" table that is populated soon before doing more complex SELECT.
Re: Ugly View Not Cooperating [message #548260 is a reply to message #548243] Wed, 21 March 2012 01:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
How about telling the optimizer to analyze the tmp_device_search tmp table? I usually set the instance parameter OPTIMIZER_DYNAMIC_SAMPLING to 4, I have never known this cause a problem and sometimes it delivers spectacular benefits. Or you could use the DYNAMIC_SAMPLING hint to take it up to 10 for this one statement.
Re: Ugly View Not Cooperating [message #548317 is a reply to message #548260] Wed, 21 March 2012 06:46 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Do either of these do anything useful to the plan?

with subqlst as (SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
SELECT /*+ cardinality(subqlst 10) push_subq(subqlst)*/
* 
FROM network_device_all nda 
join subqlst on nda.net_device_id = subqlst.net_device_id
WHERE 1 = 1
/

with subqlst as (SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
SELECT /*+ cardinality(subqlst 10) leading(subqlst)*/
* 
FROM network_device_all nda 
join subqlst on nda.net_device_id = subqlst.net_device_id
WHERE 1 = 1
/



This kind of thing is very environment specific so usually takes me some trial and error to get just right, I'd start with that though see if the plan changed much.
Re: Ugly View Not Cooperating [message #548479 is a reply to message #548216] Thu, 22 March 2012 07:30 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Can you edit your post to include the predicate section as well ? Also, if you are running on 10g or above can you post the execution plan along with the actual/estimate cardinality output as well please.

Thanks

Raj
Previous Topic: TABLESPACE EXTENT
Next Topic: Order of steps in execution path and order of predicates in the Plan
Goto Forum:
  


Current Time: Fri Mar 29 09:52:25 CDT 2024