Home » RDBMS Server » Performance Tuning » tuning against count with group by clause (oracle 10g)
tuning against count with group by clause [message #549497] Sat, 31 March 2012 04:29 Go to next message
manoj12
Messages: 175
Registered: March 2008
Location: India
Senior Member
Hi Sir

This query is taking 7 hours to execute as I am retrieving data from history table dept_hist.
This query is taking 7 hours to execute
select count(distinct empid), e.group_nm, d.date,
from emp e, dept_hist d 
where e.deptno = d.deptno
  and e.up_ts > sysdate -30
  and e.stat_cd between 100 and 200
  and e.group_nm like '%EMPNO%'
  and employee_id =1
  and e.employee_id = d.employee_id
group by group_nm,d.date;

Its taking 7 hours to execute.can u help me in restructing this query.Any methods to tune this query will be helpful?

Thanks & Regards


[EDITED by LF: after topics have being merged, I reformatted the code and applied [code] tags. Manoj did that himself in a duplicate message which has lost after merging]

[Updated on: Sat, 31 March 2012 08:35] by Moderator

Report message to a moderator

Re: tuning against count with group by clause [message #549499 is a reply to message #549497] Sat, 31 March 2012 05:30 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Hello, Manoj. You know what you need to to do before anyone can help: provide detail of the tables and any indexes, the object statistics, the statement statistics, the execution plan.

[Updated on: Sat, 31 March 2012 05:30]

Report message to a moderator

Re: tuning against count with group by clause [message #550269 is a reply to message #549499] Sun, 08 April 2012 00:43 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
Pl. proivde the explain plan, Check the CBO/RBO. Remove Between clause/instead use AND conditions
Re: tuning against count with group by clause [message #550272 is a reply to message #550269] Sun, 08 April 2012 00:46 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
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: tuning against count with group by clause [message #550286 is a reply to message #550269] Sun, 08 April 2012 01:20 Go to previous message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Remove Between clause/instead use AND conditions


Why?

Regards
Michel
Previous Topic: Need help on SQL Query in decode function performance tunning
Next Topic: Bulk Inserts
Goto Forum:
  


Current Time: Thu Oct 02 07:46:18 CDT 2014

Total time taken to generate the page: 0.05841 seconds