OraFAQ Forum: Performance Tuning » oracle query running slow

Home » RDBMS Server » Performance Tuning » oracle query running slow

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

oracle query running slow [message #420073]

Wed, 26 August 2009 23:10

rareraja
Messages: 22
Registered: March 2005
Location: NJ, USA

Junior Member

I have a table named customer which has 10k rows in both oracle 9i and 11G.

The below query when I run in 9i, it executes in much lesser time as compared to 11g.

select * from customer this_ where this_.first_name like '%WORLDFORME' and this_.last_name like '%RAJ'

Time taken in oracle 9i : 2.071 sec
Time taken in oracle 11G : 52.915 sec

can anyone tell me why that much difference in execution time ?
what are the possible causes for this difference ?

Any help would be appreciated.

Thanks in advance.

Report message to a moderator

Re: oracle query running slow [message #420075 is a reply to message #420073]

Wed, 26 August 2009 23:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

post EXPLAIN PLAN from both systems

do the following on both system so we can compare & contrast

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem SQL here
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

There is a difference. It is your job to find the difference.

Report message to a moderator

Re: oracle query running slow [message #420093 is a reply to message #420073]

Thu, 27 August 2009 00:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

Report message to a moderator

Previous Topic:	How to change SYS password?
Next Topic:	dataguard tunning: checkpoint completed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 12:22:48 CST 2024