FreeLists / oracle-l / RE: Lookup/Code table as hot block

["Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>]

Hi Jaffar,

The original purpose of the minimize records per block feature was to help 
reduce the size of bitmap indexes, by guaranteeing that a particular table will 
never have more than X rows/block.  However, it can be (ab)used on any table, 
even one without any bitmap indexes on it, to control row density at the block 
level.

If you do something like:
create table testit as select * from dba_objects where rownum=1;
alter table testit minimize records_per_block;
insert into testit select * from dba_objects;

You'll wind up with a table that has one record per block.

-Mark



-----Original Message-----
From: Syed Jaffar Hussain [mailto:sjaffarhussain@xxxxxxxxx]
Sent: Tue 5/2/2006 5:15 AM
To: Bobak, Mark
Cc: jkstill@xxxxxxxxx; ranko.mosic@xxxxxxxxx; _oracle_L_list
Subject: Re: Lookup/Code table as hot block
 
I think minimize records_per_block is use when there is bitmap index on it.
If the tables has index on it, I hope that root block of the index is
not the cause for hot block.

Jaffar

On 5/2/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:
> Or you can use the minimize records per block trick....
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Jared Still
> Sent: Mon 5/1/2006 6:59 PM
> To: ranko.mosic@xxxxxxxxx
> Cc: _oracle_L_list
> Subject: Re: Lookup/Code table as hot block
>
> One method to deal with this is to rebuild the table
> with a sufficiently high pctfree that there is only
> one row per block.
>
> Jared
>
>
> On 4/24/06, Ranko Mosic <ranko.mosic@xxxxxxxxx> wrote:
> >
> > Hi List,
> > I have couple of thousand of users all accessing the same, very small
> > lookup
> > table.
> > This is hot block in database cache.
> > How can I avoid this ? Multiple table copies ? How to do this - all users
> > connect under single name .
> >
> > --
> > Regards,
> > Ranko Mosic
> > Contract Senior Oracle DBA
> > B. Eng, Oracle 10g, 9i Certified Database Professional
> > Phone: 416-450-2785
> > email: mosicr@xxxxxxxxxx
> >
> > http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
> >
> >
> > Hi List,
> > I have couple of thousand of users all accessing the same, very small
> > lookup table.
> > This is hot block in database cache.
> > How can I avoid this ? Multiple table copies ? How to do this - all users
> > connect under single name .
> >
> > --
> > Regards,
> > Ranko Mosic
> > Contract Senior Oracle DBA
> > B. Eng, Oracle 10g, 9i Certified Database Professional
> > Phone: 416-450-2785
> > email: mosicr@xxxxxxxxxx
> > http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
> >
> >
> >
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g, OCP DBA
Banque Saudi Fransi,
Saudi Arabia
http://jaffardba.blogspot.com/
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."

--
http://www.freelists.org/webpage/oracle-l