x

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

vidya 1 gravatar 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 oldest

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

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(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

StewStryker gravatar image

StewStryker
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

Ankur kaushik gravatar image

Ankur kaushik
21 1 3 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x378

asked: Oct 07, 2010 at 03:01 AM

Seen: 2436 times

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