1 2 3 Previous Next 30 Replies Latest reply: Apr 24, 2013 5:26 AM by David_Pasternak RSS

    Select statement on a View takes much time

    David_Pasternak
      Hi @ everybody,

      a colleague came to me and told me, that a select statement needs for about 1,5 hour to execute at our new database server. Since the old server was running he just needen for about 5 minutes for the statement.

      First of all, here the facts:

      Two database machines:
      - old productive server: Oracle 10g (10.2.0.1.0), dual core cpu, 3,5 GB RAM, now non-productive because of change to new server
      - new productive server: Oracle 11g (11.2.0.3.0), 8 core processor, 24 GB RAM

      Here is the Select statement:
      Select TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "SysDate" , 'PR_CB_PRODUCT_ITEMS_ORIG' "View " , Count(*) "Count" , TO_CHAR(MIN(UPDATED_AT),'YYYY-MM-DD HH24:MI:SS') "Min Update Date" , TO_CHAR(MAX(UPDATED_AT),'YYYY-MM-DD HH24:MI:SS') "Max Update Date" from PR_CB_PRODUCT_ITEMS_ORIG;
      And here is the View:

      At the old Server
        CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_CB_PRODUCT_ITEMS_ORIG" ("CLIENT_ID", "ILN_USER", "ILN_LINK", "EAN", "CODE", "ARTICLE_TEXT", "LANG_CODE", "LANR", "OLD_LANR", "ITEM_BY_NO", "UOM_UNIT", "PRICE_UOM", "PRICE", "CURRENCY", "PRICE_QTY", "SALES_UNIT", "UPDATED_AT") AS 
        (SELECT
            PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_LINK,
            PR_PRODUCT_ITEMS_ORIG.EAN,
            PR_PRODUCT_ITEMS_ORIG.CODE,
            PR_PRODUCT_ITEMS_ORIG.ARTICLE_TEXT,
            PR_PRODUCT_ITEMS_ORIG.LANG_CODE,
            PR_PRODUCT_ITEMS_ORIG.LANR,
            PR_PRODUCT_ITEMS_ORIG.OLD_LANR,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ITEM_BY_NO,
            PR_PRODUCT_ITEMS_ORIG.UOM_UNIT,
            PR_PRODUCT_ITEMS_ORIG.PRICE_UOM,
            PR_PRODUCT_ITEMS_ORIG.PRICE,
            PR_PRODUCT_ITEMS_ORIG.CURRENCY,
            PR_PRODUCT_ITEMS_ORIG.PRICE_QTY,
            PR_PRODUCT_ITEMS_ORIG.SALES_UNIT,
            PR_PRODUCT_ITEMS_ORIG.UPDATED_AT
                      FROM PR_CONSTRUCTOR_ARTICLE_ORIG, PR_PRODUCT_ITEMS_ORIG, PR_CUSTOMER_MASTER_DATA
                      WHERE PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER=PR_PRODUCT_ITEMS_ORIG.ILN_USER
            AND PR_CUSTOMER_MASTER_DATA.ILN_USER=PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER
            AND PR_CUSTOMER_MASTER_DATA.LANG_CODE=PR_PRODUCT_ITEMS_ORIG.LANG_CODE
            AND PR_CUSTOMER_MASTER_DATA.CLIENT_ID=PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID
            AND PR_CONSTRUCTOR_ARTICLE_ORIG.LANR=PR_PRODUCT_ITEMS_ORIG.LANR
            AND PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID=PR_PRODUCT_ITEMS_ORIG.CLIENT_ID
      )
      At the new Server:
        CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_CB_PRODUCT_ITEMS_ORIG" ("CLIENT_ID", "ILN_USER", "ILN_LINK", "EAN", "CODE", "ARTICLE_TEXT", "LANG_CODE", "LANR", "OLD_LANR", "ITEM_BY_NO", "UOM_UNIT", "PRICE_UOM", "PRICE", "CURRENCY", "PRICE_QTY", "SALES_UNIT", "UPDATED_AT") AS 
        (SELECT
            PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_LINK,
            PR_PRODUCT_ITEMS_ORIG.EAN,
            PR_PRODUCT_ITEMS_ORIG.CODE,
            PR_PRODUCT_ITEMS_ORIG.ARTICLE_TEXT,
            PR_PRODUCT_ITEMS_ORIG.LANG_CODE,
            PR_PRODUCT_ITEMS_ORIG.LANR,
            PR_PRODUCT_ITEMS_ORIG.OLD_LANR,
            PR_CONSTRUCTOR_ARTICLE_ORIG.ITEM_BY_NO,
            PR_PRODUCT_ITEMS_ORIG.UOM_UNIT,
            PR_PRODUCT_ITEMS_ORIG.PRICE_UOM,
            PR_PRODUCT_ITEMS_ORIG.PRICE,
            PR_PRODUCT_ITEMS_ORIG.CURRENCY,
            PR_PRODUCT_ITEMS_ORIG.PRICE_QTY,
            PR_PRODUCT_ITEMS_ORIG.SALES_UNIT,
            PR_PRODUCT_ITEMS_ORIG.UPDATED_AT
                      FROM PR_CONSTRUCTOR_ARTICLE_ORIG, PR_PRODUCT_ITEMS_ORIG, PR_CUSTOMER_MASTER_DATA
                      WHERE PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER=PR_PRODUCT_ITEMS_ORIG.ILN_USER
            AND PR_CUSTOMER_MASTER_DATA.ILN_USER=PR_CONSTRUCTOR_ARTICLE_ORIG.ILN_USER
            AND PR_CUSTOMER_MASTER_DATA.LANG_CODE=PR_PRODUCT_ITEMS_ORIG.LANG_CODE
            AND PR_CUSTOMER_MASTER_DATA.CLIENT_ID=PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID
            AND PR_CONSTRUCTOR_ARTICLE_ORIG.LANR=PR_PRODUCT_ITEMS_ORIG.LANR
            AND PR_CONSTRUCTOR_ARTICLE_ORIG.CLIENT_ID=PR_PRODUCT_ITEMS_ORIG.CLIENT_ID
      )
      Before 3 weeks i've exported the schema, where the view belogns to, with expdp from the old server and imported it to the new server with impdp. So i can't see any reason, why the select statement takes so long time.

      Could you help me please?

      Thanks a lot and best regards,
      David
        • 1. Re: Select statement on a View takes much time
          asahide
          Hi,

          Could you show us both of execution plan?
          And If you can set same init parameter and same statistics on both system.



          Regards,
          • 2. Re: Select statement on a View takes much time
            Paul  Horth
            Please read {message:id=9360003} and provide the requested information.
            • 3. Re: Select statement on a View takes much time
              Manik
              Want to compare the plans.. were indexes included in export and import?

              Cheers,
              Manik.
              • 4. Re: Select statement on a View takes much time
                David_Pasternak
                Thanks for the quick answers.

                The indexes were included by import and export. Also on the old server i can't find indexes for that view.

                @asahide: What do you mean with execution plan? The select statement would be executed manually.

                EDIT: Just forgot: The parameters are the same at both server or higher at the new server. How can i control and configure that with the statistics?

                Edited by: David_Pasternak on 22.04.2013 02:50
                • 5. Re: Select statement on a View takes much time
                  Manik
                  @asahide: What do you mean with execution plan?
                  search for explain plan and post the results of both your SQL queries used in the views.

                  When your query takes too long ...

                  Cheers,
                  Manik.
                  • 6. Re: Select statement on a View takes much time
                    asahide
                    Hi,
                    execution plan
                    <<http://www.orafaq.com/node/1420>>
                    How can i control and configure that with the statistics?
                    Show here.
                    <<http://www.dba-oracle.com/t_export_import_cbo_optimizer_statistics_dbms_stats.htm>>


                    Regards,
                    • 7. Re: Select statement on a View takes much time
                      David_Pasternak
                      I've tried to create the explain plans for both servers:

                      But i can't find my view inside there.

                      BTW: Can i attach something to a reply? The output is too long for inserting as text ;)
                      • 8. Re: Select statement on a View takes much time
                        BluShadow
                        David_Pasternak wrote:
                        I've tried to create the explain plans for both servers:

                        But i can't find my view inside there.

                        BTW: Can i attach something to a reply? The output is too long for inserting as text ;)
                        No, the forum does not accept attachments. You would have to split it across posts. If the output is really that large, you may want to see if you can break the problem down into smaller issues and deal with it in those units so there's not so much to look at.
                        • 9. Re: Select statement on a View takes much time
                          David_Pasternak
                          I've just executed the explain plan in that style:
                          explain plan set statement_id='VIEW_TESTING' for Select TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "SysDate" , 'PR_CB_PRODUCT_ITEMS_ORIG' "View " , Count(*) "Count" , TO_CHAR(MIN(UPDATED_AT),'YYYY-MM-DD HH24:MI:SS') "Min Update Date" , TO_CHAR(MAX(UPDATED_AT),'YYYY-MM-DD HH24:MI:SS') "Max Update Date" from DUPONT_CCG2_P.PR_CB_PRODUCT_ITEMS_ORIG;
                          The plan in next Re.

                          Edited by: David_Pasternak on 23.04.2013 00:08
                          • 10. Re: Select statement on a View takes much time
                            David_Pasternak
                            At old server, Part I:
                            SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display('PLAN_TABLE','VIEW_TEST','TYPICAL'));
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            Plan hash value: 1130695283                                                                                                                           
                                                                                                                                                                                  
                            -----------------------------------------------------------------------------------------------------------------------------                         
                            | Id  | Operation                             | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                         
                            -----------------------------------------------------------------------------------------------------------------------------                         
                            |   0 | SELECT STATEMENT                      |                             |     1 |   176 |       |   103K  (3)| 00:20:45 |                         
                            |   1 |  SORT AGGREGATE                       |                             |     1 |   176 |       |            |          |                         
                            |*  2 |   TABLE ACCESS BY INDEX ROWID         | PR_CUSTOMER_MASTER_DATA     |     1 |    17 |       |     1   (0)| 00:00:01 |                         
                            |   3 |    NESTED LOOPS                       |                             |     1 |   176 |       |   103K  (3)| 00:20:45 |                         
                            |*  4 |     HASH JOIN                         |                             |     1 |   159 |       |   103K  (3)| 00:20:45 |                         
                            |   5 |      VIEW                             | PR_CONSTRUCTOR_ARTICLE_ORIG |     3 |   216 |       | 51386   (3)| 00:10:17 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |   6 |       HASH UNIQUE                     |                             |     3 |   801 |       | 51386   (3)| 00:10:17 |                         
                            |*  7 |        HASH JOIN                      |                             |     3 |   801 |       | 51385   (3)| 00:10:17 |                         
                            |*  8 |         HASH JOIN                     |                             |    18 |  3600 |       | 26092   (2)| 00:05:14 |                         
                            |   9 |          NESTED LOOPS                 |                             |    61 |  8723 |       |  1020   (1)| 00:00:13 |                         
                            |  10 |           NESTED LOOPS                |                             |    61 |  7991 |       |  1019   (1)| 00:00:13 |                         
                            |* 11 |            HASH JOIN                  |                             |  1025 | 82000 |       |    95   (5)| 00:00:02 |                         
                            |* 12 |             HASH JOIN                 |                             |  1713 | 87363 |       |    52   (4)| 00:00:01 |                         
                            |* 13 |              TABLE ACCESS FULL        | PR_ADDRESS                  |  2780 | 80620 |       |    42   (3)| 00:00:01 |                         
                            |  14 |              TABLE ACCESS FULL        | PR_CUSTOMER_MASTER_DATA     |  3450 | 75900 |       |     9   (0)| 00:00:01 |                         
                            |* 15 |             TABLE ACCESS FULL         | PR_ADDRESS                  |  4151 |   117K|       |    42   (3)| 00:00:01 |                         
                            |* 16 |            TABLE ACCESS BY INDEX ROWID| PR_COMPANY_ITEM_MATCH       |     1 |    51 |       |     1   (0)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |* 17 |             INDEX RANGE SCAN          | UK_PR_COMPANY_ITEM_MATCH    |    22 |       |       |     1   (0)| 00:00:01 |                         
                            |* 18 |           INDEX UNIQUE SCAN           | PK_PR_ARTICLE               |     1 |    12 |       |     1   (0)| 00:00:01 |                         
                            |  19 |          VIEW                         | PR_ALL_USED_ARTICLE_TEXT    |  1052K|    57M|       | 25047   (2)| 00:05:01 |                         
                            |  20 |           SORT UNIQUE                 |                             |  1052K|    93M|   220M| 25047  (25)| 00:05:01 |                         
                            |  21 |            UNION-ALL                  |                             |       |       |       |            |          |                         
                            |* 22 |             HASH JOIN                 |                             |   776K|    74M|       |  1074   (5)| 00:00:13 |                         
                            |* 23 |              HASH JOIN                |                             |   784 | 34496 |       |    21  (10)| 00:00:01 |                         
                            |* 24 |               HASH JOIN               |                             |   741 | 18525 |       |    11  (10)| 00:00:01 |                         
                            |  25 |                TABLE ACCESS FULL      | ST_USER                     |   741 |  5187 |       |     5   (0)| 00:00:01 |                         
                            |  26 |                TABLE ACCESS FULL      | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                         
                            |  27 |               TABLE ACCESS FULL       | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |  28 |              TABLE ACCESS FULL        | PR_ARTICLE_TEXT             |   404K|    21M|       |  1035   (3)| 00:00:13 |                         
                            |* 29 |             HASH JOIN                 |                             |   275K|    19M|       |  1054   (4)| 00:00:13 |                         
                            |  30 |              VIEW                     | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                         
                            |  31 |               HASH GROUP BY           |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                         
                            |* 32 |                TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                         
                            |* 33 |              TABLE ACCESS FULL        | PR_ARTICLE_TEXT             | 43130 |  2400K|       |  1037   (3)| 00:00:13 |                         
                            |* 34 |         VIEW                          | PR_COUNT_USED_ARTICLE_TEXT  |  1052K|    67M|       | 25268   (3)| 00:05:04 |                         
                            |  35 |          HASH GROUP BY                |                             |  1052K|    54M|       | 25268   (3)| 00:05:04 |                         
                            |  36 |           VIEW                        | PR_ALL_USED_ARTICLE_TEXT    |  1052K|    54M|       | 25047   (2)| 00:05:01 |                         
                            |  37 |            SORT UNIQUE                |                             |  1052K|    93M|   220M| 25047  (25)| 00:05:01 |                         
                            |  38 |             UNION-ALL                 |                             |       |       |       |            |          |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |* 39 |              HASH JOIN                |                             |   776K|    74M|       |  1074   (5)| 00:00:13 |                         
                            |* 40 |               HASH JOIN               |                             |   784 | 34496 |       |    21  (10)| 00:00:01 |                         
                            |* 41 |                HASH JOIN              |                             |   741 | 18525 |       |    11  (10)| 00:00:01 |                         
                            |  42 |                 TABLE ACCESS FULL     | ST_USER                     |   741 |  5187 |       |     5   (0)| 00:00:01 |                         
                            |  43 |                 TABLE ACCESS FULL     | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                         
                            |  44 |                TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                         
                            |  45 |               TABLE ACCESS FULL       | PR_ARTICLE_TEXT             |   404K|    21M|       |  1035   (3)| 00:00:13 |                         
                            |* 46 |              HASH JOIN                |                             |   275K|    19M|       |  1054   (4)| 00:00:13 |                         
                            |  47 |               VIEW                    | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                         
                            |  48 |                HASH GROUP BY          |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                         
                            |* 49 |                 TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |* 50 |               TABLE ACCESS FULL       | PR_ARTICLE_TEXT             | 43130 |  2400K|       |  1037   (3)| 00:00:13 |                         
                            |  51 |      VIEW                             | PR_PRODUCT_ITEMS_ORIG       |     7 |   609 |       | 52292   (3)| 00:10:28 |                         
                            |  52 |       HASH UNIQUE                     |                             |     7 |  2919 |       | 52292   (3)| 00:10:28 |                         
                            |* 53 |        HASH JOIN                      |                             |     7 |  2919 |       | 52291   (3)| 00:10:28 |                         
                            |* 54 |         HASH JOIN                     |                             |    37 | 12950 |       | 26998   (2)| 00:05:24 |                         
                            |  55 |          NESTED LOOPS                 |                             |   123 | 21033 |       |  1926   (1)| 00:00:24 |                         
                            |  56 |           NESTED LOOPS                |                             |   123 | 15129 |       |  1914   (1)| 00:00:23 |                         
                            |* 57 |            HASH JOIN                  |                             |  2064 |   102K|       |    52   (4)| 00:00:01 |                         
                            |  58 |             TABLE ACCESS FULL         | PR_CUSTOMER_MASTER_DATA     |  3450 | 75900 |       |     9   (0)| 00:00:01 |                         
                            |* 59 |             TABLE ACCESS FULL         | PR_ADDRESS                  |  4151 |   117K|       |    42   (3)| 00:00:01 |                         
                            |* 60 |            TABLE ACCESS BY INDEX ROWID| PR_COMPANY_ITEM_MATCH       |     1 |    72 |       |     1   (0)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |* 61 |             INDEX RANGE SCAN          | UK_PR_COMPANY_ITEM_MATCH    |    22 |       |       |     1   (0)| 00:00:01 |                         
                            |  62 |           TABLE ACCESS BY INDEX ROWID | PR_ARTICLE                  |     1 |    48 |       |     1   (0)| 00:00:01 |                         
                            |* 63 |            INDEX UNIQUE SCAN          | PK_PR_ARTICLE               |     1 |       |       |     1   (0)| 00:00:01 |                         
                            |  64 |          VIEW                         | PR_ALL_USED_ARTICLE_TEXT    |  1052K|   179M|       | 25047   (2)| 00:05:01 |                         
                            |  65 |           SORT UNIQUE                 |                             |  1052K|    93M|   220M| 25047  (25)| 00:05:01 |                         
                            |  66 |            UNION-ALL                  |                             |       |       |       |            |          |                         
                            |* 67 |             HASH JOIN                 |                             |   776K|    74M|       |  1074   (5)| 00:00:13 |                         
                            |* 68 |              HASH JOIN                |                             |   784 | 34496 |       |    21  (10)| 00:00:01 |                         
                            |* 69 |               HASH JOIN               |                             |   741 | 18525 |       |    11  (10)| 00:00:01 |                         
                            |  70 |                TABLE ACCESS FULL      | ST_USER                     |   741 |  5187 |       |     5   (0)| 00:00:01 |                         
                            |  71 |                TABLE ACCESS FULL      | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |  72 |               TABLE ACCESS FULL       | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                         
                            |  73 |              TABLE ACCESS FULL        | PR_ARTICLE_TEXT             |   404K|    21M|       |  1035   (3)| 00:00:13 |                         
                            |* 74 |             HASH JOIN                 |                             |   275K|    19M|       |  1054   (4)| 00:00:13 |                         
                            |  75 |              VIEW                     | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                         
                            |  76 |               HASH GROUP BY           |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                         
                            |* 77 |                TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                         
                            |* 78 |              TABLE ACCESS FULL        | PR_ARTICLE_TEXT             | 43130 |  2400K|       |  1037   (3)| 00:00:13 |                         
                            |* 79 |         VIEW                          | PR_COUNT_USED_ARTICLE_TEXT  |  1052K|    67M|       | 25268   (3)| 00:05:04 |                         
                            |  80 |          HASH GROUP BY                |                             |  1052K|    54M|       | 25268   (3)| 00:05:04 |                         
                            |  81 |           VIEW                        | PR_ALL_USED_ARTICLE_TEXT    |  1052K|    54M|       | 25047   (2)| 00:05:01 |                         
                            |  82 |            SORT UNIQUE                |                             |  1052K|    93M|   220M| 25047  (25)| 00:05:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |  83 |             UNION-ALL                 |                             |       |       |       |            |          |                         
                            |* 84 |              HASH JOIN                |                             |   776K|    74M|       |  1074   (5)| 00:00:13 |                         
                            |* 85 |               HASH JOIN               |                             |   784 | 34496 |       |    21  (10)| 00:00:01 |                         
                            |* 86 |                HASH JOIN              |                             |   741 | 18525 |       |    11  (10)| 00:00:01 |                         
                            |  87 |                 TABLE ACCESS FULL     | ST_USER                     |   741 |  5187 |       |     5   (0)| 00:00:01 |                         
                            |  88 |                 TABLE ACCESS FULL     | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                         
                            |  89 |                TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                         
                            |  90 |               TABLE ACCESS FULL       | PR_ARTICLE_TEXT             |   404K|    21M|       |  1035   (3)| 00:00:13 |                         
                            |* 91 |              HASH JOIN                |                             |   275K|    19M|       |  1054   (4)| 00:00:13 |                         
                            |  92 |               VIEW                    | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                         
                            |  93 |                HASH GROUP BY          |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                         
                            
                            PLAN_TABLE_OUTPUT                                                                                                                                     
                            ------------------------------------------------------------------------------------------------------------------------------------------------------
                            |* 94 |                 TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                         
                            |* 95 |               TABLE ACCESS FULL       | PR_ARTICLE_TEXT             | 43130 |  2400K|       |  1037   (3)| 00:00:13 |                         
                            |* 96 |     INDEX RANGE SCAN                  | UK_PR_CUSTOMER_MASTER_DATA  |     1 |       |       |     1   (0)| 00:00:01 |                         
                            ----------------------------------------------------------------------------------------------------------------------------- 
                            • 11. Re: Select statement on a View takes much time
                              David_Pasternak
                              At old server, Part II:
                              Predicate Information (identified by operation id):                                                                                                   
                              ---------------------------------------------------                                                                                                   
                                                                                                                                                                                    
                                 2 - filter("PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_PRODUCT_ITEMS_ORIG"."LANG_CODE")                                                              
                                 4 - access("PR_CONSTRUCTOR_ARTICLE_ORIG"."ILN_USER"="PR_PRODUCT_ITEMS_ORIG"."ILN_USER" AND                                                         
                                            "PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR"="PR_PRODUCT_ITEMS_ORIG"."LANR" AND                                                                 
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                            "PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID"="PR_PRODUCT_ITEMS_ORIG"."CLIENT_ID")                                                          
                                 7 - access("PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_COUNT_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                 
                                            "PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG"="PR_COUNT_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                     
                                            "PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE"="PR_COUNT_USED_ARTICLE_TEXT"."LANG_CODE")                                                        
                                     filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_ALL_USED_ARTICLE_TEXT"."PRIO"=1 AND                                                   
                                            "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                                                                             
                                 8 - access("PR_COMPANY_ITEM_MATCH"."SALES_ORG"="PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                          
                                            "PR_ARTICLE"."SUP_ITEM_NO"="PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                                 
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE")                                                           
                                11 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                                            "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                12 - access("PR_ADDRESS"."CLIENT_ID"="PR_CUSTOMER_MASTER_DATA"."CLIENT_ID" AND                                                                      
                                            "PR_ADDRESS"."ILN_LINK"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                           
                                13 - filter("PR_ADDRESS"."ORDER_TYPE"='CB' AND "PR_ADDRESS"."ILN_USER"<>"PR_ADDRESS"."ILN_LINK")                                                    
                                15 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
                                16 - filter((TO_DATE("PR_COMPANY_ITEM_MATCH"."END_DATE",'YYYYMMDD')-SYSDATE@!>0 OR                                                                  
                                            "PR_COMPANY_ITEM_MATCH"."END_DATE" IS NULL) AND (SYSDATE@!-TO_DATE("PR_COMPANY_ITEM_MATCH"."START_DATE",'YYYYMMDD')>0                   
                                             OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS NULL))                                                                                      
                                17 - access("PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_COMPANY_ITEM_MATCH"."CLIENT_ID" AND                                                           
                                            "PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_COMPANY_ITEM_MATCH"."SALES_ORG" AND                                                           
                                            "PR_COMPANY_ITEM_MATCH"."ILN_USER"="PR_ADDRESS"."ILN_LINK")                                                                             
                                18 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                22 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                23 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                24 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                29 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                32 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                33 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                34 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
                                39 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                40 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                41 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                46 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                49 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                50 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                53 - access("PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_COUNT_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                 
                                            "PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG"="PR_COUNT_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                     
                                            "PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE"="PR_COUNT_USED_ARTICLE_TEXT"."LANG_CODE")                                                        
                                     filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_ALL_USED_ARTICLE_TEXT"."PRIO"=1 AND                                                   
                                            "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                                                                             
                                54 - access("PR_COMPANY_ITEM_MATCH"."SALES_ORG"="PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                          
                                            "PR_ARTICLE"."SUP_ITEM_NO"="PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                                 
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE")                                                           
                                57 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                                            "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
                                59 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
                                60 - filter((TO_DATE("PR_COMPANY_ITEM_MATCH"."END_DATE",'YYYYMMDD')-SYSDATE@!>0 OR                                                                  
                                            "PR_COMPANY_ITEM_MATCH"."END_DATE" IS NULL) AND (SYSDATE@!-TO_DATE("PR_COMPANY_ITEM_MATCH"."START_DATE",'YYYYMMDD')>0                   
                                             OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS NULL))                                                                                      
                                61 - access("PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_COMPANY_ITEM_MATCH"."CLIENT_ID" AND                                                           
                                            "PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_COMPANY_ITEM_MATCH"."SALES_ORG" AND                                                           
                                            "PR_COMPANY_ITEM_MATCH"."ILN_USER"="PR_ADDRESS"."ILN_LINK")                                                                             
                                63 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                67 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                68 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                69 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                74 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                77 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                78 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                79 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
                                84 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                            "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                85 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                              
                              PLAN_TABLE_OUTPUT                                                                                                                                     
                              ------------------------------------------------------------------------------------------------------------------------------------------------------
                                86 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                91 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                94 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                95 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                96 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_CONSTRUCTOR_ARTICLE_ORIG"."ILN_USER" AND                                                       
                                            "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID")                                                        
                              
                              182 rows selected.
                              • 12. Re: Select statement on a View takes much time
                                David_Pasternak
                                Now at new server, Part I:
                                SQL> select PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','VIEW_TEST','TYPICAL'));
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                Plan hash value: 1578476791                                                                                                                           
                                                                                                                                                                                      
                                --------------------------------------------------------------------------------------------------------------------------------------                
                                | Id  | Operation                                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                
                                --------------------------------------------------------------------------------------------------------------------------------------                
                                |   0 | SELECT STATEMENT                               |                             |     1 |   106 |       | 67963   (2)| 00:13:36 |                
                                |   1 |  SORT AGGREGATE                                |                             |     1 |   106 |       |            |          |                
                                |   2 |   NESTED LOOPS                                 |                             |       |       |       |            |          |                
                                |   3 |    NESTED LOOPS                                |                             |     1 |   106 |       | 67963   (2)| 00:13:36 |                
                                |   4 |     NESTED LOOPS                               |                             |     1 |    89 |       | 67961   (2)| 00:13:36 |                
                                |   5 |      VIEW                                      | PR_CONSTRUCTOR_ARTICLE_ORIG |    37 |  2516 |       | 63342   (1)| 00:12:41 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |   6 |       HASH UNIQUE                              |                             |    37 |  9879 |       | 63342   (1)| 00:12:41 |                
                                |*  7 |        HASH JOIN                               |                             |    37 |  9879 |       | 63341   (1)| 00:12:41 |                
                                |*  8 |         HASH JOIN                              |                             |    25 |  5950 |       | 63305   (1)| 00:12:40 |                
                                |*  9 |          HASH JOIN                             |                             |   111 | 18981 |       | 33000   (1)| 00:06:36 |                
                                |  10 |           NESTED LOOPS                         |                             |   331 | 37734 |       |  3399   (2)| 00:00:41 |                
                                |* 11 |            HASH JOIN                           |                             |   331 | 33762 |       |  3399   (2)| 00:00:41 |                
                                |* 12 |             TABLE ACCESS FULL                  | PR_COMPANY_ITEM_MATCH       |  2423 |   120K|       |  3354   (2)| 00:00:41 |                
                                |* 13 |             HASH JOIN                          |                             |  3469 |   172K|       |    45   (3)| 00:00:01 |                
                                |  14 |              TABLE ACCESS FULL                 | PR_CUSTOMER_MASTER_DATA     |  3469 | 76318 |       |     9   (0)| 00:00:01 |                
                                |* 15 |              TABLE ACCESS FULL                 | PR_ADDRESS                  |  4171 |   118K|       |    35   (0)| 00:00:01 |                
                                |* 16 |            INDEX UNIQUE SCAN                   | PK_PR_ARTICLE               |     1 |    12 |       |     0   (0)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  17 |           VIEW                                 | PR_ALL_USED_ARTICLE_TEXT    |  1276K|    69M|       | 29596   (1)| 00:05:56 |                
                                |  18 |            SORT UNIQUE                         |                             |  1276K|   112M|   132M| 29596  (18)| 00:05:56 |                
                                |  19 |             UNION-ALL                          |                             |       |       |       |            |          |                
                                |* 20 |              HASH JOIN                         |                             |  1004K|    96M|       |  1206   (1)| 00:00:15 |                
                                |* 21 |               HASH JOIN                        |                             |   798 | 35112 |       |    20   (5)| 00:00:01 |                
                                |  22 |                TABLE ACCESS FULL               | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                
                                |* 23 |                HASH JOIN                       |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                
                                |  24 |                 TABLE ACCESS FULL              | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                
                                |  25 |                 TABLE ACCESS FULL              | PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                
                                |  26 |               TABLE ACCESS FULL                | PR_ARTICLE_TEXT             |   403K|    21M|       |  1182   (1)| 00:00:15 |                
                                |* 27 |              HASH JOIN                         |                             |   271K|    15M|       |  1194   (1)| 00:00:15 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  28 |               VIEW                             | PR_USED_LANG_SALES_ORG      |   129 |   516 |       |    10  (10)| 00:00:01 |                
                                |  29 |                HASH GROUP BY                   |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                
                                |* 30 |                 TABLE ACCESS FULL              | PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                
                                |* 31 |               TABLE ACCESS FULL                | PR_ARTICLE_TEXT             | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                
                                |* 32 |          VIEW                                  | PR_COUNT_USED_ARTICLE_TEXT  |  1276K|    81M|       | 30301   (1)| 00:06:04 |                
                                |  33 |           HASH GROUP BY                        |                             |  1276K|    65M|       | 30301   (1)| 00:06:04 |                
                                |  34 |            VIEW                                | PR_ALL_USED_ARTICLE_TEXT    |  1276K|    65M|       | 30263   (1)| 00:06:04 |                
                                |  35 |             SORT UNIQUE                        |                             |  1276K|   115M|   135M| 30263  (20)| 00:06:04 |                
                                |  36 |              UNION-ALL                         |                             |       |       |       |            |          |                
                                |* 37 |               HASH JOIN                        |                             |  1004K|    96M|       |  1206   (1)| 00:00:15 |                
                                |* 38 |                HASH JOIN                       |                             |   798 | 35112 |       |    20   (5)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  39 |                 TABLE ACCESS FULL              | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                
                                |* 40 |                 HASH JOIN                      |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                
                                |  41 |                  TABLE ACCESS FULL             | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                
                                |  42 |                  TABLE ACCESS FULL             | PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                
                                |  43 |                TABLE ACCESS FULL               | PR_ARTICLE_TEXT             |   403K|    21M|       |  1182   (1)| 00:00:15 |                
                                |* 44 |               HASH JOIN                        |                             |   271K|    18M|       |  1194   (1)| 00:00:15 |                
                                |  45 |                VIEW                            | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    10  (10)| 00:00:01 |                
                                |  46 |                 HASH GROUP BY                  |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                
                                |* 47 |                  TABLE ACCESS FULL             | PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                
                                |* 48 |                TABLE ACCESS FULL               | PR_ARTICLE_TEXT             | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                
                                |* 49 |         TABLE ACCESS FULL                      | PR_ADDRESS                  |  2799 | 81171 |       |    35   (0)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  50 |      VIEW PUSHED PREDICATE                     | PR_PRODUCT_ITEMS_ORIG       |     1 |    21 |       |   125  (11)| 00:00:02 |                
                                |  51 |       SORT UNIQUE                              |                             |     1 |   418 |       |   125  (11)| 00:00:02 |                
                                |  52 |        NESTED LOOPS                            |                             |       |       |       |            |          |                
                                |  53 |         NESTED LOOPS                           |                             |     1 |   418 |       |   124  (10)| 00:00:02 |                
                                |  54 |          NESTED LOOPS                          |                             |     1 |   345 |       |   122  (10)| 00:00:02 |                
                                |  55 |           NESTED LOOPS                         |                             |     1 |   316 |       |   120  (10)| 00:00:02 |                
                                |* 56 |            HASH JOIN                           |                             |     1 |   294 |       |   118  (11)| 00:00:02 |                
                                |* 57 |             VIEW                               | PR_COUNT_USED_ARTICLE_TEXT  |    98 |  6566 |       |    58  (11)| 00:00:01 |                
                                |  58 |              SORT GROUP BY                     |                             |    98 |  5292 |       |    58  (11)| 00:00:01 |                
                                |  59 |               VIEW                             | PR_ALL_USED_ARTICLE_TEXT    |    98 |  5292 |       |    57   (9)| 00:00:01 |                
                                |  60 |                SORT UNIQUE                     |                             |    98 |  9534 |       |    57  (34)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  61 |                 UNION-ALL                      |                             |       |       |       |            |          |                
                                |* 62 |                  HASH JOIN                     |                             |    85 |  8585 |       |    40   (5)| 00:00:01 |                
                                |* 63 |                   HASH JOIN                    |                             |    75 |  7050 |       |    34   (3)| 00:00:01 |                
                                |  64 |                    TABLE ACCESS FULL           | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                
                                |* 65 |                    HASH JOIN                   |                             |   370 | 28120 |       |    29   (4)| 00:00:01 |                
                                |  66 |                     TABLE ACCESS BY INDEX ROWID| PR_ARTICLE_TEXT             |    19 |  1083 |       |    19   (0)| 00:00:01 |                
                                |* 67 |                      INDEX RANGE SCAN          | PK_PR_ARTICLE_TEXT          |    19 |       |       |     3   (0)| 00:00:01 |                
                                |  68 |                     TABLE ACCESS FULL          | PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                
                                |  69 |                   TABLE ACCESS FULL            | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                
                                |* 70 |                  HASH JOIN                     |                             |    13 |   949 |       |    16  (13)| 00:00:01 |                
                                |  71 |                   TABLE ACCESS BY INDEX ROWID  | PR_ARTICLE_TEXT             |     2 |   114 |       |     5   (0)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |* 72 |                    INDEX RANGE SCAN            | PK_PR_ARTICLE_TEXT          |     2 |       |       |     3   (0)| 00:00:01 |                
                                |  73 |                   VIEW                         | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    10  (10)| 00:00:01 |                
                                |  74 |                    SORT GROUP BY               |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                
                                |* 75 |                     TABLE ACCESS FULL          | PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                
                                |  76 |             NESTED LOOPS                       |                             |    98 | 22246 |       |    59   (9)| 00:00:01 |                
                                |  77 |              TABLE ACCESS BY INDEX ROWID       | PR_ARTICLE                  |     1 |    48 |       |     2   (0)| 00:00:01 |                
                                |* 78 |               INDEX UNIQUE SCAN                | PK_PR_ARTICLE               |     1 |       |       |     1   (0)| 00:00:01 |                
                                |  79 |              VIEW                              | PR_ALL_USED_ARTICLE_TEXT    |    98 | 17542 |       |    57   (9)| 00:00:01 |                
                                |  80 |               SORT UNIQUE                      |                             |    98 |  9378 |       |    57  (34)| 00:00:01 |                
                                |  81 |                UNION-ALL                       |                             |       |       |       |            |          |                
                                |* 82 |                 HASH JOIN                      |                             |    85 |  8585 |       |    40   (5)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |* 83 |                  HASH JOIN                     |                             |    75 |  7050 |       |    34   (3)| 00:00:01 |                
                                |  84 |                   TABLE ACCESS FULL            | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                
                                |* 85 |                   HASH JOIN                    |                             |   370 | 28120 |       |    29   (4)| 00:00:01 |                
                                |  86 |                    TABLE ACCESS BY INDEX ROWID | PR_ARTICLE_TEXT             |    19 |  1083 |       |    19   (0)| 00:00:01 |                
                                |* 87 |                     INDEX RANGE SCAN           | PK_PR_ARTICLE_TEXT          |    19 |       |       |     3   (0)| 00:00:01 |                
                                |  88 |                    TABLE ACCESS FULL           | PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                
                                |  89 |                  TABLE ACCESS FULL             | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                
                                |* 90 |                 HASH JOIN                      |                             |    13 |   793 |       |    16  (13)| 00:00:01 |                
                                |  91 |                  TABLE ACCESS BY INDEX ROWID   | PR_ARTICLE_TEXT             |     2 |   114 |       |     5   (0)| 00:00:01 |                
                                |* 92 |                   INDEX RANGE SCAN             | PK_PR_ARTICLE_TEXT          |     2 |       |       |     3   (0)| 00:00:01 |                
                                |  93 |                  VIEW                          | PR_USED_LANG_SALES_ORG      |   129 |   516 |       |    10  (10)| 00:00:01 |                
                                
                                PLAN_TABLE_OUTPUT                                                                                                                                     
                                ------------------------------------------------------------------------------------------------------------------------------------------------------
                                |  94 |                   SORT GROUP BY                |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                
                                |* 95 |                    TABLE ACCESS FULL           | PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                
                                |* 96 |            TABLE ACCESS BY INDEX ROWID         | PR_CUSTOMER_MASTER_DATA     |     1 |    22 |       |     2   (0)| 00:00:01 |                
                                |* 97 |             INDEX RANGE SCAN                   | UK_PR_CUSTOMER_MASTER_DATA  |     1 |       |       |     1   (0)| 00:00:01 |                
                                |* 98 |           TABLE ACCESS BY INDEX ROWID          | PR_ADDRESS                  |     1 |    29 |       |     2   (0)| 00:00:01 |                
                                |* 99 |            INDEX RANGE SCAN                    | UK_PR_ADDRESS               |     1 |       |       |     1   (0)| 00:00:01 |                
                                |*100 |          INDEX UNIQUE SCAN                     | UK_PR_COMPANY_ITEM_MATCH    |     1 |       |       |     1   (0)| 00:00:01 |                
                                |*101 |         TABLE ACCESS BY INDEX ROWID            | PR_COMPANY_ITEM_MATCH       |     1 |    73 |       |     2   (0)| 00:00:01 |                
                                |*102 |     INDEX RANGE SCAN                           | UK_PR_CUSTOMER_MASTER_DATA  |     1 |       |       |     1   (0)| 00:00:01 |                
                                |*103 |    TABLE ACCESS BY INDEX ROWID                 | PR_CUSTOMER_MASTER_DATA     |     1 |    17 |       |     2   (0)| 00:00:01 |                
                                --------------------------------------------------------------------------------------------------------------------------------------                
                                • 13. Re: Select statement on a View takes much time
                                  David_Pasternak
                                  At new server, Part II:
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                                                        
                                  Predicate Information (identified by operation id):                                                                                                   
                                  ---------------------------------------------------                                                                                                   
                                                                                                                                                                                        
                                     7 - access("PR_ADDRESS"."CLIENT_ID"="PR_CUSTOMER_MASTER_DATA"."CLIENT_ID" AND                                                                      
                                                "PR_ADDRESS"."ILN_LINK"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                           
                                     8 - access("PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_COUNT_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                 
                                                "PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG"="PR_COUNT_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                     
                                                "PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE"="PR_COUNT_USED_ARTICLE_TEXT"."LANG_CODE")                                                        
                                         filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_ALL_USED_ARTICLE_TEXT"."PRIO"=1 AND                                                   
                                                "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                                                                             
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                     9 - access("PR_COMPANY_ITEM_MATCH"."SALES_ORG"="PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                          
                                                "PR_ARTICLE"."SUP_ITEM_NO"="PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                                 
                                                "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE")                                                           
                                    11 - access("PR_COMPANY_ITEM_MATCH"."ILN_USER"="PR_ADDRESS"."ILN_LINK" AND                                                                          
                                                "PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_COMPANY_ITEM_MATCH"."SALES_ORG" AND                                                           
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_COMPANY_ITEM_MATCH"."CLIENT_ID")                                                              
                                    12 - filter((TO_DATE("PR_COMPANY_ITEM_MATCH"."END_DATE",'YYYYMMDD')-SYSDATE@!>0 OR "PR_COMPANY_ITEM_MATCH"."END_DATE" IS                            
                                                NULL) AND (SYSDATE@!-TO_DATE("PR_COMPANY_ITEM_MATCH"."START_DATE",'YYYYMMDD')>0 OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS              
                                                NULL))                                                                                                                                  
                                    13 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                    15 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
                                    16 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
                                    20 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                                "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                    21 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                    23 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                    27 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                    30 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                    31 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                    32 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
                                    37 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                    38 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                    40 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                    44 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                    47 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                    48 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
                                    49 - filter("PR_ADDRESS"."ORDER_TYPE"='CB' AND "PR_ADDRESS"."ILN_USER"<>"PR_ADDRESS"."ILN_LINK")                                                    
                                    56 - access("PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_COUNT_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                 
                                                "PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG"="PR_COUNT_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                     
                                                "PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE"="PR_COUNT_USED_ARTICLE_TEXT"."LANG_CODE")                                                        
                                         filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_ALL_USED_ARTICLE_TEXT"."PRIO"=1 AND                                                   
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                                                                             
                                    57 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
                                    62 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                    63 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                    65 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                                "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                    67 - access("PR_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR")                                                                   
                                    70 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                    72 - access("PR_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR" AND "PR_ARTICLE_TEXT"."LANG_CODE"='EN')                            
                                    75 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                    78 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR")                                                                        
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                    82 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
                                    83 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
                                    85 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                                                "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
                                    87 - access("PR_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR")                                                                   
                                    90 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
                                    92 - access("PR_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR" AND "PR_ARTICLE_TEXT"."LANG_CODE"='EN')                            
                                    95 - filter("LANG_CODE"<>'XX')                                                                                                                      
                                    96 - filter("PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ALL_USED_ARTICLE_TEXT"."LANG_CODE")                                                           
                                    97 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_CONSTRUCTOR_ARTICLE_ORIG"."ILN_USER" AND                                                       
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID")                                                        
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                    98 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
                                    99 - access("PR_ADDRESS"."ILN_USER"="PR_CONSTRUCTOR_ARTICLE_ORIG"."ILN_USER" AND                                                                    
                                                "PR_ADDRESS"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID")                                                                     
                                         filter("PR_ADDRESS"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID" AND                                                                  
                                                "PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
                                   100 - access("PR_COMPANY_ITEM_MATCH"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID" AND                                                       
                                                "PR_COMPANY_ITEM_MATCH"."SALES_ORG"="PR_ALL_USED_ARTICLE_TEXT"."SALES_ORG" AND                                                          
                                                "PR_COMPANY_ITEM_MATCH"."ILN_USER"="PR_ADDRESS"."ILN_LINK" AND                                                                          
                                                "PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO"="PR_CONSTRUCTOR_ARTICLE_ORIG"."LANR")                                                             
                                         filter("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_COMPANY_ITEM_MATCH"."SALES_ORG" AND                                                           
                                  
                                  PLAN_TABLE_OUTPUT                                                                                                                                     
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_COMPANY_ITEM_MATCH"."CLIENT_ID")                                                              
                                   101 - filter((TO_DATE("PR_COMPANY_ITEM_MATCH"."END_DATE",'YYYYMMDD')-SYSDATE@!>0 OR "PR_COMPANY_ITEM_MATCH"."END_DATE" IS                            
                                                NULL) AND (SYSDATE@!-TO_DATE("PR_COMPANY_ITEM_MATCH"."START_DATE",'YYYYMMDD')>0 OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS              
                                                NULL))                                                                                                                                  
                                   102 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_CONSTRUCTOR_ARTICLE_ORIG"."ILN_USER" AND                                                       
                                                "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_CONSTRUCTOR_ARTICLE_ORIG"."CLIENT_ID")                                                        
                                   103 - filter("PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_PRODUCT_ITEMS_ORIG"."LANG_CODE")                                                              
                                  
                                  194 Zeilen ausgewahlt.
                                  • 14. Re: Select statement on a View takes much time
                                    David_Pasternak
                                    I think, the most differences are at the top, here for new server:
                                    --------------------------------------------------------------------------------------------------------------------------------------                
                                    | Id  | Operation                                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                
                                    --------------------------------------------------------------------------------------------------------------------------------------                
                                    |   0 | SELECT STATEMENT                               |                             |     1 |   106 |       | 67963   (2)| 00:13:36 |                
                                    |   1 |  SORT AGGREGATE                                |                             |     1 |   106 |       |            |          |                
                                    |   2 |   NESTED LOOPS                                 |                             |       |       |       |            |          |                
                                    |   3 |    NESTED LOOPS                                |                             |     1 |   106 |       | 67963   (2)| 00:13:36 |                
                                    |   4 |     NESTED LOOPS                               |                             |     1 |    89 |       | 67961   (2)| 00:13:36 |                
                                    |   5 |      VIEW                                      | PR_CONSTRUCTOR_ARTICLE_ORIG |    37 |  2516 |       | 63342   (1)| 00:12:41 |                
                                    
                                    PLAN_TABLE_OUTPUT                                                                                                                                     
                                    ------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |   6 |       HASH UNIQUE                              |                             |    37 |  9879 |       | 63342   (1)| 00:12:41 |                
                                    |*  7 |        HASH JOIN                               |                             |    37 |  9879 |       | 63341   (1)| 00:12:41 |                
                                    |*  8 |         HASH JOIN                              |                             |    25 |  5950 |       | 63305   (1)| 00:12:40 |                
                                    |*  9 |          HASH JOIN                             |                             |   111 | 18981 |       | 33000   (1)| 00:06:36 |                
                                    |  10 |           NESTED LOOPS                         |                             |   331 | 37734 |       |  3399   (2)| 00:00:41 |                
                                    |* 11 |            HASH JOIN                           |                             |   331 | 33762 |       |  3399   (2)| 00:00:41 |                
                                    |* 12 |             TABLE ACCESS FULL                  | PR_COMPANY_ITEM_MATCH       |  2423 |   120K|       |  3354   (2)| 00:00:41 |                
                                    |* 13 |             HASH JOIN                          |                             |  3469 |   172K|       |    45   (3)| 00:00:01 |                
                                    |  14 |              TABLE ACCESS FULL                 | PR_CUSTOMER_MASTER_DATA     |  3469 | 76318 |       |     9   (0)| 00:00:01 |                
                                    |* 15 |              TABLE ACCESS FULL                 | PR_ADDRESS                  |  4171 |   118K|       |    35   (0)| 00:00:01 |                
                                    |* 16 |            INDEX UNIQUE SCAN                   | PK_PR_ARTICLE               |     1 |    12 |       |     0   (0)| 00:00:01 |                
                                    
                                    PLAN_TABLE_OUTPUT                                                                                                                                     
                                    ------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |  17 |           VIEW                                 | PR_ALL_USED_ARTICLE_TEXT    |  1276K|    69M|       | 29596   (1)| 00:05:56 |                
                                    |  18 |            SORT UNIQUE                         |                             |  1276K|   112M|   132M| 29596  (18)| 00:05:56 |
                                    But that is lesser time than at the old server. So why i the execution so long at the new server. Terrible...

                                    Edited by: David_Pasternak on 23.04.2013 00:17
                                    1 2 3 Previous Next