Re: Problem with indexing/ slow access to Oracle

In article <35fe2b6d.43294628_at_news.u-net.com>,   russ_at_u-net.net (R Fray) wrote:
>
> I'm sure that I am doing something wrong when writing indexes and
> creating tables.
>
> I am running Oracle 8.0.4.0.0 on Solaris 2.6, on a Sun with 512MB
> memory and a fast 27GB Raid drive.
>
> When I perform a simple query, such as 'select * from customer where
> customer_number = 12345', results are returned very quickly.
>
> Likewise, if I perform a query on a second table in a similar fashion,
> this is also very fast.
>
> The problem and slow access arises when I am querying multiple tables.
> Ie, if I run :
>
> select * from customer, agreement where customer.agreement_id =
> agreement.agreement_id and customer.customer_num = 12345;
>
> - This takes about 10 seconds to bring back the data which is
> unfortunately unacceptable for what is a real time system.
>
> I only have about 30,000 customer and 150,000 agreement records, so
> surely it should be quicker than this?
>
> I am confused as to what I should set as a primary key when creating
> tables, and what fields/criteria I should use when creating indexes.
> Can you index 'too many' fields? Is it better to have something as a
> primary key or an index, as you can't do both - or is a primary key
> automatically indexed upon table creation?
>
> Please cc replies to russ_at_u-net.net if possible.
>
> Thanks,
> Russ.
>
> --
>
> Russell Fray
> U-NET Ltd.
>

Hi Russel,

first of all: yes a primary key is automatically indexed with a unique index on the primary key column(s). Of course you should create primary key constraints and foreign key constraints on your tables because this prevents you from getting invalid or orphaned data. In Oracle 8 there are two different optimizer schemes. The "old" rule based optimizer and the cost baes optimizer. in order to use the better cost based optimizer you should analyze your tables regularly using analyze table xyz compute statistics or begin dbms_utility.analyze_schema('schema_name','COMPUTE'); end; This will give the optimizer all information regarding to the data, the indices, et al. The optimizer can now use the best (lowest cost) was to get your data. You can get execution plans from your sql statements if you user explain plan. There you can see what optimizer goal is used and which ways the optimizer has used to get your data.

Next problem could be a sga that is too small. Have you checked the cache hit ratio of your db buffers and the shared pool? If this goes below 95 % you should adjust your sga to use more memory. Each miss will get data from disk but from memory and this will slow down massively your selects.

Hope this helps
Andy

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Oct 01 1998 - 05:03:07 CDT