SAP Resource Optuimization

[Former Member]

Hi,

We have 5 TB of DB size with 1 DB and 4 Dialog instance.

RAM 32 GB IBM AIX + Oracle

SGA of 6 GB.

But most of the dialog WP are in sequential read status......there are more no. of records in each table but we have proper indexing.

Is there any other way to improve the performance other than table archiving to avoid many sequential reads...like which buffers I have to increase?

Regards,

Yash

[Former Member · Accepted solution]

Well, if you have many statements that are executing sequential reads, this indicates that your indexes are far from optimal.. Do you run the database statistics on a regular basis? Also check the parameter recommendations for Oracle and the database patches. There are some fixes on the CBO which can also result in unfavorable access paths to the database.

You should also check the execution plan of your statements. Go to st04old -> detailed analysis -> Oracle session -> and select one of the statements. Here you can see the cost of the particular statement and the index that it's using and in what way.. If you have many full table scans, than there is really something wrong..

Just by archiving data your problem will most likely not be solved.

Kind regards,

Mark

[Former Member · Accepted solution]

Thanks Mark,

I do understand that we have to tune the statements.

But there are many sequential reads for standard tables like MARC, EKBE, MBEW with standard reports so we can hardly achieve anything after tuning.

So I was seeking to get some information on memory related areas where I can tune buffers or increasing the SGA size or oracle processes will boost my system performance?

Regards,

Yash

[Former Member · Accepted solution]

By increasing the db_cache_size, which is part of the SGA, you can increase performance slightly..

First I would start investigating the statements.. Also check the quality of your indexes. If the quality is below 25% (I believe), than you should rebuild them.. Check note "444287 - Checking the index storage quality" for further details.

Kind regards,

Mark

[former_member182114 · Accepted solution]

Hi Yash,

But there are many sequential reads for standard tables like MARC, EKBE, MBEW with standard reports so we can hardly achieve anything after tuning.

Sometimes we run out of ideas when trying to solve the "database" problem because we ignore the business and users, specially for standard reports. Filters/restrictions informed by the user can easily provoke full scans.

Maybe it's not your case, but keep it in mind. Sometimes the solution is training.

Try to follow these sequential reads monitoring STAD to check the transaction and user that are performing it.

Something that can help:

Atenciosamente, Fernando Da Ró

[Former Member · Accepted solution]

Thank you all.

I have also done analysis from my side and after going through EWA, there it was showing high DB time.

I checked the Index storage quality and it is more than 85% so hopefully this should not be the problem.

Yes, end user training also be userful as there are 2-3 users executing the same report so there are 6 sequential reads on the table which may be the cause of this issue.

Now I started tuning the queries..lets c if I get any success I'll update here...

Cherrs,

Yash

[rakesh_ranjan]

Hi Yash,

as you are seeing lots of sequential reads often causing performance problem, you need to fine tune expensive SQLs even before you think of archiving the tables.

In order to pick expensive SQL you can do this

pick the PID (Process ID) long running sequential reads from SM66 -> ST04 -> Detailed Analysis menu -> Oracle Process -> fine the Oracle process with PID -> see the sql statement and explain plan. Try to tune this statement.

Regards,

rakesh

[former_member189546]

Hello,

Check your memory parameters with these notes

Large extended memory on AIX (64-bit) as of Kernel 789477

912425 - AIX: Unexplained high memory consumption

regards,

John Feely