This discussion is archived
1 Reply Latest reply: Dec 11, 2012 9:30 AM by L-MachineGun RSS

Performance issues while query data from a table having large records

mhosur Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    CREATE INDEX mtl_transaction_accounts_n0
      ON mtl_transaction_accounts (
                                   transaction_date 
                                 , organization_id
                                 , reference_account
                                 , accounting_line_type
                                  )
    /
    :p

Legend

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