-
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.
-
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...
-
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
-
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
-
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.
-
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 11:40 AM.
-
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.
-
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 05:05 PM.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|