Re: EXPLAIN PLAN madness!

On Fri, 16 Nov 2001 09:49:54 GMT, nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote:

>In a sudden spasm of awareness,
>Ed Stevens doodled thusly:
>
>>
>>On both databases we find:
>> hash_area_size = 0
>> hash_join_enabled = true
>> hash_multiblock_io_count = 1
>>these are defaulted values, there is no mention of them in the
>>init.ora files. How about differences in some memory spec in the SGA?
>>
>
>block sizes different?
>
>Also, db_multiblock_read-size (or whatever its spelling is nowadays)
>makes a huge diff. So does sort_area-size and its relationship to
>other parameters such as temp tablespace extents and such. I've found
>all of those make a difference in the darn thing selecting hash joins
>or not.
>
>Because of these "undocumented" consistencies, I've now taken to
>disable the hash joins unless I absolutely have to have them. Too
>dangerous otherwise.
>
>
>Cheers
>Nuno Souto
>nsouto_at_optushome.com.au.nospam

Well, sometimes my awareness comes in spasms. Other times it simply oozes in at an excrutiatingly slow pace . .. . ;-)

Differences in sort_area parms seemed to be the difference (see my reply to Mark Townsend for more detail.) I'm curious about your desire to avoid hash joins. What is the problem with them? Below are the two plans (before I got the two databases to perform the same). As you can see, the one with the hash joins is far cheaper than the ones without . . . .

(Sorry about the line wrap . . )

OPERATION                 OPTIONS         OBJECT NAME   ORDER        OPT

------------------------- ---------------
---------------------------------------------- ------------ ------           
 SELECT STATEMENT                         COST = 5777
0-0-5777     CHOOSE             
SORT                    UNIQUE
1-0-1                         
   HASH JOIN              OUTER
2-1-1                         
    HASH JOIN             OUTER
3-2-1                         
     HASH JOIN            OUTER
4-3-1                         
      HASH JOIN           OUTER
5-4-1                         
       FILTER
6-5-1                         
        HASH JOIN         OUTER
7-6-1                         
         HASH JOIN
8-7-1                         
          TABLE ACCESS    FULL            NPP_REQUISITION
9-8-1        ANALYZ           
          REMOTE
10-8-2                        
         TABLE ACCESS     FULL            NPP_PURCHASE_ORDER
11-7-2       ANALYZ           
       TABLE ACCESS       FULL            NPP_REQUEST_FOR_QUOTE
12-5-2       ANALYZ           
      REMOTE
13-4-2                        
     REMOTE
14-3-2                        
    TABLE ACCESS          FULL            NPP_QUOTE
15-2-2       ANALYZ           

16 rows selected.

OPERATION                 OPTIONS         OBJECT NAME
ORDER        OPT              
------------------------- ---------------
----------------------------------------------- ------------ ------           
 SELECT STATEMENT                         COST = 12905
0-0-12905    CHOOSE           
  SORT                    UNIQUE
1-0-1                         
   CONCATENATION
2-1-1                         
    NESTED LOOPS          OUTER
3-2-1                         
     NESTED LOOPS         OUTER
4-3-1                         
      NESTED LOOPS        OUTER
5-4-1                         
       NESTED LOOPS       OUTER
6-5-1                         
        NESTED LOOPS      OUTER
7-6-1                         
         NESTED LOOPS
8-7-1                         
          TABLE ACCESS    FULL            NPP_REQUISITION
9-8-1        ANALYZ           
          REMOTE
10-8-2                        
         REMOTE
11-7-2                        
        REMOTE
12-6-2                        
       TABLE ACCESS       BY INDEX ROWID  NPP_REQUEST_FOR_QUOTE
13-5-2       ANALYZ           
        INDEX             UNIQUE SCAN     NPRFQ_IDX1
14-13-1      ANALYZ           
      TABLE ACCESS        BY INDEX ROWID  NPP_PURCHASE_ORDER
15-4-2       ANALYZ           
       INDEX              UNIQUE SCAN     NPPO_IDX1
16-15-1      ANALYZ           
     INDEX                RANGE SCAN      NPQTE_IDX1
17-3-2       ANALYZ           
    NESTED LOOPS          OUTER
18-2-2                        
     FILTER
19-18-1                       
      NESTED LOOPS        OUTER
20-19-1                       
       NESTED LOOPS       OUTER
21-20-1                       
        NESTED LOOPS      OUTER
22-21-1                       
         NESTED LOOPS     OUTER
23-22-1                       
          NESTED LOOPS
24-23-1                       
           TABLE ACCESS   FULL            NPP_REQUISITION
25-24-1      ANALYZ           
           REMOTE
26-24-2                       
          REMOTE
27-23-2                       
         REMOTE
28-22-2                       
        TABLE ACCESS      BY INDEX ROWID  NPP_REQUEST_FOR_QUOTE
29-21-2      ANALYZ           
         INDEX            UNIQUE SCAN     NPRFQ_IDX1
30-29-1      ANALYZ           
       TABLE ACCESS       BY INDEX ROWID  NPP_PURCHASE_ORDER
31-20-2      ANALYZ           
        INDEX             UNIQUE SCAN     NPPO_IDX1
32-31-1      ANALYZ           
     INDEX                RANGE SCAN      NPQTE_IDX1
33-18-2      ANALYZ           

34 rows selected.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)