Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
18%
200 - 500 GB
29%
500 - 800 GB
3%
800 - 1200 GB
8%
1200 - 1500 GB
8%
1500 - 2000 GB
12%
> 2000 GB
23%
Total votes: 77

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 27th January 2004, 14:43
Markus Schmitz's Avatar
Markus Schmitz Markus Schmitz is offline
Guru
 
Join Date: Feb 2002
Location: Cyprus
Posts: 668
Markus Schmitz is on a distinguished road
Baan: Ivc4 - DB: Oracle8i/9i/10g - OS: HP-Ux11i
Performance issues after migrating to O9

Hi everybody,

we migrated the test system from oracle 8.0.6 to oracle 9i by doing export/import. We are workfing on BaanIVc4 (Portingset 06.06) and Hp-Ux.

After the migration, we have some strange performance problems. If anybody experienced the same or has an idea how to analyze it better, please let me know:

a) Some session (customer maint., item maint) have now a very long startup time, if started for the very first time.

b) One session (maintain purchase orders) takes up to 2 min, if you change from going next to previous, or the other way around.

c) Another session (maintain sales orders) shows the same as above, plus very long (again 2 min) startup times each time, if used by some specific users.


As the problems are session related, we are mainly looking in Baan for the source of the problem. For all the sessions, we have the current up to date version. We can not find a difference between the users to cause the different behaviour.

When we trace the session in Oracle, we find:

a) The time is spend mainly in one sql statement to fetch the main data row to be displayed.

b) The sql statement and execution plan is the same independend from the oracle version and user

c) In the problematic cases, oracle fetches up to 250000 records, in the good cases oracle fetches only 4.


We are absolutely clueless, what might cause this behaviour.

Any ideas, what we can test or look into?

Regards

Markus

P.S.: we set BAAN_ORACLE_PREFETCH = 0 according to Baan knowledge base
Reply With Quote
  #2  
Old 27th January 2004, 16:22
gguymer's Avatar
gguymer gguymer is offline
Unconfirmed User
 
Join Date: Aug 2001
Location: Lufkin, Texas
Posts: 194
gguymer is on a distinguished road
Baan: Baan IVc4 SP12 - DB: Oracle 8i, 9i - OS: AIX 4.3, 5.3
As Oracle 8 required more memory than 7.3 did, so it goes with Oracle 9. I would suggest running the STATSPACK package and examine the results. Without knowing for sure, I would venture a guess that the parameters controlling the memory allocation for Oracle are too low. And it may be that Oracle's memory demands are greater than your server can give.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.
Reply With Quote
  #3  
Old 27th January 2004, 19:57
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,107
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
Markus,

As Gilbert suggested you have to check the Oracle parameters.

Also refer to the thread -

Oracle 9i on AIX has poor performance

You can also try to execute a schema level analyze for all tables and indexes.

Can you post the $BSE/lib/defaults/db_resource settings here?
__________________
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
  #4  
Old 27th January 2004, 20:46
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
Hi Markus,

NPRao probably hit it on the head.. Have you done oracle statistics since the upgrade?

Also, do you have optimizer_max_permutations set in the init.ora?

Dave
Reply With Quote
  #5  
Old 28th January 2004, 10:04
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
Another thing to check

I heard that sometimes statistics on dictionary tables has been advised (by Oracle) for Oracle 9.

If you have set them try to remove them as I have heard about customers who used it and improved performance after removing these statistics.

Yeah, a new release and every DBA who thought that he knew everything has to start over again.

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
  #6  
Old 28th January 2004, 10:12
Markus Schmitz's Avatar
Markus Schmitz Markus Schmitz is offline
Guru
 
Join Date: Feb 2002
Location: Cyprus
Posts: 668
Markus Schmitz is on a distinguished road
Baan: Ivc4 - DB: Oracle8i/9i/10g - OS: HP-Ux11i
config

Hi everybody and thanks for your help!

We did a "analyze tables" over all tables after the import. Actually this was my first idea.

I found the baan_oracle_prefetch two days ago on the knowledgebase. If we do not set it at all or set it to 0, then we get the described behaviour. If we set it to 1, then the behaviour is even much worse.

In regards to the oracle_max_permutations, we had this parameter in Oracle8, but in Oracle9 it was deleted, because by default it is 2000 in O9. I will try on Thursday to set it to 1000 to see, wether it makes a difference. Today and tomorrow the system is not available, because of HW changes.

In general we are setting most parameters not in the db_resource, but a environment variables, which are set both in ASCII and GUI. Anyway here are the settings, which were allready used for O8:

