Question Re: Buffer gets value is too high in Additional Q&A

Buffer gets value is too high

Former Member — Wed, 30 Dec 2009 01:16:59 GMT

Dear All,

I am in the process of monitoring ERP system performance. I thought of looking at expensive SQL statement and the criteria I gave was 5% of total logical read that displayed in the initial screen of ST04N. When I give 5% as a criteria to view SQL request I get an error message u201CInput value to large (maximum 2147483647)u201D. In this scenario what is the idle BUFFER GET value that I can look for to analysis expensive statement?

5% of buffer gets comes around 5656101704.

Regards,

Arvind.K.M

Re: Buffer gets value is too high

Former Member — Fri, 01 Jan 2010 21:55:08 GMT

Hi,

I'm not sure what the exact questions is but there are lots af ways to analyse performance bottlenecks. One of them is looking in the shared cursor cache(i asume that is what you where doing). The following line i found in the ADM315 cource. Hope this wil help you.

(If you are using Oracle you may want to check Note 618868 - FAQ: Oracle performance)

Finding Expensive SQL Statements Using the Shared

Cursor Cache

1. Call ST04 and choose Detailed Analysis Menuu2192SQL Request: Analysis of

Shared Cursor Cache.

2. On the popup selection screen, enter the following:

For Buffer gets, choose a number equal to 5% of reads from the entry

screen of ST04. There might be expensive statements causing less than

5% load on the database, but usually these have little impact on system

performance, even when optimized.

Choose List sort for Buffer gets.

3. On the resulting screen, you can analyze:

Sorting for disk reads yields the SQL requests causing significant load

on your physical devices (hard disks).

Sorting for statements that result in more than 5 Bgets/row indicate

expensive statements.

In the SQL statement column you will find the statement causing the buffer

gets.

4. Click on the SQL statement and proceed to analyze this statement as

described in the How to Use the Database Process Monitor list.

Result

You have identified and analyzed the SQL statements that cause the highest

number of buffer gets on your database system. These statements are likely

candidates for tuning measures. However, not all SQL statements can be

optimized by you. Also, you need to know that you have not yet found the

statements causing the highest number of hard disk accesses -- we did not select

for those. If you are interested in those statements, change the selection options

given above accordingly.

Kind regards, Glenn

Re: Buffer gets value is too high

Former Member — Wed, 06 Jan 2010 00:59:42 GMT

Dear Rooji van

Let me put my question little more clearly. To analysis expensive statement I go to ST04 and choose Detailed Analysis Menu u2013 SQL request: Analysis of Shared Cursor Cache. On the popup selection screen we have to enter buffer gets, ideally we give 5% of reads from the entry screen of ST04. In my case, I guess goes beyond SAP limit of maximum 2147483647. In this case, what would an ideal value to list the expensive statement to analysis?

Regards,

Arvind K.M

Re: Buffer gets value is too high

Former Member — Wed, 06 Jan 2010 07:33:53 GMT

Just enter a value that is accepted like 2mio and order the list descending on the "buffer gets" column. Then work your way down the list. By the way it looks like the more current releases support now higher values. In a ERP 6.0 SP 15 system i can enter values like 2'000'000'000 without a problem.

Cheers Michael