RE: Lookup/Code table as hot block

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_at_gmail.com] Sent: Tue 5/2/2006 5:15 AM
To: Bobak, Mark
Cc: jkstill_at_gmail.com; ranko.mosic_at_gmail.com; _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_at_il.proquest.com> wrote:
> Or you can use the minimize records per block trick....
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org on behalf of Jared Still
> Sent: Mon 5/1/2006 6:59 PM
> To: ranko.mosic_at_gmail.com
> 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_at_gmail.com> 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_at_rogers.com
> >
> > http://ca.geocities.com/mosicr@rogers.com/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_at_rogers.com
> > http://ca.geocities.com/mosicr@rogers.com/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