export ORA_MAX_ARRAY_FETCH=5
export ORA_MAX_ARRAY_INSERT=5
export RDS_FULL=5
export SSTS_SET_ROWS=5
export BDB_MAX_SESSION_SCHEDULE=5
export DBSINIT="01"
export DS_TIMEOUT_DETECT=40
export LOCK_RETRY="30*100,30*500"
export BDB_MAX_SERVER_SCHEDULE=2
export ORA_COLUMN_FORMAT=7
export COMMIT_GLD212=10
export COMMIT_GLD215=10
export MAX_NO_TRANS=100
export DBCINIT="01"


For O9 we added:

export BAAN_ORACLE_PREFETCH=0

To be honest, there are a few variables, which I am not certain about and which did exist in the system, before I was responsible for it. These are DBCINIT, COMMIT_GLD212, COMMIT_GLD215, MAX_NO_TRANS. I will test also on thursday, how the system performs without them.

Any ideas to the settings would be appreciated,

Regards

Markus
Reply With Quote
  #7  
Old 28th January 2004, 10:14
Markus Schmitz's Avatar
Markus Schmitz Markus Schmitz is offline
Guru
 
Join Date: Feb 2002
Location: Cyprus
Posts: 668
Markus Schmitz is on a distinguished road
Baan: Ivc4 - DB: Oracle8i/9i/10g - OS: HP-Ux11i
Just a small addon:

We did analyze tables. But only for the Baan user tables!

according to my knowledge, this is the was to go and to leave system tables by themselves. Is this correct?

Regards

Markus
Reply With Quote
  #8  
Old 28th January 2004, 11:34
torwin torwin is offline
Member
 
Join Date: Mar 2002
Location: England
Posts: 39
torwin is on a distinguished road
Baan: IVc4 - DB: Oracle 8.1.7/9i - OS: Solaris 2.8/AIX 5.2/HP-UX11
Markus,

Which oracle driver are you using within Baan ( 7 or 8 ? ).

We were experiencing long wait times when going into session tisfc1500m000 - Display Production Planning. At the time we were running with the Oracle7 drivers ( due to other problems we had encountered ) when we switched to the Oracle 8 driver the wait times reduced dramatically.

Tim
Reply With Quote
  #9  
Old 28th January 2004, 11:46
Markus Schmitz's Avatar
Markus Schmitz Markus Schmitz is offline
Guru
 
Join Date: Feb 2002
Location: Cyprus
Posts: 668
Markus Schmitz is on a distinguished road
Baan: Ivc4 - DB: Oracle8i/9i/10g - OS: HP-Ux11i
We are using the Oracle8 driver.

We are running in hostmode and we want to avoid the overhead to go via SQL-Net. If I am not mistaken, to access a Oracle8 or 9 database with the Oracle7 driver, you have to use Sql-Net, correckt?
Reply With Quote
  #10  
Old 28th January 2004, 12:51
torwin torwin is offline
Member
 
Join Date: Mar 2002
Location: England
Posts: 39
torwin is on a distinguished road
Baan: IVc4 - DB: Oracle 8.1.7/9i - OS: Solaris 2.8/AIX 5.2/HP-UX11
Yes, we do run over SQLNET.
Reply With Quote
  #11  
Old 29th January 2004, 10:50
patrickmonks patrickmonks is offline
Junior Member
 
Join Date: Feb 2003
Location: South Africa
Posts: 26
patrickmonks is on a distinguished road
Baan: Triton 2.2, Baan IV b2, c4 - DB: Bisam, Oracle 7-8, Informix - OS: Aix,Solaris, NT, Unixware
Try to make your ora_storage very small
then run the maint item and see if faster...
Reply With Quote
Sponsored Links
  #12  
Old 29th January 2004, 14:01
Martin Martin is offline
Senior Member
 
Join Date: Aug 2001
Location: Germany
Posts: 135
Martin is on a distinguished road
Baan: IVc, ERP5c - DB: oracle 9i, 10g, 11g - OS: Windows, HP-UX, SLES8/9/10/11
@marcus

I had running an Oracle9 on HP-UX 11i on RP5470 Machine (5 Gig mem, 180 Baan User, 3-t variant, connect via sql-net,2 GBit NIC with load balancing and failover)
That was a migaration from Tbase.
Respone times very fast (like Tbase).
The Probelm on O9 is: install the Database with only local-managed tablespaces, automatic undo-management, automatic extend-management and automatic-space management. (very important !) Use only deticated server connections, no MTS.
Set the following parameter in ini.ora :
HPUX_SCHED_NOAGE=178

Hope this helps.
Martin
Reply With Quote
  #13  
Old 29th January 2004, 14:08
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
Try to do run some traces

After all good ideas maybe you just need to run some traces. My document explains how to do so.

