This discussion is archived
12 Replies Latest reply: Feb 1, 2013 8:15 AM by user522961 RSS

query  with union does not finish

user522961 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    what is the difference in plan with UNION and UNION ALL?
  • 2. Re: query  with union does not finish
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Realy thank you Jonathan.

Legend

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