set oracle buffer pool for different application.

Checkout the following link (Section "Configuring Multiple Buffer Pools"):

https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA538

hi,

in what situation will configuring multiple buffer cache pool for different application?

I don't get how good it is as if just one KEEP pool in the SGA, still accessible by all application in the SAME RAM !

Quote from the Link:

For most systems, a single default buffer pool is generally adequate. However, database administrators with detailed knowledge of an application's buffer pool may benefit from configuring multiple buffer pools.
For segments that have atypical access patterns, consider storing blocks from these segments in two separate buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (sometimes referred to as hot) or infrequently accessed (such as a large segment that is accessed by a batch job only once a day).

sorry what I mean is, as Buffer pool all in RAM, then access from RAM very fast already, why still need this.

I read the above already but seems do not ansewr my concern.

In the normal scenario, (segment) blocks are kept on LRU list. When a cache becomes full and you need space for new things - you discard the least recently used items first (things you haven't used for a while but are in the cache consuming space). KEEP pool make sure the blocks (of segments) in this pool are NEVER aged out thus making them always be in cache.

" KEEP pool make sure the blocks (of segments) in this pool are NEVER aged out thus making them always be in cache."

I knew this and the recycle pool is for very old item before they ALSO get wrapped out, right?

what i mean I if we CREATE buffer pool other than KEEP and recycle,  as the new created pool ALSO on RAM, I am not sure how ALL NEW POOL created can benefit applicatoin !

As per my knowledge, you cannot create a new pool other than KEEP & RECYCLE for the cache.

oh, then can't assign different application to differenet buffer cache pool also ?\

I don't think DBA will assign application to recycle pool too, right?

RECYCLE is default pool for all segments.

"RECYCLE is default pool for all segments':

oik! so it keep old data and can't see why application keep using it.

so by this, we can only move application to use KEEP  pool if application is slow and need frequent modified data?

KEEP pool is nothing but to make sure that blocks remains in cache at any give time. So once you use KEEP pool for segments, make sure to do full testing to measure the performance and conclude.

You don't point an application to a pool.

Ora_Techie,

"KEEP pool is nothing but to make sure that blocks remains in cache at any give time. So once you use KEEP pool for segment"

so we have to specify any segement to use  KEEP pool ? as by default ALL application connect to recycle pool ?

slightwv,


"You don't point an application to a pool."

and all application by default will use recycle pool then ... ?

Hi, in short there is no reason to use multiple buffer pool. What is the problem you are trying to solve?

>> so we have to specify any segement to use  KEEP pool ? as by default ALL application connect to recycle pool ?

Yes, you need to pin objects in the pools.  Applications do not "connect" to the pool.  It requests data blocks.  Those data blocks are in one of the pools.

>> and all application by default will use recycle pool then ... ?

All connections use the same SGA.  What database blocks are in what pools is up to the DBA based on application usage.

Personally, I've NEVER used KEEP or RECYCLE.  I've let Oracle do what it deems best in the SGA.

Whenever you try to out think Oracle, you normally make things worse.

Franck Pachot,

"there is no reason to use multiple buffer pool."
I read a doc about this, it say create more than one pool for different application can improvement.

slightwv,

" Those data blocks are in one of the pools."

but we don't know and anyway to check this out ?

"Personally, I've NEVER used KEEP or RECYCLE.  I've let Oracle do what it deems best in the SGA."

ASMM right?

"What database blocks are in what pools is up to the DBA based on application usage."

but still only \KEEP and recycle pool right?

"Yes, you need to pin objects in the pools.
"
how?

>> create more than one pool for different application can improvement

It "can".  Everything depends on the data and how the applications access the data.  Understanding about the different pools and what they do is just another tool available to use.

I've been doing Oracle for over 25 years and have never used them.

>> but we don't know and anyway to check this out ?

There are queries I've seen that claim to show pool usage,

>> ASMM right?

Correct.

>> but still only \KEEP and recycle pool right?

Yes.

>> how?

It's in the docs.  Have you look in the doc link posted above?

Using Multiple Buffer Pools

To define a default buffer pool for an object, use the BUFFER_POOL keyword of the STORAGE clause. This clause is valid for the following SQL statements:

• CREATE TABLE
• CREATE CLUSTER
• CREATE INDEX
• ALTER TABLE
• ALTER CLUSTER
• ALTER INDEX

>> I read a doc about this, it say create more than one pool for different application can improvement.

Can you link or copy the context? This alone does not make sense, especially on current versions with current server RAM, and current recommended settings (ASMM). If an application has a really specific pattern, like benefiting from keeping data in cache even when rarely read, or the opposite, like not wanting to load in warm cache side some frequently read blocks, then multiple pools or other alternatives (like 12 full caching) can be a solution.

Franck Pachot,

"Can you link or copy the context? "

https://www.youtube.com/watch?v=6uXqH0f3aG4

"then multiple pools or other alternatives (like 12 full caching) can be a solution."

this also means more than one pool for application?

slightwv,

"Using Multiple Buffer Pools
To define a default buffer pool for an object, use the BUFFER_POOL keyword of the STORAGE clause. This clause is valid for the following SQL statements"

checked that but the complete pool create statement seems not included, is it ?

>> this also means more than one pool for application?

No.  You cannot create buffer pools.  Applications don't get a pool.

The three pools handle database block storage in different ways.  That is all.  They all relate to how long the read database blocks reside in memory.  Nothing else.

Again, everything you need is in the docs:

https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA95405

To improve database performance for table scans and LOB data access, especially for workloads that are limited by I/O throughput or response time, consider using force full database caching mode whenever the size of the database buffer cache is greater than the size of the database.

>> checked that but the complete pool create statement seems not included, is it ?

I don't understand what you are asking.  If you are asking how to create a pool, you don't.  We have already covered this.

Hi,
I see in the video: "isolate the most used tables into different buffer pools"
This makes no sense. There is no per-table buffer pool. I suppose he thinks about the 'keep' buffer pool but that's an old recommendation. Now with serial direct path other mechanisms are available and recommended, like setting DB_BIG_TABLE_CACHE_PERCENT_TARGET for the tables that should stay in buffer cache. And what means "most used?" full scan? index access? modifications?. And what if you set a keep cache and one table becomes larger?
I tried to contact the author of this video but he is retired. I recommend that you forget about this recommendation. The main benefit of a cache is that it is shared: all tables can go there, stay when frequently used, and releasing space for others when not used for a while.
Regards,
Franck.

slightwv,

"The three pools handle database block storage in different ways.  That is all.  They all relate to how long the read database blocks reside in memory.  Nothing else."

and you are saying ASMM will handle it for us and we and application do not even know how it use and which pool we are using at all ?

should be 2 xpool, KEEP and recycle, right?

">> checked that but the complete pool create statement seems not included, is it ?"


I mean I don't the full command to create/alter/manager the buffer pool.

Franck Pachot,

" but that's an old recommendation"

so the video showing me sth depreciated and need to do in that way anymore?

" And what if you set a keep cache and one table becomes larger?
"
what is that mean ?

"I tried to contact the author of this video but he is retired."

very proactive !

"The main benefit of a cache is that it is shared: all tables can go there, stay when frequently used, and releasing space for others when not used for a while."

automatically release space for other if they hit age is too long and more than cache object retention period ?

>>I mean I don't the full command to create/alter/manager the buffer pool.

You don't create/alter pools.  The only thing you can do is set the memory sizes for those pools.

>>and you are saying ASMM will handle it for us and we

Everything you need to know is in the docs...

https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN00207

Pay attention to the word "Manually":

Table 6-3 Manually Sized SGA Components that Use SGA_TARGET Space

>>and application do not even know how it use and which pool we are using at all ?

Exactly!

>> depreciated and need to do in that way anymore?

It is a tool still available to you.  You need to understand the pros and cons.  We really cannot say if you should use it or not.  Every database is different.

I do agree with Franck and mentioned it above:  I've never messed with them in over 25 years of doing this.  You probably shouldn't need to worry about them either.

You now know they exist.  You should know the purpose for each one.  Now forget about them and move on to another Oracle subject area.  You probably won't ever use them.

hi,

"You don't create/alter pools.  The only thing you can do is set the memory sizes for those pools."

with ASMM still need to set the size of buffer pool, which is in SGA ?

"I do agree with Franck and mentioned it above:  I've never messed with them in over 25 years of doing this.  You probably shouldn't need to worry about them either."

yeah, so just turn on ASMM, it will handle it for us.

"You probably won't ever use them."

and you said

The only thing you can do is set the memory sizes

why still need to set/adjust the size ?

slightwv,

"Yes, you need to pin objects in the pools.  Applications do not "connect" to the pool.  It requests data blocks.  Those data blocks are in one of the pools."

so they exists at the same time and they both keep data (KEEP for frequently updated data, RECYCLE pool keep old data), and data flow between them and manage by oracle internally, that's why we don't have to know it at all and application of course do not point to them directly ! of course they don't

>>with ASMM still need to set the size of buffer pool, which is in SGA ?
>>why still need to set/adjust the size ?

You tell Oracle how much RAM it is allowed to use and forget about it.  You just need to make sure you allocate enough overall for the applications that use the database.

"You just need to make sure you allocate enough overall for the applications that use the database."

I just keep adding RAM until AWR report'd db sequential write do not spend most of the time already ok? or how ?


"You tell Oracle how much RAM it is allowed to use"

when I add RAM I think Oracle will use ALL of it?

>>when I add RAM I think Oracle will use ALL of it?

It can only use up to MEMORY_TARGET.

>>or how ?

Everything you need is in the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-memory.html#GUID-98B11E31-DFAB-4AF6-9438-6B3C9DDB9847 

6.3.3 Monitoring and Tuning Automatic Memory Management

so once add RAM we have to set MEMORY_TARGET, then buffer pool will then enlarge accordingly. ?

The docs explain how Oracle manages the buffers.

I read this:
https://docs.oracle.com/en/database/oracle/oracle-database/20/admqs/managing-the-oracle-instance.html#GUID-5740E372-F950-4A99-A3F8-4D3FF5D0D62D

To change to automatic shared memory management if manual shared memory management is currently enabled:

If manual shared memory management is currently enabled, but you would like Oracle Database to help you determine optimal sizes of the SGA and instance PGA, you can disable manual shared memory management and enable automatic shared memory management.

so turns on ASMM already make PGA and SGA auto managed but I am not sure why AFTER we add memory, we need to take care that !

and I am not sure why need Oracle Enterprise Manager Database Express (EM Express)  to configure start up value of memory, SQL develper can't do it ?

It can only use up to MEMORY_TARGET.

you are saying even we add more RAM to the Oracle it can't use more RAM than the GB value set in the MEMORY_TARGET ?

and change MEMORY_TARGET we MUST use EM Express ?

I keep this problem in :
https://docs.oracle.com/en/database/oracle/oracle-database/20/admqs/managing-the-oracle-instance.html#GUID-47B7DE62-CF0B-45B7-8235-FCEF6193FC99

To change between manual memory management and ASMM:

Ref: https://docs.oracle.com/en/database/oracle/oracle-database/20/admqs/managing-the-oracle-instance.html#GUID-5740E372-F950-4A99-A3F8-4D3FF5D0D62D

In SQL*Plus, run the following query in the database to obtain a value for SGA_TARGET:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;


1. In EM Express, from the Configuration menu, select Initialization Parameters.
The Initialization Parameters page appears.
2. In the Search field, enter SGA_TARGET.
3. Select SGA_TARGET, and then click Set.
The Set Initialization Parameter page appears.
4. In the Value field, enter the SGA_TARGET value from step 1 above (354M in this example), specify a Scope of Memory, and then click OK.
A confirmation message appears.
5. From the Configuration menu, select Memory.
Note that in the SGA Memory subsection of the Memory Settings section, the Management Mode value is now Auto. This indicates that automatic shared memory management is enabled.

and

Enabling Manual Shared Memory Management

This section describes how to enabled manual shared memory management.
Follow these steps to enable manual shared memory management:
      1. In Oracle Enterprise Manager Database Express (EM Express), from the Configuration menu, select Initialization Parameters.
The Initialization Parameters page appears.
      2. In the Search field, enter SGA_TARGET.
      3. Select SGA_TARGET, and then click Set.
The Set Initialization Parameter page appears.
      4. In the Value field, enter 0, specify a Scope of Memory, and then click OK.
A confirmation message appears.
      5. In the Search field, enter MEMORY_TARGET.
      6. Select MEMORY_TARGET, and then click Set.
The Set Initialization Parameter page appears.
      7. In the Value field, enter 0, specify a Scope of Memory, and then click OK.
A confirmation message appears.

From the Configuration menu, select Memory.
Note that under the SGA Memory section, the Management Mode value is now Manual. This indicates that manual shared memory management is enabled.

it seems that in both case, auto or not, ASMM ALSO enabled !

and it seems if we need to set memory target, we need to disable the ASMM ... ?

and here:

MEMORY_TARGET = SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, MAXIMUM PGA ALLOCATED)

