Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Red face Unanswered: How to increase the performance of this query??

    Hi All,
    Can any one help me in increasing the speed of this query?? It is taking more than two hours to fetch 334579 much records.

    Query:
    -------------
    Code:
     SELECT /*+ index(dd FA_DEPRN_DETAIL_U1) */
              dh.asset_id,
              cb.category_id,
              dh.location_id,
                 'YTD',
                 dh.code_combination_id,
                 nvl(dd.cost,0),
                 dh.units_assigned + nvl(dh.TRANSACTION_UNITS, 0),
              nvl(dd.deprn_amount,0),
              dd.period_counter
    --        nvl(dd.YTD_DEPRN,0)
            FROM fa.fa_distribution_history dh,
                 fa.fa_deprn_detail         dd,
                 fa.fa_asset_history        ah,
                 fa.fa_category_books       cb,
                 fa.fa_books                bk,
            fa.fa_deprn_periods      fdp
           WHERE dh.asset_id = NVL(p_asset_id, dh.asset_id)
             AND dh.book_type_code = p_asset_book
             AND dd.asset_id = dh.asset_id
             AND dd.book_type_code = dh.book_type_code
             AND dd.distribution_id = dh.distribution_id
             AND dd.period_counter <= p_period_counter
           AND fdp.period_counter = p_period_counter
           AND fdp.book_type_code = bk.book_type_code
             AND ah.asset_id = dh.asset_id
             AND ah.asset_type != 'EXPENSED'
             AND cb.category_id = ah.category_id
             AND cb.book_type_code = dd.book_type_code
             AND bk.book_type_code = cb.book_type_code
             AND bk.asset_id = dd.asset_id
           AND bk.transaction_header_id_in =
                (SELECT MAX (ifb.transaction_header_id_in)
                   FROM fa.fa_books ifb
                  WHERE ifb.book_type_code = bk.book_type_code
                    AND ifb.asset_id = bk.asset_id
                    AND ifb.date_effective < NVL (fdp.period_close_date, SYSDATE))
           AND   dh.transaction_header_id_in >= ah.transaction_header_id_in
             AND   dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
             AND NVl(bk.period_counter_fully_retired,
                     p_period_counter + 1) > p_period_counter_first + 1
          AND dd.period_counter  between p_period_counter_first + 1 and p_period_counter
             AND DECODE(ah.asset_type,
                        'CAPITALIZED',
                        cb.asset_cost_acct,
                        NULL) IS NOT NULL
             -----------------------------------------------------------------------
             AND TO_CHAR(dh.asset_id) LIKE NVL(to_char(p_asset_id),'%')
             -----------------------------------------------------------------------
            ;
    Explain Plan:
    ------------
    Code:
    SELECT STATEMENT  ALL_ROWSCost: 1,177  Bytes: 209  Cardinality: 1  									
    	21 FILTER  								
    		17 NESTED LOOPS  							
    			15 NESTED LOOPS  Cost: 1,170  Bytes: 209  Cardinality: 1  						
    				13 NESTED LOOPS  Cost: 1,166  Bytes: 173  Cardinality: 1  					
    					10 NESTED LOOPS  Cost: 1,164  Bytes: 120  Cardinality: 1  				
    						7 MERGE JOIN CARTESIAN  Cost: 1,163  Bytes: 99  Cardinality: 1  			
    							4 NESTED LOOPS  Cost: 946  Bytes: 60  Cardinality: 1  		
    								2 TABLE ACCESS BY INDEX ROWID TABLE FA.FA_DEPRN_PERIODS Cost: 1  Bytes: 25  Cardinality: 1  	
    									1 INDEX UNIQUE SCAN INDEX (UNIQUE) FA.FA_DEPRN_PERIODS_U3 Cost: 0  Cardinality: 1  
    								3 TABLE ACCESS FULL TABLE FA.FA_BOOKS Cost: 945  Bytes: 35  Cardinality: 1  	
    							6 BUFFER SORT  Cost: 218  Bytes: 3,446,664  Cardinality: 88,376  		
    								5 TABLE ACCESS FULL TABLE FA.FA_ASSET_HISTORY Cost: 217  Bytes: 3,446,664  Cardinality: 88,376  	
    						9 TABLE ACCESS BY INDEX ROWID TABLE FA.FA_CATEGORY_BOOKS Cost: 1  Bytes: 21  Cardinality: 1  			
    							8 INDEX UNIQUE SCAN INDEX (UNIQUE) FA.FA_CATEGORY_BOOKS_U1 Cost: 0  Cardinality: 1  		
    					12 TABLE ACCESS BY INDEX ROWID TABLE FA.FA_DISTRIBUTION_HISTORY Cost: 2  Bytes: 53  Cardinality: 1  				
    						11 INDEX RANGE SCAN INDEX FA.FA_DISTRIBUTION_HISTORY_N2 Cost: 1  Cardinality: 1  			
    				14 INDEX RANGE SCAN INDEX (UNIQUE) FA.FA_DEPRN_DETAIL_U1 Cost: 2  Cardinality: 2  					
    			16 TABLE ACCESS BY INDEX ROWID TABLE FA.FA_DEPRN_DETAIL Cost: 4  Bytes: 36  Cardinality: 1  						
    		20 SORT AGGREGATE  Bytes: 30  Cardinality: 1  							
    			19 TABLE ACCESS BY INDEX ROWID TABLE FA.FA_BOOKS Cost: 7  Bytes: 30  Cardinality: 1  						
    				18 INDEX SKIP SCAN INDEX FA.FA_BOOKS_U2 Cost: 6  Cardinality: 1
    Last edited by andrewst; 04-20-11 at 07:51.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Please edit your post and use [code] tags to make it readable.

    Details on the usage of the tags are here:
    http://www.dbforums.com/misc.php?do=bbcode

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What indexes do you have on FA.FA_ASSET_HISTORY?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •