Re: Tuning Question

hi from Paris

if your table is about 1 Mo large, and supposing your block size is 8k, this means it represents 128 blocks.

Supposing too that your db_file_multiblock_read_count is 16, this means you will full scan the table in 8 logical reads.

you should compare this with selectivity of indexes, knowing reading a row take at least 2 blocks (1 for index, and 1 for row).

So, it should be interesting to put an index, only if your queries are getting less than 4 rows per execution....

From my experience, keep this table accessed by full scan, and help it to stay in memory (by setting the cache attribute, or even better, putting it on the keep buffer pool).

-> alter table XXX cache / nocache ;

"Burton Peltier" <burttemp1REMOVE_THIS_at_bellsouth.net> a écrit dans le message de 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 Sun Jun 22 2003 - 17:40:57 CDT