query performance is very slow
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: query performance is very slow

  1. #1
    Join Date
    Mar 2006
    Posts
    1

    query performance is very slow

    select pn.produce_name,produce_grade,pn.produce_code,a.mpproducecode,p.UOMID, pn.catcode,pm.catname ,a.mpmodalprice,
    round( sum(p.AMOUNT_REC) / sum(p.QTY_ALLOTED*pq.wtperpack),2)prev_wtavg2
    from
    safal.tb_produce_name pn, safal.tb_packuomqty_master pq , safal.tb_prodcat_master pm , safal.tbl_au_postauct_details p,
    mis.tb_misproducedetail a
    where
    p.au_date between TO_DATE('1-FEB-2006','DD-MON-YYYY') and TO_DATE('1-FEB-2006','DD-MON-YYYY')
    and pn.catcode=pm.catcode and pn.produce_code=p.itemid and pq.pkid=p.pk_id and p.uom=auom and
    pn.produce_code=a.mpproducecode
    and pn.catcode='104'
    group by pm.catname,pn.catcode,produce_name,produce_grade,p.uomid,a.mpmodalprice,pn.produce_code,a.mpproducec ode


    1. i have taken the explain plan for the above query,
    this query is taking very long time to run and i have created index on some columns but even then indexes r not used in this query and query performance is not increased.i just want to know even thougn i created indexes why it is going for full table scan and why indexes r not used.pls clarify me on this.

    2. how to force the index through Hints for the above query pls let me know about it.


    explain plan statistics

    SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=8278 Bytes=836078)
    SORT* (GROUP BY) (Cost=90 Card=8278 Bytes=836078)
    HASH JOIN* (Cost=68 Card=8278 Bytes=836078)
    HASH JOIN* (Cost=67 Card=780 Bytes=69420)
    TABLE ACCESS* (FULL) OF TB_PRODUCE_NAME (Cost=1 Card=159 Bytes=6201)
    HASH JOIN* (Cost=66 Card=3245 Bytes=162250)
    NESTED LOOPS* (Cost=2 Card=2381 Bytes=42858)
    TABLE ACCESS* (BY INDEX ROWID) OF TB_PRODCAT_MASTER (Cost=1 Card=1 Bytes=10)
    INDEX (UNIQUE SCAN) OF PK_PRODCATMASTER (UNIQUE)
    TABLE ACCESS* (FULL) OF TB_PACKUOMQTY_MASTER (Cost=1 Card=2381 Bytes=19048)
    TABLE ACCESS* (FULL) OF TBL_AU_POSTAUCT_DETAILS (Cost=64 Card=586 Bytes=18752)
    TABLE ACCESS* (FULL) OF TB_MISPRODUCEDETAIL (Cost=1 Card=3638 Bytes=43656)

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    try posting it again properly using the [ code ] tages, impossible to read otherwise

  3. #3
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Hi Balkrishna,

    If you have all the tables regulerly analyzed, the CBO will use best execution path,

    Still if you want to use any index hint, or join mechanism like HASH JOIN or Nested loop you can keep hints like this.

    For Index use /*+ index(index_name) */
    For specified join /*+ use_hash(table1,table2) */ for hash join
    or /*+ use_nl(table1,table2) */

    Dilip.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width