Oracle query потребляет больше временного пространства

У меня есть следующий запрос, который завершается ошибкой » ORA-01652
невозможно расширить временной сегмент» при попытке запуска. (сервер-1)

Я попробовал тот же запрос на другом сервере с большей нагрузкой, и запрос работает нормально и возвращает результирующий набор.(сервер-2)

Обе базы данных 11.2.0.2.

Табличное пространство, выделенное для TEMP на обоих серверах, составляет 1,6 ГБ
Я пытался увеличить табличное пространство для TEMP в server — 1 до 5 ГБ, и все еще проблема существует в server-1

Ниже приведен запрос:

SELECT DISTINCT 
    NC.CONNECTIONID C1, 
    NC.CONNECTIONNAME, 
    VCG.CONNECTIONNAME, 
    NC.CONNECTIONRATE, 
    NC.CONNECTIONSHAPE, 
    CASE WHEN SYSPARAMS.VALUE = 'COMBINED_ONLY' THEN DECODE(SO.ORDERTYPE, 8, NC.CONNECTIONSTATE,DECODE (SO.ORDERSTATE,'2', '9', NC.CONNECTIONSTATE)) ELSE NC.CONNECTIONSTATE END CONNECTIONSTATE, 
    NC.CONNECTIONDIRECTION, 
    NC.CONNECTIONCATEGORY, 
    NC.DISCREPANCY, 
    .
    .
    .
    NC.TANDEMASSOCIATIONTAG, 
    NC.CUSTOMERNAME, 
    TO_CHAR(CAST(SECONDS_TO_DATE(SO.INEFFECTDATE) AS TIMESTAMP), 'YYYYMMDDHH24MISS.SS') || '+0000' INEFFECTDATE, NVL (NCG.NCGROUPNAME,'') BUNDLEAGGREGATENAME, 
    NVL (NCG.NCGROUPTYPE,'') NCGROUPTYPE, 
    NVL (BDCST.NCGROUPNAME, '') BROADCASTGROUPNAME

FROM    SYSPARAMS, 
        SERVICEORDER SO, 
        NETWORKCONNECTION NC, 
        NETWORKCONNECTION VCG, 
        OMSCONNENDTPS NCC, 
        ( SELECT UNIQUE NX.HANDLE FROM NODE NX, TOPLEVELSUBNETWORK TLS, MANAGER WHERE TLS.NAME LIKE 'abcd' AND (NX.TOPMLSHANDLE = TLS.HANDLE OR NX.MLSHANDLE = TLS.HANDLE OR MANAGER.HANDLE = TLS.HANDLE) ) NES, 
        NCGROUP NCG, 
        NCGROUP BDCST 

WHERE 

    NC.CONNECTIONRATE IN (1165,1155,1125,1120,1115,1110,1105)  
    AND SYSPARAMS.NAME = 'NBI_TMF_SNC_OPS'  
    AND NC.CONNECTIONSTATE IN (2,7,8,9) 
    AND SO.ORDERTYPE != 3  
    AND NCC.CONNECTIONID=NC.CONNECTIONID 
    AND SO.CONNECTIONID=NC.CONNECTIONID  
    AND NC.CONNECTIONSHAPE IN (1, 2, 8, 10, 13, 14)  

    AND NCC.SRCNEID=NES.HANDLE 
    AND ( NCC.SINKTPID != NCC.SRCTPID  OR (NCC.SINKTPID = NCC.SRCTPID  AND NC.CONNECTIONSHAPE = 10 ))  
    AND NC.NCGROUPID = NCG.NCGROUPID (+) 
    AND NC.BDCSTGROUPID = BDCST.NCGROUPID (+) 
    AND NC.NCGROUPID = VCG.SERVERGROUPID (+) 

    ORDER BY C1,OMSCONNENDTPSID 

Я попытался сравнить планы выполнения запроса на обеих машинах

