Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Поменялся план запроса после перехода на 11g  [new]
mimmi
Member

Откуда: Омск
Сообщений: 7
Перенесли базу с windows, 10.2.0.5 на linux, 11.2.0.3 c помощью imp/exp.
После этого запрос который выполнялся в течении 5 сек на 10g, на 11g стал выполняться в течении нескольких часов. Обнаружила, что план запроса поменялся (сейчас нет возможности его показать).
Поменяла значение параметра optimizer_features_enable с 11.2.0.3 на 10.2.0.5 и запрос стал выполняться по плану запроса который был на 10g.
Все бы было хорошо, но после этого такие же проблемы возникли в другом запросе:

на 10g выполняется меньше чем за секунду:
SELECT   v_goodssupply.*
    FROM v_goodssupply
   WHERE nprn = 10088485
ORDER BY nrestfact;
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                           |     1 |   280 |    28   (8)|
|   1 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|   2 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|*  3 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|   4 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|   5 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|*  7 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|   8 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|*  9 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  10 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  11 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 12 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  13 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  14 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  15 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 16 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  17 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 18 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  19 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  20 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 21 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  22 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  23 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  24 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 25 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  26 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 27 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  28 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  29 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 30 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  31 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  32 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  33 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 34 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  35 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 36 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  37 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  38 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 39 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  40 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  41 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  42 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 43 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  44 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 45 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  46 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  47 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 48 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  49 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  50 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  51 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 52 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  53 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 54 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  55 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  56 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 57 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  58 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  59 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  60 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 61 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  62 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 63 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  64 |  SORT AGGREGATE                              |                           |     1 |    15 |            |
|  65 |   FIRST ROW                                  |                           |     1 |    15 |     3   (0)|
|* 66 |    INDEX RANGE SCAN (MIN/MAX)                | I_STOREOPERJOURN_OPERDATE |     1 |    15 |     3   (0)|
|  67 |  SORT AGGREGATE                              |                           |     1 |    24 |            |
|  68 |   NESTED LOOPS                               |                           |     1 |    24 |     3   (0)|
|  69 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADSSP               |     1 |    14 |     2   (0)|
|* 70 |     INDEX RANGE SCAN                         | I_OVERHEADSSP_SUPPLY_FK   |     1 |       |     1   (0)|
|  71 |    TABLE ACCESS BY INDEX ROWID               | OVERHEADS                 |     1 |    10 |     1   (0)|
|* 72 |     INDEX UNIQUE SCAN                        | C_OVERHEADS_PK            |     1 |       |     0   (0)|
|  73 |  SORT ORDER BY                               |                           |     1 |   280 |    28   (8)|
|* 74 |   FILTER                                     |                           |       |       |            |
|  75 |    NESTED LOOPS                              |                           |     1 |   280 |    27   (4)|
|  76 |     NESTED LOOPS                             |                           |     1 |   261 |    26   (4)|
|  77 |      NESTED LOOPS OUTER                      |                           |     1 |   251 |    25   (4)|
|  78 |       NESTED LOOPS OUTER                     |                           |     1 |   236 |    24   (5)|
|  79 |        NESTED LOOPS                          |                           |     1 |   208 |    21   (5)|
|  80 |         NESTED LOOPS                         |                           |     1 |   197 |    20   (5)|
|  81 |          NESTED LOOPS                        |                           |     1 |   179 |    19   (6)|
|* 82 |           HASH JOIN SEMI                     |                           |     1 |   158 |    18   (6)|
|  83 |            NESTED LOOPS                      |                           |     2 |   312 |    11   (0)|
|  84 |             NESTED LOOPS                     |                           |     1 |   111 |     7   (0)|
|  85 |              NESTED LOOPS OUTER              |                           |     1 |    95 |     6   (0)|
|  86 |               NESTED LOOPS OUTER             |                           |     1 |    79 |     5   (0)|
|  87 |                NESTED LOOPS                  |                           |     1 |    71 |     4   (0)|
|  88 |                 NESTED LOOPS OUTER           |                           |     1 |    52 |     3   (0)|
|  89 |                  NESTED LOOPS                |                           |     1 |    41 |     3   (0)|
|  90 |                   TABLE ACCESS BY INDEX ROWID| GOODSPARTIES              |     1 |    27 |     2   (0)|
|* 91 |                    INDEX UNIQUE SCAN         | C_GOODSPARTIES_PK         |     1 |       |     1   (0)|
|  92 |                   TABLE ACCESS BY INDEX ROWID| NOMMODIF                  | 50308 |   687K|     1   (0)|
|* 93 |                    INDEX UNIQUE SCAN         | C_NOMMODIF_RN_PK          |     1 |       |     0   (0)|
|  94 |                  TABLE ACCESS BY INDEX ROWID | NOMNMODIFPACK             |     1 |    11 |     0   (0)|
|* 95 |                   INDEX UNIQUE SCAN          | C_NOMNMODIFPACK_PK        |     1 |       |     0   (0)|
|  96 |                 TABLE ACCESS BY INDEX ROWID  | DICNOMNS                  | 39043 |   724K|     1   (0)|
|* 97 |                  INDEX UNIQUE SCAN           | C_DICNOMNS_RN_PK          |     1 |       |     0   (0)|
|  98 |                TABLE ACCESS BY INDEX ROWID   | NOMNPACK                  |     1 |     8 |     1   (0)|
|* 99 |                 INDEX UNIQUE SCAN            | C_NOMNPACK_PK             |     1 |       |     0   (0)|
| 100 |               TABLE ACCESS BY INDEX ROWID    | DICMUNTS                  |   257 |  4112 |     1   (0)|
|*101 |                INDEX UNIQUE SCAN             | C_DICMUNTS_PK             |     1 |       |     0   (0)|
| 102 |              TABLE ACCESS BY INDEX ROWID     | DICMUNTS                  |   257 |  4112 |     1   (0)|
|*103 |               INDEX UNIQUE SCAN              | C_DICMUNTS_PK             |     1 |       |     0   (0)|
| 104 |             TABLE ACCESS BY INDEX ROWID      | GOODSSUPPLY               |     2 |    90 |     4   (0)|
|*105 |              INDEX RANGE SCAN                | C_GOODSSUPPLY_UK          |     2 |       |     2   (0)|
| 106 |            VIEW                              | VW_SQ_1                   |     5 |    10 |     6   (0)|
|*107 |             FILTER                           |                           |       |       |            |
| 108 |              TABLE ACCESS BY INDEX ROWID     | USERPRIV                  |    71 |  1420 |     6   (0)|
|*109 |               INDEX RANGE SCAN               | I_USERPRIV_UNITCODE_FK    |    71 |       |     3   (0)|
|*110 |              INDEX UNIQUE SCAN               | C_USERROLES_ROLEAUTH_PK   |     1 |    11 |     1   (0)|
| 111 |           TABLE ACCESS BY INDEX ROWID        | AZSAZSLISTMT              |     1 |    21 |     1   (0)|
|*112 |            INDEX UNIQUE SCAN                 | C_AZSAZSLISTMT_RN_PK      |     1 |       |     0   (0)|
| 113 |          TABLE ACCESS BY INDEX ROWID         | GOODSSUPPLY               |     1 |    18 |     1   (0)|
|*114 |           INDEX UNIQUE SCAN                  | C_GOODSSUPPLY_PK          |     1 |       |     0   (0)|
| 115 |         TABLE ACCESS BY INDEX ROWID          | AZSAZSLISTMT              |     1 |    11 |     1   (0)|
|*116 |          INDEX UNIQUE SCAN                   | C_AZSAZSLISTMT_RN_PK      |     1 |       |     0   (0)|
| 117 |        TABLE ACCESS BY INDEX ROWID           | REGPRICE                  |     1 |    28 |     3   (0)|
|*118 |         INDEX RANGE SCAN                     | C_REGPRICE_REGPRICE_UK    |     1 |       |     2   (0)|
| 119 |       TABLE ACCESS BY INDEX ROWID            | DICTAXGR                  |     1 |    15 |     1   (0)|
|*120 |        INDEX UNIQUE SCAN                     | C_DICTAXGR_RN_PK          |     1 |       |     0   (0)|
| 121 |      TABLE ACCESS BY INDEX ROWID             | CURNAMES                  |     1 |    10 |     1   (0)|
|*122 |       INDEX UNIQUE SCAN                      | C_CURNAMES_PK             |     1 |       |     0   (0)|
| 123 |     TABLE ACCESS BY INDEX ROWID              | JURPERSONS                |     1 |    19 |     1   (0)|
|*124 |      INDEX UNIQUE SCAN                       | C_JURPERSONS_PK           |     1 |       |     0   (0)|
| 125 |    SORT AGGREGATE                            |                           |     1 |    15 |            |
| 126 |     FIRST ROW                                |                           |     1 |    15 |     3   (0)|
|*127 |      INDEX RANGE SCAN (MIN/MAX)              | C_REGPRICE_REGPRICE_UK    |     1 |    15 |     3   (0)|
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("SOJ"."GOODSSUPPLY"=:B1)
   7 - access("OVHS"."GOODSSUPPLY"=:B1)
   9 - access("OVHS"."PRN"="OVH"."RN")
  12 - access("SOJ"."GOODSSUPPLY"=:B1)
  16 - access("OVHS"."GOODSSUPPLY"=:B1)
  18 - access("OVHS"."PRN"="OVH"."RN")
  21 - access("SOJ"."GOODSSUPPLY"=:B1)
  25 - access("OVHS"."GOODSSUPPLY"=:B1)
  27 - access("OVHS"."PRN"="OVH"."RN")
  30 - access("SOJ"."GOODSSUPPLY"=:B1)
  34 - access("OVHS"."GOODSSUPPLY"=:B1)
  36 - access("OVHS"."PRN"="OVH"."RN")
  39 - access("SOJ"."GOODSSUPPLY"=:B1)
  43 - access("OVHS"."GOODSSUPPLY"=:B1)
  45 - access("OVHS"."PRN"="OVH"."RN")
  48 - access("SOJ"."GOODSSUPPLY"=:B1)
  52 - access("OVHS"."GOODSSUPPLY"=:B1)
  54 - access("OVHS"."PRN"="OVH"."RN")
  57 - access("SOJ"."GOODSSUPPLY"=:B1)
  61 - access("OVHS"."GOODSSUPPLY"=:B1)
  63 - access("OVHS"."PRN"="OVH"."RN")
  66 - access("SOJ"."GOODSSUPPLY"=:B1)
  70 - access("OVHS"."GOODSSUPPLY"=:B1)
  72 - access("OVHS"."PRN"="OVH"."RN")
  74 - filter("P"."ADATE" IS NULL OR "P"."ADATE"= (SELECT MAX("P1"."ADATE") FROM PARUS."REGPRICE" "P1" 
              WHERE "P1"."PRN"=:B1))
  82 - access("ITEM_1"="SUP"."JUR_PERS")
  91 - access("PAR"."RN"=10088485)
  93 - access("PAR"."NOMMODIF"="MOD"."RN")
  95 - access("PAR"."NOMNMODIFPACK"="PAC"."RN"(+))
  97 - access("MOD"."PRN"="NOM"."RN")
  99 - access("PAC"."NOMENPACK"="NPAC"."RN"(+))
 101 - access("NOM"."UMEAS_ALT"="MU2"."RN"(+))
 103 - access("NOM"."UMEAS_MAIN"="MU1"."RN")
 105 - access("SUP"."PRN"=10088485)
 107 - filter("UP"."AUTHID"=USER@! OR  EXISTS (SELECT 0 FROM PARUS."USERROLES" "UR" WHERE 
              "UR"."AUTHID"=USER@! AND "UR"."ROLEID"=:B1))
 109 - access("UP"."UNITCODE"='GoodsSupply')
 110 - access("UR"."ROLEID"=:B1 AND "UR"."AUTHID"=USER@!)
 112 - access("SUP"."STORE"="AZS"."RN")
 114 - access("SUP"."RN"="G"."RN")
 116 - access("G"."STORE"="ST"."RN")
 118 - access("G"."RN"="P"."PRN"(+))
 120 - access("P"."TAXGR"="T"."RN"(+))
 122 - access("AZS"."CURRENCY"="C"."RN")
 124 - access("SUP"."JUR_PERS"="J"."RN")
 127 - access("P1"."PRN"=:B1)
 
