Performance Issue with Oracle

[Former Member]

Dear All,

Let me tell you about the system first:

4.6D Oracle 9i HP UX on super Dome.

DB Size 5.4 TB

Concurrent Users approx 4000, Periodic Jobs daily 1500

Oracle SGA MAX set to 70 GB, DB server RAM 132 GB. 11 Application servers.

Now come towards the actual Issue;

During Month End Closing we are facing lot of performance issues with DB server. we have to limit overall background jobs running at the time to 50 Only we can not make it more than this otherwise we will face serious performance issues with the system to Normal users.

The system behaves normal during other days of the month but during MEC 4 days we have to suffer a lot with lot of background jobs delaying and affecting lot of companies for Month End Closing.

Next month we are planning to Upgrade oracle to 10G 4th Patch, not because of the issues but other reasons.

During this upgrade I would really like to know the enhancement we can make at DB Level to increase the performance. according to ST04 it really shows normal behavior over month like DB Quality is over 96% user per recursive calls are 16,5

we have currently 3 DB Writer process can we increase them? Do we get any benefits from this?

Please help me concentrate on the enhancement I can make with this upgrade.

Regards

Shailesh

[former_member524429 · Accepted solution]

Hi,

Performance Tuning activity is ongoing process.

Such performance tuning should be done not only at DB level, it also should be done at SAP R/3 and OS level. Lot of factors you will have to monitor and analyze, which are causing such performance bottleneck such as,

-> DB I/O load,

-> CPU load at SAP Instance level as well as at DB level,

-> Expensive SQL Statements, Expensive DB Table access path due to old DB statistics or missing indexes, etc.

-> Memory Bottleneck at SAP instance Level as well as DB level,

-> NFS issues (if its there), etc, etc...

Refer these useful Best Practice documents ([doc1|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/0667b7c9-0e01-0010-e4a3-873e87656048?quicklink=index&overridelayout=true], [doc2|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234?quicklink=index&overridelayout=true]) to get more information with detailed understanding.

Regards

Bhavik G. Shroff

[Former Member · Accepted solution]

Dear Bhavik,

I completely understood your views towards the process.

Thankd for the docs will go through it carefully.

did you put some light on the DB Writer process? can we add some more. Do you have experience with such large DB's? and what are the advatages we get with 10G is mainly my intrest here?

Regards

Shailesh

[Former Member]

Hi,

Like the others said, it's not easy to address performance issues through a forum like this, you have to make a full analysis at your environment, especially during the the periods of worse performance, each environment is very specific.

During month end closing usually you have intensive updates and inserts at the database, but increasing the number of db writers can be worse depending on what is your bottleneck.

Check for operating system configuration to give you the best performance on filesystems according to note 1077887 if you haven't done so already.

Check if you don't get a lot of checkpoint not complete, and adjust the redo logs accordingly. Also check note 1068186 (after the upgrade to 10g).

After the upgrade, don't forget to run the noworkload dictionary and system statistics as per note 838725. And check for bad SQL commands - it's always nice to do it regulary, but after the upgrade you no longer have rule based optimization, a few reports may present a performance degradation due to this.

Hope it helps.

[Former Member]

Hi,

Is it possible to paste you int<SID>.ora file....

What disk subsystem are you on ?

What flava apps are you running?

What are your OP modes setup for end of month.

Are you archiving ?

Are you table reorging ?

How big are yor redo logs ?

Have you split online/offline redos onto separate physical disks ?

Mark

[fidel_vales]

Hi,

First I'd like to apologize in advance.

why, because I think that everybody is shooting bullets to the air hoping that one works.

In resume, guessing, and I do not think that is the correct way.

Some of he points addressed are valid and may even solve the problem.

But in definitive, the problem is NOT known. ant it is the work of the OP do a propeer analysis.

Unfortunately he is in 9i, version that is in "payable" extended maintenance and, more important in this case, do not have any of he nice new features of 10g (ASH,AWR)

what do we know?

During Month End Closing we are facing lot of performance issues with DB server. we have to limit overall background jobs running at the time to 50 Only we can not make it more than this otherwise we will face serious performance issues with the system to Normal users. 

How do you know is the DB server the one with problems?