План выполнения на сервере-1

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                              |   128K|    35M|       | 25930   (3)| 00:05:12 |
|   1 |  SORT UNIQUE                                  |                              |   128K|    35M|    37M| 17886   (4)| 00:03:35 |
|   2 |   NESTED LOOPS OUTER                          |                              |   128K|    35M|       |  9842   (6)| 00:01:59 |
|*  3 |    HASH JOIN RIGHT OUTER                      |                              |   128K|    33M|       |  9841   (6)| 00:01:59 |
|   4 |     TABLE ACCESS FULL                         | NCGROUP                      | 31020 |   514K|       |    28  (11)| 00:00:01 |
|*  5 |     HASH JOIN                                 |                              |   128K|    31M|       |  9805   (6)| 00:01:58 |
|   6 |      VIEW                                     |                              | 20614 |   100K|       |   827  (72)| 00:00:10 |
|   7 |       HASH UNIQUE                             |                              | 20614 |   583K|  5672K|   827  (11)| 00:00:10 |
|   8 |        CONCATENATION                          |                              |       |       |       |            |          |
|   9 |         MERGE JOIN                            |                              |   144K|  4086K|       |   151  (12)| 00:00:02 |
|  10 |          MERGE JOIN CARTESIAN                 |                              | 20614 |   322K|       |   105   (6)| 00:00:02 |
|  11 |           INDEX FULL SCAN                     | MANAGER_PKEY                 |     1 |     2 |       |     1   (0)| 00:00:01 |
|  12 |           BUFFER SORT                         |                              | 20614 |   281K|       |   104   (6)| 00:00:02 |
|  13 |            TABLE ACCESS FULL                  | NODE                         | 20614 |   281K|       |   104   (6)| 00:00:02 |
|* 14 |          SORT JOIN                            |                              |     7 |    91 |       |    40  (15)| 00:00:01 |
|  15 |           VIEW                                | TOPLEVELSUBNETWORK           |     7 |    91 |       |    39  (13)| 00:00:01 |
|  16 |            SORT UNIQUE                        |                              |     7 |  1507 |       |    39  (83)| 00:00:01 |
|  17 |             UNION-ALL                         |                              |       |       |       |            |          |
|  18 |              NESTED LOOPS OUTER               |                              |     1 |   114 |       |     7   (0)| 00:00:01 |
|  19 |               NESTED LOOPS                    |                              |     1 |    95 |       |     6   (0)| 00:00:01 |
|  20 |                NESTED LOOPS                   |                              |     1 |    51 |       |     2   (0)| 00:00:01 |
|* 21 |                 TABLE ACCESS BY INDEX ROWID   | SYSPARAMS                    |     1 |    27 |       |     1   (0)| 00:00:01 |
|* 22 |                  INDEX UNIQUE SCAN            | SYSPARAMS_PKEY               |     1 |       |       |     1   (0)| 00:00:01 |
|* 23 |                 TABLE ACCESS BY INDEX ROWID   | NODE                         |     1 |    24 |       |     1   (0)| 00:00:01 |

| 126 |                  UNION-ALL                    |                              |       |       |       |            |          |
|*127 |                   HASH JOIN                   |                              |     2 |   126 |       |     9  (12)| 00:00:01 |
| 128 |                    TABLE ACCESS BY INDEX ROWID| NCG                          |     1 |    40 |       |     1   (0)| 00:00:01 |
|*129 |                     INDEX RANGE SCAN          | NCG_NAME                     |     1 |       |       |     1   (0)| 00:00:01 |
| 130 |                    TABLE ACCESS FULL          | NETWORKCONTROLLER            |    76 |  1748 |       |     7   (0)| 00:00:01 |
|*131 |                   TABLE ACCESS BY INDEX ROWID | NODE                         |     1 |    26 |       |     1   (0)| 00:00:01 |
|*132 |                    INDEX RANGE SCAN           | NODE_NAME                    |     1 |       |       |     1   (0)| 00:00:01 |
|*133 |           INDEX RANGE SCAN                    | NODE_TPLVLSBNTWKHDL          |  1085 |       |       |     1   (0)| 00:00:01 |
|*134 |          TABLE ACCESS BY INDEX ROWID          | NODE                         |    35 |   490 |       |    30   (0)| 00:00:01 |
|*135 |      HASH JOIN                                |                              |   128K|    30M|    15M|  9535   (6)| 00:01:55 |
|*136 |       TABLE ACCESS FULL                       | SERVICEORDER                 |   325K|    12M|       |  1266   (8)| 00:00:16 |
|*137 |       HASH JOIN RIGHT OUTER                   |                              |   121K|    24M|    11M|  6153   (6)| 00:01:14 |
| 138 |        TABLE ACCESS FULL                      | NETWORKCONNECTION            |   255K|  8981K|       |  1310   (8)| 00:00:16 |
|*139 |        HASH JOIN                              |                              |   121K|    20M|  9984K|  3146   (8)| 00:00:38 |
| 140 |         TABLE ACCESS FULL                     | OMSCONNENDTPS                |   255K|  6985K|       |   322  (10)| 00:00:04 |
| 141 |         NESTED LOOPS                          |                              |   120K|    17M|       |  1387  (13)| 00:00:17 |
| 142 |          TABLE ACCESS BY INDEX ROWID          | SYSPARAMS                    |     1 |    27 |       |     1   (0)| 00:00:01 |
|*143 |           INDEX UNIQUE SCAN                   | SYSPARAMS_PKEY               |     1 |       |       |     1   (0)| 00:00:01 |
|*144 |          TABLE ACCESS FULL                    | NETWORKCONNECTION            |   120K|    14M|       |  1386  (13)| 00:00:17 |
| 145 |    TABLE ACCESS BY INDEX ROWID                | NCGROUP                      |     1 |    14 |       |     1   (0)| 00:00:01 |
|*146 |     INDEX UNIQUE SCAN                         | NCGROUP_PKEY                 |     1 |       |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------  

