Hi Experts,

I'm working with LN FP7.

I have a problem, I ask your help please:
1. We have a report that a production environment (AIX - Oracle 11), execution takes about 30 minutes.
2. The same report in our development environment (Windows - SQL Server 2008), with the same data and the same selection filter execution takes 4 minutes.
3. We have reviewed and optimized report queries in order to improve but we have not succeeded.

As far as I understand the programming logic is independent of the application architecture

Can anyone help me to know whether to fix something BD level, configuration or operating system?



Thanks.

The first thing I think of is - have you had your oracle dba's look at the execution plan or just watch it run? I know our DBA's can recommend indexes or put in profiles to assist performance.

While there is scope for tuning at OS , db level and baan driver level ,there could be 2-3 things to cause performance difference.

1. porting set difference / bug in bd
2. app load on servers due to difference in number of users
3. Difference in server resources like ram / processor etc.

The first thing I think of is - have you had your oracle dba's look at the execution plan or just watch it run? I know our DBA's can recommend indexes or put in profiles to assist performance.

Thank you dear for your reply.

I previously mentioned the case in the forum, DBAs and talked to told me that all maintenance processes database are current. You refer to this process, ie run statistics database?

While there is scope for tuning at OS , db level and baan driver level ,there could be 2-3 things to cause performance difference.

1. porting set difference / bug in bd
2. app load on servers due to difference in number of users
3. Difference in server resources like ram / processor etc.


While there is scope for tuning at OS , db level and baan driver level ,there could be 2-3 things to cause performance difference.

1. porting set difference / bug in bd, "THIS TOPIC I'M GOING TO VERIFY"
2. app load on servers due to difference in number of users, "THIS TOPIC IS NOT IMPORTANT, BECAUSE I HAVE TESTED IN THE SERVER WITHOUT USERS"
3. Difference in server resources like ram / processor etc. "THIS TOPIC IS NOT IMPORTANT, BECAUSE THE PRODUCTION SERVER, IS MORE STRONG THAT OWN DEVELOPMENT SERVER THE RELATION IS 4 TO 1"

ABOUT FIRST TOPIC WHAT SHOULD I DO?

Thank you dear for your reply.

I previously mentioned the case in the forum, DBAs and talked to told me that all maintenance processes database are current. You refer to this process, ie run statistics database?

Not just database statistics - we update those I think nightly. No - our Oracle DBA's can run grid control to see what queries are running, running long, etc. Then they can pull out recommendations - build an index, or add a profile. I am not a DBA so I don't know all the tricks.

Just last week they watched an application run a query. Oracle recommended an index, they went and added it. The query went from running in 20-25 minutes to 3-4 minutes. This was not baan, but they have used it before with BaaN.

Not just database statistics - we update those I think nightly. No - our Oracle DBA's can run grid control to see what queries are running, running long, etc. Then they can pull out recommendations - build an index, or add a profile. I am not a DBA so I don't know all the tricks.

Just last week they watched an application run a query. Oracle recommended an index, they went and added it. The query went from running in 20-25 minutes to 3-4 minutes. This was not baan, but they have used it before with BaaN.

I am very grateful to all the experts for their help.

I have talked with the DBAs and tell me they do not know the solution that I suggest. I will consult you if someone can give me a guide to start research on this topic.

But I have a question, based on your answers, I understand that as the engine of the database, we must perform additional actions, for which additional shares will have to implement when there are performance issues?

Thank you all.

Another question is it the report or is it the session which generates the data for the report that is taking longer? You can turn on some tracing for baan - launch the program and get some of the run times for the queries. Sometimes that helps you see which query is having the issue. Also making sure you use index - instead of letting oracle pick which index to use. You can search on tracing or debugging to find some recommendations on it. Plus there is a Performance and Tuning guide you can get from Infor - it is on the board somewhere, but maybe infor has something more current.

Another question is it the report or is it the session which generates the data for the report that is taking longer? You can turn on some tracing for baan - launch the program and get some of the run times for the queries. Sometimes that helps you see which query is having the issue. Also making sure you use index - instead of letting oracle pick which index to use. You can search on tracing or debugging to find some recommendations on it. Plus there is a Performance and Tuning guide you can get from Infor - it is on the board somewhere, but maybe infor has something more current.

Thank you for your help.

Can you attach this guide?

Hi gget,

Since you are not complaining about other sessions are slow I would suggest to start tracing the session that's slow first.

A good way to start is to run it with TT_SQL_TRACE=0200. That gives you the fetch times for the queries. In the logfile you should be able to find the 'slow' query.

Another thing you could do is to ask your dba to trace your sql session within oracle or, if you have enough permissions, you can do this yourselve using SQL_TRACE=true. With tkprof your dba can analyze the trace and probably identify the problem too.

Regards,
Han

Han pretty much covered the option I use, but you can start with these two threads
http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665 or
http://www.baanboard.com/baanboard/showthread.php?s=&threadid=95&highlight=bshell+options.

Han pretty much covered the option I use, but you can start with these two threads
http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665 or
http://www.baanboard.com/baanboard/showthread.php?s=&threadid=95&highlight=bshell+options.

Hi,

Thank you very much.

I found before a good trace, this trace show me that some queries are slow, this queries, with the same data in my development environment (windows - SQL server 2008), taking 4 times more than that time take production server (AIX - Oracle 11).


The trace is the next:
-- -set INFPROF=3 -set EXPLAIN=1 -set BLAT="sql s 01111 th 3" -set PROFILE_ALL=1 -set PROF_RTIME=1 -set BDB_ALWAYS_FLUSH=1 -set PROF_DIR=C:\tmp\get\prof tfcpe4400m001