1 Reply Latest reply: Dec 11, 2012 11:30 AM by L-MachineGun RSS

    Performance issues while query data from a table having large records

    mhosur
      Hi all,
      I have a performance issues on the queries on mtl_transaction_accounts table which has around 48,000,000 rows. One of the query is as below
      SQL ID: 98pqcjwuhf0y6 Plan Hash: 3227911261
      
      SELECT SUM (B.BASE_TRANSACTION_VALUE) 
      FROM
       MTL_TRANSACTION_ACCOUNTS B , MTL_PARAMETERS A   
      WHERE A.ORGANIZATION_ID =    B.ORGANIZATION_ID  
      AND A.ORGANIZATION_ID =  :b1  
      AND B.REFERENCE_ACCOUNT =    A.MATERIAL_ACCOUNT  
      AND B.TRANSACTION_DATE <=  LAST_DAY (TO_DATE (:b2 ,   'MON-YY' )  )   
      AND B.ACCOUNTING_LINE_TYPE !=  15   
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          0          0           0
      Execute      3      0.02       0.05          0          0          0           0
      Fetch        3    134.74     722.82     847951    1003824          0           2
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        7    134.76     722.87     847951    1003824          0           2
      
      Misses in library cache during parse: 1
      Misses in library cache during execute: 2
      Optimizer mode: ALL_ROWS
      Parsing user id: 193  (APPS)
      Number of plan statistics captured: 1
      
      Rows (1st) Rows (avg) Rows (max)  Row Source Operation
      ---------- ---------- ----------  ---------------------------------------------------
               1          1          1  SORT AGGREGATE (cr=469496 pr=397503 pw=0 time=237575841 us)
          788242     788242     788242   NESTED LOOPS  (cr=469496 pr=397503 pw=0 time=337519154 us cost=644 size=5920 card=160)
               1          1          1    TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=2 pr=0 pw=0 time=59 us cost=1 size=10 card=1)
               1          1          1     INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=1 pr=0 pw=0 time=40 us cost=0 size=0 card=1)(object id 181399)
          788242     788242     788242    TABLE ACCESS BY INDEX ROWID MTL_TRANSACTION_ACCOUNTS (cr=469494 pr=397503 pw=0 time=336447304 us cost=643 size=4320 card=160)
         8704356    8704356    8704356     INDEX RANGE SCAN MTL_TRANSACTION_ACCOUNTS_N3 (cr=28826 pr=28826 pw=0 time=27109752 us cost=28 size=0 card=7316)(object id 181802)
      
      
      Rows     Execution Plan
      -------  ---------------------------------------------------
            0  SELECT STATEMENT   MODE: ALL_ROWS
            1   SORT (AGGREGATE)
       788242    NESTED LOOPS
            1     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                      'MTL_PARAMETERS' (TABLE)
            1      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                       'MTL_PARAMETERS_U1' (INDEX (UNIQUE))
       788242     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                      'MTL_TRANSACTION_ACCOUNTS' (TABLE)
      8704356      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'MTL_TRANSACTION_ACCOUNTS_N3' (INDEX)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        row cache lock                                 29        0.00          0.02
        SQL*Net message to client                       2        0.00          0.00
        db file sequential read                    847951        0.40        581.90
        latch: object queue header operation            3        0.00          0.00
        latch: gc element                              14        0.00          0.00
        gc cr grant 2-way                               3        0.00          0.00
        latch: gcs resource hash                        1        0.00          0.00
        SQL*Net message from client                     2        0.00          0.00
        gc current block 3-way                          1        0.00          0.00
      ********************************************************************************
      On a 5 node rac environment the program completes in 15 hours whereas on a single node environemnt the program completes in 2 hours.
      Is there any way I can improve the performance of this query?

      Regards

      Edited by: mhosur on Dec 10, 2012 2:41 AM

      Edited by: mhosur on Dec 10, 2012 2:59 AM

      Edited by: mhosur on Dec 11, 2012 10:32 PM
        • 1. Re: Performance issues while query data from a table having large records
          L-MachineGun
          CREATE INDEX mtl_transaction_accounts_n0
            ON mtl_transaction_accounts (
                                         transaction_date 
                                       , organization_id
                                       , reference_account
                                       , accounting_line_type
                                        )
          /
          :p