Thread: sql query tuning

Hi , I am using Oracle 9.0.4.0.
my issue i have a quey as follows. it's taking 45 mins time. Please help me to increase the performance.I have done Explain Plan and stats.But still I am getting the same problem. Can you please tell me how to use HINTS or any other way to solve this issue.

Code:

SELECT      im.location       ,      
                im.item           ,
                cs.case_size     ,
                im.ondate         ,
                im.offdate        , 
                mas.status                       
FROM  case_size       cs, 
         item               im,                          
         master            mas,                   
         warehouse       wh             
WHERE mas.pack_ind                   = 'N'               
AND     mas.item_level                  = mas.trns_level               
AND     mas.status                       = 'A'               
AND    mas.foreind                        = 'Y'               
AND     mas.item                          = im.item               
AND     im.location_type                   = 'S'               
AND    mas.item                            = cs.item               
AND    cs.supply_indicator             = 'Y'               
AND    cs.country_indicator            = 'Y'               
AND    im.location =wh.warehose_no               
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')

here CASE is the view name and remaining are tablenames.
the execution plan is as follows

Code:

Execution Plan
----------------------------------------------------------   
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15085 Card=1139512 Bytes=134462416)   
1    0   HASH JOIN (Cost=15085 Card=1139512 Bytes=134462416)   
2    1     HASH JOIN (Cost=580 Card=30772 Bytes=1569372)   
3    2       TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=1306 Bytes=18284)   
4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ITEM' (Cost=2 Card=105311 Bytes=3369952)   
5    4         NESTED LOOPS (Cost=86 Card=4296685 Bytes=158977345)   
6    5           TABLE ACCESS (FULL) OF 'WAREHOUSE' (Cost=4 Card=41 Bytes=205)   
7    5           INDEX (RANGE SCAN) OF 'PK_LOCATION' (UNIQUE) (Cost=1 Card=210622)   
8    1     VIEW (Cost=14271 Card=48399 Bytes=3242733)   
9    8       SORT (UNIQUE) (Cost=14271 Card=48399 Bytes=6098274)  
10    9         HASH JOIN (Cost=992 Card=187614 Bytes=23639364)  
11   10           HASH JOIN (Cost=186 Card=7449 Bytes=581022)  
12   11             TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=10451 Bytes=156765)  
13   11             HASH JOIN (Cost=105 Card=12489 Bytes=786807)  
14   13               MERGE JOIN (CARTESIAN) (Cost=40 Card=12489 Bytes=549516)  
15   14                 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=13)  
16   15                   TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=3)  
17   15                   BUFFER (SORT) (Cost=3 Card=1 Bytes=10)  
18   17                     TABLE ACCESS (FULL) OF 'SYSTEM'          (Cost=3 Card=1 Bytes=10)  
19   14                 BUFFER (SORT) (Cost=37 Card=12489 Bytes=387159)  
20   19                   TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY'           (Cost=34 Card=12489  
                                 Bytes=387159)  
21   13               TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=28 Card=24989 Bytes=474791)  
22   10           VIEW (Cost=536 Card=172449 Bytes=8277552)  
23   22             UNION-ALL  
24   23               INDEX (FAST FULL SCAN) OF 'PK_ITEM_SUPPLIER_COUNTRY' (UNIQUE) (Cost=11 Card=
                              24978 Bytes=324714)  
25   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
                               399648)  
26   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
                               374670)  
27   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
                               499560)  
28   23               VIEW (Cost=141 Card=24179 Bytes=1039697)  
29   28                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)  
30   29                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 
                                Bytes=507759)  
31   23               VIEW (Cost=141 Card=24179 Bytes=1039697)  
32   31                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)  
33   32                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 
                                       Bytes=507759)  
34   23               VIEW (Cost=141 Card=24179 Bytes=1039697)  
35   34                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)  
36   35                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 
                                        Bytes=507759)

after that I have done

Code:

exec dbms_stats.gather_table_stats('owner_name','table_name',cascade=>true);

but I am not getting the result.

Thank you.