How Performance can be improved at Oracle Database level

[Former Member]

Hi Everyone,

Need a suggestion ......

I m managing SAP Landscape of Retail Sector Client.

where SAP ERP PRD database size is : 2 TB.

at SAP Application Layer .... we have setup five application system to distribute load because around 1200 users work concurrently.

Out of these 1200 Users around 300 Users keeps on fetching

reports from database which worsen database performance.

We have changed parameters of Database to improve performance.

What we have thought to setup replica of PRD system .

On which Only Report Users will execute reports.

& that System will work as Disaster Recovery Site of PRD System.

In process of that We have restored the PRD Backup on another System. But How can we replicate daily changes of PRD System on Restored System.

One which we have thought by applying offline archive files.

daily . But somebody has told us that we can not apply

offline archive files of PRD System to restored system

because that Restored database is in Open stage & being

used.

(ii) Secondly, Incremental Backup which is very time-consuming action.

Please suggest , If you have an idea & I want to know How

offline archive log file application on DR Site .

Thanks in advance !!!

Points will be rewarded !!!

Thanks

Deepak !!!

[former_member204746 · Accepted solution]

Install SAP BI (previously named SAP BW).

then ask people to get reports on the BI system.

SAP R/3 (or ECC) is a transactional system and is not the best for reporting. BI is the tool you should use for reporting.

[former_member204746 · Accepted solution]

maybe you did not like my answer, but using SAP BI is the real long-term solution for you.

[Former Member]

Thanks to everyone who has replied !!!

[Former Member]

Hi Fidel,

SAP Note which you have mentioned has clearly stated

that we can not run SAP when database opened as read only

& why .

Which has cleared my doubt ..

Thanks !!!

[Former Member]

Hi Eric ,

Its not like that I have not liked your answer .

What you have suggested is very right & best solution ?

In our landscape we don't have SAP BI solution.

We are negotiating on that with vendors , which will take time.

I know Z* reports in our R/3 Systems are not rightly coded.

There are expensive sql statements . But our management wants an immediate solution of that & one senior IT head has recommended for replica of Primary database & wants the load of reporting to be shifted on that.

Management asked from me How can we go ahead for that.

Database in constant sync with PRD database & also open End Users for fetching reports

I suppose now you have get clear understanding why I am very keen on this solution.

Regards

Deepak

[fidel_vales]

Hello

>

> I know Z* reports in our R/3 Systems are not rightly coded.
> There are expensive sql statements . But our management wants an immediate solution of that & one senior IT head has recommended for replica of Primary database & wants the load of reporting to be shifted on that.

If you want to improve those reports then you have to "tune" them.

You mention that there are expensive sql statements. Are they missing indexes, bad indexes, bad coding, too many records returned, repeated queries ....?

they should be tune, that is the best solution in your current situation.

[Former Member]

Thanks Lars for your reply !!!

I have one doubt ..... I know you have already mentioned in your reply that we can't have this but again...for clarification..:-)

Can We keep standby database in read-only mode ......?

i.e. Users can use to extract reports (extraction of reports

is reading data as I know) no insert/update/delete on standby database .

If Yes , then by keeping standby database open in read only mode . Can we apply the changes ( i.e. offline redolog files

of Primary Database) daily on Standby database which has been opened in read-only mode.

If not , then Can You Please tell me the reason ..??

Thanks again !!!

[fidel_vales]

Hi,

Lars indication is because even if the DB is read only (so you can apply logs afterwards) SAP is trying to to perform changes on the database.

SAP note 817253 talks about it.

[lbreddemann]

>

> Hi Everyone,

Hi Deepak!

> Need a suggestion ......

I'll try my best

> I m managing SAP Landscape of Retail Sector Client.

> where SAP ERP PRD database size is : 2 TB.

> at SAP Application Layer .... we have setup five application system to distribute load because around 1200 users work concurrently.

>

> Out of these 1200 Users around 300 Users keeps on fetching

> reports from database which worsen database performance.

Hmm.. might happen.

> We have changed parameters of Database to improve performance.

Oh - which ones? Why? Who told you to?

> What we have thought to setup replica of PRD system .

> On which Only Report Users will execute reports.

> & that System will work as Disaster Recovery Site of PRD System.

This setup does not work for SAP environments. To get reports out of a system with SAP the database must be open to writes.

> In process of that We have restored the PRD Backup on another System. But How can we replicate daily changes of PRD System on Restored System.

Such thing would be done via a Standby-DB.

> One which we have thought by applying offline archive files.

> daily . But somebody has told us that we can not apply

> offline archive files of PRD System to restored system

> because that Restored database is in Open stage & being

> used.

That person told you the truth.

> (ii) Secondly, Incremental Backup which is very time-consuming action.

Depends on your reference. Compared to what is it very time-consuming?

Compared with a full DB-Backup this is likely to be the less time-consuming option.

> Please suggest , If you have an idea & I want to know How

> offline archive log file application on DR Site .

For a how-to in Standby-Setup you may want to have a look into the Oracle documentation like here [Introduction to Oracle Data Guard |http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/concepts.htm#SBYDB00010]

> Thanks in advance !!!

You're welcome !!!

> Points will be rewarded !!!

Oh - nice !!!!!!!!!!

Now get a bit earnest as well:

From what you described it sounds like you're setup is a bit undersized.

If your report users use BI for their reports, than it may pay off to setup a seperate BI system that gets it's data from the ERP system automatically and not whenever a user starts a report.

Another option may be to consider using a RAC cluster - but this would be a very expensive option.

In any case the important first step has to be to find out, what the real cause for the performance degration is.

Perhabs the I/O Subsystem is the problem, perhabs the network. Perhabs another CPU would do wonder.

You'll never know - before you analyse this in depth.

Reading hints would include these notes:

618868 FAQ: Oracle performance

72638 Performance problems with SQL*Net

354080 Note collection for Oracle performance problems

853576 Oracle 10g: Performance analysis w/ ASH and Oracle Advisors

793113 FAQ: Oracle I/O configuration

619188 FAQ: Oracle wait events

805934 FAQ: Database time

Best regards,

Lars