Home » RDBMS Server » Performance Tuning » Query taking more time (Oracle 10g R2)
Query taking more time [message #511666] Tue, 14 June 2011 08:49 Go to next message
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. Sad
explain plan for the query is also diff on both the Db.
Kindly help in resolving the same.
Re: Query taking more time [message #511667 is a reply to message #511666] Tue, 14 June 2011 08:55 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Prod and UAT are not identical mirrors is my bet.

[Updated on: Tue, 14 June 2011 08:55]

Report message to a moderator

Re: Query taking more time [message #511668 is a reply to message #511666] Tue, 14 June 2011 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

make Production EXPLAIN PLAN look like UAT
Re: Query taking more time [message #511676 is a reply to message #511668] Tue, 14 June 2011 09:30 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
how to do that?
Re: Query taking more time [message #511677 is a reply to message #511667] Tue, 14 June 2011 09:32 Go to previous messageGo to next message
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 #511679 is a reply to message #511677] Tue, 14 June 2011 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior 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.

*EXACT* replica give *EXACT* results.
*DIFFERENT* results come from *DIFFERENT* configurations.
If you insist upon no difference, then you'll never find what is different.
Post EXPLAIN PLAN from both DBs for same SQL

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Query taking more time [message #511681 is a reply to message #511679] Tue, 14 June 2011 09:38 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Attached is the file for explain plan for both the servers.
  • Attachment: explain.txt
    (Size: 14.74KB, Downloaded 1638 times)
Re: Query taking more time [message #511685 is a reply to message #511681] Tue, 14 June 2011 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are statistics current for all table & indexes involved with SQL on Prod?
Re: Query taking more time [message #511746 is a reply to message #511685] Tue, 14 June 2011 16:27 Go to previous messageGo to next message
nirawkakad
Messages: 1
Registered: June 2011
Location: Mumbai
Junior Member
Check for staistics of all the tables In Prod ad Dev . Also if possible give me the query,so that I can have a look into it.

Re: Query taking more time [message #511979 is a reply to message #511746] Thu, 16 June 2011 07:12 Go to previous messageGo to next message
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 #511980 is a reply to message #511685] Thu, 16 June 2011 07:14 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
i have gathered statistics on all the related tables last night on prod. but still the same result.
Re: Query taking more time [message #512003 is a reply to message #511980] Thu, 16 June 2011 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query taking more time [message #514164 is a reply to message #512003] Sat, 02 July 2011 00:45 Go to previous messageGo to next message
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 #514411 is a reply to message #514164] Mon, 04 July 2011 10:24 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello,

what would be an acceptable solution for you? Can you change this sql? Are the literals in this sql always the same?

Regards
Leonid
Re: Query taking more time [message #514721 is a reply to message #511666] Wed, 06 July 2011 01:23 Go to previous messageGo to next message
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

Re: Query taking more time [message #514797 is a reply to message #514721] Wed, 06 July 2011 08:54 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Still waiting for OP to provide results of my six point step. OP... did you check any of this yet?

Kevin
Previous Topic: problem with outlines #2
Next Topic: Become DB import too slow after server reboot
Goto Forum:
  


Current Time: Sun Jul 21 00:12:29 CDT 2024