План выполнения на сервере-2

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                              |   290K|    76M|       | 51114   (3)| 00:10:14 |
|   1 |  SORT UNIQUE                                |                              |   290K|    76M|    81M| 33578   (4)| 00:06:43 |
|*  2 |   HASH JOIN                                 |                              |   290K|    76M|       | 16041   (6)| 00:03:13 |
|   3 |    VIEW                                     |                              | 24239 |   118K|       |   370  (20)| 00:00:05 |
|   4 |     HASH UNIQUE                             |                              | 24239 |   710K|       |   370  (20)| 00:00:05 |
|   5 |      CONCATENATION                          |                              |       |       |       |            |          |
|   6 |       MERGE JOIN                            |                              |   121K|  3550K|       |   210  (10)| 00:00:03 |
|   7 |        MERGE JOIN CARTESIAN                 |                              | 24239 |   402K|       |   156   (6)| 00:00:02 |
|   8 |         INDEX FULL SCAN                     | MANAGER_PKEY                 |     1 |     2 |       |     1   (0)| 00:00:01 |
|   9 |         BUFFER SORT                         |                              | 24239 |   355K|       |   155   (6)| 00:00:02 |
|  10 |          TABLE ACCESS FULL                  | NODE                         | 24239 |   355K|       |   155   (6)| 00:00:02 |
|* 11 |        SORT JOIN                            |                              |     5 |    65 |       |    49  (13)| 00:00:01 |
|  12 |         VIEW                                | TOPLEVELSUBNETWORK           |     5 |    65 |       |    48  (11)| 00:00:01 |
|  13 |          SORT UNIQUE                        |                              |     5 |  1046 |       |    48  (67)| 00:00:01 |
|  14 |           UNION-ALL                         |                              |       |       |       |            |          |
|  15 |            NESTED LOOPS OUTER               |                              |     1 |   117 |       |    16   (0)| 00:00:01 |
|  16 |             NESTED LOOPS                    |                              |     1 |    97 |       |    15   (0)| 00:00:01 |
|  17 |              NESTED LOOPS                   |                              |     1 |    53 |       |     2   (0)| 00:00:01 |
|* 18 |               TABLE ACCESS BY INDEX ROWID   | SYSPARAMS                    |     1 |    27 |       |     1   (0)| 00:00:01 |
|* 19 |                INDEX UNIQUE SCAN            | SYSPARAMS_PKEY               |     1 |       |       |     1   (0)| 00:00:01 |
|* 20 |               TABLE ACCESS BY INDEX ROWID   | NODE                         |     1 |    26 |       |     1   (0)| 00:00:01 |
|* 21 |                INDEX RANGE SCAN             | NODE_NAME                    |     1 |       |       |     1   (0)| 00:00:01 |
|  22 |              TABLE ACCESS BY INDEX ROWID    | MULTILAYERSUBNETWORK         |     1 |    44 |       |    13   (0)| 00:00:01 |
|* 23 |               INDEX SKIP SCAN               | MULTILAYERSUBNETWORK_NCHDLNM |     1 |       |       |    12   (0)| 00:00:01 |