The little description you give it could also point to a lack of enough workprocesses for the users.

what are the syntoms? what sre the sql statements, what is the DB load? reallly, lot of not answered questions.

How do you analyzed the performance of the DB server? are you running STATSPACK or any other monitoring tool?

During the MEC, what are the predominant wait events (HOW is the DB time distributed)?

> During this upgrade I would really like to know the enhancement we can make at DB Level to increase the performance.

Difficult to provide real recommendations about performance if you do not know the ccurrent bottlenecks. Have you contacted SAP about this? there are services were they can analyze the DB or the MOC process and provide you recommendations with more data than the one you probably provide here.

> DB Quality is over 96%

Forget about that and learn about wait event analyssis, that is a much better way to analyze performance (I would say is thee correct way)

> we have currently 3 DB Writer process can we increase them?

Have you seen any issue with the DBWriter? if not, incereasing them will not help at all.

As I mentioned, you have to analyze the DB (or the MEC process) to know where are the bottlenecks to address them, any other thing is shooting bullets hoping that one hits the target. Find firsyt the target.

[Former Member]

Note for your ref:

SAP Note 793113

Regards,

Nick Loy

[Former Member]

Thanks Nick,

We have db_cache_size set to 0. and SGA_MAX_SIZE to 70Gb.

Thanks for the inputs.

Can you just list some of the features from Oracle 10G which can make difference in this case. so that I can concentrate on those only.

Regards

Shailesh

[Former Member]

Hi,

I know it might not so much help ful in your concerns.

But kindly cross check all the kernel parameters as per your existing hardware configutation.

Regards,

Kamal

[Former Member]

Oracle SGA MAX set to 70 GB, DB server RAM 132 GB

What about the db_cache_size? what is the current value do you have?

Coming to Oracle 10g, there are so many features added realted to application and as well as performance.

First of all upgradation to 10g will give you good performance, after upgrading just install all bugfixes and work with you FS config at OS level.

For file system configurations, just search SMP with keywords SAP Recommended file system configurations

Regards,

Nick Loy

[anindya_bose]

Sailesh

Is there any recommendation in Early Watch Alert Report?

In general, you can look for Database Reorganization, Database Statistics collection etc..

Please check SAP Note 354080 - Note collection for Oracle performance problems

Regards

Anindya

[Former Member]

Dear Anindya,

Thanks for the inputs, but we already have all process in place for the current DB, all the staticstics are current, all tables are well maintained with Indexes with all possibilities.

Thankd for the Note I will go through this once again in detail.

Regards

Shailesh Mamidwar

[anindya_bose]

Sailesh

Can you please tell me what is the value of Parameter OPTIMIZER_INDEX_COST_ADJ?

Could you please check Note 483995 - Oracle <= 9i, OLTP: Parameter OPTIMIZER_INDEX_COST_ADJ?

Thanks and Regards

Anindya

[Former Member]

Dear Anindya,

The value is 10 according to the Note and I think its a standard value for this parameter. And in Oracle 10G this value we are going to ser it to 20 as per the standard Parameter recommendation.

Regards

Shailesh

[fidel_vales]

I'll continue here as post too big do not get formated properly

> Database Reorganization, Database Statistics collection etc..

DB Reorganizations O_o. Sorry, but my opinion is that before doing a reorganization you have to analyze if really will bring any benefit. If you want to ssave space, may be. For performance ... it may help or may leave things worse or change nothing..

Statistics MUST be calculated afresh after the upgrade. Then the "standard rules apply".

Please follow properly the upgrade guide (I've seen too many upgrades that didn't follow it and had issues)

Review the parameters and install all required patches (paying special attention to the HOTNEWS and the optimizer merge fix patch)

Personally, I do not like much the documents pointed, I think they have some wrong information and misconceptions, but that is something else.

> We have db_cache_size set to 0. and SGA_MAX_SIZE to 70Gb.

why db_cache_size is set to 0? are you using DB_BLOCK_BUFFERS?

this does not make much sense

One thing that it is not mentioned on the guide is to increase the AWR retention time (note 1326067) but this is only valid if you are going to use AWR/ASH analysis (Highly recommended) and you need an extra licende for that (unless Oracle licence was adquired with SAP)