Sql Query Tuning

[mail.madhankumar]

Hi Tim,
i am facing problem with below query . it's taking more time.. i want to reduce it. it returns more record from those tables, so index is not useful here.. wat should i do if table contain more records and query returns almost 70% of records from those tables .. with join conditions.. wat method should we approch here.. my production is 11g std edition.. otherwise i would have tried query rewrite materialized view . . below query is written on procedure with dynamic sql. even i tried using bind variable for litterals.. but not that much improvement. please tell me your suggestions.

FS_AC_VOUCHERMASTER - 546707 records
FS_AC_VOUCHERDTL - 1515425 records
FS_AC_ACCTMASTER - 793 records.

Code: Select all

 SELECT VM.VOUCHERTYPE, VM.BRANCH_ID, VM.VOUCHERNUMBER, VM.VOUCHERDATE,
          NVL(VD.DEBITAMOUNT, 0) DEBITAMT,
          NVL(VD.CREDITAMOUNT, 0) CREDITAMT, VM.BOOKID,
          VD.BANKCASHPARTYCODEIND, VM.SERIAL_NO, VM.DEPT_ID,
          (CASE WHEN VM.VOUCHERTYPE IN ('SALES INVOICE')THEN
              (SELECT TRN.INVOICE_NOTES
                  FROM FS_AC_TRANSACTION TRN
             WHERE TRN.TRANSACTION_ID = VM.VOUCHERNUMBER)
             ELSE
              VM.NARRATION
              END) NARRATION,
          VM.CONVFACTOR, VM.PARTY_ID, VM.FCURRENCYID,
          DECODE(VD.DEBITAMOUNT, 0, 'PMT', 'INV') ETYPE, VM.ENTRY_TYPE,
          ACM.ACCTID, ACM.ACCTNAME, VM.REV_SERIAL_NO, VM.CHEQUEDDNO,
          VD.ACCTCODE,VD.DEBITAMOUNT - VD.CREDITAMOUNT,
          (CASE
            WHEN (VM.PARTY_ID IS NULL OR VM.VOUCHERTYPE IN ('CREDIT NOTE','DEBIT NOTE')) THEN
             (SELECT TB.TRANSACTION_ID
              FROM FS_AC_TRANSACTION_BANK TB
              WHERE TB.TRANS_SERIAL_NO = VM.SERIAL_NO
                    AND ROWNUM = 1)
            ELSE
             VM.VOUCHERNUMBER
          END), VM.VOUCHERDATE,
          (SELECT VTM.VOUCHERID
            FROM FS_AC_VOUCHERTYPESMASTER VTM
            WHERE VTM.VOUCHERTYPE = VM.VOUCHERTYPE
                  AND VTM.BOOKID = VM.BOOKID),
          (SELECT CINFO.ACCTYEAR
            FROM FS_AC_COMPANYINFO CINFO
            WHERE CINFO.BOOKID = VM.BOOKID),
          (SELECT BP.DCS_PARTY_NAME
            FROM ET_BUSINESS_PARTY BP
            WHERE BP.DCS_CODE = VM.PARTY_ID),
          (SELECT TRN.EXTERNAL_REFERENCE
            FROM FS_AC_TRANSACTION TRN
            WHERE TRN.TRANSACTION_ID = VM.VOUCHERNUMBER),
           'AR',
          (CASE
            WHEN (VD.ACCTCODE LIKE '%AR_____' AND
                 (VD.DEBITAMOUNT - VD.CREDITAMOUNT) >= 0) THEN
             VD.DEBITAMOUNT
            WHEN (VD.ACCTCODE LIKE '%AR_____' AND
                 (VD.DEBITAMOUNT - VD.CREDITAMOUNT) < 0) THEN
             VD.CREDITAMOUNT * -1
          END),
          (CASE
            WHEN (VD.ACCTCODE LIKE '%AR_____' AND
                 (VD.DEBITAMOUNT - VD.CREDITAMOUNT) >= 0) THEN
             VD.FCURRAMOUNT
            WHEN (VD.ACCTCODE LIKE '%AR_____' AND
                 (VD.DEBITAMOUNT - VD.CREDITAMOUNT) < 0) THEN
             VD.FCURRAMOUNT * -1
          END), 1,
          (NVL((NVL(VD.DEBITAMOUNT, 0) - NVL(VD.CREDITAMOUNT, 0)), 0) *
           NVL(1, 0))
   FROM FS_AC_VOUCHERMASTER VM, FS_AC_VOUCHERDTL VD, FS_AC_ACCTMASTER ACM
   WHERE VM.SERIAL_NO = VD.SERIAL_NO
         AND ACM.ACCTCODE = VD.ACCTCODE
         AND VM.APPROVALSTATUS = 'Y'
         and NVL(VM.ENTRY_TYPE, 'X') not in ('CAN')
         and VD.ACCTCODE like '%AR_____'
         and VM.COMPANY_ID in ('FJL')and TRUNC(VM.VOUCHERDATE) between '01-APR-12' and '25-OCT-12'


