Please advise to increase the oracle data buffer quality from 84% to 95%

[former_member190251]

Hi,

We found that oracle data buffer quality is 84.1% in our production system.We have to increase this to 95%. Please advise me on this.

Also Please let me know if you need any more information for this issue.

database start up is :27.10.2013   02:02:09

SQL> show parameter SGA

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 6304M

sga_target                           big integer 0

SQL> show parameter db_cache

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_advice                      string      ON

db_cache_size                        big integer 4576M

SQL> show parameter shared_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_reserved_size            big integer 26004684

shared_pool_size                     big integer 1040M

SQL> show parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 350M

Total Physical RAM is : 40GB

Regards

Subbu

[Former Member]

Dear Buddy,

Could you please send me a init.ora file to check the parameter.?

were you done the oracle upgrade (10g to 11g)recently.?

Thank you

Mahendran.

[stefan_koehler]

Hi Subbu,

> We found that oracle data buffer quality is 84.1% in our production system.We have to increase this to 95%.

Who says that you have to (maybe EWA)? This sounds like the classic "compulsive tuning disorder". For more information you can check out the white paper "Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok".

If you want to you can run a script that pushes your Oracle data buffer quality up top 99 % (or above) without adding one byte of memory to buffer cache. Sounds cool and your target is reached, but does it makes sense? I don't think so.

Regards

Stefan

[ACE-SAP]

Hello

It does not really make sens to increase cache efficiency targeting a specific ratio...

You can try to increase it but there are limits related to the usage  / available memory...

You Oracle memory dispatching is quite Ok for SGA (buffer cache / shared pool) ...

PGA seems too small (OLTP = 20 % of Oracle available memory OLAP = 40 % of Oracle available memory)

What is your Oracle version ?

What is running on that server ?

Is there is an SAP server running on it, what is roughly its memory usage  ?

How many workprocess, users ?

If some of the memory is not used you can increase the SGA size and then increase accordingly buffer cache (DB_Cache_Size). This should increase you DB cache efficiency.

You are not supposed to define shared_pool_reserved_size (get that advice from a GLC report)

Never forget that SAP buffers (the sap own data buffer cache) are far more efficient than Oracle cache... Oracle has no specific knowledge about the data in its cache.

Tables are buffered at SAP level because it can have a major impact on data access.

Thus check

1) is there free RAM on my server

2) is SAP memory well setup, could it benefit from extra RAM ?

and then you can check if it can be useful to extend Oracle SGA RAM

Best regards

[former_member190251]

Hello

Please find the below information of the production server

Oracle Version : 11.2.0.3.0

SAP : SAP ERP Central Component 5.0

AIX

Kernel release

640

Patch: 390

Unlocked  users: 2123

abap/buffersize	1300000
abap/heap_area_nondia	20971520
abap/heap_area_dia	20971520
abap/use_paging	0
abap/heap_area_total	2000683008
rdisp/wp_no_dia	40
rdisp/wp_no_vb	6
rdisp/wp_no_vb2	3
rdisp/wp_no_enq	1
rdisp/wp_no_btc	15
EM/MAX_SIZE_MB	20480
EM/TOTAL_SIZE_MB	20480
em/initial_size_MB	2048
em/blocksize_KB	4096
em/global_size_MB	250
ztta/roll_first	1
ztta/roll_area	6500352
ztta/parameter_area	16000
ztta/roll_extension	2000683008
em/global_area_MB	256

Current status of memory and paging

Regards

Subbu

[ACE-SAP]

There seems to be some free RAM, so it can be allocated to PGA / SGA or SAP usage.

Please check ST02 to see if there are some buffer swaps.

If not you could extend SGA of 2 or 4 Go, mainly the buffer cache...

And see if buffer quality is improved, it should but as Stefan says it is not a issue nor a systematic optimization path...

Regards