-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|