As it are only a few sessions maybe the problem can be explained by looking at the queries of these traces.

About using the Oracle 7 driver: if you have ora_column_format set to 8 than you can't use the Oracle 7 driver.

About the ora_storage: This counts for the startup of sessions. Also take a look at ora_storage2

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
  #14  
Old 12th May 2004, 12:48
makiju's Avatar
makiju makiju is offline
Senior Member
 
Join Date: Oct 2001
Location: Finland
Posts: 120
makiju is on a distinguished road
Baan: IVc4, LN6.1 - DB: Oracle, Informix - OS: all
Is this solved Martin?

Hi !
We are facing exactly same situation (8.0.5 to 9.2.0.5).
How did you solve problems?
Item data browsing from record to record takes now 1 sec. if query goes to edm-tables (revision controlled item). There is much more buffer gets than with 8.0.5. I can't see any possibility that this could be solved by db_resource settings. My opinion is that optimizer works badly. If I delete statistics from these edm-tables it's little bit faster....

optimizer_features_enable is defined to 8.0.0 like in 8.0.5.

Also:
_index_join_enabled = FALSE #MP
_b_tree_bitmap_plans = FALSE #MP

tested without success.

Problem query looks like this (sqltrace):
Code:
select  tiedm100.eitm, tiedm100.indt, tiedm100.revi
                                ,tiedm101.cprj, tiedm101.item
                        from tiedm101, tiedm100, tiedm010, tiedm310
                        where tiedm101._index1 = {:i.cprj, :i.item}
                        and tiedm101.cmbb refers to tiedm100
                        and tiedm100.indt <= :i.date

                        and     (tiedm100.exdt > :i.date or tiedm100.exdt = 0)

                        and tiedm100.rele = tiedm.rele.yes
                        and tiedm101.eitm refers to tiedm010
                        and tiedm010.prdr = tcyesno.yes
                        and tiedm100.orno refers to tiedm310
                        and (( tiedm100.orno <> 0
                                and tiedm310.osta = tiedm.osta.actualised)
                        or      (tiedm100.orno = 0
                        and (( tiedm010.kitm = tckitm.manufacture
                              and ( tiedm101.ebcp = tcyesno.yes
                              or  
                              not exists ( 
                                select tiedm110.*
                                from tiedm010, tiedm110
                                where tiedm110._index1 = {tiedm101.eitm,
                                         tiedm101.revi}
                                and tiedm110.comp refers to tiedm010.eitm
                                and ( tiedm110.cmtp = tiedm.cmtp.item
                                or (tiedm110.cmtp = tiedm.cmtp.eitm 
                                and tiedm010.prdr = tcyesno.yes)))))
                        or  ( tiedm010.kitm = tckitm.purchase          
                              and  tiedm101.eicp = tcyesno.yes ))))    
                                               
                        order by tiedm101.cprj, tiedm101.item, 
                                tiedm100._index3 desc
                        as set with 1 rows

------------------------------------------------
Nr Rows Fetched                 :     0
Total Time                      :  0.980 sec
Reply With Quote
  #15  
Old 12th May 2004, 16:16
gguymer's Avatar
gguymer gguymer is offline
Unconfirmed User
 
Join Date: Aug 2001
Location: Lufkin, Texas
Posts: 194
gguymer is on a distinguished road
Baan: Baan IVc4 SP12 - DB: Oracle 8i, 9i - OS: AIX 4.3, 5.3
Use Oracle's STATSPACK utility. It will give a much better picture of what is going on at the database level. It will also give you advice on what Oracle settings to look at. Changing the db_resource settings won't give you nearly as much control as you will get from modifying the Oracle settings. The Oracle optimizer is only as good as the statistics that it has to use. Tables need to be analyzed for reliable statistics so that the optimizer can make good excution decisions. I know from past experience that the max permutations setting needed to be set for 1500 because of the optimizer had a habit of going down all 80,000 permutations before returning an excution plan. In those cases it would have taken less time to execute a less efficient plan than to wait for a more optimal one that it took 80,000 permutations to find.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.
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
Performance Tracing and Tuning Dikkie Dik Performance & Tuning 20 10th January 2013 17:10
Performance Issues after migrating Baan IVc4 to W2K and Oracle 8i dsplingaire Performance & Tuning 4 27th January 2003 16:22
performance decrease when migrating to Oracle toolslp Tools Development 5 30th November 2002 01:53
Item Issues by Period - by date staugner Tools Development 13 20th November 2002 19:57
Performance Issues with AFS calls baaniac AFS/DDC/OLE: Function servers 0 29th May 2002 12:41


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


©2001-2018 - Baanboard.com - Baanforums.com