and we have to take care this as well : MEMORY_MAX_TARGET, so is that mean the max RAM available to Oracle instance is set by MEMORY_MAX_TARGET?

and MEMORY_TARGET is ONLY to limit the growth of total PGA and SGA so that total RAM for DB operation can't be more than MEMORY_MAX_TARGET, the RAM serve for the WHOLE oracle ?

and this means we reserver some RAM for OTHER oracle operation and the amount is MEMORY_MAX_TARGET - MEMORY_TARGET ?

>>you are saying even we add more RAM to the Oracle it can't use more RAM than the GB value set in the MEMORY_TARGET ?
Yes.

>>and change MEMORY_TARGET we MUST use EM Express ?

No.  It is an spfile parameter so an "alter system" command will work from any tool you use.

>>and this means we reserver some RAM for OTHER oracle operation and the amount is MEMORY_MAX_TARGET - MEMORY_TARGET ?

You don't want the database to use ALL available RAM in a server.  You have server processes that require RAM.  You NEVER want to swap to disk!!!

As for the rest of everything you posted:
I believe you are trying to get WAY TOO FAR into the weeds.

If should be as simple as:
Figure out how much RAM your instance really needs, set the MEMORY_TARGET and MEMORY_MAX_TARGET to that number.  Add a little just for fun and a safety net.  Forget about it.  Monitor it from time to time with everything else you monitor as a DBA and adjust when necessary.

