Lot of Sequential Reads, Performance Issues

[former_member524429]

Hello Friends,

Lot of Sequential Reads on lot of daily routine Tables.

Currently, on our SAP R3 4.6C PRD server, lot of sequential reads are happened. This shows that search is carried out without using Indexes. The Sequential reads are running same both for Standard Programs as well as Z Programs.

Also, The Quality Ratio of Database Buffer is below 88%. The size of the R3 Database is arround 1.5 TB.

I traced and cheked all the indexes of daily used Tables, on which the Sequential read are happening.

I also noticed that, even though the Indexes are used in search, its taking long time and showing the "Sequential Read" status while monitoring.

I also updated the Statistics of Tables and their concerned Indexes, but its not giving th expected results.

Due to lot of sequential reads on Tables, most of Dia and BGD Work Processes are taking long time to complete steps, and so, performance related issues are coming, since 1 Month.

I want to resolve this issue with minimum downtime.

I will wait for your preciouse replies

Thanks & Regards,

Bhavik G Shroff.

[Former Member]

hi ,

if you believe you are almost Ok with your ORACLE parameters , tack immediatly

FULL SCAN on big table by ST12

or ST04 and by doing the analysis of the oracle cache and look at the different ration gets/executed ......

good luck

Eric

[Former Member]

Eric is right. If you have Solution Manager System setup to run EWA reports for your R/3, then analyze the report and start looking at the programs that are having issues, get development involved as well. There could be tables that would need reorganization that is reported by EWA.

Also look at ST03N, workload monitor and spend time analyzing the system behaviour over the past months / weeks / days and notice how the performance has deteriorated. Check if system usage has increased.

Directly making changes to database without spending time on analysis and planning is dangerous to R/3 system or any enterprise application.

[former_member524429]

Hi,

In System Landscape, Solution Manager 3.1 is working. So, there is no scope of generating EWA Report Locally.

I have a doubt on the Database Buffer Quality Ratio, which is right now below 86%. It is also one of the affecting Performance Related factors.

Also, I can not ignore the load of Z Programs, which are coming in hit list of Global WorkLoad Analysis. There are also several Standard Programs, which are seen in the list along with Z Programs.

Anyway, Right now , I am doing total Analysis from the scratch.

I will share the factors, which are critical and can be helpful in Tuning the Performance.

Regards,

Bhavik Shroff

[Former Member]

check your storage, problem may be with storage also check whether you have any I/O wait on database drive/filesystem.

on which OS you are on? windows/UNIX and how is your storage configured?

cheers,

-Sunil

[markus_doehr2]

Currently, on our SAP R3 4.6C PRD server, lot of sequential reads are happened. This shows that search is carried out without using Indexes. The Sequential reads are running same both for Standard Programs as well as Z Programs.

If you see in SM50 "sequential read" doesn´t mean, that no index is used. See Note 825653 - Oracle: Common misconceptions, point 15.

I also noticed that, even though the Indexes are used in search, its taking long time and showing the "Sequential Read" status while monitoring.

Again - see above. What do you see in ST04 for that session?

Due to lot of sequential reads on Tables, most of Dia and BGD Work Processes are taking long time to complete steps, and so, performance related issues are coming, since 1 Month.

Did you upgrade your database? Did you implement patches?

Markus

[former_member524429]

Hello,

Did you upgrade your database? Did you implement patches?

The Oracle Database virsion is 9.2.0.6.0.

The SAP R3 Kernel status: 46D, 2113 Level.

Nither SAP Kernel nor Oracle Database are upgradaed since its Implementation.

The Size of Database is 1500 GB (Growth Rate 60 GB/Month).

Regards,

Bhavik Shroff

[markus_doehr2]

Ok.

So what do you see in ST04 during those long runs? Did you create an SQL trace for a program in ST05 to check if the correct index is used?

Markus

[former_member524429]

There are frequent Event Messages like "db file sequential read" for those Expensive Sessions, I noticed, before.

I also checked the SQL statments, which are having this kind of Event Status. Most of SQL Statments are following Index Scaning while fatching Records.

[markus_doehr2]

Ok.

So to analyze what the problem might be I suggest you open an OSS call and let the support have a look on the system. It's difficult, if not impossible, to suggest here something without having system access and without having the possibility to look at the statement and the table content itself.

Markus

[former_member204746]

schedule an earlyWatch session from SAP, you are entitles of 2 per year per SID. it is free and is part of your contract.