12 Replies Latest reply: Feb 1, 2013 10:15 AM by user522961 RSS

    query  with union does not finish

    user522961
      Hi all,
      on 11 gR2 my query does not finish :
      show parameter optimizer
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     2
      optimizer_features_enable            string      11.2.0.3
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
      
      EXPLAIN PLAN FOR
      SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                   PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                   D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                   SELECT  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                   BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                   PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                   D.LANGUAGE_CD='FRA') ORDER BY 1
      SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
      
      PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                                                                                                                                                                                                                                                                                                                   
      -----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
      | Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)|                                                                                                                                                                                                        
      -----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
      |   0 | SELECT STATEMENT                  |                    |  9943M|   537G|       |   313M  (1)|                                                                                                                                                                                                        
      |   1 |  SORT ORDER BY                    |                    |  9943M|   537G|   410G|   313M  (1)|                                                                                                                                                                                                        
      |   2 |   SORT UNIQUE                     |                    |  9943M|   537G|   632G|   188M  (1)|                                                                                                                                                                                                        
      |   3 |    UNION-ALL                      |                    |       |       |       |            |                                                                                                                                                                                                        
      |*  4 |     HASH JOIN                     |                    |  9943M|   537G|  3944K| 31251  (98)|                                                                                                                                                                                                        
      |*  5 |      INDEX FAST FULL SCAN         | PSCRECV_HDR        |   201K|  1576K|       |   187   (2)|                                                                                                                                                                                                        
      |*  6 |      HASH JOIN                    |                    | 98578 |  4813K|       |   191   (2)|                                                                                                                                                                                                        
      |   7 |       NESTED LOOPS                |                    |       |       |       |            |                                                                                                                                                                                                        
      |   8 |        NESTED LOOPS               |                    |     1 |    42 |       |     3   (0)|                                                                                                                                                                                                        
      |   9 |         MERGE JOIN CARTESIAN      |                    |     1 |    12 |       |     2   (0)|                                                                                                                                                                                                        
      |* 10 |          INDEX RANGE SCAN         | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
      |  11 |          BUFFER SORT              |                    |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
      |* 12 |           INDEX RANGE SCAN        | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
      |* 13 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_LANG   |     1 |       |       |     0   (0)|                                                                                                                                                                                                        
      |  14 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_LANG   |     1 |    30 |       |     1   (0)|                                                                                                                                                                                                        
      |* 15 |       INDEX FAST FULL SCAN        | PSCRECV_HDR        |   201K|  1576K|       |   187   (2)|                                                                                                                                                                                                        
      |* 16 |     FILTER                        |                    |       |       |       |            |                                                                                                                                                                                                        
      |* 17 |      HASH JOIN                    |                    |   100K|  3447K|       |   190   (2)|                                                                                                                                                                                                        
      |  18 |       TABLE ACCESS BY INDEX ROWID | PS_BUS_UNIT_TBL_FS |     1 |    27 |       |     2   (0)|                                                                                                                                                                                                        
      |* 19 |        INDEX RANGE SCAN           | PS_BUS_UNIT_TBL_FS |     1 |       |       |     1   (0)|                                                                                                                                                                                                        
      |* 20 |       INDEX FAST FULL SCAN        | PSCRECV_HDR        |   201K|  1576K|       |   187   (2)|                                                                                                                                                                                                        
      |  21 |      NESTED LOOPS                 |                    |  9170 |   214K|       |   681   (1)|                                                                                                                                                                                                        
      |  22 |       NESTED LOOPS                |                    |     1 |    16 |       |     0   (0)|                                                                                                                                                                                                        
      |* 23 |        INDEX UNIQUE SCAN          | PS_BUS_UNIT_LANG   |     1 |    10 |       |     0   (0)|                                                                                                                                                                                                        
      |* 24 |        INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     0   (0)|                                                                                                                                                                                                        
      |* 25 |       INDEX RANGE SCAN            | PSCRECV_HDR        |  9170 | 73360 |       |   681   (1)|                                                                                                                                                                                                        
      -----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                                   
      Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
      ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                   
         4 - access("A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT" AND                                                                                                                                                                                                                                                    
                    "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
         5 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
         6 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
        10 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
             filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
        12 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
             filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
        13 - access("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                           
             filter("C"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
        15 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
        16 - filter( NOT EXISTS (SELECT 0 FROM SYSADM."PS_BUS_UNIT_LANG"                                                                                                                                                                                                                                           
                    "C",SYSADM."PS_BUS_UNIT_TBL_FS" "B",SYSADM."PS_RECV_HDR" "A" WHERE                                                                                                                                                                                                                             
                    "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."RECV_STATUS"<>'C' AND                                                                                                                                                                                                                         
                    "A"."BUSINESS_UNIT"=:B1 AND "B"."BUSINESS_UNIT"=:B2 AND                                                                                                                                                                                                                                        
                    "B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA' AND                                                                                                                                                                                                                        
                    "C"."BUSINESS_UNIT"=:B3))                                                                                                                                                                                                                                                                      
        17 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
        19 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
             filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
        20 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
        23 - access("C"."BUSINESS_UNIT"=:B1 AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                                           
        24 - access("B"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                                                       
             filter("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
        25 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
             filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                   
      Note                                                                                                                                                                                                                                                                                                         
      -----                                                                                                                                                                                                                                                                                                        
         - 'PLAN_TABLE' is old version                                                                                                                                                                                                                                                                             
      
      65 rows selected
      
      
      SQL> select osuser, program, state, event,machine,BLOCKING_SESSION_STATUS from v$session where program like '%SQL Developer%';
      
      OSUSER               PROGRAM              STATE      EVENT                          MACHINE           BLOCKING_SE
      -------------------- -------------------- ---------- ------------------------------ -------------------- -----------
      
      osuser             SQL Developer        WAITED SHO SQL*Net message to client      server    NOT IN WAIT
                                                RT TIME
      If I run this query with union all it is immediate and takes only 0.15 S.


      Thank for any help.
        • 1. Re: query  with union does not finish
          vlethakula
          what is the difference in plan with UNION and UNION ALL?
          • 2. Re: query  with union does not finish
            Jonathan Lewis
            user522961 wrote:
            Hi all,
            on 11 gR2 my query does not finish :

            ...

            If I run this query with union all it is immediate and takes only 0.15 S.

            How many rows does the query return when you run it with UNION ALL - and do you select ALL of them back to SQL Developer or just the first 20 or so ?
            Are the cardinality estimates correct in the plan you've shown - are you expectings millions of rows (that have to be sorted for uniqueness before the first one is returned ?
            How long have you waited before deciding the query is not going to "finish" ?

            Regards
            Jonathan Lewis
            • 3. Re: query  with union does not finish
              user522961
              thank.
              Here with union all :
              EXPLAIN PLAN FOR
              SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                           PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                           D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                           ALL SELECT  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                           BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                           PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                           D.LANGUAGE_CD='FRA') ORDER BY 1
              SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
              
              PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                                                                                                                                                                                                                                                                                                                           
              ---------------------------------------------------------------------------------------------                                                                                                                                                                                                                
              | Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)|                                                                                                                                                                                                                
              ---------------------------------------------------------------------------------------------                                                                                                                                                                                                                
              |   0 | SELECT STATEMENT                  |                    |     2 |    55 |   586   (5)|                                                                                                                                                                                                                
              |   1 |  SORT ORDER BY                    |                    |     2 |    55 |   586   (5)|                                                                                                                                                                                                                
              |   2 |   UNION-ALL                       |                    |       |       |            |                                                                                                                                                                                                                
              |*  3 |    HASH JOIN                      |                    |     1 |    32 |   390   (5)|                                                                                                                                                                                                                
              |   4 |     VIEW                          | PS_SP_RECV1_NONVW  |     1 |     6 |   195   (5)|                                                                                                                                                                                                                
              |   5 |      SORT UNIQUE                  |                    |     1 |    33 |   195   (5)|                                                                                                                                                                                                                
              |   6 |       NESTED LOOPS                |                    |       |       |            |                                                                                                                                                                                                                
              |   7 |        NESTED LOOPS               |                    |     1 |    33 |   194   (5)|                                                                                                                                                                                                                
              |   8 |         VIEW                      | VW_DTP_55E0DA61    |     1 |     6 |   193   (5)|                                                                                                                                                                                                                
              |   9 |          SORT UNIQUE              |                    |     1 |     8 |   193   (5)|                                                                                                                                                                                                                
              |* 10 |           INDEX FAST FULL SCAN    | PSCRECV_HDR        |   201K|  1576K|   187   (2)|                                                                                                                                                                                                                
              |* 11 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_TBL_FS |     1 |       |     0   (0)|                                                                                                                                                                                                                
              |  12 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_TBL_FS |     1 |    27 |     1   (0)|                                                                                                                                                                                                                
              |  13 |     VIEW                          | PS_SP_RCV1_NONVWLN |     1 |    26 |   195   (5)|                                                                                                                                                                                                                
              |  14 |      SORT UNIQUE                  |                    |     1 |    42 |   195   (5)|                                                                                                                                                                                                                
              |  15 |       NESTED LOOPS                |                    |       |       |            |                                                                                                                                                                                                                
              |  16 |        NESTED LOOPS               |                    |     1 |    42 |   194   (5)|                                                                                                                                                                                                                
              |  17 |         NESTED LOOPS              |                    |     1 |    12 |   193   (5)|                                                                                                                                                                                                                
              |  18 |          VIEW                     | VW_DTP_479EA4B5    |     1 |     6 |   193   (5)|                                                                                                                                                                                                                
              |  19 |           SORT UNIQUE             |                    |     1 |     8 |   193   (5)|                                                                                                                                                                                                                
              |* 20 |            INDEX FAST FULL SCAN   | PSCRECV_HDR        |   201K|  1576K|   187   (2)|                                                                                                                                                                                                                
              |* 21 |          INDEX UNIQUE SCAN        | PS_BUS_UNIT_TBL_FS |     1 |     6 |     0   (0)|                                                                                                                                                                                                                
              |* 22 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_LANG   |     1 |       |     0   (0)|                                                                                                                                                                                                                
              |  23 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_LANG   |     1 |    30 |     1   (0)|                                                                                                                                                                                                                
              |* 24 |    FILTER                         |                    |       |       |            |                                                                                                                                                                                                                
              |  25 |     VIEW                          | PS_SP_RECV1_NONVW  |     1 |    23 |   193   (4)|                                                                                                                                                                                                                
              |  26 |      SORT UNIQUE                  |                    |     1 |    35 |   193   (4)|                                                                                                                                                                                                                
              |* 27 |       HASH JOIN                   |                    |   100K|  3447K|   190   (2)|                                                                                                                                                                                                                
              |  28 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_TBL_FS |     1 |    27 |     2   (0)|                                                                                                                                                                                                                
              |* 29 |         INDEX RANGE SCAN          | PS_BUS_UNIT_TBL_FS |     1 |       |     1   (0)|                                                                                                                                                                                                                
              |* 30 |        INDEX FAST FULL SCAN       | PSCRECV_HDR        |   201K|  1576K|   187   (2)|                                                                                                                                                                                                                
              |  31 |     NESTED LOOPS                  |                    |     2 |    48 |     2   (0)|                                                                                                                                                                                                                
              |  32 |      NESTED LOOPS                 |                    |     1 |    16 |     0   (0)|                                                                                                                                                                                                                
              |* 33 |       INDEX UNIQUE SCAN           | PS_BUS_UNIT_LANG   |     1 |    10 |     0   (0)|                                                                                                                                                                                                                
              |* 34 |       INDEX UNIQUE SCAN           | PS_BUS_UNIT_TBL_FS |     1 |     6 |     0   (0)|                                                                                                                                                                                                                
              |* 35 |      INDEX RANGE SCAN             | PSCRECV_HDR        |     2 |    16 |     2   (0)|                                                                                                                                                                                                                
              ---------------------------------------------------------------------------------------------                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                           
              Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
              ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                           
                 3 - access("D"."BUSINESS_UNIT"="A"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                10 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
                11 - access("ITEM_1"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                                  
                     filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                20 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
                21 - access("ITEM_1"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                                  
                     filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                22 - access("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                           
                     filter("C"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                24 - filter( NOT EXISTS (SELECT 0 FROM SYSADM."PS_BUS_UNIT_LANG"                                                                                                                                                                                                                                           
                            "C",SYSADM."PS_BUS_UNIT_TBL_FS" "B",SYSADM."PS_RECV_HDR" "A" WHERE                                                                                                                                                                                                                             
                            "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."RECV_STATUS"<>'C' AND                                                                                                                                                                                                                         
                            "A"."BUSINESS_UNIT"=:B1 AND "B"."BUSINESS_UNIT"=:B2 AND                                                                                                                                                                                                                                        
                            "B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA' AND                                                                                                                                                                                                                        
                            "C"."BUSINESS_UNIT"=:B3))                                                                                                                                                                                                                                                                      
                27 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                29 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                     filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                30 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
                33 - access("C"."BUSINESS_UNIT"=:B1 AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                                           
                34 - access("B"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                                                       
                     filter("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                35 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                     filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                           
              Note                                                                                                                                                                                                                                                                                                         
              -----                                                                                                                                                                                                                                                                                                        
                 - 'PLAN_TABLE' is old version                                                                                                                                                                                                                                                                             
              
              73 rows selected
              • 4. Re: query  with union does not finish
                6363
                user522961 wrote:

                If I run this query with union all it is immediate and takes only 0.15 S.
                How are you measuring the time, are all the rows are fetched immediately or just the first screen of rows in 0.15s?
                How many rows does the union all return?
                • 5. Re: query  with union does not finish
                  767217
                  Hi, I guess you have incorrect joining condition between two tables - actually you get something like CROSS join between two tables in first part of query.
                  |*  4 |     HASH JOIN                     |                    |  9943M|   537G|  3944K| 31251  (98)|                                                                                                                                                                                                        
                  |*  5 |      INDEX FAST FULL SCAN         | PSCRECV_HDR        |   201K|  1576K|       |   187   (2)|                                                                                                                                                                                                        
                  |*  6 |      HASH JOIN                    |                    | 98578 |  4813K|       |   191   (2)|                                                                                                                                                                                                        
                   
                  as result you could see millions of records in output. I guess "BUSINESS_UNIT" is not unique in both tables.
                  • 6. Re: query  with union does not finish
                    user522961
                    Thank you.
                    I set timing on. And time is written by Oracle at the end of query with UNION ALL.
                    • 7. Re: query  with union does not finish
                      Jonathan Lewis
                      user522961 wrote:
                      I set timing on. And time is written by Oracle at the end of query with UNION ALL.
                      That's good to know.

                      Since the fast plan also has a final "sort order by" this allows us to infer that the actual volume of data involved is small and the optimizer has come up with some very bad cardinality estimates in the UNION plan which, for some reason, have been over-ridden by the strategy taken for the UNION ALL plan.

                      This may be an optimizer bug, or it may be a feature/restriction of the "distinct placement" transformation that appears in the second plan (the VW_DTP bits). You might need to raise this as an SR with Oracle, but if you're happy with a short-term workaround then take a look at the non-mergeable use of views with the UNION ALL plan - adding a pair of no_merge() hints to your original query may be enough to get a fast plan with the UNION.
                      SELECT /*+ no_merge(a) no_merge(d) */ A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                                   PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                                   D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                                   SELECT  /*+ no_merge(a) */  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                                   BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                                   PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                                   D.LANGUAGE_CD='FRA') ORDER BY 1
                      Regards
                      Jonathan Lewis
                      • 8. Re: query  with union does not finish
                        user522961
                        Thank you Jonathan.
                        It is a standard Oracle peoplesoft script then we prefer not to modify it but know why it does not end ?

                        Both of them (UNION and UNION ALL) return and have to return 1 row.

                        On another database the query (UNION, without ALL) works fine.

                        For how many time waiting before saying never end, we receive timeout error from Peoplesoft application.
                        Here is the Explain Plan from the databse where it works well :
                        SQL> show parameter optimizer
                        
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ----------
                        optimizer_capture_sql_plan_baselines boolean     FALSE
                        optimizer_dynamic_sampling           integer     2
                        optimizer_features_enable            string      11.2.0.3
                        optimizer_index_caching              integer     0
                        optimizer_index_cost_adj             integer     100
                        optimizer_mode                       string      ALL_ROWS
                        optimizer_secure_view_merging        boolean     TRUE
                        optimizer_use_invisible_indexes      boolean     FALSE
                        optimizer_use_pending_statistics     boolean     FALSE
                        optimizer_use_sql_plan_baselines     boolean     TRUE
                        EXPLAIN PLAN FOR
                        SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                                     PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                                     D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                                     SELECT  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                                     BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                                     PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                                     D.LANGUAGE_CD='FRA') ORDER BY 1
                        SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
                        explain plan réussi.
                        PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
                        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                        Plan hash value: 1299176078                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                                     
                        --------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     
                        | Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                     
                        --------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     
                        |   0 | SELECT STATEMENT                  |                    |     4 |   170 |     9  (34)| 00:00:01 |                                                                                                                                                                                                     
                        |   1 |  SORT ORDER BY                    |                    |     4 |   170 |     9  (34)| 00:00:01 |                                                                                                                                                                                                     
                        |   2 |   SORT UNIQUE                     |                    |     4 |   170 |     8  (63)| 00:00:01 |                                                                                                                                                                                                     
                        |   3 |    UNION-ALL                      |                    |       |       |            |          |                                                                                                                                                                                                     
                        |   4 |     NESTED LOOPS                  |                    |     1 |    62 |     3   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |   5 |      NESTED LOOPS                 |                    |     1 |    56 |     3   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |   6 |       NESTED LOOPS                |                    |     1 |    48 |     2   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |   7 |        NESTED LOOPS               |                    |     3 |    42 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |*  8 |         INDEX RANGE SCAN          | PSCRECV_HDR        |     3 |    24 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |*  9 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_TBL_FS |     1 |     6 |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |  10 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_LANG   |     1 |    34 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 11 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_LANG   |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 12 |       INDEX RANGE SCAN            | PSCRECV_HDR        |     2 |    16 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 13 |      INDEX UNIQUE SCAN            | PS_BUS_UNIT_TBL_FS |     1 |     6 |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |  14 |     NESTED LOOPS                  |                    |       |       |            |          |                                                                                                                                                                                                     
                        |  15 |      NESTED LOOPS                 |                    |     1 |    36 |     2   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 16 |       INDEX RANGE SCAN            | PSCRECV_HDR        |     1 |     8 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |  17 |        NESTED LOOPS               |                    |     1 |    24 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |  18 |         NESTED LOOPS              |                    |     1 |    16 |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 19 |          INDEX UNIQUE SCAN        | PS_BUS_UNIT_LANG   |     1 |    10 |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 20 |          INDEX UNIQUE SCAN        | PS_BUS_UNIT_TBL_FS |     1 |     6 |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 21 |         INDEX RANGE SCAN          | PSCRECV_HDR        |     1 |     8 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |* 22 |       INDEX UNIQUE SCAN           | PS_BUS_UNIT_TBL_FS |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                     
                        |  23 |      TABLE ACCESS BY INDEX ROWID  | PS_BUS_UNIT_TBL_FS |     1 |    28 |     1   (0)| 00:00:01 |                                                                                                                                                                                                     
                        --------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                     
                        Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
                        ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                     
                           8 - access("A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                               filter("A"."BUSINESS_UNIT" LIKE 'I%' AND "A"."RECV_STATUS"<>'C')                                                                                                                                                                                                                                      
                           9 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                               filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                          11 - access("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                           
                               filter("C"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                          12 - access("A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                               filter("A"."BUSINESS_UNIT" LIKE 'I%' AND "A"."RECV_STATUS"<>'C')                                                                                                                                                                                                                                      
                          13 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                               filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                          16 - access("A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                               filter("A"."BUSINESS_UNIT" LIKE 'I%' AND "A"."RECV_STATUS"<>'C' AND  NOT EXISTS (SELECT                                                                                                                                                                                                               
                                      0 FROM SYSADM."PS_BUS_UNIT_LANG" "C",SYSADM."PS_BUS_UNIT_TBL_FS" "B",SYSADM."PS_RECV_HDR" "A"                                                                                                                                                                                                  
                                      WHERE "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."BUSINESS_UNIT"=:B1 AND                                                                                                                                                                                                                  
                                      "A"."RECV_STATUS"<>'C' AND "B"."BUSINESS_UNIT"=:B2 AND "B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT"                                                                                                                                                                                                 
                                      AND "C"."LANGUAGE_CD"='FRA' AND "C"."BUSINESS_UNIT"=:B3))                                                                                                                                                                                                                                      
                          19 - access("C"."BUSINESS_UNIT"=:B1 AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                                           
                          20 - access("B"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                                                       
                               filter("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                          21 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                               filter("A"."BUSINESS_UNIT"=:B1 AND "A"."RECV_STATUS"<>'C')                                                                                                                                                                                                                                            
                          22 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
                               filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
                        
                        57 rows selected
                        Thanks and regards.

                        Regards.
                        • 9. Re: query  with union does not finish
                          Jonathan Lewis
                          user522961 wrote:

                          It is a standard Oracle peoplesoft script then we prefer not to modify it but know why it does not end ?
                          That may explain why the arithmetic looks all wrong - Peoplesoft dictates a number of "unusual" parameter settings for the database, and strange optimizer problems can appear as a side effect.
                          You could test my hypothesis and, if it gives you the performance you need' you could choose to create an SQL Plan Baseline for the query so that you can get the benefit of the hints without changing the production code.
                          e.g. http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          • 10. Re: query  with union does not finish
                            Girish Sharma
                            It is a standard Oracle peoplesoft script then we prefer not to modify it but know why it does not end ?
                            Or you can test the solution provided by Sir Jonathan on a test database by either using hinted query or by creating a SQL Plan Baseline for the query.

                            Overview of SQL Plan Baselines
                            http://docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm#PFGRF95105

                            Regards
                            Girish Sharma
                            • 11. Re: query  with union does not finish
                              user522961
                              Thank you Jonathan.
                              Following your article "Fake a sql Plan baseline" can I give the Explain Plan of UNION ALL for the query having just UNION ?


                              When I run the following , it returns no data because myquery is no more in v$sql. Any solution ?
                              Thank you.

                              Edited by: user522961 on Jan 28, 2013 4:41 AM
                              • 12. Re: query  with union does not finish
                                user522961
                                Realy thank you Jonathan.