Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 13th November 2008, 23:19
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Oracle 10 Question Looking for Suggestions
Baan: Baan IVc4
DB: Oracle 10.2.0.x
OS: Solaris 10 (SunOS 5.10)
C/S: None/Unknown

In this case I am talking about a custom session. We recently upgraded to a new SP and Oracle 10g. This had horrible performance - an Oracle profile was added and the session runs fine. Well, because of other issues we set all recommended parameters, bounce the database. When it comes backup - we test this session just fine. The next day the users hit the session and we are back to horrible performance. The DBA's remove the profile and then the session works fine. Now two weeks later(yesterday) the session starts performing bad again - DBA's add the Oracle profile and the session is back to normal. So far none of the other session problems have come back since we set the suggest parameters - again,so far just this one session cropped back up as a problem. Any suggestions on what might cause something like this?

I included one of the main queries on the session ---please note that I did not write either of these queries --- I want to rework the query and break it up, but so far we have agreed to wait and see if the problem comes back again. Is there something in 1 of these queries which might cause problems - maybe something like UNREF CLEAR or refers to???? The two queries are called based on input parameters - query 1 is below:
Code:
	select	tipgc520.*,
		tppdm065.desc,		| Project Group description
		tccom020.clan, 		| Supplier Language
		tccom020.nama, 		| Supplier Name
		tcmcs002.crnd, 		| Currency rounding factor
		tcmcs046.lang,		| Language
		tbuyr.nama:buyr.nama,	| Buyers name
		tcplb.nama:cplb.nama,	| Planners name
		tiitm001.citg,		| Item Group
		tiitm001.cuni		| Conversion
		,tiitm001.dsca,		| Descritpion		|#40b2a&d2.n
		tiitm001.csig		| Signal Code
	from	tipgc520, tppdm065, tccom020, 
		tcmcs002, tccom001 tbuyr, tccom001 tcplb, 
		tiitm001
	where   tipgc520._index1 inrange {:ccot.f, :orno.f}
	                             and {:ccot.t, :orno.t}
	    and tipgc520.item between :item.f and :item.t
	    and tipgc520.suno between :suno.f and :suno.t
	    and tipgc520.podt between :podt.f and :podt.t
	    and tipgc520.osta between :osta.f and :osta.t
	    and tipgc520.buyr between :buyr.f and :buyr.t
	    and tipgc520.cplb between :cplb.f and :cplb.t
	    and tiitm001.citg between :citg.f and :citg.t
	    and tipgc520.osta <> tcorsa.actualized	| Transfered
	    and tipgc520.item refers to tiitm001
	    and tipgc520.ccot refers to tppdm065
	    and tipgc520.suno refers to tccom020 UNREF CLEAR
	    and tccom020.clan refers to tcmcs046 UNREF CLEAR
	    and tipgc520.ccur refers to tcmcs002
	    and tipgc520.buyr refers to tbuyr UNREF CLEAR
	    and tipgc520.cplb refers to tcplb UNREF CLEAR
	order by tipgc520._index1
	selectdo

Query 2 is run when the user includes the project and CCN
Code:
	domain  tcamnt  discount.amount		| to calculate discount	|552.sn
	domain  tcamnt  net.amount		|   ,,     ,,     ,,	|552.en

 	select	tipgc820.cprj, tipgc820.cspa, tppdm600.cprj, tppdm600.ccot,
 		tipgc521.*, tipgc520.*, tppdm065.desc, tccom020.clan,
 		tccom020.nama, tcmcs002.crnd, tcmcs046.lang, tiitm001.citg,
 		tiitm001.cuni, tiitm001.dsca, tiitm001.csig,
		tbuyr.nama:buyr.nama, tcplb.nama:cplb.nama
 	from	tipgc820, tppdm600, tipgc521, tipgc520, tppdm065, tccom020,
 		tcmcs002, tcmcs046, tiitm001, tccom001 tbuyr, tccom001 tcplb
	where	tipgc820._index2 inrange {:cprj.f, :cspa.f} and
					 {:cprj.t, :cspa.t} and
 		tipgc820.cprj refers to tppdm600 and
 		tppdm600.ccot inrange :ccot.f and :ccot.t and
		tipgc521.cprj = tipgc820.cprj and
		tipgc521.butm = tipgc820.butm and
		tipgc521.sbtm = tipgc820.sbtm and
		tipgc521.eser = tipgc820.eser and
		tipgc521.eseq inrange tipgc820.unfr and tipgc820.unto and
		tipgc520._index5 = {tipgc521.orno} and
		tipgc520.item inrange :item.f and :item.t and
		tipgc520.suno inrange :suno.f and :suno.t and
		tipgc520.podt inrange :podt.f and :podt.t and
		tipgc520.osta inrange :osta.f and :osta.t and
		tipgc520.buyr inrange :buyr.f and :buyr.t and
		tipgc520.cplb inrange :cplb.f and :cplb.t and
 		tiitm001.citg inrange :citg.f and :citg.t and
		tipgc520.osta <> tcorsa.actualized and
 		tipgc520.item refers to tiitm001 and
 		tipgc520.ccot refers to tppdm065 and
 		tipgc520.suno refers to tccom020 UNREF CLEAR and
 		tccom020.clan refers to tcmcs046 UNREF CLEAR and
 		tipgc520.ccur refers to tcmcs002 and
 		tipgc520.buyr refers to tbuyr UNREF CLEAR and
 		tipgc520.cplb refers to tcplb UNREF CLEAR
	order 	by tipgc520.ccot, tipgc521._index1
	selectdo