"No.  It is an spfile parameter so an "alter system" command will work from any tool you use."

and this one no need to restart, right? that one is dynamical setting.

"You don't want the database to use ALL available RAM in a server.  You have server processes that require RAM.  You NEVER want to swap to disk!!!"

yeah, SAME as MS SQL on Windows, but actually I use all RAM for SQL Server no problem will happen,  

so why we need 2 x setting, MEMORY_TARGET  and  MEMORY_MAX_TARGET? why we can't use MEMORY_TARGET   as MEMORY_MAX_TARGET ?

oracle need RAM for other operation other than PGA and SGA .... ?

in MSSQL we only set 2x RAM setting, min and max RAM usage, and MS SQL will manage it automatically for us !

"I believe you are trying to get WAY TOO FAR into the weeds."

 just want to understand the whole picture under the hood!

"Figure out how much RAM your instance really needs, set the MEMORY_TARGET and MEMORY_MAX_TARGET to that number."

by running the memory advisor or AWR report ?

" Add a little just for fun and a safety net"

add a little RAM ?

>>and this one no need to restart, right? that one is dynamical setting.

The docs will tell you this (pay attention to the Modifiable column):
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/MEMORY_TARGET.html#GUID-186DE5CB-8104-4C8B-B673-6B831DFB42DB 
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/MEMORY_MAX_TARGET.html#GUID-C72F25D8-5CB1-4113-B3FA-14CE390E8F60 

