Re: Tuning Question

Burton Peltier wrote:

> --
>
> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3EF5E5E0.9C09012A_at_exxesolutions.com...
> > 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?
> >
>
> Basically, the other DBA is asking me to put the index - without testing.
> Then, I asked this group a question that as you have pointed out - really
> cannot be answered here... requires testing. My mistake for asking.
>
> As others have pointed out (and I was thinking about), it could actually
> increase logical I/Os which would not be good and this will have to be
> tested.
>
> > --
> > 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)
> >
> >

I think the real issue is a "DBA" (quote marks intentional) that would put something into production without
first insisting on testing. This is a potentially dangerous person and I'd be thinking CYA every time I did
anything that interacted with them.

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