Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th June 2013, 16:08
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Performance report problem (windows - sql vs. AIX - Oracle)
Baan: Other/Unknown
C/S: None/Unknown

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.
Reply With Quote
  #2  
Old 6th June 2013, 17:18
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 6th June 2013, 17:30
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
user app server load

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.
Reply With Quote
  #4  
Old 6th June 2013, 18:54
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Quote:
Originally Posted by mark_h View Post
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?
Reply With Quote
  #5  
Old 6th June 2013, 18:59
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Quote:
Originally Posted by Hitesh Shah View Post
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?
Reply With Quote
  #6  
Old 6th June 2013, 22:22
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Quote:
Originally Posted by gget79 View Post
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #7  
Old 7th June 2013, 15:13
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Quote:
Originally Posted by mark_h View Post
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.
Reply With Quote
  #8  
Old 7th June 2013, 15:56
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #9  
Old 7th June 2013, 16:12
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Quote:
Originally Posted by mark_h View Post
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?
Reply With Quote
  #10  
Old 10th June 2013, 10:15
Han Brinkman's Avatar
Han Brinkman Han Brinkman is offline
Guru
 
Join Date: Aug 2001
Location: The Netherlands
Posts: 1,155
Han Brinkman has a spectacular aura aboutHan Brinkman has a spectacular aura about
Baan: All - DB: Oracle/ms-sql/db2 - OS: *nix/windows
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
Reply With Quote
  #11  
Old 10th June 2013, 14:53
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Han pretty much covered the option I use, but you can start with these two threads
http://www.baanboard.com/baanboard/s...&threadid=7665 or
http://www.baanboard.com/baanboard/s...bshell+options.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Sponsored Links
  #12  
Old 10th June 2013, 15:29
gget79 gget79 is offline
Senior Member
 
Join Date: Nov 2006
Posts: 263
gget79 is on a distinguished road
Baan: 5 - DB: ORACLE - OS: UNIX
Quote:
Originally Posted by mark_h View Post
Han pretty much covered the option I use, but you can start with these two threads
http://www.baanboard.com/baanboard/s...&threadid=7665 or
http://www.baanboard.com/baanboard/s...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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL report package msg error renwee510 Tools Development 3 30th July 2008 14:15
solving recursion problems rmarles Tools Development 2 25th January 2005 21:23
Oracle 9i on AIX has poor performance Caner.B Performance & Tuning 7 22nd January 2004 06:48
BaanIVc4 and Oracle 8.1.6 performance problem xmorales Performance & Tuning 15 7th March 2003 08:45
Oracle 8i performance solution gguymer Performance & Tuning 19 26th January 2002 13:07


All times are GMT +2. The time now is 09:09.


©2001-2017 - Baanboard.com - Baanforums.com