This discussion is archived
4 Replies Latest reply: Aug 31, 2013 5:03 AM by user5690975 RSS

Query is slow

user5690975 Newbie
Currently Being Moderated

SELECT SUM(A.NO_MONTH_CONSUMPTION),SUM(A.BASE_CONSUMPTION),SUM(A.CURRENT_DOC_AMT),SUM(A.CUR_TAX),SUM(B.CURRENT_DOC_AMT)
FROM VW_x A,(SELECT CURRENT_DOC_AMT,DOC_NO
FROM VW_y B
WHERE NVL(B.VOID_STATUS,0)=0 AND B.TR_TYPE_CODE='SW' AND B.BPREF_NO=:B4 AND B.SERVICE_CODE=:B3 AND B.BIZ_PART_CODE=:B2 AND B.CONS_CODE=:B1 ) B
WHERE A.BPREF_NO=:B4 AND A.SERVICE_CODE=:B3 AND A.BIZ_PART_CODE=:B2 AND A.CONS_CODE=:B1 AND A.BILL_MONTH >:B5 AND NVL(A.VOID_STATUS,0)=0 AND NVL(A.AVG_IND,0)= 2 AND A.DOC_NO=B.DOC_NO(+)


the above view "VW_x" has around 40 million records from two tables and avg_ind column has only 0 and 2 values. I created a functional index on both table something like create index on x1 nvl(avg,0)

 

TRACE OUT PUT

 

STATISTICS

15  recursive calls

0  db block gets

  18  consistent gets

4  physical reads

0  redo size

357 bytes sent via SQL*Net to client

252 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

 

 

 

but still the query is slow...please suggest the best practise to make it fast

 

 

thanks

  • 1. Re: Query is slow
    InoL Guru
    Currently Being Moderated

    >please suggest the best practise


    Best practice is to post a question in the appropriate forum. This is the Oracle Forms forum. Go to the database forum for your question.

  • 2. Re: Query is slow
    user5690975 Newbie
    Currently Being Moderated

    iam new to this site please give me the url for the db

  • 3. Re: Query is slow
    Soofi Explorer
    Currently Being Moderated

    Hi,

     

    https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

     

    Post Your Query related questions here.

     

     

     

    Regards,

    Soofi

  • 4. Re: Query is slow
    user5690975 Newbie
    Currently Being Moderated

    Hi sorry i was out of office for a while please check the execution plan for my query.

     

    Below query i am calling in a procedure passing the parameters

     

      While i execute the query separatly it works fine but the same thing when i call in procedure and the procedure has loop which goes and check around 400,000 records thats where i get the problem

     

     

    select sum(a.no_month_consumption),sum(a.base_consumption),sum(a.current_doc_amt),sum(a.cur_tax),sum(b.current_doc_amt

     

    )

     

     

     

    --into vnomonths,vcons,vconsamt,vtaxamt,vsewage

     

     

    from bill_View a,(select current_doc_amt,doc_no from dbcr_View b where nvl(b.void_status,0)=0 and b.tr_type_code='SWGDBG' and b.bpref_no='Q12345' and b.service_code='E' and b.biz_part_code='MHEW') b

    where a.bpref_no='Q12345' and a.service_code='E' and a.biz_part_code='MHEW'

    and a.bill_month >'30-aPR-2011' and nvl(a.void_status,0)=0 and decode(a.avg_ind,null,0,a.avg_ind)= 2

     

    and a.doc_no=b.doc_no(+);

     

     

     

     

     

     

     

     

    I created functionaly inde on avg_ind column (nvl(avg_ind,0))

     

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=77 Card=1 Bytes=93
              )

       1    0   SORT (AGGREGATE)
       2    1     HASH JOIN (OUTER) (Cost=77 Card=4 Bytes=372)
       3    2       VIEW OF 'VW_IBS_BILL' (VIEW) (Cost=54 Card=3 Bytes=198
              )

       4    3         UNION-ALL
       5    4           TABLE ACCESS (BY INDEX ROWID) OF 'IBS_S_T_BILL' (T
              ABLE) (Cost=8 Card=1 Bytes=50)

       6    5             INDEX (RANGE SCAN) OF 'STBILL_BPREF_NO' (INDEX)
              (Cost=3 Card=5)

       7    4           TABLE ACCESS (BY INDEX ROWID) OF 'IBS_X_T_BILL' (T
              ABLE) (Cost=46 Card=2 Bytes=114)

       8    7             INDEX (RANGE SCAN) OF 'XTBILL' (INDEX) (Cost=3 C
              ard=43)

       9    2       VIEW OF 'VW_IBS_DBCR' (VIEW) (Cost=22 Card=4 Bytes=108
              )

      10    9         UNION-ALL
      11   10           TABLE ACCESS (BY INDEX ROWID) OF 'IBS_T_DBCR' (TAB
              LE) (Cost=2 Card=1 Bytes=54)

      12   11             INDEX (RANGE SCAN) OF 'TDBCR_BPREFNO' (INDEX) (C
              ost=1 Card=1)

      13   10           TABLE ACCESS (BY INDEX ROWID) OF 'IBS_S_T_DBCR' (T
              ABLE) (Cost=7 Card=1 Bytes=43)

      14   13             INDEX (RANGE SCAN) OF 'STDBCR_BPREFNO' (INDEX) (
              Cost=3 Card=4)

      15   10           TABLE ACCESS (BY INDEX ROWID) OF 'IBS_X_T_DBCR' (T
              ABLE) (Cost=13 Card=2 Bytes=88)

      16   15             INDEX (RANGE SCAN) OF 'XTDBCR' (INDEX) (Cost=3 C
              ard=11)

     

    what is Card and Cost attributes in the above output..................... ?

     

Legend

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