Note
-----
   - 'PLAN_TABLE' is old version


на 11g с полминуты:

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |     1 |   287 |       |  1819   (2)|
|   1 |  SORT AGGREGATE                          |                            |     1 |    15 |       |            |
|   2 |   FIRST ROW                              |                            |     1 |    15 |       |     3   (0)|
|*  3 |    INDEX RANGE SCAN (MIN/MAX)            | I_STOREOPERJOURN_OPERDATE  |     1 |    15 |       |     3   (0)|
|   4 |  SORT AGGREGATE                          |                            |     1 |    24 |       |            |
|   5 |   NESTED LOOPS                           |                            |     1 |    24 |       |     3   (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID           | OVERHEADSSP                |     1 |    14 |       |     2   (0)|
|*  7 |     INDEX RANGE SCAN                     | I_OVERHEADSSP_SUPPLY_FK    |     1 |       |       |     1   (0)|
|   8 |    TABLE ACCESS BY INDEX ROWID           | OVERHEADS                  |     1 |    10 |       |     1   (0)|
|*  9 |     INDEX UNIQUE SCAN                    | C_OVERHEADS_PK             |     1 |       |       |     0   (0)|
|  10 |  SORT ORDER BY                           |                            |     1 |   287 |       |  1819   (2)|
|* 11 |   HASH JOIN                              |                            |     1 |   287 |       |  1818   (2)|
|  12 |    NESTED LOOPS                          |                            |     1 |   207 |       |    20   (5)|
|  13 |     NESTED LOOPS                         |                            |     1 |   197 |       |    19   (6)|
|  14 |      NESTED LOOPS                        |                            |     1 |   176 |       |    18   (6)|
|* 15 |       HASH JOIN SEMI                     |                            |     1 |   157 |       |    17   (6)|
|  16 |        NESTED LOOPS                      |                            |     2 |   310 |       |    11   (0)|
|  17 |         NESTED LOOPS                     |                            |     1 |   111 |       |     7   (0)|
|  18 |          NESTED LOOPS OUTER              |                            |     1 |    95 |       |     6   (0)|
|  19 |           NESTED LOOPS OUTER             |                            |     1 |    79 |       |     5   (0)|
|  20 |            NESTED LOOPS                  |                            |     1 |    71 |       |     4   (0)|
|  21 |             NESTED LOOPS OUTER           |                            |     1 |    52 |       |     3   (0)|
|  22 |              NESTED LOOPS                |                            |     1 |    41 |       |     3   (0)|
|  23 |               TABLE ACCESS BY INDEX ROWID| GOODSPARTIES               |     1 |    27 |       |     2   (0)|
|* 24 |                INDEX UNIQUE SCAN         | C_GOODSPARTIES_PK          |     1 |       |       |     1   (0)|
|  25 |               TABLE ACCESS BY INDEX ROWID| NOMMODIF                   | 50608 |   691K|       |     1   (0)|
|* 26 |                INDEX UNIQUE SCAN         | C_NOMMODIF_RN_PK           |     1 |       |       |     0   (0)|
|  27 |              TABLE ACCESS BY INDEX ROWID | NOMNMODIFPACK              |     1 |    11 |       |     0   (0)|
|* 28 |               INDEX UNIQUE SCAN          | C_NOMNMODIFPACK_PK         |     1 |       |       |     0   (0)|
|  29 |             TABLE ACCESS BY INDEX ROWID  | DICNOMNS                   | 39303 |   729K|       |     1   (0)|
|* 30 |              INDEX UNIQUE SCAN           | C_DICNOMNS_RN_PK           |     1 |       |       |     0   (0)|
|  31 |            TABLE ACCESS BY INDEX ROWID   | NOMNPACK                   |     1 |     8 |       |     1   (0)|
|* 32 |             INDEX UNIQUE SCAN            | C_NOMNPACK_PK              |     1 |       |       |     0   (0)|
|  33 |           TABLE ACCESS BY INDEX ROWID    | DICMUNTS                   |   257 |  4112 |       |     1   (0)|
|* 34 |            INDEX UNIQUE SCAN             | C_DICMUNTS_PK              |     1 |       |       |     0   (0)|
|  35 |          TABLE ACCESS BY INDEX ROWID     | DICMUNTS                   |   257 |  4112 |       |     1   (0)|
|* 36 |           INDEX UNIQUE SCAN              | C_DICMUNTS_PK              |     1 |       |       |     0   (0)|
|  37 |         TABLE ACCESS BY INDEX ROWID      | GOODSSUPPLY                |     2 |    88 |       |     4   (0)|
|* 38 |          INDEX RANGE SCAN                | C_GOODSSUPPLY_UK           |     2 |       |       |     2   (0)|
|  39 |        VIEW                              | VW_SQ_1                    |     3 |     6 |       |     5   (0)|
|* 40 |         FILTER                           |                            |       |       |       |            |
|  41 |          TABLE ACCESS BY INDEX ROWID     | USERPRIV                   |    53 |  1060 |       |     5   (0)|
|* 42 |           INDEX RANGE SCAN               | I_USERPRIV_UNITCODE_FK     |    53 |       |       |     3   (0)|
|* 43 |          INDEX UNIQUE SCAN               | C_USERROLES_ROLEAUTH_PK    |     1 |    11 |       |     1   (0)|
|  44 |       TABLE ACCESS BY INDEX ROWID        | JURPERSONS                 |     1 |    19 |       |     1   (0)|
|* 45 |        INDEX UNIQUE SCAN                 | C_JURPERSONS_PK            |     1 |       |       |     0   (0)|
|  46 |      TABLE ACCESS BY INDEX ROWID         | AZSAZSLISTMT               |     1 |    21 |       |     1   (0)|
|* 47 |       INDEX UNIQUE SCAN                  | C_AZSAZSLISTMT_RN_PK       |     1 |       |       |     0   (0)|
|  48 |     TABLE ACCESS BY INDEX ROWID          | CURNAMES                   |     1 |    10 |       |     1   (0)|
|* 49 |      INDEX UNIQUE SCAN                   | C_CURNAMES_PK              |     1 |       |       |     0   (0)|
|  50 |    VIEW                                  | V_GOODSSUPPLY_REGPRICE     |   140 | 11200 |       |  1798   (2)|
|* 51 |     FILTER                               |                            |       |       |       |            |
|* 52 |      HASH JOIN RIGHT OUTER               |                            |   140 | 10080 |       |  1798   (2)|
|  53 |       VIEW                               | index$_join$_021           |     8 |   120 |       |     3  (34)|
|* 54 |        HASH JOIN                         |                            |       |       |       |            |
|  55 |         INDEX FAST FULL SCAN             | C_DICTAXGR_CODE_UK         |     8 |   120 |       |     1   (0)|
|  56 |         INDEX FAST FULL SCAN             | C_DICTAXGR_RN_PK           |     8 |   120 |       |     1   (0)|
|* 57 |       HASH JOIN                          |                            |   140 |  7980 |       |  1795   (1)|
|* 58 |        HASH JOIN OUTER                   |                            |   140 |  6440 |  6624K|  1792   (1)|
|  59 |         TABLE ACCESS FULL                | GOODSSUPPLY                |   226K|  3974K|       |   448   (2)|
|  60 |         TABLE ACCESS FULL                | REGPRICE                   |   264K|  7235K|       |   517   (2)|
|  61 |        VIEW                              | index$_join$_020           |    36 |   396 |       |     3  (34)|
|* 62 |         HASH JOIN                        |                            |       |       |       |            |
|  63 |          INDEX FAST FULL SCAN            | C_AZSAZSLISTMT_RN_PK       |    36 |   396 |       |     1   (0)|
|  64 |          INDEX FAST FULL SCAN            | I_AZSAZSLISTMT_CURRENCY_FK |    36 |   396 |       |     1   (0)|
|  65 |      SORT AGGREGATE                      |                            |     1 |    15 |       |            |
|  66 |       FIRST ROW                          |                            |     1 |    15 |       |     3   (0)|
|* 67 |        INDEX RANGE SCAN (MIN/MAX)        | C_REGPRICE_REGPRICE_UK     |     1 |    15 |       |     3   (0)|
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("SOJ"."GOODSSUPPLY"=:B1)
   7 - access("OVHS"."GOODSSUPPLY"=:B1)
   9 - access("OVHS"."PRN"="OVH"."RN")
  11 - access("SUP"."RN"="GRP"."NGOODSSUPPLY")
  15 - access("ITEM_1"="SUP"."JUR_PERS")
  24 - access("PAR"."RN"=10088485)
  26 - access("PAR"."NOMMODIF"="MOD"."RN")
  28 - access("PAR"."NOMNMODIFPACK"="PAC"."RN"(+))
  30 - access("MOD"."PRN"="NOM"."RN")
  32 - access("PAC"."NOMENPACK"="NPAC"."RN"(+))
  34 - access("NOM"."UMEAS_ALT"="MU2"."RN"(+))
  36 - access("NOM"."UMEAS_MAIN"="MU1"."RN")
  38 - access("SUP"."PRN"=10088485)
  40 - filter("UP"."AUTHID"=USER@! OR  EXISTS (SELECT 0 FROM PARUS."USERROLES" "UR" WHERE 
              "UR"."AUTHID"=USER@! AND "UR"."ROLEID"=:B1))
  42 - access("UP"."UNITCODE"='GoodsSupply')
  43 - access("UR"."ROLEID"=:B1 AND "UR"."AUTHID"=USER@!)
  45 - access("SUP"."JUR_PERS"="J"."RN")
  47 - access("SUP"."STORE"="AZS"."RN")
  49 - access("AZS"."CURRENCY"="C"."RN")
  51 - filter("P"."ADATE" IS NULL OR "P"."ADATE"= (SELECT MAX("P1"."ADATE") FROM PARUS."REGPRICE" "P1" 
              WHERE "P1"."PRN"=:B1))
  52 - access("P"."TAXGR"="T"."RN"(+))
  54 - access(ROWID=ROWID)
  57 - access("G"."STORE"="ST"."RN")
  58 - access("G"."RN"="P"."PRN"(+))
  62 - access(ROWID=ROWID)
  67 - access("P1"."PRN"=:B1)
 
Note
-----
   - 'PLAN_TABLE' is old version


возможности менять запрос нет, подскажите пожалуйста, как можно это исправить или хотя бы в каком направлении двигаться
17 окт 12, 07:01    [13330561]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
Добрый Э - Эх
Guest
У тебя запрос идет из вьюхи, если что... Кури на предмет её оптимизации.
Текст запроса
SELECT   v_goodssupply.*
    FROM v_goodssupply
   WHERE nprn = 10088485
ORDER BY nrestfact;
при этом никак не изменится...
17 окт 12, 07:27    [13330583]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
На 11g V_GOODSSUPPLY_REGPRICE non-mergeable. Соединение с этой view идет предикатом "SUP"."RN"="GRP"."NGOODSSUPPLY". На 10g я вообще не вижу в предикатах "GRP"."NGOODSSUPPLY". Действительно ли view, участвующие в запросе (v_goodssupply, V_GOODSSUPPLY_REGPRICE, ...), одинаковы на обеих базах?
17 окт 12, 07:29    [13330586]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
mimmi
Member

Откуда: Омск
Сообщений: 7
wurdu,
эти представления идентичны на обеих базах.
Если сейчас поменять параметр optimizer_features_enable обратно на 11.2.0.3 то план запроса будет такой же как на 10g
17 окт 12, 07:57    [13330617]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Я бы выставил optimizer_features_enable обратно на 11.2.0.3 и разбирался с первым запросом.
17 окт 12, 08:07    [13330634]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
mimmi
Member

Откуда: Омск
Сообщений: 7
wurdu,

с первым запросом такая же проблема, все идентично, только различаются планы. Пока возможности показать план нет, к вечеру достану))
17 окт 12, 08:19    [13330675]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
mimmi
wurdu,

