Results 1 to 7 of 7

Thread: sql query tuning

  1. #1
    Junior Member
    Join Date
    Apr 2008
    Answers
    3

    Smile 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.

    Last edited by srlsrao; 04-16-2008 at 05:01 AM.

  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: sql query tuning

    Are you sure there is some data that matches your where condition ?

    Are you getting any error from your query ?


  3. #3
    Junior Member
    Join Date
    Apr 2008
    Answers
    3

    Re: sql query tuning

    Hi I am getting result from this query. there is no errors in the query. But it is getting result from View and 3 tables. So it's taking 45 mins to retrieve the data.So i need to tune this query...


  4. #4
    Junior Member
    Join Date
    Apr 2008
    Answers
    3

    Re: sql query tuning

    Quote Originally Posted by debasisdas View Post
    Are you sure there is some data that matches your where condition ?

    Are you getting any error from your query ?


    I have used /*+leading(vcs)*/ in the query . Then also it's taking same time as before. is there any other way to resolve this issue?

    Thank u


  5. #5
    Junior Member
    Join Date
    Mar 2009
    Answers
    28

    Re: sql query tuning

    try outer join if necessory in the query


  6. #6

    Re: sql query tuning

    Hi Rao
    Did you check the index that beeing used in the query? Please let me know


  7. #7
    Junior Member
    Join Date
    Apr 2009
    Answers
    1

    Re: sql query tuning

    hi try this query. Kindly place the mandatory condition as the first statement in the where clause. And create the necessary index.
    And most important thing is to replace the view. U have select only case size for particular item. So u have to add the particular column of the table in your query.

    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.item_level = mas.trns_level

    AND im.location =wh.warehose_no
    AND mas.item = im.item
    AND mas.item = cs.item
    AND im.location_type = 'S'
    and mas.pack_ind = 'N'
    AND cs.supply_indicator = 'Y'
    AND cs.country_indicator = 'Y'
    AND mas.status = 'A'
    AND mas.foreind = 'Y'
    AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact