Re: Tuning Question

Hi!

You don't provide enough information such what type of queries you execute, are they lookup queries for just one-few rows of joins with all of the data.

If this is some sort of a lookup table, then definitely index it, possibly with composite index with all columns queried or create index organized table instead. No perfomance or storage penalty, because the table is so small and lookup tables generally aren't updated that much. Even though you can cache the whole data you will still win in CPU cycles, especially when you can put unique constraints/indexes on table and query on those columns.

Tanel.

"Burton Peltier" <burttemp1REMOVE_THIS_at_bellsouth.net> wrote in message news:Dx3Ja.22714$T37.5078_at_fe05.atl2.webusenet.com...
> Just looking for opinions... I have my own and another DBA I work with has
> his opinion. Looking for some other opinions...
>
> Some info on setup first...
>
> - SunFire V880 8x900MHz CPUs
> - 16Gig Memory
> - 3 databases
> - db1 - 360M SGA - 5 Gig allocated space
> - db2 - 1200M SGA - 55Gig allocated space
> - db3 - 1200M SGA - 60Gig allocated space
> - all of these SGAs are mostly allocated to db buffers, but all have over
> 100 meg for shared pool
> - all in archive log mode
> - nothing else runs on this machine except these 3 Oracle databases and
only
> DBAs login
> - all version 8.1.7.4
>
> Scenario: Suppose you have a small table less than 1Meg (.82) that is
> queried 140,000 times a day on db2. The table currently has no index, and
> the query runs in less than 1 second (100Msecs) when the server is idle
(no
> other activity).
>
> Question: Would you put an index to improve the 1 query to improve averall
> system usage?
>
> My opinion is the table is so small , if it isn't always cached, then it
> should be forced to be cached.
>
> Other DBA's opinion is to add a combo-index on the 2 columns queried in
this
> 1 query.
>
> Note: I am not sure of other usage of this table. There could be other
> querys on other columns or a lot of inserts ... not sure right now.
>
> --
>
>
>
Received on Sat Jun 21 2003 - 17:39:30 CDT