DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 25

Thread: Query running slow in 9i after migrate

Threaded View

  1. #1
    Join Date
    Nov 2003
    Posts
    89

    Query running slow in 9i after migrate

    [SIZE=1]Hi,
    I Migrated oracle from 8.1.7.4 to 9.2.0.6.
    My queries was running slow in 9i and changed optimizer_features_enable to 8.1.7. 95% of queries are running at the same speed as 8i. I am having problem with below query.

    Code:
      SELECT   r_rcvry_handl_ofc_dsk_tmp_cod, r_rcvry_bank_accnt_num,
             r_wc_subro_cod, r_wc_setl_typ_cod, r_crgblty_ind, r_1099_cod,
             r_1099_ssn_val, r_1099_fein_val, r_cov_suit_dspstn_tmp_cod,
             r_exp_adjust_val, iss_dte, frm_dte, to_dte, rep_dte, sch_dte,
                 FROM payment_view
       WHERE EXISTS (
                SELECT 1
                  FROM usracgrp
                 WHERE usracgrp.user_id = 'ABCD'
                   AND usracgrp.asec_grp = payment_view.asec_grp)
         AND status = 'H'
         AND unq_id = '238746234'
         AND clm_sfx = '0111'
         AND acc_num = '9743359874395'
    ORDER BY unq_id, clm_sfx, grp_id
    
    
    
    
    Tk prof out put is 
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.01          0          0          0           0
    Execute      1      0.00       0.01          0          0          0           0
    Fetch        1     79.39     187.91     603869    2746238          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3     79.41     187.94     603869    2746238          0           1
    
    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 18
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  FILTER
          1   TABLE ACCESS BY INDEX ROWID CLAIM
          3    NESTED LOOPS
          1     TABLE ACCESS BY INDEX ROWID PAYMENT
    8219134      INDEX FULL SCAN PA_GRP (object id 3418)
          1     INDEX RANGE SCAN IX_HOMEPAY (object id 20577)
          1   INDEX UNIQUE SCAN UL_USR (object id 3914)
    
    =============================
    
    Explain plan out put  in 9i 
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=FIRST_ROWS		1  	 	1289192  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	PYRAMID.CLAIM	1  	47  	1  	 	      	             	 
          NESTED LOOPS		1  	455  	1289192  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PYRAMID.PAYMENT	4  	1 K	1289188  	 	      	             	 
              INDEX FULL SCAN	PYRAMID.PA_GRP	8 M	 	29212  	 	      	             	 
            INDEX RANGE SCAN	PYRAMID.IX_HOMEPAY	1  	 	1  	 	      	             	 
        INDEX UNIQUE SCAN	PYRAMID.UL_USR	1  	16  	1  	 	      	             	 
    ================================
    Explain plan output in 8i
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=FIRST_ROWS		1  	 	16  	 	      	             	 
      SORT ORDER BY		1  	454  	16  	 	      	             	 
        FILTER		  	 	 	 	      	             	 
          NESTED LOOPS		1  	454  	6  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PYRAMID.PAYMENT	3  	1 K	3  	 	      	             	 
              INDEX RANGE SCAN	PYRAMID.NEW_PA_CLM	3  	 	4  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PYRAMID.CLAIM	1  	47  	1  	 	      	             	 
              INDEX RANGE SCAN	PYRAMID.CL_COV_MAJ	1  	 	1  	 	      	             	 
          INDEX UNIQUE SCAN	PYRAMID.UL_USR	1  	16  	1
    Kindly help what to do.
    Last edited by srt; 01-12-2006 at 05:17 PM.

Posting Permissions

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


Click Here to Expand Forum to Full Width