4 Replies Latest reply: Aug 31, 2013 7:03 AM by user5690975 RSS

    Query is slow

    user5690975

      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

          >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

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

            • 3. Re: Query is slow
              Soofi

              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

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