|*126 |                   INDEX RANGE SCAN          | NCG_NAME                     |     1 |       |       |     1   (0)| 00:00:01 |
| 127 |                  TABLE ACCESS FULL          | NETWORKCONTROLLER            |   127 |  3048 |       |     7   (0)| 00:00:01 |
|*128 |                 TABLE ACCESS BY INDEX ROWID | NODE                         |     1 |    28 |       |     1   (0)| 00:00:01 |
|*129 |                  INDEX RANGE SCAN           | NODE_NAME                    |     1 |       |       |     1   (0)| 00:00:01 |
|*130 |         INDEX RANGE SCAN                    | NODE_TPLVLSBNTWKHDL          |   673 |       |       |     1   (0)| 00:00:01 |
|*131 |        TABLE ACCESS BY INDEX ROWID          | NODE                         |    35 |   525 |       |    18   (0)| 00:00:01 |
| 132 |    NESTED LOOPS OUTER                       |                              |   290K|    75M|       | 15699   (5)| 00:03:09 |
|*133 |     HASH JOIN RIGHT OUTER                   |                              |   290K|    71M|       | 15688   (5)| 00:03:09 |
| 134 |      TABLE ACCESS FULL                      | NCGROUP                      | 17940 |   315K|       |    21  (10)| 00:00:01 |
|*135 |      HASH JOIN                              |                              |   290K|    66M|    20M| 15652   (5)| 00:03:08 |
|*136 |       TABLE ACCESS FULL                     | SERVICEORDER                 |   427K|    15M|       |  1791   (8)| 00:00:22 |
|*137 |       HASH JOIN                             |                              |   284K|    54M|    14M|  9901   (6)| 00:01:59 |
| 138 |        TABLE ACCESS FULL                    | OMSCONNENDTPS                |   354K|    10M|       |   440  (10)| 00:00:06 |
|*139 |        HASH JOIN RIGHT OUTER                |                              |   281K|    45M|    14M|  6272   (7)| 00:01:16 |
| 140 |         TABLE ACCESS FULL                   | NETWORKCONNECTION            |   348K|    10M|       |  1689   (8)| 00:00:21 |
| 141 |         NESTED LOOPS                        |                              |   281K|    37M|       |  1799  (14)| 00:00:22 |
| 142 |          TABLE ACCESS BY INDEX ROWID        | SYSPARAMS                    |     1 |    27 |       |     1   (0)| 00:00:01 |
|*143 |           INDEX UNIQUE SCAN                 | SYSPARAMS_PKEY               |     1 |       |       |     1   (0)| 00:00:01 |
|*144 |          TABLE ACCESS FULL                  | NETWORKCONNECTION            |   281K|    29M|       |  1798  (14)| 00:00:22 |
| 145 |     TABLE ACCESS BY INDEX ROWID             | NCGROUP                      |     1 |    15 |       |     1   (0)| 00:00:01 |
|*146 |      INDEX UNIQUE SCAN                      | NCGROUP_PKEY                 |     1 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------

Интересно, что server-1 (с выпуском) показывает низкотемпературное пространство, необходимое в плане выполнения.
по сравнению с server-2 (нет проблем с большей нагрузкой)

Я также сравнил следующие параметры на обеих машинах, и они остаются теми же:
optimizer_mode,optimizer_index_cost_adj,optimizer_index_caching,db_file_multiblock_read_count,parallel_automatic_tuning,hash_area_size,sort_area_size

Может ли кто-то помочь мне в вопросах, которые нужно сравнить на обеих машинах, которые могли бы повлиять на план выполнения и использование временного пространства.

1 ответ

  1. Древний синтаксис соединения, который вы используете, запутывает, как вы связываете таблицы. Используйте явные соединения, чтобы увидеть, что происходит. Вот ваша производная таблица NES переписана:

    select unique 
      nx.handle 
    from node nx
    cross join manager 
    join toplevelsubnetwork tls 
      on  tls.name like 'abcd' 
      and tls.handle in (nx.topmlshandle, nx.mlshandle, manager.handle)
    

    Это создает большой промежуточный результат. И поскольку вы пересекаете узел соединения с каждым менеджером, этого достаточно для tls.ручка, чтобы соответствовать любому менеджеру.ручка и вы получаете все nx.обрабатывать существующие. Подумайте над этим подзапросом, и ваша проблема должна быть решена.

    (И когда вы это сделаете, замените неявные соединения в вашем основном запросе также явными соединениями. Это неприятно, когда приходится путаться через все предложение where только для того, чтобы увидеть, как вы присоединяетесь к таблицам.)