Need to create separate bufferpool for a table in Oracle

[Former Member]

Hi,

We have a custom table for idoc on which physical read is very high. The table is more than 60 GB in size and very frequently whenever any idoc is getting processed, the table is being scanned fully to check whether any failed / unprocessed idoc is there. To avoid this I need to create a separate buffer for this table so that whenever it is required it can be scanned from there only and thus it will not block any other object / tables to be scanned in the main buffer.

Can any one tell me how can I create a separate buffer for this purpose and activate that. Please let me know if any other suggestion is there.

Thanks,

Suman.

[Former Member]

Hi Suman,

You have to take this step by step.

First of all, you should analyze the SQL statements that cause a full table scan on the big idoc table. Does the Oracle optimizer choose a full scan because there is no suitable index? Another possibility: there is an index but Oracle won't use it because it considers the index columns as unselective (which can be caused by a highly skewed data distribution for the column). If a good index is available but Oracle ignores it, you can make it use the index by adding a hint to the ABAP code or setting artificial DB statistics. A performance expert should definitely investigate this closely (standard Early Watch Alert is too superficial in a case like this).

Only as a last resort should you tackle this problem by giving the table a separate bufferpool. Keep in mind that the bufferpool will have to be big enough to hold the entire table, including future growth and 60 GB is a lot of RAM. By default Oracle allocates one bufferpool only. It is possible to allocate up to two extra pools, the "keep pool" and the "recycle pool". Although the documentation states that they server different purposes, e.g. keeping small and often-read tables in memory, all bufferpools are managed in exactly the same way so you can use them for whatever purpose that suits you. SAP gives no specific advice about the use of these pools but you are allowed to configure them (see note 762808).

To enable one of the bufferpools and allocate the table, do the following:

1) Set the parameter db_keep_cache_size (keep pool) or db_recycle_cache_size (recycle pool) to the desired size. The parameters are static, so you will have to restart Oracle.

2) Allocate the table to the bufferpool you have configured, e.g. ALTER TABLE tabname STORAGE (BUFFER_POOL KEEP). Don't forget that SAP does not know about this table attribute, so if the table ever gets changed, converted, upgraded, ... from the SAP Dictionary, it will revert to the default pool.

Using this feature is very unusual, but I have had success with it in several cases in the past. However, I repeat that you should do everything to avoid having to use it and to optimize the SQL statement itself in any way possible.

Regards,

Mark