how to reduce query execution time, in oracle?

if query contains some joins and order by clause, then execution speed slows down, Is there any solution to execute the query fast.

more ▼

asked Oct 07, 2010 at 03:01 AM in Default

avatar image

vidya 1
1 1 1 1

We will need a much more specific problem to work on.

Oct 21, 2010 at 03:29 PM Leigh Riffel
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Performance tuning is a vast subject, and this can't possibly be answered simply.

In general, please refer to the Oracle Performance Tuning Guide: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/toc.htm

If you provide more details, then I (or others) might be able to help in the specific case.

more ▼

answered Oct 19, 2010 at 10:02 AM

avatar image

Andrew Mobbs
1.6k 3 5

(comments are locked)
10|1200 characters needed characters left

Andrew's suggestion is the correct one, though probably totally unsatisfying for those looking for a quick answer.

Here's my attempt at some possible avenues to investigate to get you thinking about what might be going wrong.

1) First check for Cartesian Products in your joins, that is joined tables you haven't identified reasonable values to join over, resulting in getting ALL rows from both tables. This is the most-common culprit for slow queries.

e.g. SELECT * from emp, dept; -- not including WHERE emp.dept_id = dept.dept_id

2) The next place to look would be to see if you can join over fields that are indexed in one or more of the tables.

3) Or if your WHERE clause is trying to limit values based on the result of a function (especially a non-built-in SQL function).

e.g. SELECT * from emp where my_plsql_func(emp.dept_id) > 500

Good luck!

more ▼

answered Nov 03, 2010 at 04:36 PM

avatar image

150 3

(comments are locked)
10|1200 characters needed characters left

create indexes on tables which is frequently used define datatypes properly and lru tables keep in instance

more ▼

answered Nov 22, 2010 at 07:12 AM

avatar image

Ankur kaushik
21 2 5 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 07, 2010 at 03:01 AM

Seen: 3459 times

Last Updated: Oct 07, 2010 at 03:01 AM

Copyright 2018 Redgate Software. Privacy Policy