This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Apr 24, 2013 3:26 AM by David_Pasternak Go to original post RSS
  • 15. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Oh, just forgotten: i've exported and imported the statistics by exporting and importing the schema. I think, that should be enough, or not?
  • 16. Re: Select statement on a View takes much time
    Manik Expert
    Currently Being Moderated
    First question:

    Are the stats up to date for the new server, I see table PR_CUSTOMER_MASTER_DATA in new server is not using index.

    Cheers,
    Manik

    Edited by: Manik on Apr 23, 2013 12:50 PM
  • 17. Re: Select statement on a View takes much time
    Manik Expert
    Currently Being Moderated
    Can you gather the stats in new server again and try.. I hope it should fix your issue.

    Cheers,
    Manik.
  • 18. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    You mean like here?: [http://www.dba-oracle.com/t_export_import_cbo_optimizer_statistics_dbms_stats.htm]
  • 19. Re: Select statement on a View takes much time
    Manik Expert
    Currently Being Moderated
    This link ....
    [url http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i41448]click me

    [url http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php]Example

    Cheers,
    Manik.

    Edited by: Manik on Apr 23, 2013 1:01 PM
  • 20. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Now i've tried it in two ways, without any changes.

    First i've recreated the statistics on the new server for the schema were the view belongs to:
    EXEC DBMS_STATS.delete_schema_stats('My_SCHEMA');
    EXEC DBMS_STATS.gather_schema_stats('MY_SCHEMA');
    There was no change by the execution time of the Select statement. Then i tried to export the statistics from the old server and importet them at the new server:
    At the old server:
    EXEC DBMS_STATS.create_stat_table('MY_SCHEMA','STATS_TABLE');
    EXEC DBMS_STATS.export_schema_stats('MY_SCHEMA','STATS_TABLE',NULL,'MY_SCHEMA');
    
    exp file=my_schema_stats.dmp log=stats.log tables=stats_table rows=yes
    And at the new server:
    imp file=my_schema_stats.dmp log=stats.log tables=stats_table rows=yes
    
    EXEC DBMS_STATS.import_schema_stats('MY_SCHEMA','STATS_TABLE',NULL,'MY_SCHEMA');
    EXEC DBMS_STATS.drop_stat_table('MY_SCHEMA','STATS_TABLE');
    After both i've executed the Select command, but also it will run over 1,5 hour. So i can't see any changes here.
  • 21. Re: Select statement on a View takes much time
    651776 Newbie
    Currently Being Moderated
    The view PR_CB_PRODUCT_ITEMS_ORIG is identical in both.
    But, what about the others views? Are identical too?
    PR_CONSTRUCTOR_ARTICLE_ORIG
    PR_PRODUCT_ITEMS_ORIG

    You can compare the sql code and the explain plan of each of this views. If you find another view you can continue analizing and so on.
    Post the sql code and the explain plan if you need help.
  • 22. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Good morning. Thanks for answer. First of all, here are the views:

    PR_CONSTRUCTOR_ARTICLE_ORIG at old Server:
      CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_CONSTRUCTOR_ARTICLE_ORIG" ("CLIENT_ID", "ILN_USER", "ILN_LINK", "LANR", "ITEM_BY_NO") AS 
      (SELECT DISTINCT
          PR_ADDRESS.CLIENT_ID,
          PR_ADDRESS.ILN_USER,
          PR_ADDRESS.ILN_LINK,
          PR_PRODUCT_ITEMS_ORIG.LANR,
          PR_PRODUCT_ITEMS_ORIG.ITEM_BY_NO
                    FROM PR_ADDRESS, PR_PRODUCT_ITEMS_ORIG
                    WHERE PR_ADDRESS.ORDER_TYPE = 'CB'
          AND PR_ADDRESS.ILN_USER <> PR_ADDRESS.ILN_LINK
          AND PR_ADDRESS.CLIENT_ID=PR_PRODUCT_ITEMS_ORIG.CLIENT_ID
          AND PR_ADDRESS.ILN_LINK=PR_PRODUCT_ITEMS_ORIG.ILN_USER
    )
    PR_CONSTRUCTOR_ARTICLE_ORIG at new server:
      CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_CONSTRUCTOR_ARTICLE_ORIG" ("CLIENT_ID", "ILN_USER", "ILN_LINK", "LANR", "ITEM_BY_NO") AS 
      (SELECT DISTINCT
          PR_ADDRESS.CLIENT_ID,
          PR_ADDRESS.ILN_USER,
          PR_ADDRESS.ILN_LINK,
          PR_PRODUCT_ITEMS_ORIG.LANR,
          PR_PRODUCT_ITEMS_ORIG.ITEM_BY_NO
                    FROM PR_ADDRESS, PR_PRODUCT_ITEMS_ORIG
                    WHERE PR_ADDRESS.ORDER_TYPE = 'CB'
          AND PR_ADDRESS.ILN_USER <> PR_ADDRESS.ILN_LINK
          AND PR_ADDRESS.CLIENT_ID=PR_PRODUCT_ITEMS_ORIG.CLIENT_ID
          AND PR_ADDRESS.ILN_LINK=PR_PRODUCT_ITEMS_ORIG.ILN_USER
    )
    PR_PRODUCT_ITEMS_ORIG at old server:
      CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_PRODUCT_ITEMS_ORIG" ("CLIENT_ID", "ILN_USER", "EAN", "CODE", "ARTICLE_TEXT", "LANG_CODE", "LANR", "OLD_LANR", "ITEM_BY_NO", "UOM_UNIT", "PRICE_UOM", "PRICE", "CURRENCY", "PRICE_QTY", "SALES_UNIT", "START_DATE", "END_DATE", "UPDATED_AT") AS 
      (SELECT DISTINCT
          PR_CUSTOMER_MASTER_DATA.CLIENT_ID,
          PR_CUSTOMER_MASTER_DATA.ILN_USER,
          PR_ARTICLE.EAN,
          PR_ARTICLE.CODE,
          PR_USED_ARTICLE_TEXT_ORIG.ARTICLE_TEXT,
          PR_USED_ARTICLE_TEXT_ORIG.LANG_CODE,
          PR_ARTICLE.SUP_ITEM_NO,
          PR_ARTICLE.OLD_ITEM_NO,
          PR_COMPANY_ITEM_MATCH.ITEM_BY_NO,
          PR_COMPANY_ITEM_MATCH.UOM_UNIT,
          PR_COMPANY_ITEM_MATCH.PRICE_UOM,
          PR_COMPANY_ITEM_MATCH.PRICE,
          PR_COMPANY_ITEM_MATCH.CURRENCY,
          PR_COMPANY_ITEM_MATCH.PRICE_QTY,
          PR_COMPANY_ITEM_MATCH.SALES_UNIT,
          PR_COMPANY_ITEM_MATCH.START_DATE,
          PR_COMPANY_ITEM_MATCH.END_DATE,
          PR_ARTICLE.UPDATED_AT
            FROM PR_ARTICLE, PR_COMPANY_ITEM_MATCH, PR_USED_ARTICLE_TEXT_ORIG, PR_CUSTOMER_MASTER_DATA, PR_ADDRESS
            WHERE PR_ADDRESS.ORDER_TYPE='REP'
          AND PR_CUSTOMER_MASTER_DATA.ILN_USER=PR_ADDRESS.ILN_USER
          AND 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
          AND PR_CUSTOMER_MASTER_DATA.CLIENT_ID=PR_ADDRESS.CLIENT_ID
          AND PR_ARTICLE.SUP_ITEM_NO=PR_COMPANY_ITEM_MATCH.SUP_ITEM_NO
          AND PR_COMPANY_ITEM_MATCH.SALES_ORG=PR_USED_ARTICLE_TEXT_ORIG.SALES_ORG
          AND PR_ARTICLE.SUP_ITEM_NO=PR_USED_ARTICLE_TEXT_ORIG.SUP_ITEM_NO
          AND PR_CUSTOMER_MASTER_DATA.LANG_CODE=PR_USED_ARTICLE_TEXT_ORIG.LANG_CODE
          AND ( PR_COMPANY_ITEM_MATCH.END_DATE IS NULL OR to_date(PR_COMPANY_ITEM_MATCH.END_DATE,'YYYYMMDD') - sysdate > 0)
          AND ( PR_COMPANY_ITEM_MATCH.START_DATE IS NULL OR sysdate - to_date(PR_COMPANY_ITEM_MATCH.START_DATE,'YYYYMMDD') > 0)
    )
     
    PR_PRODUCT_ITEMS_ORIG at new server:
      CREATE OR REPLACE FORCE VIEW "DUPONT_CCG2_P"."PR_PRODUCT_ITEMS_ORIG" ("CLIENT_ID", "ILN_USER", "EAN", "CODE", "ARTICLE_TEXT", "LANG_CODE", "LANR", "OLD_LANR", "ITEM_BY_NO", "UOM_UNIT", "PRICE_UOM", "PRICE", "CURRENCY", "PRICE_QTY", "SALES_UNIT", "START_DATE", "END_DATE", "UPDATED_AT") AS 
      (SELECT DISTINCT
          PR_CUSTOMER_MASTER_DATA.CLIENT_ID,
          PR_CUSTOMER_MASTER_DATA.ILN_USER,
          PR_ARTICLE.EAN,
          PR_ARTICLE.CODE,
          PR_USED_ARTICLE_TEXT_ORIG.ARTICLE_TEXT,
          PR_USED_ARTICLE_TEXT_ORIG.LANG_CODE,
          PR_ARTICLE.SUP_ITEM_NO,
          PR_ARTICLE.OLD_ITEM_NO,
          PR_COMPANY_ITEM_MATCH.ITEM_BY_NO,
          PR_COMPANY_ITEM_MATCH.UOM_UNIT,
          PR_COMPANY_ITEM_MATCH.PRICE_UOM,
          PR_COMPANY_ITEM_MATCH.PRICE,
          PR_COMPANY_ITEM_MATCH.CURRENCY,
          PR_COMPANY_ITEM_MATCH.PRICE_QTY,
          PR_COMPANY_ITEM_MATCH.SALES_UNIT,
          PR_COMPANY_ITEM_MATCH.START_DATE,
          PR_COMPANY_ITEM_MATCH.END_DATE,
          PR_ARTICLE.UPDATED_AT
            FROM PR_ARTICLE, PR_COMPANY_ITEM_MATCH, PR_USED_ARTICLE_TEXT_ORIG, PR_CUSTOMER_MASTER_DATA, PR_ADDRESS
            WHERE PR_ADDRESS.ORDER_TYPE='REP'
          AND PR_CUSTOMER_MASTER_DATA.ILN_USER=PR_ADDRESS.ILN_USER
          AND 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
          AND PR_CUSTOMER_MASTER_DATA.CLIENT_ID=PR_ADDRESS.CLIENT_ID
          AND PR_ARTICLE.SUP_ITEM_NO=PR_COMPANY_ITEM_MATCH.SUP_ITEM_NO
          AND PR_COMPANY_ITEM_MATCH.SALES_ORG=PR_USED_ARTICLE_TEXT_ORIG.SALES_ORG
          AND PR_ARTICLE.SUP_ITEM_NO=PR_USED_ARTICLE_TEXT_ORIG.SUP_ITEM_NO
          AND PR_CUSTOMER_MASTER_DATA.LANG_CODE=PR_USED_ARTICLE_TEXT_ORIG.LANG_CODE
          AND ( PR_COMPANY_ITEM_MATCH.END_DATE IS NULL OR to_date(PR_COMPANY_ITEM_MATCH.END_DATE,'YYYYMMDD') - sysdate > 0)
          AND ( PR_COMPANY_ITEM_MATCH.START_DATE IS NULL OR sysdate - to_date(PR_COMPANY_ITEM_MATCH.START_DATE,'YYYYMMDD') > 0)
    )
    For the explain plans i've token that columns from the both views, that where used by the view of beginning. I will give you the plans soon.

    Edited by: David_Pasternak on 23.04.2013 23:46
  • 23. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    So and here are the explain plans. First of all, these are the Select statement with which i've created the explain plans:
    select EAN, CODE, ARTICLE_TEXT, LANG_CODE, LANR, OLD_LANR, UOM_UNIT, PRICE_UOM, PRICE, CURRENCY, PRICE_QTY, SALES_UNIT, UPDATED_AT FROM DUPONT_CCG2_P.PR_PRODUCT_ITEMS_ORIG;
    select CLIENT_ID, ILN_USER, ILN_LINK, ITEM_BY_NO from DUPONT_CCG2_P.pr_constructor_article_orig;
    Explain plan for PR_PRODUCT_ITEMS_ORIG at the old server:
    SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display('PLAN_TABLE','VIEW_TEST_1','TYPICAL'));
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 395889582                                                                                                                            
                                                                                                                                                          
    ------------------------------------------------------------------------------------------------------------------------                              
    | Id  | Operation                         | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                              
    ------------------------------------------------------------------------------------------------------------------------                              
    |   0 | SELECT STATEMENT                  |                            |     7 |  2499 |       | 52595   (3)| 00:10:32 |                              
    |   1 |  VIEW                             | PR_PRODUCT_ITEMS_ORIG      |     7 |  2499 |       | 52595   (3)| 00:10:32 |                              
    |   2 |   HASH UNIQUE                     |                            |     7 |  2926 |       | 52595   (3)| 00:10:32 |                              
    |*  3 |    HASH JOIN                      |                            |     7 |  2926 |       | 52594   (3)| 00:10:32 |                              
    |*  4 |     HASH JOIN                     |                            |    36 | 12636 |       | 27045   (2)| 00:05:25 |                              
    |   5 |      NESTED LOOPS                 |                            |   120 | 20640 |       |  1719   (1)| 00:00:21 |                              
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |   6 |       NESTED LOOPS                |                            |   120 | 14880 |       |  1707   (1)| 00:00:21 |                              
    |*  7 |        HASH JOIN                  |                            |  2064 |   102K|       |    52   (4)| 00:00:01 |                              
    |   8 |         TABLE ACCESS FULL         | PR_CUSTOMER_MASTER_DATA    |  3450 | 75900 |       |     9   (0)| 00:00:01 |                              
    |*  9 |         TABLE ACCESS FULL         | PR_ADDRESS                 |  4151 |   117K|       |    42   (3)| 00:00:01 |                              
    |* 10 |        TABLE ACCESS BY INDEX ROWID| PR_COMPANY_ITEM_MATCH      |     1 |    73 |       |     1   (0)| 00:00:01 |                              
    |* 11 |         INDEX RANGE SCAN          | UK_PR_COMPANY_ITEM_MATCH   |    18 |       |       |     1   (0)| 00:00:01 |                              
    |  12 |       TABLE ACCESS BY INDEX ROWID | PR_ARTICLE                 |     1 |    48 |       |     1   (0)| 00:00:01 |                              
    |* 13 |        INDEX UNIQUE SCAN          | PK_PR_ARTICLE              |     1 |       |       |     1   (0)| 00:00:01 |                              
    |  14 |      VIEW                         | PR_ALL_USED_ARTICLE_TEXT   |  1063K|   181M|       | 25300   (2)| 00:05:04 |                              
    |  15 |       SORT UNIQUE                 |                            |  1063K|    95M|   222M| 25300  (25)| 00:05:04 |                              
    |  16 |        UNION-ALL                  |                            |       |       |       |            |          |                              
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 17 |         HASH JOIN                 |                            |   786K|    75M|       |  1074   (5)| 00:00:13 |                              
    |* 18 |          HASH JOIN                |                            |   798 | 35112 |       |    21  (10)| 00:00:01 |                              
    |  19 |           TABLE ACCESS FULL       | ST_USER                    |   754 |  5278 |       |     5   (0)| 00:00:01 |                              
    |* 20 |           HASH JOIN               |                            |   704 | 26048 |       |    15   (7)| 00:00:01 |                              
    |  21 |            TABLE ACCESS FULL      | ST_ADDRESS                 |   665 | 11970 |       |     5   (0)| 00:00:01 |                              
    |  22 |            TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA    |  3450 | 65550 |       |     9   (0)| 00:00:01 |                              
    |  23 |          TABLE ACCESS FULL        | PR_ARTICLE_TEXT            |   402K|    21M|       |  1035   (3)| 00:00:13 |                              
    |* 24 |         HASH JOIN                 |                            |   277K|    19M|       |  1054   (4)| 00:00:13 |                              
    |  25 |          VIEW                     | PR_USED_LANG_SALES_ORG     |   129 |  2064 |       |    11  (19)| 00:00:01 |                              
    |  26 |           HASH GROUP BY           |                            |   129 |  1032 |       |    11  (19)| 00:00:01 |                              
    |* 27 |            TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA    |  3450 | 27600 |       |     9   (0)| 00:00:01 |                              
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 28 |          TABLE ACCESS FULL        | PR_ARTICLE_TEXT            | 42949 |  2390K|       |  1036   (3)| 00:00:13 |                              
    |* 29 |     VIEW                          | PR_COUNT_USED_ARTICLE_TEXT |  1063K|    67M|       | 25524   (3)| 00:05:07 |                              
    |  30 |      HASH GROUP BY                |                            |  1063K|    54M|       | 25524   (3)| 00:05:07 |                              
    |  31 |       VIEW                        | PR_ALL_USED_ARTICLE_TEXT   |  1063K|    54M|       | 25300   (2)| 00:05:04 |                              
    |  32 |        SORT UNIQUE                |                            |  1063K|    95M|   222M| 25300  (25)| 00:05:04 |                              
    |  33 |         UNION-ALL                 |                            |       |       |       |            |          |                              
    |* 34 |          HASH JOIN                |                            |   786K|    75M|       |  1074   (5)| 00:00:13 |                              
    |* 35 |           HASH JOIN               |                            |   798 | 35112 |       |    21  (10)| 00:00:01 |                              
    |  36 |            TABLE ACCESS FULL      | ST_USER                    |   754 |  5278 |       |     5   (0)| 00:00:01 |                              
    |* 37 |            HASH JOIN              |                            |   704 | 26048 |       |    15   (7)| 00:00:01 |                              
    |  38 |             TABLE ACCESS FULL     | ST_ADDRESS                 |   665 | 11970 |       |     5   (0)| 00:00:01 |                              
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  39 |             TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA    |  3450 | 65550 |       |     9   (0)| 00:00:01 |                              
    |  40 |           TABLE ACCESS FULL       | PR_ARTICLE_TEXT            |   402K|    21M|       |  1035   (3)| 00:00:13 |                              
    |* 41 |          HASH JOIN                |                            |   277K|    19M|       |  1054   (4)| 00:00:13 |                              
    |  42 |           VIEW                    | PR_USED_LANG_SALES_ORG     |   129 |  2064 |       |    11  (19)| 00:00:01 |                              
    |  43 |            HASH GROUP BY          |                            |   129 |  1032 |       |    11  (19)| 00:00:01 |                              
    |* 44 |             TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA    |  3450 | 27600 |       |     9   (0)| 00:00:01 |                              
    |* 45 |           TABLE ACCESS FULL       | PR_ARTICLE_TEXT            | 42949 |  2390K|       |  1036   (3)| 00:00:13 |                              
    ------------------------------------------------------------------------------------------------------------------------                              
                                                                                                                                                          
    Predicate Information (identified by operation id):                                                                                                   
    ---------------------------------------------------                                                                                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                          
       3 - 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)                                                                                             
       4 - 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")                                                           
       7 - 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                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
       9 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
      10 - 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",'YYYYMMD                        
                  D')>0 OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS NULL))                                                                                 
      11 - 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")                                                                             
      13 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
      17 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      18 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      20 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      24 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      27 - filter("LANG_CODE"<>'XX')                                                                                                                      
      28 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
      29 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
      34 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      35 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      37 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      41 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      44 - filter("LANG_CODE"<>'XX')                                                                                                                      
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      45 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
    
    89 rows selected.
  • 24. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Explain plan for PR_PRODUCT_ITEMS_ORIG at the new server:
    SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display('PLAN_TABLE','VIEW_TEST_1','TYPICAL'));
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2265390915                                                                                                                           
                                                                                                                                                          
    -------------------------------------------------------------------------------------------------------------------                                   
    | Id  | Operation                    | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                   
    -------------------------------------------------------------------------------------------------------------------                                   
    |   0 | SELECT STATEMENT             |                            |    25 |  8925 |       | 63376   (1)| 00:12:41 |                                   
    |   1 |  VIEW                        | PR_PRODUCT_ITEMS_ORIG      |    25 |  8925 |       | 63376   (1)| 00:12:41 |                                   
    |   2 |   HASH UNIQUE                |                            |    25 | 10450 |       | 63376   (1)| 00:12:41 |                                   
    |*  3 |    HASH JOIN                 |                            |    25 | 10450 |       | 63375   (1)| 00:12:41 |                                   
    |*  4 |     HASH JOIN                |                            |   111 | 38961 |       | 33070   (1)| 00:06:37 |                                   
    |*  5 |      HASH JOIN               |                            |   331 | 56932 |       |  3469   (2)| 00:00:42 |                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |*  6 |       HASH JOIN              |                            |   331 | 41044 |       |  3399   (2)| 00:00:41 |                                   
    |*  7 |        TABLE ACCESS FULL     | PR_COMPANY_ITEM_MATCH      |  2423 |   172K|       |  3354   (2)| 00:00:41 |                                   
    |*  8 |        HASH JOIN             |                            |  3469 |   172K|       |    45   (3)| 00:00:01 |                                   
    |   9 |         TABLE ACCESS FULL    | PR_CUSTOMER_MASTER_DATA    |  3469 | 76318 |       |     9   (0)| 00:00:01 |                                   
    |* 10 |         TABLE ACCESS FULL    | PR_ADDRESS                 |  4171 |   118K|       |    35   (0)| 00:00:01 |                                   
    |  11 |       TABLE ACCESS FULL      | PR_ARTICLE                 | 20968 |   982K|       |    69   (0)| 00:00:01 |                                   
    |  12 |      VIEW                    | PR_ALL_USED_ARTICLE_TEXT   |  1276K|   217M|       | 29596   (1)| 00:05:56 |                                   
    |  13 |       SORT UNIQUE            |                            |  1276K|   112M|   132M| 29596  (18)| 00:05:56 |                                   
    |  14 |        UNION-ALL             |                            |       |       |       |            |          |                                   
    |* 15 |         HASH JOIN            |                            |  1004K|    96M|       |  1206   (1)| 00:00:15 |                                   
    |* 16 |          HASH JOIN           |                            |   798 | 35112 |       |    20   (5)| 00:00:01 |                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  17 |           TABLE ACCESS FULL  | ST_USER                    |   754 |  5278 |       |     5   (0)| 00:00:01 |                                   
    |* 18 |           HASH JOIN          |                            |   704 | 26048 |       |    15   (7)| 00:00:01 |                                   
    |  19 |            TABLE ACCESS FULL | ST_ADDRESS                 |   665 | 11970 |       |     5   (0)| 00:00:01 |                                   
    |  20 |            TABLE ACCESS FULL | PR_CUSTOMER_MASTER_DATA    |  3469 | 65911 |       |     9   (0)| 00:00:01 |                                   
    |  21 |          TABLE ACCESS FULL   | PR_ARTICLE_TEXT            |   403K|    21M|       |  1182   (1)| 00:00:15 |                                   
    |* 22 |         HASH JOIN            |                            |   271K|    15M|       |  1194   (1)| 00:00:15 |                                   
    |  23 |          VIEW                | PR_USED_LANG_SALES_ORG     |   129 |   516 |       |    10  (10)| 00:00:01 |                                   
    |  24 |           HASH GROUP BY      |                            |   129 |  1032 |       |    10  (10)| 00:00:01 |                                   
    |* 25 |            TABLE ACCESS FULL | PR_CUSTOMER_MASTER_DATA    |  3468 | 27744 |       |     9   (0)| 00:00:01 |                                   
    |* 26 |          TABLE ACCESS FULL   | PR_ARTICLE_TEXT            | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                                   
    |* 27 |     VIEW                     | PR_COUNT_USED_ARTICLE_TEXT |  1276K|    81M|       | 30301   (1)| 00:06:04 |                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  28 |      HASH GROUP BY           |                            |  1276K|    65M|       | 30301   (1)| 00:06:04 |                                   
    |  29 |       VIEW                   | PR_ALL_USED_ARTICLE_TEXT   |  1276K|    65M|       | 30263   (1)| 00:06:04 |                                   
    |  30 |        SORT UNIQUE           |                            |  1276K|   115M|   135M| 30263  (20)| 00:06:04 |                                   
    |  31 |         UNION-ALL            |                            |       |       |       |            |          |                                   
    |* 32 |          HASH JOIN           |                            |  1004K|    96M|       |  1206   (1)| 00:00:15 |                                   
    |* 33 |           HASH JOIN          |                            |   798 | 35112 |       |    20   (5)| 00:00:01 |                                   
    |  34 |            TABLE ACCESS FULL | ST_USER                    |   754 |  5278 |       |     5   (0)| 00:00:01 |                                   
    |* 35 |            HASH JOIN         |                            |   704 | 26048 |       |    15   (7)| 00:00:01 |                                   
    |  36 |             TABLE ACCESS FULL| ST_ADDRESS                 |   665 | 11970 |       |     5   (0)| 00:00:01 |                                   
    |  37 |             TABLE ACCESS FULL| PR_CUSTOMER_MASTER_DATA    |  3469 | 65911 |       |     9   (0)| 00:00:01 |                                   
    |  38 |           TABLE ACCESS FULL  | PR_ARTICLE_TEXT            |   403K|    21M|       |  1182   (1)| 00:00:15 |                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 39 |          HASH JOIN           |                            |   271K|    18M|       |  1194   (1)| 00:00:15 |                                   
    |  40 |           VIEW               | PR_USED_LANG_SALES_ORG     |   129 |  2064 |       |    10  (10)| 00:00:01 |                                   
    |  41 |            HASH GROUP BY     |                            |   129 |  1032 |       |    10  (10)| 00:00:01 |                                   
    |* 42 |             TABLE ACCESS FULL| PR_CUSTOMER_MASTER_DATA    |  3468 | 27744 |       |     9   (0)| 00:00:01 |                                   
    |* 43 |           TABLE ACCESS FULL  | PR_ARTICLE_TEXT            | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                                   
    -------------------------------------------------------------------------------------------------------------------                                   
                                                                                                                                                          
    Predicate Information (identified by operation id):                                                                                                   
    ---------------------------------------------------                                                                                                   
                                                                                                                                                          
       3 - access("PR_ALL_USED_ARTICLE_TEXT"."SUP_ITEM_NO"="PR_COUNT_USED_ARTICLE_TEXT"."SUP_ITEM_NO" AND                                                 
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
                  "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)                                                                                             
       4 - 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")                                                           
       5 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
       6 - 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")                                                              
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
       7 - 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",'YY                             
                  YYMMDD')>0 OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS NULL))                                                                            
       8 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                  "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
      10 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
      15 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      16 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      18 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      22 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      25 - filter("LANG_CODE"<>'XX')                                                                                                                      
      26 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
      27 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
      32 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      33 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      35 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      39 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      42 - filter("LANG_CODE"<>'XX')                                                                                                                      
      43 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
    
    87 Zeilen ausgewahlt.
  • 25. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Explain plan for PR_CONSTRUCTOR_ARTICLE_ORIG at the old server:
    SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display('PLAN_TABLE','VIEW_TEST_2','TYPICAL'));
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1778270448                                                                                                                           
                                                                                                                                                          
    -------------------------------------------------------------------------------------------------------------------------                             
    | Id  | Operation                         | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                             
    -------------------------------------------------------------------------------------------------------------------------                             
    |   0 | SELECT STATEMENT                  |                             |     3 |   402 |       | 51793   (3)| 00:10:22 |                             
    |   1 |  VIEW                             | PR_CONSTRUCTOR_ARTICLE_ORIG |     3 |   402 |       | 51793   (3)| 00:10:22 |                             
    |   2 |   HASH UNIQUE                     |                             |     3 |   801 |       | 51793   (3)| 00:10:22 |                             
    |*  3 |    HASH JOIN                      |                             |     3 |   801 |       | 51792   (3)| 00:10:22 |                             
    |*  4 |     HASH JOIN                     |                             |    18 |  3600 |       | 26243   (2)| 00:05:15 |                             
    |   5 |      NESTED LOOPS                 |                             |    60 |  8580 |       |   918   (1)| 00:00:12 |                             
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |   6 |       NESTED LOOPS                |                             |    60 |  7860 |       |   917   (1)| 00:00:11 |                             
    |*  7 |        HASH JOIN                  |                             |  1025 | 82000 |       |    95   (5)| 00:00:02 |                             
    |*  8 |         HASH JOIN                 |                             |  1713 | 87363 |       |    52   (4)| 00:00:01 |                             
    |*  9 |          TABLE ACCESS FULL        | PR_ADDRESS                  |  2780 | 80620 |       |    42   (3)| 00:00:01 |                             
    |  10 |          TABLE ACCESS FULL        | PR_CUSTOMER_MASTER_DATA     |  3450 | 75900 |       |     9   (0)| 00:00:01 |                             
    |* 11 |         TABLE ACCESS FULL         | PR_ADDRESS                  |  4151 |   117K|       |    42   (3)| 00:00:01 |                             
    |* 12 |        TABLE ACCESS BY INDEX ROWID| PR_COMPANY_ITEM_MATCH       |     1 |    51 |       |     1   (0)| 00:00:01 |                             
    |* 13 |         INDEX RANGE SCAN          | UK_PR_COMPANY_ITEM_MATCH    |    18 |       |       |     1   (0)| 00:00:01 |                             
    |* 14 |       INDEX UNIQUE SCAN           | PK_PR_ARTICLE               |     1 |    12 |       |     1   (0)| 00:00:01 |                             
    |  15 |      VIEW                         | PR_ALL_USED_ARTICLE_TEXT    |  1063K|    57M|       | 25300   (2)| 00:05:04 |                             
    |  16 |       SORT UNIQUE                 |                             |  1063K|    95M|   222M| 25300  (25)| 00:05:04 |                             
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  17 |        UNION-ALL                  |                             |       |       |       |            |          |                             
    |* 18 |         HASH JOIN                 |                             |   786K|    75M|       |  1074   (5)| 00:00:13 |                             
    |* 19 |          HASH JOIN                |                             |   798 | 35112 |       |    21  (10)| 00:00:01 |                             
    |  20 |           TABLE ACCESS FULL       | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                             
    |* 21 |           HASH JOIN               |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                             
    |  22 |            TABLE ACCESS FULL      | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                             
    |  23 |            TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                             
    |  24 |          TABLE ACCESS FULL        | PR_ARTICLE_TEXT             |   402K|    21M|       |  1035   (3)| 00:00:13 |                             
    |* 25 |         HASH JOIN                 |                             |   277K|    19M|       |  1054   (4)| 00:00:13 |                             
    |  26 |          VIEW                     | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                             
    |  27 |           HASH GROUP BY           |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                             
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 28 |            TABLE ACCESS FULL      | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                             
    |* 29 |          TABLE ACCESS FULL        | PR_ARTICLE_TEXT             | 42949 |  2390K|       |  1036   (3)| 00:00:13 |                             
    |* 30 |     VIEW                          | PR_COUNT_USED_ARTICLE_TEXT  |  1063K|    67M|       | 25524   (3)| 00:05:07 |                             
    |  31 |      HASH GROUP BY                |                             |  1063K|    54M|       | 25524   (3)| 00:05:07 |                             
    |  32 |       VIEW                        | PR_ALL_USED_ARTICLE_TEXT    |  1063K|    54M|       | 25300   (2)| 00:05:04 |                             
    |  33 |        SORT UNIQUE                |                             |  1063K|    95M|   222M| 25300  (25)| 00:05:04 |                             
    |  34 |         UNION-ALL                 |                             |       |       |       |            |          |                             
    |* 35 |          HASH JOIN                |                             |   786K|    75M|       |  1074   (5)| 00:00:13 |                             
    |* 36 |           HASH JOIN               |                             |   798 | 35112 |       |    21  (10)| 00:00:01 |                             
    |  37 |            TABLE ACCESS FULL      | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                             
    |* 38 |            HASH JOIN              |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                             
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  39 |             TABLE ACCESS FULL     | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                             
    |  40 |             TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA     |  3450 | 65550 |       |     9   (0)| 00:00:01 |                             
    |  41 |           TABLE ACCESS FULL       | PR_ARTICLE_TEXT             |   402K|    21M|       |  1035   (3)| 00:00:13 |                             
    |* 42 |          HASH JOIN                |                             |   277K|    19M|       |  1054   (4)| 00:00:13 |                             
    |  43 |           VIEW                    | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    11  (19)| 00:00:01 |                             
    |  44 |            HASH GROUP BY          |                             |   129 |  1032 |       |    11  (19)| 00:00:01 |                             
    |* 45 |             TABLE ACCESS FULL     | PR_CUSTOMER_MASTER_DATA     |  3450 | 27600 |       |     9   (0)| 00:00:01 |                             
    |* 46 |           TABLE ACCESS FULL       | PR_ARTICLE_TEXT             | 42949 |  2390K|       |  1036   (3)| 00:00:13 |                             
    -------------------------------------------------------------------------------------------------------------------------                             
                                                                                                                                                          
    Predicate Information (identified by operation id):                                                                                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------                                                                                                   
                                                                                                                                                          
       3 - 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)                                                                                             
       4 - 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")                                                           
       7 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
                  "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
       8 - access("PR_ADDRESS"."CLIENT_ID"="PR_CUSTOMER_MASTER_DATA"."CLIENT_ID" AND                                                                      
                  "PR_ADDRESS"."ILN_LINK"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                           
       9 - filter("PR_ADDRESS"."ORDER_TYPE"='CB' AND "PR_ADDRESS"."ILN_USER"<>"PR_ADDRESS"."ILN_LINK")                                                    
      11 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
      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"."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")                                                                             
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      14 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
      18 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      19 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      21 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      25 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      28 - filter("LANG_CODE"<>'XX')                                                                                                                      
      29 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
      30 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
      35 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      36 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      38 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      42 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      45 - filter("LANG_CODE"<>'XX')                                                                                                                      
      46 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
    
    93 rows selected.
  • 26. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Explain plan for PR_CONSTRUCTOR_ARTICLE_ORIG at the new server:
    SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display('PLAN_TABLE','VIEW_TEST_2','TYPICAL'));
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1314330391                                                                                                                           
                                                                                                                                                          
    ---------------------------------------------------------------------------------------------------------------------                                 
    | Id  | Operation                     | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                 
    ---------------------------------------------------------------------------------------------------------------------                                 
    |   0 | SELECT STATEMENT              |                             |    37 |  4958 |       | 63342   (1)| 00:12:41 |                                 
    |   1 |  VIEW                         | PR_CONSTRUCTOR_ARTICLE_ORIG |    37 |  4958 |       | 63342   (1)| 00:12:41 |                                 
    |   2 |   HASH UNIQUE                 |                             |    37 |  9879 |       | 63342   (1)| 00:12:41 |                                 
    |*  3 |    HASH JOIN                  |                             |    37 |  9879 |       | 63341   (1)| 00:12:41 |                                 
    |*  4 |     HASH JOIN                 |                             |    25 |  5950 |       | 63305   (1)| 00:12:40 |                                 
    |*  5 |      HASH JOIN                |                             |   111 | 18981 |       | 33000   (1)| 00:06:36 |                                 
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |   6 |       NESTED LOOPS            |                             |   331 | 37734 |       |  3399   (2)| 00:00:41 |                                 
    |*  7 |        HASH JOIN              |                             |   331 | 33762 |       |  3399   (2)| 00:00:41 |                                 
    |*  8 |         TABLE ACCESS FULL     | PR_COMPANY_ITEM_MATCH       |  2423 |   120K|       |  3354   (2)| 00:00:41 |                                 
    |*  9 |         HASH JOIN             |                             |  3469 |   172K|       |    45   (3)| 00:00:01 |                                 
    |  10 |          TABLE ACCESS FULL    | PR_CUSTOMER_MASTER_DATA     |  3469 | 76318 |       |     9   (0)| 00:00:01 |                                 
    |* 11 |          TABLE ACCESS FULL    | PR_ADDRESS                  |  4171 |   118K|       |    35   (0)| 00:00:01 |                                 
    |* 12 |        INDEX UNIQUE SCAN      | PK_PR_ARTICLE               |     1 |    12 |       |     0   (0)| 00:00:01 |                                 
    |  13 |       VIEW                    | PR_ALL_USED_ARTICLE_TEXT    |  1276K|    69M|       | 29596   (1)| 00:05:56 |                                 
    |  14 |        SORT UNIQUE            |                             |  1276K|   112M|   132M| 29596  (18)| 00:05:56 |                                 
    |  15 |         UNION-ALL             |                             |       |       |       |            |          |                                 
    |* 16 |          HASH JOIN            |                             |  1004K|    96M|       |  1206   (1)| 00:00:15 |                                 
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 17 |           HASH JOIN           |                             |   798 | 35112 |       |    20   (5)| 00:00:01 |                                 
    |  18 |            TABLE ACCESS FULL  | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                                 
    |* 19 |            HASH JOIN          |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                                 
    |  20 |             TABLE ACCESS FULL | ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                                 
    |  21 |             TABLE ACCESS FULL | PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                                 
    |  22 |           TABLE ACCESS FULL   | PR_ARTICLE_TEXT             |   403K|    21M|       |  1182   (1)| 00:00:15 |                                 
    |* 23 |          HASH JOIN            |                             |   271K|    15M|       |  1194   (1)| 00:00:15 |                                 
    |  24 |           VIEW                | PR_USED_LANG_SALES_ORG      |   129 |   516 |       |    10  (10)| 00:00:01 |                                 
    |  25 |            HASH GROUP BY      |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                                 
    |* 26 |             TABLE ACCESS FULL | PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                                 
    |* 27 |           TABLE ACCESS FULL   | PR_ARTICLE_TEXT             | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                                 
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 28 |      VIEW                     | PR_COUNT_USED_ARTICLE_TEXT  |  1276K|    81M|       | 30301   (1)| 00:06:04 |                                 
    |  29 |       HASH GROUP BY           |                             |  1276K|    65M|       | 30301   (1)| 00:06:04 |                                 
    |  30 |        VIEW                   | PR_ALL_USED_ARTICLE_TEXT    |  1276K|    65M|       | 30263   (1)| 00:06:04 |                                 
    |  31 |         SORT UNIQUE           |                             |  1276K|   115M|   135M| 30263  (20)| 00:06:04 |                                 
    |  32 |          UNION-ALL            |                             |       |       |       |            |          |                                 
    |* 33 |           HASH JOIN           |                             |  1004K|    96M|       |  1206   (1)| 00:00:15 |                                 
    |* 34 |            HASH JOIN          |                             |   798 | 35112 |       |    20   (5)| 00:00:01 |                                 
    |  35 |             TABLE ACCESS FULL | ST_USER                     |   754 |  5278 |       |     5   (0)| 00:00:01 |                                 
    |* 36 |             HASH JOIN         |                             |   704 | 26048 |       |    15   (7)| 00:00:01 |                                 
    |  37 |              TABLE ACCESS FULL| ST_ADDRESS                  |   665 | 11970 |       |     5   (0)| 00:00:01 |                                 
    |  38 |              TABLE ACCESS FULL| PR_CUSTOMER_MASTER_DATA     |  3469 | 65911 |       |     9   (0)| 00:00:01 |                                 
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |  39 |            TABLE ACCESS FULL  | PR_ARTICLE_TEXT             |   403K|    21M|       |  1182   (1)| 00:00:15 |                                 
    |* 40 |           HASH JOIN           |                             |   271K|    18M|       |  1194   (1)| 00:00:15 |                                 
    |  41 |            VIEW               | PR_USED_LANG_SALES_ORG      |   129 |  2064 |       |    10  (10)| 00:00:01 |                                 
    |  42 |             HASH GROUP BY     |                             |   129 |  1032 |       |    10  (10)| 00:00:01 |                                 
    |* 43 |              TABLE ACCESS FULL| PR_CUSTOMER_MASTER_DATA     |  3468 | 27744 |       |     9   (0)| 00:00:01 |                                 
    |* 44 |            TABLE ACCESS FULL  | PR_ARTICLE_TEXT             | 42138 |  2345K|       |  1182   (1)| 00:00:15 |                                 
    |* 45 |     TABLE ACCESS FULL         | PR_ADDRESS                  |  2799 | 81171 |       |    35   (0)| 00:00:01 |                                 
    ---------------------------------------------------------------------------------------------------------------------                                 
                                                                                                                                                          
    Predicate Information (identified by operation id):                                                                                                   
    ---------------------------------------------------                                                                                                   
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                          
       3 - access("PR_ADDRESS"."CLIENT_ID"="PR_CUSTOMER_MASTER_DATA"."CLIENT_ID" AND                                                                      
                  "PR_ADDRESS"."ILN_LINK"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                           
       4 - 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)                                                                                             
       5 - 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")                                                           
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
       7 - 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")                                                              
       8 - 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",'YYYY                           
                  MMDD')>0 OR "PR_COMPANY_ITEM_MATCH"."START_DATE" IS NULL))                                                                              
       9 - access("PR_CUSTOMER_MASTER_DATA"."ILN_USER"="PR_ADDRESS"."ILN_USER" AND                                                                        
                  "PR_CUSTOMER_MASTER_DATA"."CLIENT_ID"="PR_ADDRESS"."CLIENT_ID")                                                                         
      11 - filter("PR_ADDRESS"."ORDER_TYPE"='REP')                                                                                                        
      12 - access("PR_ARTICLE"."SUP_ITEM_NO"="PR_COMPANY_ITEM_MATCH"."SUP_ITEM_NO")                                                                       
      16 - 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")                                                                    
      17 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      19 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
      23 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      26 - filter("LANG_CODE"<>'XX')                                                                                                                      
      27 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
      28 - filter("PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT"=1 OR "PR_COUNT_USED_ARTICLE_TEXT"."ART_COUNT">1)                                               
      33 - access("PR_CUSTOMER_MASTER_DATA"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG" AND                                                                 
                  "PR_CUSTOMER_MASTER_DATA"."LANG_CODE"="PR_ARTICLE_TEXT"."LANG_CODE")                                                                    
      34 - access("ST_USER"."ADDRESS_ID"="ST_ADDRESS"."ADDRESS_ID")                                                                                       
      36 - access("ST_ADDRESS"."ILN"="PR_CUSTOMER_MASTER_DATA"."ILN_USER")                                                                                
    
    PLAN_TABLE_OUTPUT                                                                                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      40 - access("PR_USED_LANG_SALES_ORG"."SALES_ORG"="PR_ARTICLE_TEXT"."SALES_ORG")                                                                     
      43 - filter("LANG_CODE"<>'XX')                                                                                                                      
      44 - filter("PR_ARTICLE_TEXT"."LANG_CODE"='EN')                                                                                                     
      45 - filter("PR_ADDRESS"."ORDER_TYPE"='CB' AND "PR_ADDRESS"."ILN_USER"<>"PR_ADDRESS"."ILN_LINK")                                                    
    
    92 Zeilen ausgewahlt.
  • 27. Re: Select statement on a View takes much time
    Manik Expert
    Currently Being Moderated
    TABLE ACCESS BY INDEX ROWID| PR_COMPANY_ITEM_MATCH in the old and TABLE ACCESS FULL     | PR_COMPANY_ITEM_MATCH in new server???

    Why is it different?
    Are the stats collected properly in new server? and also comment about the data.. is it the same? Just want to make sure this is answered because, Orale picks up best plan based on few factors like stats, data, etc...

    Cheers,
    Manik.
  • 28. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    Good question! I can't tell you. The own difference between the two servers is, that the new server has some more rows in the view, because the new server ist productive vor about 5 weeks for that schema.

    To transport all of the data of the schema i've used that two exp and imp commands:
    exp file=<dataname>21.03.2013_exp.dmp owner=SCHEMANAME log=<dataname>21.03.2013.log consistent=Y direct=Y feedback=50 indexes=Y grants=Y
    
    imp file=<dataname>21.03.2013_exp.dmp fromuser=SCHEMANAME  touser=SCHEMANAME  log=<dataname>21.03.2013.log grants=Y feedback=50 indexes=Y
    So i think all relevantive data should be on the new server.

    First i'd tried it with data pump but there was an integration problem with other materialized views. By an internet search i found the solution to use the old exp and imp tools.

    If the stats would be collected properly in the new server i can't tell you. How can i look for that? And what do you mean with "comment about the data"?
  • 29. Re: Select statement on a View takes much time
    David_Pasternak Newbie
    Currently Being Moderated
    I've retried to import the statistics of the schema from the old server. When i want to import them, im getting this:
    exec dbms_stats.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
    BEGIN dbms_stats.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA'); END;
    
    *
    FEHLER in Zeile 1:
    ORA-20002: Version of statistics table DBASCHEMA.STATS_TABLE is too old.  Please
    try upgrading it with dbms_stats.upgrade_stat_table
    ORA-06512: in "SYS.DBMS_STATS", Zeile 11476
    ORA-06512: in "SYS.DBMS_STATS", Zeile 11493
    ORA-06512: in "SYS.DBMS_STATS", Zeile 12628
    ORA-06512: in Zeile 1
    So he means that i've to to this one:
    exec dbms_stats.upgrade_stat_table('DBASCHEMA','STATS_TABLE');
    Could it be, that the upgrading of the statistics redesinged them and then it has no efectivity?

Legend

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