Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Performance & Tuning

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 23rd March 2005, 14:45
lakoon's Avatar
lakoon lakoon is offline
Senior Member
 
Join Date: Jul 2003
Location: Switzerland
Posts: 179
lakoon is on a distinguished road
Baan: Triton 2.0, Baan IV b/c /ERP /ERP LN - DB: Oracle / tbase / MSSQL - OS: Solaris / HP /Windows
slow performance on tfgld418

Hello

We face the problem thet the booking of material receipts for project items is very slow. for standard items the booking performance is o.k.

We checked with the ORAPROF options and get for always the following result for project items:

------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:50:56.265]:
Time (parse) : 0.000000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------
------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:50:56.265]:
Time (multi_exec) : 0.000000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------
------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:52:21.280]:
Time (multi_fetch) : 85.015000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------

As the table has more then 10 million records we did not reorganize it yet.

Do you now a similar problem, and if, how did you solve it?

Thanks for your help

/lakoon
Reply With Quote
  #2  
Old 23rd March 2005, 15:18
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
It's a simple query using key fields in the table. You may want to run oracle's statspack at the same time to see what oracle is waiting on. I think you'll find
that its I/O. You can alleviate that somewhat by doing a reorg of the table as well as generating statistics on the table (using dbms_stats if you're 9i or higher)

Dave
Reply With Quote
  #3  
Old 23rd March 2005, 15:31
lakoon's Avatar
lakoon lakoon is offline
Senior Member
 
Join Date: Jul 2003
Location: Switzerland
Posts: 179
lakoon is on a distinguished road
Baan: Triton 2.0, Baan IV b/c /ERP /ERP LN - DB: Oracle / tbase / MSSQL - OS: Solaris / HP /Windows
Hello Dave

We will do the reorg. The oracle version we are running is 7.3.4....
How can we check in oracle, which tools for this version?

Thanks for your help

/lakoon
Reply With Quote
  #4  
Old 23rd March 2005, 16:23
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 Lakoon,

for SQL you can use utlbstat/utlestat (located in $ORACLE_HOME/rdbms/admin) I believe then you can upload the results
to www.oraperf.com to get a readable report.

for the statistics do
analyze table <table name> estimate statistics sample 35 percent;

Dave
Reply With Quote
  #5  
Old 23rd March 2005, 16:34
lakoon's Avatar
lakoon lakoon is offline
Senior Member
 
Join Date: Jul 2003
Location: Switzerland
Posts: 179
lakoon is on a distinguished road
Baan: Triton 2.0, Baan IV b/c /ERP /ERP LN - DB: Oracle / tbase / MSSQL - OS: Solaris / HP /Windows
Hello Dave

Thank you, we'll have a try on it. I will update with the resutls.

/lakoon
Reply With Quote
Sponsored Links
  #6  
Old 24th March 2005, 08:59
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
Just some other thoughts

Something else that could go wrong:
- index 1 is not available/corrupt. I don't think this is the problem as 85 seconds is than probably too less
- indexes are fragmented. Because 418 can have a lot of inserts at the end and deletes at the beginning, so indexes specialy in older versions can get imbalanced.

I think that a recreation of index 1 followed by an generating of statistics will help. As you use 7.3.4 I doubt if a reorg can be done when users try to access the table, so I advise to do this during a weekend.

If this doesn't help (sufficient) there are other things that can be investigated. These are the steps that can be done at that moment
- checking the excecution plan of the query
- checking the cost in Oracle
- change application query so that performance becomes better.

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
  #7  
Old 24th March 2005, 09:08
lakoon's Avatar
lakoon lakoon is offline
Senior Member
 
Join Date: Jul 2003
Location: Switzerland
Posts: 179
lakoon is on a distinguished road
Baan: Triton 2.0, Baan IV b/c /ERP /ERP LN - DB: Oracle / tbase / MSSQL - OS: Solaris / HP /Windows
Query Time goes up to 120 Seconds

Hello Dick

We have run the program serveral times with ORAPROF. Sometimes we got up to 120 seconds for the execution.

I have figured out that the table tfgld410 and tfgld417 are also out of balance to tfgld418 (269 records). We started to run otfcor0232 but have not found the difference yet.

How can we only recreate index 1?

/lakoon
Reply With Quote
  #8  
Old 24th March 2005, 10:16
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
To recreate index 1

drop index 1 of the table and recreate it by SQL*Plus.
As I don't have access at this moment to the Oracle docs nor the Baan DD of tfgld418 you have to wait for another or till tomorrow.

Kind regards,
Dick
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #9  
Old 14th April 2005, 13:13
tgk660 tgk660 is offline
Newbie
 
Join Date: Mar 2005
Posts: 1
tgk660 is on a distinguished road
Baan: Baan IVc4 - DB: Oracle - OS: WIN NT40
We are the company with the problem described by lakoon.
Last weekend we reorganized tfgld418. This solve our performance problem.
I think the problem was the corrupt index1.

Thomas
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 16:10
Losed tfgld418 records - Urgent Help Please mprakash Finance, Invoicing and Integration 13 25th September 2004 09:26
Use of performance boosters - errors... r_nagu Tools Administration & Installation 17 17th August 2004 17:24
Performance Indicator itconsultant DEM & Workflow 2 22nd October 2002 05:44


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


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