| Query taking more time [message #511666] |
Tue, 14 June 2011 08:49  |
raj9999
Messages: 49
Registered: June 2011
|
Member |
|
|
Hi,
I have a query which is taking 1 min on the UAT db whereas on
production DB it is taking more than 1 hour. 
explain plan for the query is also diff on both the Db.
Kindly help in resolving the same.
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #511677 is a reply to message #511667] |
Tue, 14 June 2011 09:32  |
raj9999
Messages: 49
Registered: June 2011
|
Member |
|
|
|
UAT is exact replica of production. i have restored the UAT with the backup of production just 2 days back. & there have been no change in either UAT or production since then.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #511979 is a reply to message #511746] |
Thu, 16 June 2011 07:12 |
raj9999
Messages: 49
Registered: June 2011
|
Member |
|
|
select Oldaccount ||'|'|| gam.foracid ||'|'|| substr(gam.acct_name,1,25) ||'|'|| gam.schm_code ||'|'|| substr(gsp.schm_desc,1,25) ||'|'|| gsh.gl_sub_head_desc ||'|'|| gam.clr_bal_amt ||'|'||
tam.cumulative_int_credited ||'|'|| tam.cumulative_int_paid ||'|'|| gam.cust_id ||'|'|| tam.deposit_period_mths ||'|'|| tam.deposit_period_days ||'|'|| acct_opn_date ||'|'|| tam.maturity_date||'|'|| tam.maturity_amount ||'|'|| tam.cumulative_principal ||'|'|| tam.deposit_amount ||'|'||
tam.TDS_TOTAL_FROM_SELF_ACCT ||'|'|| eit.last_interest_run_date_cr ||'|'|| deposit_status ||'|'|| drt.RECEIPT_NUMBER ||'|'|| cmg.cust_const ||'|'|| rct.ref_desc ||'|'|| itc.int_tbl_code ||'|'|| itc.pegged_flg ||'|'|| cmg.cust_minor_flg ||'|'|| (itc.NRML_PCNT_CR + itc.ID_CR_PREF_PCNT)||'|'|| tsd.FLOW_FREQ_MTHS ||'|'|| tam.link_oper_account ||'|'|| 'DUMMY' from gam,tam,itc,tds,rct,eit,drt,tsp,onam,cmg,gsh,gsp,
tsd where gam.foracid = trim(onam.newaccount) and gam.acid=tam.acid and gam.schm_code=tsp.schm_code and gam.schm_code=gsp.schm_code and tam.acid = itc.entity_id and gam.cust_id = cmg.cust_id and tam.acid = drt.acid and gam.acid = tds.acid (+) and gam.acid = eit.entity_id (+) and gam.acct_cls_flg != 'Y' and gam.del_flg = 'N' and gam.entity_cre_flg = 'Y' and gam.sol_id = '0575' and
gam.acct_cls_flg = 'N' and gam.gl_sub_head_code=gsh.gl_sub_head_code and gsh.crncy_code='INR' and gsh.sol_id=gam.sol_id and gam.schm_code = tsd.schm_code and tsd.flow_code IN ('II','IO') and rct.ref_rec_type='44' and rct.ref_code=cmg.cust_const and gam.gl_sub_head_code!='11060' group by Oldaccount, gam.foracid, substr(gam.acct_name,1,25), gam.schm_code, gsh.gl_sub_head_desc, gsp.schm_desc, gam.cust_id, acct_opn_date, tam.deposit_amount,
tam.cumulative_int_paid, tam.cumulative_int_credited, tam.cumulative_principal, tam.TDS_TOTAL_FROM_SELF_ACCT, gam.clr_bal_amt, tam.maturity_amount, tam.maturity_date, eit.last_interest_run_date_cr, cmg.cust_const, cmg.cust_minor_flg, (itc.NRML_PCNT_CR + itc.ID_CR_PREF_PCNT), rct.ref_desc, deposit_status, drt.RECEIPT_NUMBER, tam.deposit_period_mths, tam.deposit_period_days, itc.int_tbl_code ,itc.pegged_flg, tam.int_flow_freq_mths, tam.link_oper_account,
tsd.FLOW_FREQ_MTHS, oldaccount order by gam.schm_code, oldaccount
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #514164 is a reply to message #512003] |
Sat, 02 July 2011 00:45 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
most differences in query plans on two supposedly identical systems stem from simple problems. Not surprisingly it is because there is some difference.
1) stats are different or collected differently. Try moving stats from the good system to the bad one. Also, don't stop with this bandade. Figure out what you did that got stats different. Don't forget to check for columns stats on one but not the other as well.
2) database is different. Use TOAD or other tool to do a schema compare and look for different indexes, constraints, table definitions (datatype,nullability,partitioning)
3) space is allocated differently. Check DBA_SEGMENTS on both systems to see if space is allocated different.
4) high water mark is different because of deletes. You may be scanning a table and one of system is looking at a small number of blocks when the other is looking at a large number of blocks. If so, fix it.
5) parameter differences either by session or database. Compare paramater from v$parameter from both systems while logged in via sqlplus to sessions that exhibit the problem and look for differences. also compare database versions.
6) my favorite, schema mix-up. I can't count the number of times there were two tables with the same name on two different schemas in the same database and synonyms and privileges were the culprit ulimately causing you to access the wrong version of the object you were interested in. You think you are looking at SCOTT.TABLEX when in fact you are looking at TIMMY.TABLEX. Check for multiple objects with the same name. Then make sure your explain plan output contains the owner as well as the object name and that the owner is the correct one.
In my unscientific experience based estimate, you can solve half your "different plans across identical systems" issues with these six checks.
Good luck, Kevin
|
|
|
|
|
|
| Re: Query taking more time [message #514721 is a reply to message #511666] |
Wed, 06 July 2011 01:23 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
It looks like the problem is caused in PROD by incorrect JOIN method to PSB_OLD_NEW_ACCTNO_TABLE table. HASH join is used in Dev vs NESTED LOOP join in PROD.
Actually - the statement performs FULL table scan on PSB_OLD_NEW_ACCTNO_TABLE for each previously selected row.
Assuming that statistics are about the same - it means that each time it performs scan of 620K rows.
Try either enforcing HASH join on PSB_OLD_NEW_ACCTNO_TABLE with HINT or define function based index on TRIM(NEWACCOUNT) of that table.
HASH - preferrable.
HTH
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
