Re: Tuning Question

Burton Peltier wrote:

> 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.
>
> --

Not a question that can be answered theoretically.

If you are DBAs (I'm not questioning that you are) ... why didn't you just test it rather than trying to intellectually puzzle out an answer?

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)