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
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.
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
BlackSwan
Messages: 25050
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.
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: 64153
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.

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


Current Time: Sun Dec 11 02:40:03 CST 2016

Total time taken to generate the page: 0.10548 seconds