__________________
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
  #2  
Old 14th November 2008, 03:48
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
Did you run this with -profile flag on for some small sub-set of data?

I am not at my computer right now, but the first select looks familiar - must be from the Print Planned GRP Purchase Orders. It seems o.k. because nobody here has reported any performance issues with it. To locate the problem table, the only suggestion I can make is to start by striping out all the tables except tipgc520 and tipgc521 and then progressively add tables till performance breaks down - not very efficient.

The second select on the other hand, I would rewrite as follows:
- Outer select loop of tipgc520 and tipgc521
- within that select tipgc820, etc. and print
The reason for this is that tipgc820 will in general have several times more records than will be present in tipgc521, and breaking it down like this will help.

If you have to keep the second select as is, then try removing the order by. If this is a report session, you can always order by in the report fields. Doing a where clause on tipgc821._index2 and then ordering by a completely unrelated field make the select slow. I am not at my computer right now, and I do not remember the index for tipgc820, so I cannot recommend sorting by tipgc820._index2, but if that is a valid sort, that WILL speed up the select.

Last edited by shah_bs : 14th November 2008 at 04:48.
Reply With Quote
  #3  
Old 14th November 2008, 13:25
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
Mark,

Has your database been setup in that it automaticly performs a 'analyze tables' which is turned on by default if you install Oracle?
Check the field 'last_analyzed' in dba_tables for the tables that you are trying to read to check if that could cause the problem.

Regards,
Han
Reply With Quote
Sponsored Links
  #4  
Old 14th November 2008, 15:32
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Quote:
Originally Posted by shah_bs View Post
I am not at my computer right now, but the first select looks familiar - must be from the Print Planned GRP Purchase Orders. It seems o.k. because nobody here has reported any performance issues with it. To locate the problem table, the only suggestion I can make is to start by striping out all the tables except tipgc520 and tipgc521 and then progressively add tables till performance breaks down - not very efficient.

The second select on the other hand, I would rewrite as follows:
- Outer select loop of tipgc520 and tipgc521
- within that select tipgc820, etc. and print
The reason for this is that tipgc820 will in general have several times more records than will be present in tipgc521, and breaking it down like this will help.

If you have to keep the second select as is, then try removing the order by. If this is a report session, you can always order by in the report fields. Doing a where clause on tipgc821._index2 and then ordering by a completely unrelated field make the select slow. I am not at my computer right now, and I do not remember the index for tipgc820, so I cannot recommend sorting by tipgc820._index2, but if that is a valid sort, that WILL speed up the select.
Yep - you nailed the session and what I want to do. In both of the selects I want to strip out all the tables except the main tables. The problem is that it was decided to wait to see if the problem will reappear. I also want to nail down which query is causing the problem(still fix both), but it seems like it is the first query. I guess one of our fears is that other sessions will display the same type symptoms - but so far no problems. I will keep your suggestions in mind - I am sure they will eventually let me redesign it.

Han - I will check on that, but I thought it was mentioned that this was turned on. Will have to verify that.
__________________
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
  #5  
Old 14th November 2008, 18:53
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
It appears the session acted up again - removed the profile and it takes off. Now it appears another session - Print Pegging Data by Group Item tipgc0410m00b is acting up. So today it was profile off on the tables.

According to DBA update statistics is done nightly. One of the things I got:

execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)

Oracle 10g automatically gathers statistics on its tables when it feels that the statistics are not current enough. However, prior to going to production, I put this gather stats in place to run every night so that Baan will always have the most up to date statistics.
__________________
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.

Last edited by mark_h : 14th November 2008 at 19:16. Reason: add
Reply With Quote
  #6  
Old 14th November 2008, 20:46
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Mark,
Quote:
execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)
We use -
exec dbms_stats.gather_table_stats
Quote:
('BAAN','TZTTNT152707',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254',DEGREE=>4,CASCADE=> TRUE);
Also, the newer porting sets generate different Oracle execution plan and we found bad performance when the code had 'refers to' in query extension, once its replaced by ' = ' operator and properly joined with tables it improved.
Also refer to the note from Design Principles:
Quote:
Table references
Problem

Table references can be costly in performance. The Restricted (with counter) reference mode is very time consuming in situations where child records are added, updated, or deleted frequently.
Solution

The Lookup (restricted) reference mode must be used instead of the restricted (with counter) reference mode.
The only exceptions are if:
No index on the foreign key exists and
Parent records are almost never updated or deleted.
Example Currency code or country code.
You are on Baan-4, I am not sure how much is applicable for that version.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #7  
Old 19th November 2008, 00:02
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Quote:
Originally Posted by mark_h View Post
It appears the session acted up again - removed the profile and it takes off. Now it appears another session - Print Pegging Data by Group Item tipgc0410m00b is acting up. So today it was profile off on the tables.

According to DBA update statistics is done nightly. One of the things I got:

execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)