Code: Select all

Execution Plan
----------------------------------------------------------
Plan hash value: 2065049794

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |  2958 |   693K|  7123   (2)| 00:01:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | FS_AC_TRANSACTION         |     1 |    19 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | FS_AC_TRANSACTION_IDX_001 |     1 |       |     3   (0)| 00:00:01 |
|*  3 |  COUNT STOPKEY                 |                           |       |       |            |          |
|*  4 |   INDEX RANGE SCAN             | IX_FS_AC_TRANSACTION_BANK |     1 |    21 |     2   (0)| 00:00:01 |
|   5 |  TABLE ACCESS BY INDEX ROWID   | FS_AC_VOUCHERTYPESMASTER  |     1 |    24 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN            | PK_VT_BOOK                |     1 |       |     0   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID   | FS_AC_COMPANYINFO         |     1 |    14 |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN            | PK_COM_BOOK               |     1 |       |     0   (0)| 00:00:01 |
|   9 |  TABLE ACCESS BY INDEX ROWID   | ET_BUSINESS_PARTY         |     1 |    36 |     2   (0)| 00:00:01 |
|* 10 |   INDEX UNIQUE SCAN            | ET_BUS_PAR_PK1            |     1 |       |     1   (0)| 00:00:01 |
|  11 |  TABLE ACCESS BY INDEX ROWID   | FS_AC_TRANSACTION         |     1 |    27 |     4   (0)| 00:00:01 |
|* 12 |   INDEX RANGE SCAN             | FS_AC_TRANSACTION_IDX_001 |     1 |       |     3   (0)| 00:00:01 |
|* 13 |  FILTER                        |                           |       |       |            |          |
|* 14 |   HASH JOIN                    |                           |  2958 |   693K|  7123   (2)| 00:01:26 |
|  15 |    NESTED LOOPS                |                           |       |       |            |          |
|  16 |     NESTED LOOPS               |                           |  2958 |   268K|  2386   (1)| 00:00:29 |
|* 17 |      INDEX FAST FULL SCAN      | FS_AC_ACCTMASTER_IDX_013  |    40 |  2240 |     4   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN          | FS_AC_VDTL_ACCODE         |   103 |       |    16   (0)| 00:00:01 |
|  19 |     TABLE ACCESS BY INDEX ROWID| FS_AC_VOUCHERDTL          |    75 |  2775 |    63   (0)| 00:00:01 |
|* 20 |    TABLE ACCESS FULL           | FS_AC_VOUCHERMASTER       |   484K|    67M|  4734   (2)| 00:00:57 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TRN"."TRANSACTION_ID"=:B1)
   3 - filter(ROWNUM=1)
   4 - access("TB"."TRANS_SERIAL_NO"=:B1)
   6 - access("VTM"."VOUCHERTYPE"=:B1 AND "VTM"."BOOKID"=:B2)
   8 - access("CINFO"."BOOKID"=:B1)
  10 - access("BP"."DCS_CODE"=:B1)
  12 - access("TRN"."TRANSACTION_ID"=:B1)
  13 - filter(TO_DATE('01-APR-12')<=TO_DATE('25-OCT-12'))
  14 - access("SYS_ALIAS_7"."SERIAL_NO"="VD"."SERIAL_NO")
  17 - filter("ACM"."ACCTCODE" LIKE '%AR_____')
  18 - access("ACM"."ACCTCODE"="VD"."ACCTCODE")
       filter("VD"."ACCTCODE" LIKE '%AR_____')
  20 - filter(TRUNC(INTERNAL_FUNCTION("VOUCHERDATE"))>='01-APR-12' AND
              "SYS_ALIAS_7"."APPROVALSTATUS"='Y' AND "SYS_ALIAS_7"."COMPANY_ID"='FJL' AND
              NVL("ENTRY_TYPE",'X')<>'CAN' AND TRUNC(INTERNAL_FUNCTION("VOUCHERDATE"))<='25-OCT-12')