с первым запросом такая же проблема, все идентично, только различаются планы. Пока возможности показать план нет, к вечеру достану))
Планы различаться могут по-разному. Можно выполнить запрос с хинтом /*+ optimizer_features_enable('11.2.0.3') */, и если он выполняется часами - через некоторое время получить отчет через dbms_sqltune.report_sql_monitor, например, через type=>'TEXT', чтобы проще было показать. Там обычно видно, в чем проблема.
17 окт 12, 08:31    [13330732]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
mimmi
Member

Откуда: Омск
Сообщений: 7
wurdu, спасибо, попробую

вот план запроса с хинтом, я через spotlight отследила, что сортировка проходит в темповом табличном пространстве, а не в памяти(((
+

SELECT  /*+ optimizer_features_enable('11.2.0.3') */  * 
  FROM v_eospecs_union v
 WHERE company =474316
   AND doperation_date >= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
   AND doperation_date <= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
   AND rn IN (
          SELECT m.rn
            FROM v_oprspecs_shadow m
           WHERE company = 474316
             AND doperation_date >= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
             AND doperation_date <= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
             AND m.account_credit IN (SELECT rn
                                        FROM dicaccs
                                       WHERE acc_number LIKE '19%'));

 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                             |     1 |  1106 |  5086   (1)|
|   1 |  SORT AGGREGATE                                              |                             |     1 |    55 |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    55 |     6   (0)|
|*  3 |    INDEX RANGE SCAN                                          | I_DOCLINKS_IN_DOCUMENT      |     5 |       |     3   (0)|
|   4 |  SORT AGGREGATE                                              |                             |     1 |    57 |            |
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    57 |     4   (0)|
|*  6 |    INDEX RANGE SCAN                                          | I_DOCLINKS_OUT_DOCUMENT     |     3 |       |     3   (0)|
|   7 |  SORT AGGREGATE                                              |                             |     1 |    55 |            |
|*  8 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    55 |     4   (0)|
|*  9 |    INDEX RANGE SCAN                                          | I_DOCLINKS_OUT_DOCUMENT     |     3 |       |     3   (0)|
|* 10 |  FILTER                                                      |                             |       |       |            |
|  11 |   NESTED LOOPS OUTER                                         |                             |     1 |  1106 |  5057   (1)|
|  12 |    NESTED LOOPS OUTER                                        |                             |     1 |  1096 |  5057   (1)|
|  13 |     NESTED LOOPS OUTER                                       |                             |     1 |  1063 |  5056   (1)|
|  14 |      NESTED LOOPS OUTER                                      |                             |     1 |  1038 |  5056   (1)|
|  15 |       NESTED LOOPS OUTER                                     |                             |     1 |  1032 |  5055   (1)|
|  16 |        NESTED LOOPS OUTER                                    |                             |     1 |   995 |  5054   (1)|
|  17 |         NESTED LOOPS OUTER                                   |                             |     1 |   985 |  5054   (1)|
|  18 |          NESTED LOOPS OUTER                                  |                             |     1 |   952 |  5053   (1)|
|  19 |           NESTED LOOPS OUTER                                 |                             |     1 |   927 |  5053   (1)|
|  20 |            NESTED LOOPS OUTER                                |                             |     1 |   892 |  5052   (1)|
|  21 |             NESTED LOOPS OUTER                               |                             |     1 |   867 |  5051   (1)|
|  22 |              NESTED LOOPS OUTER                              |                             |     1 |   842 |  5050   (1)|
|  23 |               NESTED LOOPS OUTER                             |                             |     1 |   817 |  5049   (1)|
|  24 |                NESTED LOOPS OUTER                            |                             |     1 |   792 |  5048   (1)|
|  25 |                 NESTED LOOPS OUTER                           |                             |     1 |   767 |  5048   (1)|
|  26 |                  NESTED LOOPS OUTER                          |                             |     1 |   742 |  5047   (1)|
|  27 |                   NESTED LOOPS OUTER                         |                             |     1 |   717 |  5046   (1)|
|  28 |                    NESTED LOOPS OUTER                        |                             |     1 |   692 |  5045   (1)|
|  29 |                     NESTED LOOPS OUTER                       |                             |     1 |   667 |  5044   (1)|
|  30 |                      NESTED LOOPS OUTER                      |                             |     1 |   642 |  5044   (1)|
|  31 |                       NESTED LOOPS OUTER                     |                             |     1 |   629 |  5043   (1)|
|  32 |                        NESTED LOOPS OUTER                    |                             |     1 |   616 |  5042   (1)|
|  33 |                         NESTED LOOPS OUTER                   |                             |     1 |   548 |  5041   (1)|
|  34 |                          NESTED LOOPS OUTER                  |                             |     1 |   523 |  5041   (1)|
|  35 |                           NESTED LOOPS OUTER                 |                             |     1 |   498 |  5041   (1)|
|  36 |                            NESTED LOOPS OUTER                |                             |     1 |   488 |  5040   (1)|
|* 37 |                             FILTER                           |                             |       |       |            |
|  38 |                              NESTED LOOPS OUTER              |                             |     1 |   463 |  5040   (1)|
|* 39 |                               HASH JOIN OUTER                |                             |   197 | 67571 |  4451   (1)|
|* 40 |                                HASH JOIN OUTER               |                             |   197 | 57721 |  4416   (1)|
|* 41 |                                 HASH JOIN RIGHT OUTER        |                             |   197 | 47871 |  4381   (1)|
|  42 |                                  VIEW                        | index$_join$_011            |   126 |  1638 |     3  (34)|
|* 43 |                                   HASH JOIN                  |                             |       |       |            |
|  44 |                                    INDEX FAST FULL SCAN      | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  45 |                                    INDEX FAST FULL SCAN      | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|* 46 |                                  HASH JOIN RIGHT OUTER       |                             |   197 | 45310 |  4378   (1)|
|  47 |                                   VIEW                       | index$_join$_012            |   126 |  1638 |     3  (34)|
|* 48 |                                    HASH JOIN                 |                             |       |       |            |
|  49 |                                     INDEX FAST FULL SCAN     | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  50 |                                     INDEX FAST FULL SCAN     | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|* 51 |                                   HASH JOIN RIGHT OUTER      |                             |   197 | 42749 |  4375   (1)|
|  52 |                                    VIEW                      | index$_join$_013            |   126 |  1638 |     3  (34)|
|* 53 |                                     HASH JOIN                |                             |       |       |            |
|  54 |                                      INDEX FAST FULL SCAN    | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  55 |                                      INDEX FAST FULL SCAN    | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|  56 |                                    NESTED LOOPS OUTER        |                             |   197 | 40188 |  4372   (1)|
|* 57 |                                     HASH JOIN                |                             |   197 | 36248 |  4371   (1)|
|  58 |                                      VIEW                    | index$_join$_033            |     5 |    95 |     3  (34)|
|* 59 |                                       HASH JOIN              |                             |       |       |            |
|  60 |                                        INDEX FAST FULL SCAN  | C_JURPERSONS_CODE_UK        |     5 |    95 |     1   (0)|
|  61 |                                        INDEX FAST FULL SCAN  | C_JURPERSONS_PK             |     5 |    95 |     1   (0)|
|  62 |                                      MERGE JOIN CARTESIAN    |                             |   197 | 32505 |  4368   (1)|
|  63 |                                       VIEW                   | VW_NSO_3                    |     1 |    13 |  4330   (1)|
|  64 |                                        HASH UNIQUE           |                             |     1 |    52 |  4330   (1)|
|* 65 |                                         HASH JOIN            |                             |     1 |    52 |  4329   (1)|
|* 66 |                                          HASH JOIN           |                             |     1 |    49 |  3384   (1)|
|  67 |                                           VIEW               | VW_SQ_2                     |     3 |     6 |    43   (3)|
|  68 |                                            UNION-ALL         |                             |       |       |            |
|* 69 | INDEX ROWID                                 TABLE ACCESS BY  | USERPRIV                    |     1 |    16 |    20   (0)|
|* 70 |                                              INDEX SKIP SCAN | I_USERPRIV_JURPERS_UNITCODE |    53 |       |    17   (0)|
|* 71 |                                             HASH JOIN        |                             |     2 |    56 |    23   (5)|
|  72 |  INDEX ROWID                                 TABLE ACCESS BY | USERROLES                   |     5 |    55 |     2   (0)|
|* 73 | AN                                            INDEX RANGE SC | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|* 74 |  INDEX ROWID                                 TABLE ACCESS BY | USERPRIV                    |    32 |   544 |    20   (0)|
|* 75 | N                                             INDEX SKIP SCA | I_USERPRIV_JURPERS_UNITCODE |    53 |       |    17   (0)|
|  76 |                                           NESTED LOOPS       |                             |       |       |            |
|  77 |                                            NESTED LOOPS      |                             | 70472 |  3234K|  3340   (1)|
|  78 | INDEX ROWID                                 TABLE ACCESS BY  | DICACCS                     |     3 |    36 |     2   (0)|
|* 79 | N                                            INDEX RANGE SCA | C_DICACCS_ACCNUMB_UK        |     3 |       |     1   (0)|
|* 80 |                                             INDEX RANGE SCAN | I_OPRSPECS_TURNS_CR_ACC     | 23482 |       |   102   (0)|
|  81 | NDEX ROWID                                 TABLE ACCESS BY I | OPRSPECS                    | 23465 |   802K|  2053   (1)|
|  82 |                                          VIEW                | VW_SQ_1                     | 27091 | 81273 |   945   (1)|
|  83 |                                           UNION-ALL          |                             |       |       |            |
|  84 | NDEX ROWID                                 TABLE ACCESS BY I | USERPRIV                    |  6454 | 38724 |   230   (0)|
|* 85 |                                             INDEX RANGE SCAN | I_USERPRIV_AUTHID_UNIT      |  2600 |       |    11   (0)|
|* 86 |                                            HASH JOIN         |                             | 20637 |   362K|   715   (1)|
|  87 | INDEX ROWID                                 TABLE ACCESS BY  | USERROLES                   |     5 |    55 |     2   (0)|
|* 88 | N                                            INDEX RANGE SCA | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|* 89 | SCAN                                        INDEX FAST FULL  | C_USERPRIV_UK               |   277K|  1896K|   711   (1)|
|  90 |                                       BUFFER SORT            |                             |   197 | 29944 |  4368   (1)|
|  91 |  ROWID                                 TABLE ACCESS BY INDEX | ECONOPRS                    |   197 | 29944 |    38   (0)|
|* 92 |                                         INDEX RANGE SCAN     | I_ECONOPRS_OPDATECOMP       |   197 |       |     2   (0)|
|  93 | WID                                 TABLE ACCESS BY INDEX RO | DICSMRKS                    |     1 |    20 |     1   (0)|
|* 94 |                                      INDEX UNIQUE SCAN       | C_DICSMRKS_PK               |     1 |       |     0   (0)|
|  95 |                                 TABLE ACCESS FULL            | AGNLIST                     |  2732 |   133K|    34   (0)|
|  96 |                                TABLE ACCESS FULL             | AGNLIST                     |  2732 |   133K|    34   (0)|
|  97 |                               TABLE ACCESS BY INDEX ROWID    | OPRSPECS                    |     7 |   840 |     3   (0)|
|* 98 |                                INDEX RANGE SCAN              | I_EOSPS_PRN_FK              |     7 |       |     2   (0)|
|  99 |                             TABLE ACCESS BY INDEX ROWID      | MEMORDER                    |     1 |    25 |     0   (0)|
|*100 |                              INDEX UNIQUE SCAN               | C_MEMORDER_PK               |     1 |       |     0   (0)|
| 101 |                            TABLE ACCESS BY INDEX ROWID       | CURNAMES                    |     1 |    10 |     1   (0)|
|*102 |                             INDEX UNIQUE SCAN                | C_CURNAMES_PK               |     1 |       |     0   (0)|
| 103 |                           TABLE ACCESS BY INDEX ROWID        | DICBUNTS                    |     1 |    25 |     0   (0)|
|*104 |                            INDEX UNIQUE SCAN                 | C_DICBUNTS_PK               |     1 |       |     0   (0)|
| 105 |                          TABLE ACCESS BY INDEX ROWID         | DICBUNTS                    |     1 |    25 |     0   (0)|
|*106 |                           INDEX UNIQUE SCAN                  | C_DICBUNTS_PK               |     1 |       |     0   (0)|
| 107 |                         TABLE ACCESS BY INDEX ROWID          | DICNOMNS                    |     1 |    68 |     1   (0)|
|*108 |                          INDEX UNIQUE SCAN                   | C_DICNOMNS_RN_PK            |     1 |       |     0   (0)|
| 109 |                        TABLE ACCESS BY INDEX ROWID           | DICMUNTS                    |     1 |    13 |     1   (0)|
|*110 |                         INDEX UNIQUE SCAN                    | C_DICMUNTS_PK               |     1 |       |     0   (0)|
| 111 |                       TABLE ACCESS BY INDEX ROWID            | DICMUNTS                    |     1 |    13 |     1   (0)|
|*112 |                        INDEX UNIQUE SCAN                     | C_DICMUNTS_PK               |     1 |       |     0   (0)|
| 113 |                      TABLE ACCESS BY INDEX ROWID             | DICANLS                     |     1 |    25 |     0   (0)|
|*114 |                       INDEX UNIQUE SCAN                      | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 115 |                     TABLE ACCESS BY INDEX ROWID              | DICANLS                     |     1 |    25 |     1   (0)|
|*116 |                      INDEX UNIQUE SCAN                       | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 117 |                    TABLE ACCESS BY INDEX ROWID               | DICANLS                     |     1 |    25 |     1   (0)|
|*118 |                     INDEX UNIQUE SCAN                        | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 119 |                   TABLE ACCESS BY INDEX ROWID                | DICANLS                     |     1 |    25 |     1   (0)|
|*120 |                    INDEX UNIQUE SCAN                         | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 121 |                  TABLE ACCESS BY INDEX ROWID                 | DICANLS                     |     1 |    25 |     1   (0)|
|*122 |                   INDEX UNIQUE SCAN                          | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 123 |                 TABLE ACCESS BY INDEX ROWID                  | DICANLS                     |     1 |    25 |     0   (0)|
|*124 |                  INDEX UNIQUE SCAN                           | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 125 |                TABLE ACCESS BY INDEX ROWID                   | DICANLS                     |     1 |    25 |     1   (0)|
|*126 |                 INDEX UNIQUE SCAN                            | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 127 |               TABLE ACCESS BY INDEX ROWID                    | DICANLS                     |     1 |    25 |     1   (0)|
|*128 |                INDEX UNIQUE SCAN                             | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 129 |              TABLE ACCESS BY INDEX ROWID                     | DICANLS                     |     1 |    25 |     1   (0)|
|*130 |               INDEX UNIQUE SCAN                              | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 131 |             TABLE ACCESS BY INDEX ROWID                      | DICANLS                     |     1 |    25 |     1   (0)|
|*132 |              INDEX UNIQUE SCAN                               | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 133 |            TABLE ACCESS BY INDEX ROWID                       | DICACCS                     |     1 |    35 |     1   (0)|
|*134 |             INDEX UNIQUE SCAN                                | C_DICACCS_RN_PK             |     1 |       |     0   (0)|
| 135 |           TABLE ACCESS BY INDEX ROWID                        | BALELEMENT                  |     1 |    25 |     0   (0)|
|*136 |            INDEX UNIQUE SCAN                                 | C_BALELEMENT_PK             |     1 |       |     0   (0)|
| 137 |          TABLE ACCESS BY INDEX ROWID                         | ACCTYPES                    |     1 |    33 |     1   (0)|
|*138 |           INDEX UNIQUE SCAN                                  | C_ACCTYPES_PK               |     1 |       |     0   (0)|
| 139 |         TABLE ACCESS BY INDEX ROWID                          | CURNAMES                    |     1 |    10 |     0   (0)|
|*140 |          INDEX UNIQUE SCAN                                   | C_CURNAMES_PK               |     1 |       |     0   (0)|
| 141 |        TABLE ACCESS BY INDEX ROWID                           | DICACCS                     |     1 |    37 |     1   (0)|
|*142 |         INDEX UNIQUE SCAN                                    | C_DICACCS_RN_PK             |     1 |       |     0   (0)|
| 143 |       TABLE ACCESS BY INDEX ROWID                            | ACCTFORM                    |     1 |     6 |     1   (0)|
|*144 |        INDEX UNIQUE SCAN                                     | C_ACCTFORM_PK               |     1 |       |     0   (0)|
| 145 |      TABLE ACCESS BY INDEX ROWID                             | BALELEMENT                  |     1 |    25 |     0   (0)|
|*146 |       INDEX UNIQUE SCAN                                      | C_BALELEMENT_PK             |     1 |       |     0   (0)|
| 147 |     TABLE ACCESS BY INDEX ROWID                              | ACCTYPES                    |     1 |    33 |     1   (0)|
|*148 |      INDEX UNIQUE SCAN                                       | C_ACCTYPES_PK               |     1 |       |     0   (0)|
| 149 |    TABLE ACCESS BY INDEX ROWID                               | CURNAMES                    |     1 |    10 |     0   (0)|
|*150 |     INDEX UNIQUE SCAN                                        | C_CURNAMES_PK               |     1 |       |     0   (0)|
| 151 |   UNION-ALL                                                  |                             |       |       |            |
|*152 |    TABLE ACCESS BY INDEX ROWID                               | USERPRIV                    |     1 |     6 |     8   (0)|
|*153 |     INDEX RANGE SCAN                                         | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
|*154 |    HASH JOIN                                                 |                             |     3 |    54 |    11  (10)|
| 155 |     TABLE ACCESS BY INDEX ROWID                              | USERROLES                   |     5 |    55 |     2   (0)|
|*156 |      INDEX RANGE SCAN                                        | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*157 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |    38 |   266 |     8   (0)|
|*158 |      INDEX RANGE SCAN                                        | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
| 159 |   UNION-ALL                                                  |                             |       |       |            |
|*160 |    TABLE ACCESS BY INDEX ROWID                               | USERPRIV                    |     1 |    16 |     4   (0)|
|*161 |     INDEX RANGE SCAN                                         | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
|*162 |    HASH JOIN                                                 |                             |     1 |    28 |     7  (15)|
| 163 |     TABLE ACCESS BY INDEX ROWID                              | USERROLES                   |     5 |    55 |     2   (0)|
|*164 |      INDEX RANGE SCAN                                        | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*165 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |     6 |   102 |     4   (0)|
|*166 |      INDEX RANGE SCAN                                        | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("L"."OUT_UNITCODE"='OperCards' AND "L"."IN_UNITCODE"='EconomicOperationsSpecs')
   3 - access("L"."IN_DOCUMENT"=:B1)
   5 - filter("L"."IN_UNITCODE"='EconomicOperations' AND "L"."LINK_TYPE"=3 AND "L"."OUT_UNITCODE"='EconomicOperations')
   6 - access("L"."OUT_DOCUMENT"=:B1)
   8 - filter("L"."IN_UNITCODE"='Inventory' AND "L"."OUT_UNITCODE"='EconomicOperationsSpecs')
   9 - access("L"."OUT_DOCUMENT"=:B1)
  10 - filter( EXISTS ( (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") */ NULL FROM PARUS."USERPRIV" "UP" WHERE 
              "UP"."CATALOG"=:B1 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") INDEX ("UR" 
              "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."CATALOG"=:B2 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT NULL)) AND  EXISTS ( (SELECT /*+ INDEX ("UP" 
              "I_USERPRIV_JURPERS_UNITCODE") */ NULL FROM PARUS."USERPRIV" "UP" WHERE "UP"."UNITCODE"='EconomicOperations' AND 
              "UP"."JUR_PERS"=:B3 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_JURPERS_UNITCODE") INDEX 
              ("UR" "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."UNITCODE"='EconomicOperations' AND "UP"."JUR_PERS"=:B4 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT 
              NULL)))
  37 - filter("RN"=NVL("O"."RN","E"."RN"))
  39 - access("E"."AGENT_FROM"="AG1"."RN"(+))
  40 - access("E"."AGENT_TO"="AG2"."RN"(+))
  41 - access("E"."VALID_DOCTYPE"="DV"."RN"(+))
  43 - access(ROWID=ROWID)
  46 - access("E"."FACT_DOCTYPE"="DF"."RN"(+))
  48 - access(ROWID=ROWID)
  51 - access("E"."ESCORT_DOCTYPE"="DE"."RN"(+))
  53 - access(ROWID=ROWID)
  57 - access("E"."JUR_PERS"="JP"."RN")
  59 - access(ROWID=ROWID)
  65 - access("VW_COL_1"="D"."CRN")
  66 - access("VW_COL_1"="D"."JUR_PERS")
  69 - filter("UP"."AUTHID"=USER@!)
  70 - access("UP"."UNITCODE"='EconomicOperations')
       filter("UP"."UNITCODE"='EconomicOperations')
  71 - access("UP"."ROLEID"="UR"."ROLEID")
  73 - access("UR"."AUTHID"=USER@!)
  74 - filter("UP"."ROLEID" IS NOT NULL)
  75 - access("UP"."UNITCODE"='EconomicOperations')
       filter("UP"."UNITCODE"='EconomicOperations')
  79 - access("ACC_NUMBER" LIKE '19%')
       filter("ACC_NUMBER" LIKE '19%')
  80 - access("D"."ACCOUNT_CREDIT"="RN" AND "D"."COMPANY"=474316)
  85 - access("UP"."AUTHID"=USER@!)
  86 - access("UP"."ROLEID"="UR"."ROLEID")
  88 - access("UR"."AUTHID"=USER@!)
  89 - filter("UP"."ROLEID" IS NOT NULL)
  92 - access("E"."COMPANY"=474316 AND "E"."OPERATION_DATE"=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(INTERNAL_FUNCTION("OPERATION_DATE"))>=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("OPERATION_DATE"))<=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  94 - access("E"."SPECIAL_MARK"="DM"."RN"(+))
  98 - access("E"."RN"="O"."PRN"(+))
 100 - access("O"."ORDER_RN"="MO"."RN"(+))
 102 - access("O"."CURRENCY"="C"."RN"(+))
 104 - access("O"."BALUNIT_CREDIT"="B2"."RN"(+))
 106 - access("O"."BALUNIT_DEBIT"="B1"."RN"(+))
 108 - access("O"."NOMENCLATURE"="N"."RN"(+))
 110 - access("N"."UMEAS_ALT"="UA"."RN"(+))
 112 - access("N"."UMEAS_MAIN"="U"."RN"(+))
 114 - access("O"."ANALYTIC_CREDIT5"="LC5"."RN"(+))
 116 - access("O"."ANALYTIC_CREDIT4"="LC4"."RN"(+))
 118 - access("O"."ANALYTIC_CREDIT3"="LC3"."RN"(+))
 120 - access("O"."ANALYTIC_CREDIT2"="LC2"."RN"(+))
 122 - access("O"."ANALYTIC_CREDIT1"="LC1"."RN"(+))
 124 - access("O"."ANALYTIC_DEBIT5"="LD5"."RN"(+))
 126 - access("O"."ANALYTIC_DEBIT4"="LD4"."RN"(+))
 128 - access("O"."ANALYTIC_DEBIT3"="LD3"."RN"(+))
 130 - access("O"."ANALYTIC_DEBIT2"="LD2"."RN"(+))
 132 - access("O"."ANALYTIC_DEBIT1"="LD1"."RN"(+))
 134 - access("O"."ACCOUNT_CREDIT"="A2"."RN"(+))
 136 - access("A2"."BALUNIT"="BE2"."RN"(+))
 138 - access("A2"."ACCTYPES"="AT2"."RN"(+))
 140 - access("AT2"."CURRENCY"="C2"."RN"(+))
 142 - access("O"."ACCOUNT_DEBIT"="A1"."RN"(+))
 144 - access("A1"."ACC_FORM"="ACT"."NUMB"(+))
 146 - access("A1"."BALUNIT"="BE1"."RN"(+))
 148 - access("A1"."ACCTYPES"="AT1"."RN"(+))
 150 - access("AT1"."CURRENCY"="C1"."RN"(+))
 152 - filter("UP"."AUTHID"=USER@!)
 153 - access("UP"."CATALOG"=:B1)
 154 - access("UP"."ROLEID"="UR"."ROLEID")
 156 - access("UR"."AUTHID"=USER@!)
 157 - filter("UP"."ROLEID" IS NOT NULL)
 158 - access("UP"."CATALOG"=:B1)
 160 - filter("UP"."AUTHID"=USER@!)
 161 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 162 - access("UP"."ROLEID"="UR"."ROLEID")
 164 - access("UR"."AUTHID"=USER@!)
 165 - filter("UP"."ROLEID" IS NOT NULL)
 166 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 
Note
-----
   - 'PLAN_TABLE' is old version


это без хинта результат выдает за пару секунд
+

SELECT  * 
  FROM v_eospecs_union v
 WHERE company =474316
   AND doperation_date >= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
   AND doperation_date <= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
   AND rn IN (
          SELECT m.rn
            FROM v_oprspecs_shadow m
           WHERE company = 474316
             AND doperation_date >= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
             AND doperation_date <= TO_DATE ('01/08/2012', 'dd/mm/yyyy')
             AND m.account_credit IN (SELECT rn
                                        FROM dicaccs
                                       WHERE acc_number LIKE '19%'));

 
 
 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                             |     1 |  1093 | 26878   (3)|
|   1 |  SORT AGGREGATE                                              |                             |     1 |    55 |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    55 |     6   (0)|
|*  3 |    INDEX RANGE SCAN                                          | I_DOCLINKS_IN_DOCUMENT      |     5 |       |     3   (0)|
|   4 |  SORT AGGREGATE                                              |                             |     1 |    57 |            |
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    57 |     4   (0)|
|*  6 |    INDEX RANGE SCAN                                          | I_DOCLINKS_OUT_DOCUMENT     |     3 |       |     3   (0)|
|   7 |  SORT AGGREGATE                                              |                             |     1 |    55 |            |
|*  8 |   TABLE ACCESS BY INDEX ROWID                                | DOCLINKS                    |     1 |    55 |     4   (0)|
|*  9 |    INDEX RANGE SCAN                                          | I_DOCLINKS_OUT_DOCUMENT     |     3 |       |     3   (0)|
|* 10 |  FILTER                                                      |                             |       |       |            |
|* 11 |   HASH JOIN RIGHT OUTER                                      |                             |  1404 |  1498K|  2262   (2)|
|  12 |    VIEW                                                      | index$_join$_032            |     2 |    20 |     3  (34)|
|* 13 |     HASH JOIN                                                |                             |       |       |            |
|  14 |      INDEX FAST FULL SCAN                                    | C_CURNAMES_INTCODE_UK       |     2 |    20 |     1   (0)|
|  15 |      INDEX FAST FULL SCAN                                    | C_CURNAMES_PK               |     2 |    20 |     1   (0)|
|  16 |    NESTED LOOPS OUTER                                        |                             |  1404 |  1484K|  2259   (2)|
|* 17 |     HASH JOIN RIGHT OUTER                                    |                             |  1404 |  1471K|  2259   (2)|
|  18 |      TABLE ACCESS FULL                                       | ACCTYPES                    |     2 |    66 |     3   (0)|
|  19 |      NESTED LOOPS OUTER                                      |                             |  1404 |  1425K|  2255   (1)|
|* 20 |       HASH JOIN RIGHT OUTER                                  |                             |  1404 |  1412K|  2255   (1)|
|  21 |        TABLE ACCESS FULL                                     | ACCTYPES                    |     2 |    66 |     3   (0)|
|* 22 |        HASH JOIN RIGHT OUTER                                 |                             |  1404 |  1366K|  2252   (1)|
|  23 |         VIEW                                                 | index$_join$_028            |   257 |  3341 |     3  (34)|
|* 24 |          HASH JOIN                                           |                             |       |       |            |
|  25 |           INDEX FAST FULL SCAN                               | C_DICMUNTS_MNEMO_UK         |   257 |  3341 |     1   (0)|
|  26 |           INDEX FAST FULL SCAN                               | C_DICMUNTS_PK               |   257 |  3341 |     1   (0)|
|* 27 |         HASH JOIN RIGHT OUTER                                |                             |  1404 |  1349K|  2249   (1)|
|  28 |          TABLE ACCESS FULL                                   | ACCTFORM                    |    14 |    84 |     3   (0)|
|  29 |          NESTED LOOPS OUTER                                  |                             |  1404 |  1340K|  2245   (1)|
|* 30 |           HASH JOIN RIGHT OUTER                              |                             |  1404 |  1306K|  2245   (1)|
|  31 |            TABLE ACCESS FULL                                 | DICACCS                     |   249 |  9213 |     3   (0)|
|  32 |            NESTED LOOPS OUTER                                |                             |  1404 |  1255K|  2242   (1)|
|* 33 |             HASH JOIN RIGHT OUTER                            |                             |  1404 |  1221K|  2242   (1)|
|  34 |              TABLE ACCESS FULL                               | DICACCS                     |   249 |  8715 |     3   (0)|
|* 35 |              HASH JOIN RIGHT OUTER                           |                             |  1404 |  1173K|  2238   (1)|
|  36 |               TABLE ACCESS FULL                              | DICANLS                     | 31638 |   772K|   171   (1)|
|* 37 |               HASH JOIN OUTER                                |                             |  1404 |  1139K|  2066   (1)|
|* 38 |                HASH JOIN OUTER                               |                             |  1404 |  1105K|  1895   (1)|
|* 39 |                 HASH JOIN OUTER                              |                             |  1404 |  1070K|  1723   (1)|
|* 40 |                  HASH JOIN OUTER                             |                             |  1404 |  1036K|  1552   (1)|
|* 41 |                   HASH JOIN RIGHT OUTER                      |                             |  1404 |  1002K|  1380   (1)|
|  42 |                    VIEW                                      | index$_join$_029            |   257 |  3341 |     3  (34)|
|* 43 |                     HASH JOIN                                |                             |       |       |            |
|  44 |                      INDEX FAST FULL SCAN                    | C_DICMUNTS_MNEMO_UK         |   257 |  3341 |     1   (0)|
|  45 |                      INDEX FAST FULL SCAN                    | C_DICMUNTS_PK               |   257 |  3341 |     1   (0)|
|* 46 |                    HASH JOIN OUTER                           |                             |  1404 |   984K|  1377   (1)|
|  47 |                     NESTED LOOPS OUTER                       |                             |  1404 |   891K|  1137   (1)|
|* 48 |                      HASH JOIN OUTER                         |                             |  1404 |   856K|   975   (1)|
|  49 |                       NESTED LOOPS OUTER                     |                             |  1404 |   822K|   803   (1)|
|  50 |                        NESTED LOOPS OUTER                    |                             |  1404 |   788K|   711   (1)|
|  51 |                         NESTED LOOPS OUTER                   |                             |  1404 |   754K|   711   (1)|
|  52 |                          NESTED LOOPS OUTER                  |                             |  1404 |   719K|   711   (1)|
|  53 |                           NESTED LOOPS OUTER                 |                             |  1404 |   685K|   711   (1)|
|  54 |                            NESTED LOOPS OUTER                |                             |  1404 |   651K|   711   (1)|
|  55 |                             NESTED LOOPS OUTER               |                             |  1404 |   616K|   711   (1)|
|* 56 |                              HASH JOIN RIGHT OUTER           |                             |   197 | 65010 |   122   (5)|
|  57 |                               VIEW                           | index$_join$_013            |   126 |  1638 |     3  (34)|
|* 58 |                                HASH JOIN                     |                             |       |       |            |
|  59 |                                 INDEX FAST FULL SCAN         | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  60 |                                 INDEX FAST FULL SCAN         | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|* 61 |                               HASH JOIN RIGHT OUTER          |                             |   197 | 62449 |   119   (5)|
|  62 |                                VIEW                          | index$_join$_012            |   126 |  1638 |     3  (34)|
|* 63 |                                 HASH JOIN                    |                             |       |       |            |
|  64 |                                  INDEX FAST FULL SCAN        | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  65 |                                  INDEX FAST FULL SCAN        | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|* 66 |                                HASH JOIN OUTER               |                             |   197 | 59888 |   116   (4)|
|* 67 |                                 HASH JOIN RIGHT OUTER        |                             |   197 | 50038 |    81   (4)|
|  68 |                                  VIEW                        | index$_join$_011            |   126 |  1638 |     3  (34)|
|* 69 |                                   HASH JOIN                  |                             |       |       |            |
|  70 |                                    INDEX FAST FULL SCAN      | C_DOCTYPES_PK               |   126 |  1638 |     1   (0)|
|  71 |                                    INDEX FAST FULL SCAN      | C_DOCTYPES_UK               |   126 |  1638 |     1   (0)|
|* 72 |                                  HASH JOIN OUTER             |                             |   197 | 47477 |    78   (3)|
|  73 |                                   NESTED LOOPS OUTER         |                             |   197 | 37627 |    43   (3)|
|* 74 |                                    HASH JOIN                 |                             |   197 | 33687 |    42   (3)|
|  75 |                                     VIEW                     | index$_join$_033            |     5 |    95 |     3  (34)|
|* 76 |                                      HASH JOIN               |                             |       |       |            |
|  77 |                                       INDEX FAST FULL SCAN   | C_JURPERSONS_CODE_UK        |     5 |    95 |     1   (0)|
|  78 |                                       INDEX FAST FULL SCAN   | C_JURPERSONS_PK             |     5 |    95 |     1   (0)|
|  79 | WID                                 TABLE ACCESS BY INDEX RO | ECONOPRS                    |   197 | 29944 |    39   (0)|
|* 80 |                                      INDEX RANGE SCAN        | I_ECONOPRS_OPDATECOMP       |   197 |       |     3   (0)|
|  81 | ID                                 TABLE ACCESS BY INDEX ROW | DICSMRKS                    |     1 |    20 |     1   (0)|
|* 82 |                                     INDEX UNIQUE SCAN        | C_DICSMRKS_PK               |     1 |       |     0   (0)|
|  83 |                                   TABLE ACCESS FULL          | AGNLIST                     |  2732 |   133K|    34   (0)|
|  84 |                                 TABLE ACCESS FULL            | AGNLIST                     |  2732 |   133K|    34   (0)|
|  85 |                              TABLE ACCESS BY INDEX ROWID     | OPRSPECS                    |     7 |   840 |     3   (0)|
|* 86 |                               INDEX RANGE SCAN               | I_EOSPS_PRN_FK              |     7 |       |     2   (0)|
|  87 |                             TABLE ACCESS BY INDEX ROWID      | MEMORDER                    |     1 |    25 |     0   (0)|
|* 88 |                              INDEX UNIQUE SCAN               | C_MEMORDER_PK               |     1 |       |     0   (0)|
|  89 |                            TABLE ACCESS BY INDEX ROWID       | DICBUNTS                    |     1 |    25 |     0   (0)|
|* 90 |                             INDEX UNIQUE SCAN                | C_DICBUNTS_PK               |     1 |       |     0   (0)|
|  91 |                           TABLE ACCESS BY INDEX ROWID        | DICBUNTS                    |     1 |    25 |     0   (0)|
|* 92 |                            INDEX UNIQUE SCAN                 | C_DICBUNTS_PK               |     1 |       |     0   (0)|
|  93 |                          TABLE ACCESS BY INDEX ROWID         | DICANLS                     |     1 |    25 |     0   (0)|
|* 94 |                           INDEX UNIQUE SCAN                  | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
|  95 |                         TABLE ACCESS BY INDEX ROWID          | DICANLS                     |     1 |    25 |     0   (0)|
|* 96 |                          INDEX UNIQUE SCAN                   | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
|  97 |                        TABLE ACCESS BY INDEX ROWID           | DICANLS                     |     1 |    25 |     1   (0)|
|* 98 |                         INDEX UNIQUE SCAN                    | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
|  99 |                       TABLE ACCESS FULL                      | DICANLS                     | 31638 |   772K|   171   (1)|
| 100 |                      TABLE ACCESS BY INDEX ROWID             | DICANLS                     |     1 |    25 |     1   (0)|
|*101 |                       INDEX UNIQUE SCAN                      | C_DICANLS_RN_PK             |     1 |       |     0   (0)|
| 102 |                     TABLE ACCESS FULL                        | DICNOMNS                    | 39303 |  2609K|   240   (1)|
| 103 |                   TABLE ACCESS FULL                          | DICANLS                     | 31638 |   772K|   171   (1)|
| 104 |                  TABLE ACCESS FULL                           | DICANLS                     | 31638 |   772K|   171   (1)|
| 105 |                 TABLE ACCESS FULL                            | DICANLS                     | 31638 |   772K|   171   (1)|
| 106 |                TABLE ACCESS FULL                             | DICANLS                     | 31638 |   772K|   171   (1)|
| 107 |             TABLE ACCESS BY INDEX ROWID                      | BALELEMENT                  |     1 |    25 |     0   (0)|
|*108 |              INDEX UNIQUE SCAN                               | C_BALELEMENT_PK             |     1 |       |     0   (0)|
| 109 |           TABLE ACCESS BY INDEX ROWID                        | BALELEMENT                  |     1 |    25 |     0   (0)|
|*110 |            INDEX UNIQUE SCAN                                 | C_BALELEMENT_PK             |     1 |       |     0   (0)|
| 111 |       TABLE ACCESS BY INDEX ROWID                            | CURNAMES                    |     1 |    10 |     0   (0)|
|*112 |        INDEX UNIQUE SCAN                                     | C_CURNAMES_PK               |     1 |       |     0   (0)|
| 113 |     TABLE ACCESS BY INDEX ROWID                              | CURNAMES                    |     1 |    10 |     0   (0)|
|*114 |      INDEX UNIQUE SCAN                                       | C_CURNAMES_PK               |     1 |       |     0   (0)|
| 115 |   UNION-ALL                                                  |                             |       |       |            |
|*116 |    TABLE ACCESS BY INDEX ROWID                               | USERPRIV                    |     1 |     6 |     8   (0)|
|*117 |     INDEX RANGE SCAN                                         | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
|*118 |    HASH JOIN                                                 |                             |     3 |    54 |    11  (10)|
| 119 |     TABLE ACCESS BY INDEX ROWID                              | USERROLES                   |     5 |    55 |     2   (0)|
|*120 |      INDEX RANGE SCAN                                        | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*121 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |    38 |   266 |     8   (0)|
|*122 |      INDEX RANGE SCAN                                        | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
| 123 |   UNION-ALL                                                  |                             |       |       |            |
|*124 |    TABLE ACCESS BY INDEX ROWID                               | USERPRIV                    |     1 |    16 |     4   (0)|
|*125 |     INDEX RANGE SCAN                                         | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
|*126 |    HASH JOIN                                                 |                             |     1 |    28 |     7  (15)|
| 127 |     TABLE ACCESS BY INDEX ROWID                              | USERROLES                   |     5 |    55 |     2   (0)|
|*128 |      INDEX RANGE SCAN                                        | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*129 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |     6 |   102 |     4   (0)|
|*130 |      INDEX RANGE SCAN                                        | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
|*131 |   FILTER                                                     |                             |       |       |            |
|*132 |    FILTER                                                    |                             |       |       |            |
| 133 |     NESTED LOOPS OUTER                                       |                             |     1 |    89 |     6   (0)|
| 134 |      NESTED LOOPS OUTER                                      |                             |     1 |    84 |     6   (0)|
| 135 |       NESTED LOOPS                                           |                             |     1 |    79 |     6   (0)|
| 136 |        NESTED LOOPS OUTER                                    |                             |     1 |    67 |     5   (0)|
| 137 |         NESTED LOOPS OUTER                                   |                             |     1 |    60 |     5   (0)|
| 138 |          NESTED LOOPS OUTER                                  |                             |     1 |    49 |     4   (0)|
|*139 |           TABLE ACCESS BY INDEX ROWID                        | OPRSPECS                    |     1 |    38 |     3   (0)|
|*140 |            INDEX UNIQUE SCAN                                 | C_EOSPS_PK                  |     1 |       |     2   (0)|
| 141 |           TABLE ACCESS BY INDEX ROWID                        | DICACCS                     |   249 |  2739 |     1   (0)|
|*142 |            INDEX UNIQUE SCAN                                 | C_DICACCS_RN_PK             |     1 |       |     0   (0)|
| 143 |          TABLE ACCESS BY INDEX ROWID                         | DICACCS                     |   249 |  2739 |     1   (0)|
|*144 |           INDEX UNIQUE SCAN                                  | C_DICACCS_RN_PK             |     1 |       |     0   (0)|
|*145 |         INDEX UNIQUE SCAN                                    | C_DICNOMNS_RN_PK            | 39303 |   268K|     0   (0)|
|*146 |        TABLE ACCESS BY INDEX ROWID                           | DICACCS                     |     3 |    36 |     1   (0)|
|*147 |         INDEX UNIQUE SCAN                                    | C_DICACCS_RN_PK             |     1 |       |     0   (0)|
|*148 |       INDEX UNIQUE SCAN                                      | C_ACCTYPES_PK               |     2 |    10 |     0   (0)|
|*149 |      INDEX UNIQUE SCAN                                       | C_ACCTYPES_PK               |     2 |    10 |     0   (0)|
| 150 |    UNION-ALL                                                 |                             |       |       |            |
|*151 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |     1 |     6 |     8   (0)|
|*152 |      INDEX RANGE SCAN                                        | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
|*153 |     HASH JOIN                                                |                             |     3 |    54 |    11  (10)|
| 154 |      TABLE ACCESS BY INDEX ROWID                             | USERROLES                   |     5 |    55 |     2   (0)|
|*155 |       INDEX RANGE SCAN                                       | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*156 |      TABLE ACCESS BY INDEX ROWID                             | USERPRIV                    |    38 |   266 |     8   (0)|
|*157 |       INDEX RANGE SCAN                                       | I_USERPRIV_CATALOG_FK       |    19 |       |     1   (0)|
| 158 |    UNION-ALL                                                 |                             |       |       |            |
|*159 |     TABLE ACCESS BY INDEX ROWID                              | USERPRIV                    |     1 |    16 |     4   (0)|
|*160 |      INDEX RANGE SCAN                                        | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
|*161 |     HASH JOIN                                                |                             |     1 |    28 |     7  (15)|
| 162 |      TABLE ACCESS BY INDEX ROWID                             | USERROLES                   |     5 |    55 |     2   (0)|
|*163 |       INDEX RANGE SCAN                                       | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|*164 |      TABLE ACCESS BY INDEX ROWID                             | USERPRIV                    |     6 |   102 |     4   (0)|
|*165 |       INDEX RANGE SCAN                                       | I_USERPRIV_JURPERS_UNITCODE |     1 |       |     3   (0)|
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("L"."OUT_UNITCODE"='OperCards' AND "L"."IN_UNITCODE"='EconomicOperationsSpecs')
   3 - access("L"."IN_DOCUMENT"=:B1)
   5 - filter("L"."IN_UNITCODE"='EconomicOperations' AND "L"."LINK_TYPE"=3 AND "L"."OUT_UNITCODE"='EconomicOperations')
   6 - access("L"."OUT_DOCUMENT"=:B1)
   8 - filter("L"."IN_UNITCODE"='Inventory' AND "L"."OUT_UNITCODE"='EconomicOperationsSpecs')
   9 - access("L"."OUT_DOCUMENT"=:B1)
  10 - filter( EXISTS ( (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") */ NULL FROM PARUS."USERPRIV" "UP" WHERE 
              "UP"."CATALOG"=:B1 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") INDEX ("UR" 
              "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."CATALOG"=:B2 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT NULL)) AND  EXISTS ( (SELECT /*+ INDEX ("UP" 
              "I_USERPRIV_JURPERS_UNITCODE") */ NULL FROM PARUS."USERPRIV" "UP" WHERE "UP"."UNITCODE"='EconomicOperations' AND 
              "UP"."JUR_PERS"=:B3 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_JURPERS_UNITCODE") INDEX 
              ("UR" "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."UNITCODE"='EconomicOperations' AND "UP"."JUR_PERS"=:B4 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT 
              NULL)) AND  EXISTS (SELECT 0 FROM PARUS."ACCTYPES" "AT2",PARUS."ACCTYPES" "AT1",PARUS."DICACCS" "A2",PARUS."DICACCS" 
              "A1",PARUS."DICNOMNS" "N",PARUS."OPRSPECS" "D","DICACCS" "DICACCS" WHERE  EXISTS ( (SELECT /*+ INDEX ("UP" 
              "I_USERPRIV_CATALOG_FK") */ NULL FROM PARUS."USERPRIV" "UP" WHERE "UP"."CATALOG"=:B5 AND "UP"."AUTHID"=USER@!) UNION ALL 
               (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") INDEX ("UR" "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" 
              "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND "UP"."CATALOG"=:B6 AND "UP"."ROLEID"="UR"."ROLEID" AND 
              "UP"."ROLEID" IS NOT NULL)) AND  EXISTS ( (SELECT /*+ INDEX ("UP" "I_USERPRIV_JURPERS_UNITCODE") */ NULL FROM 
              PARUS."USERPRIV" "UP" WHERE "UP"."UNITCODE"='EconomicOperations' AND "UP"."JUR_PERS"=:B7 AND "UP"."AUTHID"=USER@!) UNION 
              ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_JURPERS_UNITCODE") INDEX ("UR" "I_USERROLES_AUTHID_FK") */ NULL FROM 
              PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND "UP"."UNITCODE"='EconomicOperations' AND 
              "UP"."JUR_PERS"=:B8 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT NULL)) AND :B9<=TO_DATE(' 2012-08-01 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND :B10>=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "D"."ACCOUNT_CREDIT"="RN" AND "ACC_NUMBER" LIKE '19%' AND "D"."RN"=NVL(:B11,:B12) AND "D"."COMPANY"=474316 AND 
              "D"."NOMENCLATURE"="N"."RN"(+) AND "D"."ACCOUNT_DEBIT"="A1"."RN"(+) AND "D"."ACCOUNT_CREDIT"="A2"."RN"(+) AND 
              "A1"."ACCTYPES"="AT1"."RN"(+) AND "A2"."ACCTYPES"="AT2"."RN"(+)))
  11 - access("O"."CURRENCY"="C"."RN"(+))
  13 - access(ROWID=ROWID)
  17 - access("A1"."ACCTYPES"="AT1"."RN"(+))
  20 - access("A2"."ACCTYPES"="AT2"."RN"(+))
  22 - access("N"."UMEAS_MAIN"="U"."RN"(+))
  24 - access(ROWID=ROWID)
  27 - access("A1"."ACC_FORM"="ACT"."NUMB"(+))
  30 - access("O"."ACCOUNT_DEBIT"="A1"."RN"(+))
  33 - access("O"."ACCOUNT_CREDIT"="A2"."RN"(+))
  35 - access("O"."ANALYTIC_DEBIT2"="LD2"."RN"(+))
  37 - access("O"."ANALYTIC_DEBIT3"="LD3"."RN"(+))
  38 - access("O"."ANALYTIC_DEBIT1"="LD1"."RN"(+))
  39 - access("O"."ANALYTIC_CREDIT3"="LC3"."RN"(+))
  40 - access("O"."ANALYTIC_CREDIT1"="LC1"."RN"(+))
  41 - access("N"."UMEAS_ALT"="UA"."RN"(+))
  43 - access(ROWID=ROWID)
  46 - access("O"."NOMENCLATURE"="N"."RN"(+))
  48 - access("O"."ANALYTIC_CREDIT2"="LC2"."RN"(+))
  56 - access("E"."ESCORT_DOCTYPE"="DE"."RN"(+))
  58 - access(ROWID=ROWID)
  61 - access("E"."FACT_DOCTYPE"="DF"."RN"(+))
  63 - access(ROWID=ROWID)
  66 - access("E"."AGENT_FROM"="AG1"."RN"(+))
  67 - access("E"."VALID_DOCTYPE"="DV"."RN"(+))
  69 - access(ROWID=ROWID)
  72 - access("E"."AGENT_TO"="AG2"."RN"(+))
  74 - access("E"."JUR_PERS"="JP"."RN")
  76 - access(ROWID=ROWID)
  80 - access("E"."COMPANY"=474316 AND "E"."OPERATION_DATE"=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(INTERNAL_FUNCTION("OPERATION_DATE"))>=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("OPERATION_DATE"))<=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  82 - access("E"."SPECIAL_MARK"="DM"."RN"(+))
  86 - access("E"."RN"="O"."PRN"(+))
  88 - access("O"."ORDER_RN"="MO"."RN"(+))
  90 - access("O"."BALUNIT_CREDIT"="B2"."RN"(+))
  92 - access("O"."BALUNIT_DEBIT"="B1"."RN"(+))
  94 - access("O"."ANALYTIC_CREDIT5"="LC5"."RN"(+))
  96 - access("O"."ANALYTIC_DEBIT5"="LD5"."RN"(+))
  98 - access("O"."ANALYTIC_DEBIT4"="LD4"."RN"(+))
 101 - access("O"."ANALYTIC_CREDIT4"="LC4"."RN"(+))
 108 - access("A2"."BALUNIT"="BE2"."RN"(+))
 110 - access("A1"."BALUNIT"="BE1"."RN"(+))
 112 - access("AT2"."CURRENCY"="C2"."RN"(+))
 114 - access("AT1"."CURRENCY"="C1"."RN"(+))
 116 - filter("UP"."AUTHID"=USER@!)
 117 - access("UP"."CATALOG"=:B1)
 118 - access("UP"."ROLEID"="UR"."ROLEID")
 120 - access("UR"."AUTHID"=USER@!)
 121 - filter("UP"."ROLEID" IS NOT NULL)
 122 - access("UP"."CATALOG"=:B1)
 124 - filter("UP"."AUTHID"=USER@!)
 125 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 126 - access("UP"."ROLEID"="UR"."ROLEID")
 128 - access("UR"."AUTHID"=USER@!)
 129 - filter("UP"."ROLEID" IS NOT NULL)
 130 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 131 - filter( EXISTS ( (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") */ NULL FROM PARUS."USERPRIV" "UP" WHERE 
              "UP"."CATALOG"=:B1 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_CATALOG_FK") INDEX ("UR" 
              "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."CATALOG"=:B2 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT NULL)) AND  EXISTS ( (SELECT /*+ INDEX ("UP" 
              "I_USERPRIV_JURPERS_UNITCODE") */ NULL FROM PARUS."USERPRIV" "UP" WHERE "UP"."UNITCODE"='EconomicOperations' AND 
              "UP"."JUR_PERS"=:B3 AND "UP"."AUTHID"=USER@!) UNION ALL  (SELECT /*+ INDEX ("UP" "I_USERPRIV_JURPERS_UNITCODE") INDEX 
              ("UR" "I_USERROLES_AUTHID_FK") */ NULL FROM PARUS."USERPRIV" "UP",PARUS."USERROLES" "UR" WHERE "UR"."AUTHID"=USER@! AND 
              "UP"."UNITCODE"='EconomicOperations' AND "UP"."JUR_PERS"=:B4 AND "UP"."ROLEID"="UR"."ROLEID" AND "UP"."ROLEID" IS NOT 
              NULL)))
 132 - filter(:B1<=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND :B2>=TO_DATE(' 2012-08-01 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
 139 - filter("D"."COMPANY"=474316)
 140 - access("D"."RN"=NVL(:B1,:B2))
 142 - access("D"."ACCOUNT_CREDIT"="A2"."RN"(+))
 144 - access("D"."ACCOUNT_DEBIT"="A1"."RN"(+))
 145 - access("D"."NOMENCLATURE"="N"."RN"(+))
 146 - filter("ACC_NUMBER" LIKE '19%')
 147 - access("D"."ACCOUNT_CREDIT"="RN")
 148 - access("A2"."ACCTYPES"="AT2"."RN"(+))
 149 - access("A1"."ACCTYPES"="AT1"."RN"(+))
 151 - filter("UP"."AUTHID"=USER@!)
 152 - access("UP"."CATALOG"=:B1)
 153 - access("UP"."ROLEID"="UR"."ROLEID")
 155 - access("UR"."AUTHID"=USER@!)
 156 - filter("UP"."ROLEID" IS NOT NULL)
 157 - access("UP"."CATALOG"=:B1)
 159 - filter("UP"."AUTHID"=USER@!)
 160 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 161 - access("UP"."ROLEID"="UR"."ROLEID")
 163 - access("UR"."AUTHID"=USER@!)
 164 - filter("UP"."ROLEID" IS NOT NULL)
 165 - access("UP"."JUR_PERS"=:B1 AND "UP"."UNITCODE"='EconomicOperations')
 
Note
-----
   - 'PLAN_TABLE' is old version
17 окт 12, 09:16    [13331002]     Ответить | Цитировать Сообщить модератору
 Re: Поменялся план запроса после перехода на 11g  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Если построить отчет через dbms_sqltune.report_sql_monitor или выполнить запрос с statistics_level=all и получить план со статистиками выполнения через dbms_xplan_display_cursor, то будет видно, где оптимизатор ошибся с cardinality. То же самое с /*+ gather_plan_statistics */. Это позволит не гадать. Навскидку, можно проверить, не ошибся ли оптимизатор с кардинальностью 1 вот в этой части

|  63 |                                       VIEW                   | VW_NSO_3                    |     1 |    13 |  4330   (1)|
|  64 |                                        HASH UNIQUE           |                             |     1 |    52 |  4330   (1)|
|* 65 |                                         HASH JOIN            |                             |     1 |    52 |  4329   (1)|
|* 66 |                                          HASH JOIN           |                             |     1 |    49 |  3384   (1)|
|  67 |                                           VIEW               | VW_SQ_2                     |     3 |     6 |    43   (3)|
|  68 |                                            UNION-ALL         |                             |       |       |            |
|* 69 | INDEX ROWID                                 TABLE ACCESS BY  | USERPRIV                    |     1 |    16 |    20   (0)|
|* 70 |                                              INDEX SKIP SCAN | I_USERPRIV_JURPERS_UNITCODE |    53 |       |    17   (0)|
|* 71 |                                             HASH JOIN        |                             |     2 |    56 |    23   (5)|
|  72 |  INDEX ROWID                                 TABLE ACCESS BY | USERROLES                   |     5 |    55 |     2   (0)|
|* 73 | AN                                            INDEX RANGE SC | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|* 74 |  INDEX ROWID                                 TABLE ACCESS BY | USERPRIV                    |    32 |   544 |    20   (0)|
|* 75 | N                                             INDEX SKIP SCA | I_USERPRIV_JURPERS_UNITCODE |    53 |       |    17   (0)|
|  76 |                                           NESTED LOOPS       |                             |       |       |            |
|  77 |                                            NESTED LOOPS      |                             | 70472 |  3234K|  3340   (1)|
|  78 | INDEX ROWID                                 TABLE ACCESS BY  | DICACCS                     |     3 |    36 |     2   (0)|
|* 79 | N                                            INDEX RANGE SCA | C_DICACCS_ACCNUMB_UK        |     3 |       |     1   (0)|
|* 80 |                                             INDEX RANGE SCAN | I_OPRSPECS_TURNS_CR_ACC     | 23482 |       |   102   (0)|
|  81 | NDEX ROWID                                 TABLE ACCESS BY I | OPRSPECS                    | 23465 |   802K|  2053   (1)|
|  82 |                                          VIEW                | VW_SQ_1                     | 27091 | 81273 |   945   (1)|
|  83 |                                           UNION-ALL          |                             |       |       |            |
|  84 | NDEX ROWID                                 TABLE ACCESS BY I | USERPRIV                    |  6454 | 38724 |   230   (0)|
|* 85 |                                             INDEX RANGE SCAN | I_USERPRIV_AUTHID_UNIT      |  2600 |       |    11   (0)|
|* 86 |                                            HASH JOIN         |                             | 20637 |   362K|   715   (1)|
|  87 | INDEX ROWID                                 TABLE ACCESS BY  | USERROLES                   |     5 |    55 |     2   (0)|
|* 88 | N                                            INDEX RANGE SCA | I_USERROLES_AUTHID_FK       |     5 |       |     1   (0)|
|* 89 | SCAN                                        INDEX FAST FULL  | C_USERPRIV_UK               |   277K|  1896K|   711   (1)|
что привело к MERGE JOIN CARTESIAN
18 окт 12, 04:14    [13337816]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить