Query running slow in 9i after migrate
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Query running slow in 9i after migrate

  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 04:17 PM.

  2. #2
    Join Date
    Sep 2005
    Location
    Philippines
    Posts
    21
    just a suggestion...

    upon migration to anonther schema
    using export and import utility

    i always run the dbms_utility.analyze_schema
    to compute for the table and index statistics after the import of data.

    hope this helps...

  3. #3
    Join Date
    Nov 2003
    Posts
    89
    Thanks for the suggestion. I did not upgrade with export/ Import. Still I did run the dbms_utility. Most of the queries are running fine, but the problem is only with this particular one. Its taking more than 3 minutes. If I change the hints I am able to make to 3 sec, but again this is through application where we cannot modify the code.
    Is there any parameters to be changed or do you think index need to be recreated.

    Thanks

  4. #4
    Join Date
    Nov 2005
    Posts
    32
    A few things:

    (1) From your output:

    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 18

    Why is the optimizer goal set to first_rows and not choose? By setting the goal to first_rows, you might be potentially restricting the execution path that oracle chooses.

    (2) "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."

    Setting the optimizer_features_enable to 8.1.7 should not be your long term fix to the query performance problems. It is like treating a symptom with a band-aid rather than curing the illness. The 9.2 cost optimizer will need a few "tweaking" to get optimal performance from it. The "tweaking" effort could include disabling query unnesting or complex view merging along with collecting system statistics. Refer to this link for a few tips on what you can potentially do:

    http://www.dbaxchange.com/2_3_optimizersettings.htm

    Metalink note 258167.1 also gives you some details.

    Whatever changes you make, make them to your test database and arrive at proper database settings for optimal query performance for all of your queries before propogating those changes to your production environment.

    Good luck!

    http://www.dbaxchange.com

  5. #5
    Join Date
    Nov 2003
    Posts
    89
    Thanks for your reply. In 8i I was using first_rows where there was no problems,Continued with it.
    I was reading in docs of asktom and few google searches that execution time was fast when changed back to 8.1.7 optimizer feature. It worked fine with me for all the queries which was running slow when using 9.2.0.

    This is in test database. I will try out changing to 9.2.0 and use choose optimizer and come back to you.

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    I also think that it may be realted to your optimizer goal FIRST_ROWS.

    To me it seems that oracle replaced the SORT ORDER BY (8i) by the INDEX FULL SCAN PYRAMID.PA_GRP (9i). I guess that the index PA_GRP contains the column used by the order by and so doesn't need to do an extra order by.

    A solution to the problem would probably be to replace the FIRST_ROWS by CHOSE. Also in 9i I would only use dbms_stats and not dbms_utility to gather stats.

    If you need more help it would be nice to have the explain plans a bit better formated and a description of the tables, views and indexes related the query.
    Last edited by mike9; 01-05-2006 at 10:40 AM.

  7. #7
    Join Date
    Nov 2003
    Posts
    89
    Thanks for all your replys. Sorry I ran DBMS_STATS.GATHER_TABLE_STATS not dbms_utility


    1) I changed the optimizer to choose and optimizer feature to 9.2.0 .
    The query executed fast. But other queries which were running fast was running slow.

    2) I added _B_TREE_BITMAP_PLANS = False in parameter file and Everything worked fine. It runs fine with both first rows and choose now.

    RIght now my optimizer is first rows
    optimizer feature is 9.2.0
    _B_TREE_BITMAP_PLANS = False.

    I went through docs provided by dbaxchangedba.

    Thanks for your help.

  8. #8
    Join Date
    Nov 2003
    Posts
    89
    Testing is still going on and found few more queries running slow. I have optimizer set to choose.

    I have two test databases a and b, copied b database from a. One query is runnig fast in b and slow a.
    Other few queries are running slow in both.
    I see that its not using indexes. sample output of one explain plan . Few queries are using indexes and few are not using indexes on table after upgrade. If I use hints /*+ FIRST_ROWS, USE_NL */ it works fine. Let me know if there any way that I can do without changing in SQL as these were working fine in 8i.



    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		6  	 	14840  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PYRAMID.CLAIM	1  	49  	1  	 	      	             	 
        NESTED LOOPS		6  	1 K	14840  	 	      	             	 
          NESTED LOOPS		6  	1 K	14835  	 	      	             	 
            NESTED LOOPS		6  	1 K	14832  	 	      	             	 
              NESTED LOOPS		6  	1 K	14830  	 	      	             	 
                NESTED LOOPS		7  	1 K	14826  	 	      	             	 
                  TABLE ACCESS FULL	PYRAMID.PAYMENT	7  	1 K	14825  	 	      	             	 
                  INDEX UNIQUE SCAN	PYRAMID.CM_CLM	1  	15  	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	PYRAMID.R_CHECKS	1  	11  	1  	 	      	             	 
                  INDEX RANGE SCAN	PYRAMID.IX_R_CHECKS_CHK_NUM	1  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	PYRAMID.CLMCOVER	1  	29  	1  	 	      	             	 
                INDEX UNIQUE SCAN	PYRAMID.CMC_CLM	1  	 	 	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PYRAMID.BANK	1  	58  	1  	 	      	             	 
              INDEX UNIQUE SCAN	PYRAMID.BA_BNK	1  	 	 	 	      	             	 
          INDEX RANGE SCAN	PYRAMID.IX_HOMEPAY	1  	 	1
    kindly help
    Last edited by srt; 01-12-2006 at 04:05 PM.

  9. #9
    Join Date
    Nov 2003
    Posts
    89
    second query
    Code:
    (SELECT 
    
        C.R_NAMED_INSRD1_NAM,
        P.STATUS,  P.PYE_NAM,   P.ADD_BY,
    	    P.SITE_ID,P.ISS_DTE,	CL.R_COV_HANDL_OFC_DSK_COD,
    	N.TYP
    	FROM
         P,CL,CM, C,N
    WHERE 
    P.STATUS = 'H' AND
        C.UNQ_ID = CM.UNQ_ID AND
        CM.CLM_SFX = CL.CLM_SFX AND
        CM.UNQ_ID = CL.UNQ_ID AND
        CL.COV_MIN = P.COV_MIN AND
        CL.CLM_SFX = P.CLM_SFX AND
        CL.UNQ_ID = P.UNQ_ID AND
    	CM.SOC_NUM = N.ID)
    	
    Explain Plan 
         
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		4 M	 	238210  	 	      	             	 
      MERGE JOIN		4 M	1G	238210  	 	      	             	 
        SORT JOIN		590 K	85 M	43419  	 	      	             	 
          MERGE JOIN		590 K	85 M	21965  	 	      	             	 
            SORT JOIN		286 K	33 M	18326  	 	      	             	 
              MERGE JOIN		286 K	33 M	12770  	 	      	             	 
                SORT JOIN		285 K	20 M	8480  	 	      	             	 
                  MERGE JOIN		285 K	20 M	4989  	 	      	             	 
                    SORT JOIN		282 K	13 M	3149  	 	      	             	 
                      TABLE ACCESS FULL	C	282 K	13 M	751  	 	      	             	 
                    SORT JOIN		292 K	6 M	1841  	 	      	             	 
                      TABLE ACCESS FULL	CM	292 K	6 M	367  	 	      	             	 
                SORT JOIN		456 K	21 M	4291  	 	      	             	 
                  TABLE ACCESS FULL	N	456 K	21 M	413  	 	      	             	 
            SORT JOIN		594 K	16 M	3639  	 	      	             	 
              TABLE ACCESS FULL	 CL	594 K	16 M	327  	 	      	             	 
        SORT JOIN		4 M	529 M	194792  	 	      	             	 
          TABLE ACCESS FULL	P	4 M	529 M	14825

  10. #10
    Join Date
    Nov 2005
    Posts
    32
    Couple of things:

    (1) What does the original execution plan (8i) look like for these 2 queries?

    (2) Did you happen to change these 2 parameter values for your test instance?

    SORT_AREA_SIZE
    DB_FILE_MULTIBLOCK_READ_COUNT

    http://www.dbaxchange.com

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