Re: Oracle Query Tuning

Hello,

In general, I would generate the explain plan for the query you are tying to tune and see what the server is really doing with it. It may be doing full table scans on all your tables, even though you have indexes on the columns in any join and where conditions.

Try using bitmap indexes on columns that have a low cardnality compared with the number of rows. I have read that you can start to see improvements with bitmap indexes if the cardnality is less than 5% but have heard that it is best if less than 0.1% of the total number of rows. In other words 1 different value in the column for each 1000 rows in the table. For your case any columns in table 1 with less than 250 different values, table 2 with 45 different values or table 3 with 5 different values will be strong candidtates for bitmap indexes.

Also, check to see if you are doing some grouping or other functions in the where clause of your query that is forcing full table scans.

If this 3 table join is going to be used in the same way repeatedly, you can define it as a view so that it gets better efficiency when queried repeatedly.

Also, you may want to check and see if the init.ora sizings of your various memory areas are big enough. It could be that you are having to do a lot of disk reading because only a small amount of the table rows can fit in memory.

There are a lot of good tuning books out there that go into detail on your tuning options and give good strategies to take when trying to tune your queries.

Hope this helps :)

--
Kenneth E. Murphy
University of Missouri - Columbia
Office of Social & Economic Data Analysis
kenneth_at_oseda.missouri.edu
(573) 884-8794

manoj.lahoti_at_gepex.ge.com wrote in message
<885079291.2007040019_at_dejanews.com>...


>I'm trying run a select query against 3 oracle tables with 250,000 ,45000

>and 5000 records respectively. I have indexes on almost all the fields

>that i'm using in where clause of the query. Now this query took about 30

>min to fetch the results when I tried to create index on a field which

>has only 2 values i.e. 'Y' and 'N, otherwise it takes about 10 -15 min to

>run this query. Any suggestions to improve the query  response would be

>appreciated.

>

>-------------------==== Posted via Deja News ====-----------------------

>      http://www.dejanews.com/     Search, Read, Post to Usenet

>