Keep / recycle / default and different block size

[Nicolas Dumeige]

Hello,

I'm working on a datawarehouse (700 Go) base on a classic star schema. The DB BLOCK SIZE is 8k wich is a shame, but when the project started the standard were 8k for all DB, OLTP or Datawarehouse. It sucks but we can find the time or money to make a reorganisation.

The facts table are partionned on a time field.

You have worked on the buffer management in the following way :
- fact table segment are too large to be kept in the buffer, so thoses segments are flagged recycle
- the last period segements are queried very frequently, so there flagged keep
...
You get the picture.

Now, a DBA have suggested to create a TEMPORARY TABLESPACE with 16k or 32k block size + to enlarge the cache.

==> My question : does the buffer management policy is compatible with different block size on the data DBF and the TEMPORARY TABLESPACE ?

Thanks for you comments,

Nicolas

[Fred Ruffet]

What version of Oracle are you runing ?

[Nicolas Dumeige]

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

[schneider_15]

Dear nicolas I am anxious to underline you the importance of the summons(assignement) of points on this forum.

This is a healthy mechanism, while allowing a competition of the members and a constant improvement of the knowledge which the communautée shares

That's the way thing goes

W

[Nicolas Dumeige]

Salut Willy,

Regarde mon profil, a chaque reponses recues des points ont ete affectees.

C'est pourquoi ta reponse sera recompensee par x point (où x prend la valeur 0). Je te filerai bien dix mais ca va se voir :-D

ad'taleur

n i
c

[Brian Crabtree · Accepted solution]

Nicolas,

The "create temporary tablespace" cannot specify a block size.

In 9i, you need to move away from the db_block_buffers, and start using the db_cache_size setting. This will allow you to specify db_XXk_cache_size setting, where XX is the block size of the tablespace that you are creating. This space will be subtracted from the available db_cache_size which is primarily your default buffer area.

Also, the db_keep_cache_size is set for your default block size as well.

This functionality is only true in 9i+, not 8i.

Let me know if you have any questions.

Thanks,

Brian

[Nicolas Dumeige]

Brian

Thank you very much for your answer.

My question isn't well formulated, I'm working on the subject.

Cheers,

Nicolas

[Sanjay Kumar Suri]

Hello Nicolas

My understanding is as under:

Two tablespaces SYSTEM and TEMP will always have primary block size as defined by DB_BLOCK_SIZE.

In 9i db_cache_size determines the size of the default buffer pool for buffers with the primary block size as defined by DB_BLOCK_SIZE.

db_nk_cache_size (where n=2, 4, 8, 16, 32): If nKB is not the primary block size, db_nk_cache_size specifies the size of the buffer chache for segments with a nKB block size.

db_cache_size does not include db_keep_cache_size, db_recycle_cache_size, db_nk_cache_size.

sks

[Nicolas Dumeige]

Thanks Sunjay.