Home » RDBMS Server » Performance Tuning » oracle query running slow
oracle query running slow [message #420073] Wed, 26 August 2009 23:10 Go to next message
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.
Re: oracle query running slow [message #420075 is a reply to message #420073] Wed, 26 August 2009 23:19 Go to previous messageGo to next message
Messages: 24963
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
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

-- invoke the problem SQL here

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.
Re: oracle query running slow [message #420093 is a reply to message #420073] Thu, 27 August 2009 00:38 Go to previous message
Michel Cadot
Messages: 63923
Registered: March 2007
Location: Nanterre, France, http://...
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.

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

Current Time: Tue Oct 25 20:21:25 CDT 2016

Total time taken to generate the page: 0.21019 seconds