>>why we can't use MEMORY_TARGET   as MEMORY_MAX_TARGET ?

Who says you can't?

>>but actually I use all RAM for SQL Server no problem will happen,  

If the OS swaps to disk, you have problems.

>>by running the memory advisor or AWR report ?

or one of the MANY queries out there.  You also need to apply personal knowledge of the applications that use the system.

For example:  You gather your stats for 10 months starting in January and decide on a number.  You know that,based on past history, close to the end of the year usage doubles.  You might need to account for that usage since it hasn't been covered by your metrics gathering.

>>add a little RAM ?

If you decide you need 20G for MEMORY_MAX_TARGET, set it to 21 or 22.  Give it just a little more than you think you need.

"If the OS swaps to disk, you have problems."

when do you think my OS swap to disk, during reboot ? I do this all the time and no problem

probably only for SQL 2016 and after! same concept but SQL server manage RAM quite well

"If you decide you need 20G for MEMORY_MAX_TARGET, set it to 21 or 22.  Give it just a little more than you think you need."

ok


then when MUST  we set  MEMORY_TARGET not equal to MEMORY_MAX_TARGET ?

>>when do you think my OS swap to disk, during reboot ?

If you allocate ALL available memory to any single program, any other program running will swap.  That is all I'm saying.

>>then when MUST  we set  MEMORY_TARGET not equal to MEMORY_MAX_TARGET ?

If you have multiple instances running on the same box.  If an instance only needs extra memory_target for special times, the DBA can give it the memory as long as it doesn't exceed memory_max_target.  Then they can reduce the OS memory usage at will to free up memory for other programs on the server.

"any other program running will swap.  That is all I'm saying."

ok, got it !

"? If an instance only needs extra memory_target for special times, the DBA can give it the memory as long as it doesn't exceed memory_max_target."

sorry , are you saying we can set the memory_target for an instance for some period only ?

>>sorry , are you saying we can set the memory_target for an instance for some period only ?

Did you read the doc links I posted above?  It is in the docs:

After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

together with this:

Then they can reduce the OS memory usage at will to free up memory for other programs on the server.

as MEMORY_TARGET is dynamic value can change and apply at runtime, once an instance boot up and we know it is not going to use more RAM AFTER startup, we can then lower down the value to free up RAM for other instance ? as no reboot is necessary anyway?

You "can" but should you?  Past a certain point giving more memory to Oracle will not increase performance and just waste resources.  Giving too little can greatly decrease performance.

So, your DBA and developers should have already figured out what the correct setting of that parameter should be.

Unless you have seasonal workloads what may require more resources for some period of time, something like year end processing, you really don't mess with that parameter.  Set it and forget it.

So, your DBA and developers should have already figured out what the correct setting of that parameter should be.

sorry if they didn't know I should say look at AWR report  and ASH report for recommend RAM need for each instance and then set MEMORY_TARGET to that value accordingly ? and we do not change it any more ?

>>sorry if they didn't know I should say look at AWR report  and ASH report for recommend

I answered that 7 days ago:

or one of the MANY queries out there.  You also need to apply personal knowledge of the applications that use the system.

If you monitor memory usage for 10 months starting in February and see the optimal memory setting is 10G and make it that and the Oracle Advisers don't know about the massive year-end processing that takes place every January, performance may suffer.  That is where the personal knowledge is important.  You don't tune a database in a vacuum.  You need to understand the users and applications.

>>and we do not change it any more ?

Workloads change and database tunables need to change with them.  Part of the DBA's job is to monitor database performance and adjust as necessary.  MEMORY_TARGET is just another thing you need to monitor.  How often you may need to increase memory is based on the apps and data changes.  If your data doubles every month, you may need to increase memory every few months (assuming increasing memory will actually increase performance).  If you expect it to double every 10 years, then you may only need to change it once or twice in 10 years.

so look at AWR Report or OEMM to get memory usage over the year and set EMORY_TARGET to that max value ?

Maybe, maybe not.  You have to know your database and how it is used.  What if your database doesn't do an annual processing but a 5 year?  or 10 year?

You don't set it to MAX.  You set it appropriately based on knowledge and the advice from the advisers. Remember, there is a point where giving it more memory will not help performance.  Then you are just wasting RAM.  If you only have one database on he box and no other apps running, MORE memory to Oracle doesn't hurt anything.  If you are running more than one or have a lot of other programs running, figure out the OPTIMAL setting for each database.

The advisers are a great place to start but they cannot replace common sense and personal knowledge of how the database works.

The advisers are a great place to start but they cannot replace common sense and personal knowledge of how the database works.

yeah then advisor first, then set memory_target by the value from advisor.

memory max can be larger than memory_target

Sounds about right.

got it, tks.

when we have more than one instance, the sum of memory target for all instance can't be more than memory max ?

Each instance gets it's own set of spfile parameters.  If you are running multi-tenant then the container instance sets the max for all pluggables.

"container instance"

each separate instance ?

" sets the max for all pluggables."

but memory tarage is different for each of them ?

I though as you all said, no need to touch it at all, just turn on ASMM and this is it.

just want to learn more..

tks all..