Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Keep / recycle / default and different block size

SOLVED
Go to solution
Nicolas Dumeige
Esteemed Contributor

Keep / recycle / default and different block size

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
All different, all Unix
8 REPLIES
Fred Ruffet
Honored Contributor

Re: Keep / recycle / default and different block size

What version of Oracle are you runing ?
--

"Reality is just a point of view." (P. K. D.)
Nicolas Dumeige
Esteemed Contributor

Re: Keep / recycle / default and different block size

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
All different, all Unix
schneider_15
Advisor

Re: Keep / recycle / default and different block size

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
Esteemed Contributor

Re: Keep / recycle / default and different block size

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
All different, all Unix
Brian Crabtree
Honored Contributor
Solution

Re: Keep / recycle / default and different block size

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
Esteemed Contributor

Re: Keep / recycle / default and different block size

Brian

Thank you very much for your answer.

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

Cheers,

Nicolas
All different, all Unix
Sanjay Kumar Suri
Honored Contributor

Re: Keep / recycle / default and different block size

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Nicolas Dumeige
Esteemed Contributor

Re: Keep / recycle / default and different block size

Thanks Sunjay.

All different, all Unix