Re: Imrpoving Query Speed

First of all they are not using bind variables. So that is going to hurt performance. They need to read the Oracle Application Developers Guide. (comes with the software in html format) They need to upgrade their database I think 8.0.5 isn't supported anymore. 1. Are the tables and indexes analyzed? (probably not) 2. You didn't state what the PK's were. Oracle needs the leading edge of the index to be able to use the index. e.g. if the index is city,state then a query just on state will not use the index. A query just on city or on city or state would.
3. You are going to have to look at all the queries and all the indexes and see if you have an index that matches the query. If not you are going to have to make them.
4. Also look at your db_block_buffers and how much ram you have on the server. You might be able to bump that up. (each db_block_buffer is equal to the db_block_size so 100 does not mean 100 bytes it means 100* db_block_size - which could be 2K,4K,8k etc.) 5. You need to make it clear that about 80% of all database performance problems are application problems. Clearly no one designed the database and the application together. Big mistake. If they want to assign blame then they need to look in the mirror. If they want to fix the problem then they are going to have to work with a competent DBA, data modeler, RTFM, and do some redisign of the application. Performance is part of the job as you do the job not something you tack on at the end.

Sorry Kevin there are not any easy answers. Jim
"Kevin Chasse" <kchasse_at_arinc.com> wrote in message news:56b618f4.0110090650.18801ce2_at_posting.google.com...
> Let me first say thank you in advance for your help.
>
> Here is my situation: I have been asked to improve the response time
> for an application I did not write. This application accesses an
> Oracle 8.0.5 database that is simply a collection of tables with no
> relationships, no indexes (each table does have it's own PK), etc. I
> looked at the code that is generating the SELECT statement that is
> having problems and it is basically like this:
>
> SELECT a.field1, a.field2, a.field3, b.field9 FROM a, b WHERE a.field1
> = b.field1 AND a.field2 = b.field2 AND a.field2 = 'xyz' ORDER BY
> a.field1, a.field3
>
> In this case Table A has a PK that contains more fields than Table B's
> PK, but all of Table B's PK fields are also in Table A. (I know the
> database seign sucks -- but they want a quick fix not a database
> re-design).
>
> I have noticed that if I add the following to the WHERE clause in the
> SQL statement the retrieval time is improved by a factor of 10!
>
> b.field2 = 'xyz'
>
> This solves the speed problem, but would require a code change and I
> do not want to do that as we would have to re-test the entire app
> before releasing this fix.
>
> So, I am looking for a fix that could be done on the database level
> without affecting the application adversly. I am not that experienced
> in Oracle and would appreciate any help I can get on this one.
>
> Thanks again in advance (and thanks for reading this far in my
> long-winded post).
>
> Kevin Chasse
> kchasse_at_arinc.com
Received on Tue Oct 09 2001 - 17:21:59 CEST