Data Buffer Quality (%) 84.7

[ganimede_dignan]

Hi,

from few weeks we see this in EWR

http://img263.imageshack.us/i/capture1201105032246.jpg

How can we solve it? wich parameters?

Regards.

[former_member184473 · Accepted solution]

Hello,

Check the following notes for further information about the "Data Buffer Quality":

[618868 - FAQ: Oracle performance|http://service.sap.com/sap/support/notes/618868]

[789011 - FAQ: Oracle memory areas|http://service.sap.com/sap/support/notes/789011]

Regards,

Eduardo

[Former Member]

This is a classical "it depends". You could try this quick shot:

set linesize 200
set pagesize 50000
select   disk_reads, buffer_gets, executions, sql_id, sql_text
from     v$sqlarea        
where    disk_reads > 10000
order by disk_reads asc;

The last lines show the SQL statements with the highest number of physical reads.

Maybe it is only one or two statements which cause the low buffer cache quality.

You could post the last few lines if you are unsure how to interpret the result.

[ganimede_dignan]

Hi,

this are last lines:

1104534 1063409832 2155417 fv9cg83jzcwdj SELECT * FROM "ATP_RESB" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2 AND "XLOEK" = :A3 AND "KZEAR" = :A4 AND "BDTER" < :A5 AND "BDART" <> :A6 AND "SOBKZ" IN ( :A7 , :A8 ) AND "BDMNG" > "ENMNG" AND "TXTPS" = :A9 AND "SCHGT" = :A10 AND "DBSKZ" = :A11 AND ( "VORAB" IS NULL OR "VORAB" = :A12 )

1526024 29705793 4917214 d3u63070345ha SELECT * FROM "MARD" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2

1600909 2359936 19 71h7ak7snf0jw SELECT * FROM "MARD" WHERE "MANDT" = :A0 AND "WERKS" = :A1 AND "LGORT" = :A2 AND "MATNR" LIKE :A3

2971828 17012861 2651296 91k9w9dxks217 SELECT * FROM "MDRS" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2 AND "XLOEK" = :A3 AND "KZEAR" = :A4

3112761 24687525 2651316 bmqbapqqdu15m SELECT * FROM "EBAN" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2 AND "LOEKZ" = :A3 AND "EBAKZ" = :A4 AND "BSAKZ" = :A5

3642920 31006580 7894508 fxqhqw8fv4u93 SELECT * FROM "MARC" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2

4039730 19094874 4779491 0ru487p8k9xsm SELECT * FROM "MBEW" WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "BWKEY" = :A2 AND "BWTAR" = :A3

4242245 114268162 2017839 5ynun06y6z5g0 SELECT * FROM "JEST" WHERE "MANDT" = :A0 AND "OBJNR" IN ( :A1 , :A2 , :A3 , :A4 , :A5 )

7260940 14923222 273 9xyrrk9x6rjqw SELECT * FROM "AFKO" WHERE "MANDT" = :A0 AND "AUFNR" > :A1 AND "FTRMI" BETWEEN :A2 AND :A3

Regards.

[Former Member]

Hello Ganimede,

the data suggests that the low buffer cache quality is not caused by a single SQL statement.

So maybe your Oracle buffer cache is simply too small? Please run this command, it is possible

to estimate with this data which buffer cache size is required to get a data buffer quality > 90%.

Regards,

Mark

select size_for_estimate, size_factor, estd_physical_read_factor
from v$db_cache_advice
order by size_factor;

[ganimede_dignan]

SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR

-

---

-

---

-

---

544 .0988 2.04

1088 .1977 1.6367

1632 .2965 1.4184

2176 .3953 1.3116

2720 .4942 1.235

3264 .593 1.1833

3808 .6919 1.1317

4352 .7907 1.0819

4896 .8895 1.0407

5440 .9884 1.0039

5504 1 1

5984 1.0872 .9725

6528 1.186 .9444

7072 1.2849 .9089

7616 1.3837 .8804

8160 1.4826 .8541

8704 1.5814 .8337

9248 1.6802 .8161

9792 1.7791 .7937

10336 1.8779 .7719

10880 1.9767 .7541

Now is fast....

[ganimede_dignan]

This is mu initSID.ora

*._b_tree_bitmap_plans=FALSE

*._first_spare_parameter=1

*._fix_control='5705630:ON','5765456:3','6221403:ON','6440977:ON','6626018:ON','6972291:ON','6399597:ON','6430500:ON','6670551:ON','7325597:ON','6329318:ON','4728348:OFF'

*._optim_peek_user_binds=FALSE

*._optimizer_mjc_enabled=FALSE

*._sort_elimination_cost_ratio=10

*.background_dump_dest='/oracle/SID/saptrace/background'

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/SID/origlogA/cntrl/cntrlSID.dbf','/oracle/SID/origlogB/cntrl/cntrlSID.dbf','/oracle/SID/sapdata1/cntrl/cntrlSID.dbf'

*.core_dump_dest='/oracle/SID/saptrace/background'

*.db_block_size=8192

*.db_cache_size=5771362304

*.db_files=1022

*.db_name='SID'

*.event='10028 trace name context forever, level 1','10027 trace name context forever, level 1','10142 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name contex

t forever, level 1','10411 trace name context forever, level 1','14532 trace name context forever, level 1','44951 trace name context forever, level 1024','10629 trace name context forever, level 32','38068 t

race name context forever, level 100','38087 trace name context forever, level 1','38085 trace name context forever, level 1','10753 trace name context forever, level 2'

*.filesystemio_options='SETALL'

*.java_pool_size=0

*.log_archive_dest_1='LOCATION=/oracle/SID/saparch'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

*.log_buffer=1048576

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=800

*.optimizer_index_cost_adj=20

*.parallel_execution_message_size=16384

*.parallel_max_servers=160

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=4294967296

*.processes=340

*.query_rewrite_enabled='FALSE'

*.recyclebin='off'

*.remote_os_authent=TRUE

*.replication_dependency_tracking=FALSE

*.sessions=680

*.sga_max_size=10737418240

*.shared_pool_size=721420288

*.star_transformation_enabled='TRUE'

*.undo_management='AUTO'

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='/oracle/SID/saptrace/usertrace'

[Former Member]

Sorry for jumping in here, Mark already guided you in the right direction. Here is my opinion on this so far:

- there is no single bad statement responsible for the problem

- but there are definitely a bunch of bad statements in your system. For example the SELECT * FROM "AFKO" WHERE "MANDT" = :A0 AND "AUFNR" > :A1 reads 54663 blocks per execution and has 50% diskreads. This needs to be tuned and there are more of them

- we see from the db_cache_advice that you have a buffer cache of 5.5gb and an increase to 10gb will improve the situation, but not remarkably. But if you have free memory on the server you should still increase the buffer cache.

So, how large is your database after all? The size of some of the most accessed tables would be interesting too (for example AFKO, MARD, MARC, EBAN; MBEW).

At the moment we can say, you are working on too much data in relation of your cache size. And it looks like this is mainly cause by suboptimal SQL.

Cheers Michael

[ganimede_dignan]

Hi,

>- there is no single bad statement responsible for the problem

OK,

> - but there are definitely a bunch of bad statements in your system. For example the SELECT * FROM "AFKO" WHERE >"MANDT" = :A0 AND "AUFNR" > :A1 reads 54663 blocks per execution and has 50% diskreads. This needs to be tuned and >there are more of them

Oh... each time developers told me that is SAP standard

>- we see from the db_cache_advice that you have a buffer cache of 5.5gb and an increase to 10gb will improve the >situation, but not remarkably. But if you have free memory on the server you should still increase the buffer cache.

we use sga_max_size parameter setted to 10GB... so I try to increse db_cache_size... is it OK?

>So, how large is your database after all?

PSAPSR3 1392640.000

PSAPSR3700 61.440.000

PSAPSR3FACT 102.400

PSAPSR3ODS 102.400

PSAPSR3USR 5.120.000

PSAPTEMP 40.960.000

PSAPUNDO 40.960.000

SYSAUX 2.048.000

SYSTEM 1.536.000

Total 1544908.800

>The size of some of the most accessed tables would be interesting too

>(for example AFKO, MARD, MARC, EBAN; MBEW).

oh...

>At the moment we can say, you are working on too much data in relation of your cache size.

>And it looks like this is mainly cause by suboptimal SQL.

[Former Member]

Hello Ganimede,

just because some SELECT is a standard SAP SQL statement doesn't mean it is efficient or cannot be tuned.

One could spend really lots of time searching (and fixing) bad execution plans on Oracle databases.

A quick shot would be to run in sqlplus the SQL Tuning Advisor on sql_id 9xyrrk9x6rjqw:

sqlplus "/ as sysdba"
@?/rdbms/admin/sqltrpt.sql

If a SQL Profile is suggested by the SQL Tuning Advisor then this would most probably fix this problem,

but the benefit wouldn't be large enough to significantly raise the buffer cache quality.

Maybe an upgrade to Oracle 11g would help as well, because (from my experience) the 11g optimizer

is producing on average better execution plans than the 10g optimizer.

Regards,

Mark

[Former Member]

I agree, tuning of the SQL can be quite painful.

Your database is 1.5tb and the tables from above can easily be in 1 - 10gb range or even larger. This means your "active working set" is way over 5gb, a buffer cache of 10gb will make things better, but from a first impression it looks like you rather would have to set it to something like 16 - 32gb. Adding memory is often the simplest and quickest solution (though i prefer having the SQL statements tuned).

we use sga_max_size parameter setted to 10GB... so I try to increse db_cache_size... is it OK?

Well as far as i know sga_max_size shouldn't be set in the spfile, only set db_cache_size and shared_pool_size. Remember sga_max_size = db_cache_size + shared_pool_size + <some minor memories>

So you should assign the remainder of your server's free memory to db_cache_size, be careful not to over assign the physical memory, this will make things worse.

@Mark upgrading to 11g: yes, and think of the possibility of OLTP compression, this reduces those tables by large factors AND the blocks will be compressed in the buffer cache too. This will effectively double or triple your database memory!

Cheers Michael

[ganimede_dignan]

.

[ganimede_dignan]

.

[Former Member]

Hm, there are a lot of threads on this, you should check them out: [SDN search on buffer quality|http://www.sdn.sap.com/irj/scn/advancedsearch?query=buffer+quality#sdn_content_category_value_forums]

Cheers Michael