OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query [message #472102]

Wed, 18 August 2010 17:57

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Experts,

The below query is taking 30:00 minutes to get the result.
Please help me how to inmrove the performance of this query.
The table having 350000 records.
The query is retriving 2500 records.

SELECT COUNT(*) FROM emp_history WHERE REPORT_dt like '1990%';

Thanks in advance

Report message to a moderator

Re: Query [message #472103 is a reply to message #472102]

Wed, 18 August 2010 18:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ALTER SESSION SET SQL_TRACE=TRUE;
SELECT COUNT(*) FROM emp_history WHERE REPORT_dt like '1990%';
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

Has EMP_HISTORY had many rows deleted from it?

Report message to a moderator

Re: Query [message #472112 is a reply to message #472103]

Wed, 18 August 2010 18:46

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

What datatype is report_dt?

Report message to a moderator

Re: Query [message #472293 is a reply to message #472112]

Thu, 19 August 2010 14:04

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

If REPORT_dt column defined as DATE and if you have an index defined on it and if 1990 selects small percentage of rows then using

REPORT_dt like '1990%'

implicitly converts DATE to VARCHAR ( TO_CHAR(REPORT_DT) LIKE '1990%' ) and disables possible index usage.

Try:

...REPORT_dt BETWEEN  TO_DATE('19900101','YYYYMMDD') AND TO_DATE('19901231','YYYYMMDD')

HTH.

Report message to a moderator

Re: Query [message #472314 is a reply to message #472293]

Thu, 19 August 2010 16:56

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

You forgot to factor in times:

REPORT_dt BETWEEN  TO_DATE('19900101','YYYYMMDD') AND TO_DATE('19901231 235959','YYYYMMDD HH24MISS')

Report message to a moderator

Previous Topic:	Password profile
Next Topic:	materalized view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 05 07:03:16 CDT 2024