This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Apr 24, 2013 3:26 AM by David_Pasternak RSS

Select statement on a View takes much time

David_Pasternak Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Please read {message:id=9360003} and provide the requested information.
  • 3. Re: Select statement on a View takes much time
    Manik Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    @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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points