Statistics
----------------------------------------------------------
       1441  recursive calls
          0  db block gets
     575896  consistent gets
      40042  physical reads
          0  redo size
   29421251  bytes sent via SQL*Net to client
     105716  bytes received via SQL*Net from client
       9565  SQL*Net roundtrips to/from client
        334  sorts (memory)
          0  sorts (disk)
     143458  rows processed



[Tim...]

Hi.

For a start, some of the queries in the select list look like they should really be regular joins in the FROM clause, not scalar subqueries in the select list. Typically, regular joins are more efficient than scalar subqueries. These look like prime candidates:

Code: Select all

         (SELECT VTM.VOUCHERID
            FROM FS_AC_VOUCHERTYPESMASTER VTM
            WHERE VTM.VOUCHERTYPE = VM.VOUCHERTYPE
                  AND VTM.BOOKID = VM.BOOKID),
          (SELECT CINFO.ACCTYEAR
            FROM FS_AC_COMPANYINFO CINFO
            WHERE CINFO.BOOKID = VM.BOOKID),
          (SELECT BP.DCS_PARTY_NAME
            FROM ET_BUSINESS_PARTY BP
            WHERE BP.DCS_CODE = VM.PARTY_ID),
          (SELECT TRN.EXTERNAL_REFERENCE
            FROM FS_AC_TRANSACTION TRN
            WHERE TRN.TRANSACTION_ID = VM.VOUCHERNUMBER),
           'AR',

It's possible the subqueries in the case statements could be joins also, but that depends on if the presence of the data is dependent on the conditions in the case statement.

I suggest you try moving the obvious scalar subqueries into regular joins and see if this affect the performance. When they are done as a scalar subquery, the query will be run for each row brought back from the outer query. This is extremely inefficient compared to a join.

Cheers

Tim...

[mail.madhankumar]

Hi,
i checked those record count..
FS_AC_VOUCHERTYPESMASTER -16.
FS_AC_COMPANYINFO - 1
ET_BUSINESS_PARTY - 40344
FS_AC_TRANSACTION- 139050

i had checked in explain plan, there was no problem with in select statement.. that's why i didn't concentrate on these statement. if we need to rewrite means can write using left join for that. i will try to rewrite it and update here...what can we do such a situation like only problem with joined tables in from clause.. due to huge data.. except partition,materialized view like that concept... we need to improve query level for huge data return as result set.. i don't think that much huge data i have ... it's maximum is 130000 records... but there is not much filter condition... it return almost all record from table.. in this situation what can we do...?

Regards,
Madhan

[Tim...]

Hi.

If you have no filter condition, then there is nothing you can do to improve performance of that specific fetch operation, since you are going to full table scan and that will take as long as it takes to return all the rows from the table, but that just compounds the effect of the scalar subqueries, since they will all be run once for each row returned. If there are 130000 rows returned, then each scalar subquery will be run 130000 times. Even if they are very efficient, this is still a lot of work. It is far better to do a regular join which will happen once.

Which join to use? It depends on the outcome you expect. If the data will always be present it's just JOIN. If there is a chance no data will be returned, then you need an OUTER join. The choice of LEFT or RIGHT depends on which way round you write the statement.

http://www.oracle-base.com/articles/9i/ ... .php#Joins

Cheers

Tim...