Oracle 10g automatically gathers statistics on its tables when it feels that the statistics are not current enough. However, prior to going to production, I put this gather stats in place to run every night so that Baan will always have the most up to date statistics.
I assume you applied all the parameters to the DB from the Baan important 10g parameters solution.

If that didn't fix it, then you may want to lock the statistics on some of the tables. Oracle 10g has some bugs with regard to statistics that can sometimes generate crazy execution plans.

dave
Reply With Quote
  #8  
Old 21st November 2008, 18:50
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
We have checked and double checked the parameters than baan sent us. All matched between test and production. Production had problem test did not. What we did discover is that some of the init parameters are different and are now trying to identify which one of those might be causing the problem.
__________________
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 21st November 2008, 22:52
rduncan10's Avatar
rduncan10 rduncan10 is offline
Senior Member
 
Join Date: Feb 2007
Location: Cambridge, ON Canada
Posts: 116
rduncan10 is on a distinguished road
Baan: IVc4 - DB: Oracle 10g - OS: SUSE Linux
Design Principles

I've been following Mark's threads on this because we are considering similar upgrades.

But I'm curious: Where can this "Design Principles" document be found.

Thanks.
Rob
Reply With Quote
  #10  
Old 21st November 2008, 22:59
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Rob,

I am not sure of the Baan-4 versions, but the programmer's manual and design principles help files are included in DFE installation and located in - C:\Program Files\Baan\Baan Windows\help

You might have to check with Baan support if they have a different one for Baan-4.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao

Last edited by NPRao : 21st November 2008 at 23:11.
Reply With Quote
  #11  
Old 22nd November 2008, 08:50
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
The documents they recommend are LN documents, but if you read closely somewhere in there it mentions it works for baan 4 also.
__________________
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
  #12  
Old 1st December 2008, 15:36
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
There is a difference between the command you give to the database to analyze the tables and the command that Oracle uses to refresh that data. If this is the/ a reason, make sure that this job is disabled.

I have no idea if any of the tables fluctuate in data e.g. during the weekend these are empty due to a process that is just run before the weekend and on Monday this table is really filled. This can explain why a certain query can change in performance behavior.

Hope this helps,
Dick
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #13  
Old 10th December 2008, 01:12
Cesar Lopez Cesar Lopez is offline
Member
 
Join Date: Jul 2006
Posts: 31
Cesar Lopez is on a distinguished road
Baan: ERP Baan V - DB: Oracle 9i - OS: HP-UX 11i
Shared Memory

did you try playing around with shared memory, sounds drastic but may help.
Reply With Quote
  #14  
Old 10th December 2008, 22:55
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,929
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Here is an update and I am not sure if I can explain it right. We have a server which sits in Minneapolis and sync's up with a server in Louisville. During the week the two servers could not stay in sync - blackberry exchange servers. As the week progressed more and more data was going across the wan. Someone found that this sync'ing was using up 90% of the band width between the sites. They shut this down and we no longer have any issues. Everything we tried before would not work(parameters, cache, memory sizing, etc.) - and it would usually get worse as the week went on. This syncronization was stopped last friday and we have not had any issues since then.

I have not been able to convince myself on how this could have been the problem - but I think it was it. Will have to wait another week. The DBA's mentioned that once a query locked up it looked like the client was not getting a response back from the application server. On the database server they would see that the query would lock up, get a brief burst of CPU, then it would start recycling. Maybe kind of like a re-transmit.

On a side note once this sync was turned off other sessions which were slower are now running back they way they were. Something else that makes me think this was the issue. I wish I had some data to back me up so I can prevent them from turning it back on.
__________________
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
  #15  
Old 17th December 2008, 02:12
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Gah - I knew it. it's always the network. ALWAYS! =)

Dave
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
The latest from the Oracle front Francesco General Discussion & Chat 0 15th February 2005 00:37
Oracle, PeopleSoft agree merger terms Flip_J General Discussion & Chat 1 13th December 2004 15:39
Question Regarding Data Conversion From Oracle 7.3.4 to MS-SQL 2000 terryw Operating Systems & Databases 0 5th November 2003 22:14
Baan on Oracle Guru for Hire Will travel OracleBaanGuru Jobs and Resumes 1 19th June 2002 16:57
Oracle Trigger Question Eddie Monster Operating Systems & Databases 11 26th April 2002 14:09


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


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