Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Performance & Tuning (http://www.baanboard.com/baanboard/forumdisplay.php?f=61)
-   -   How to trace SQL fetch times in ERP/LN (http://www.baanboard.com/baanboard/showthread.php?t=59289)

doornbos 29th September 2010 17:14

How to trace SQL fetch times in ERP/LN
 
Hello,

I've converted a report session from Baan4 to ERP/LN. But the session is very slow. So I would like to trace the sql fetch times to figure out what causes the delay. In Baan4 you can use TT_SQL_TRACE to trace fetch times of SQL queries. But in ERP/LN the trace output is very different and I can not see any fetch times. I use BAAN_SQL_TRACE=0200.

How do I trace the fetch times in ERP/LN?

Do you have any suggestions why the ERP/LN version of the session is so much slower (minutes in stead of seconds). I only modified sql/domains because of the database differences between Baan4 and ERP/LN.

With kindly regards,

Douwe.

bdittmar 29th September 2010 18:17

Infor Icon !
 
Quote:

Originally Posted by doornbos (Post 166355)
Hello,

I've converted a report session from Baan4 to ERP/LN. But the session is very slow. So I would like to trace the sql fetch times to figure out what causes the delay. In Baan4 you can use TT_SQL_TRACE to trace fetch times of SQL queries. But in ERP/LN the trace output is very different and I can not see any fetch times. I use BAAN_SQL_TRACE=0200.

How do I trace the fetch times in ERP/LN?

Do you have any suggestions why the ERP/LN version of the session is so much slower (minutes in stead of seconds). I only modified sql/domains because of the database differences between Baan4 and ERP/LN.

With kindly regards,

Douwe.

Hello Douwe,
right click on the Infor Icon in the taskbar.
choose Debugging/Tracing
choose Debug Bshell

fill the options in the four tabs for your requirement.

Regards

doornbos 30th September 2010 09:58

Thank you, Bernd. I already played with those options, but I never see sql fetch times in the log like these:

---
Fetch times of Query (QID : 12) in SqlBreak :
select ttdsk360.*
where
(ttdsk360.user = :logname$ or ttdsk360.user = :1)
order by ttdsk360.user desc, ttdsk360.cpac desc,
ttdsk360.rsst desc, ttdsk360.rsid desc

Nr Rows Fetched : 39
Fetch Time for 1st Row : 0.060 sec
Max Fetch Time : 0.060 sec
Average Fetch Time : 0.000 sec
Average Fetch Time (except Max) : 0.000 sec
---

What settings and/or bshell options should I use to get this information?

Kindly regards,

Douwe.

Han Brinkman 1st October 2010 11:19

Douwe,

use the callgraph option, it generates html files which are more easily to read/trace.

Use these options:
-set PROFILE_ALL=1 –set PROF_DIR=${BSE}/tmp -set PROF_RTIME=1 -set DB_ALWAYS_FLUSH=1

I don't know why your application is slow, this doesn't look normal behaviour to me. However the information supplied is not enough to give a straight answer. We should know more about what's happening.

Regards,
Han

doornbos 4th October 2010 16:18

Thank you, Han. I had a look at the html-output, which pointed me to the slow query. Further examination led me to a cut&paste typo in the sql (oops, sorry):

Code:

... and tccom100.cadr refers to tccom130.pstc...
which is apparently valid sql, but causes a very slow query. After correcting this error the performance is ok.

sushil 20th October 2010 07:41

Hi,

What is the Oracle version , you are using ?

vilmos 25th November 2010 22:11

I try this method with Baan5c and Infor 8.4 Client. I receive super usefull informations in trace file. All this infrastructure works well in Baan5c environment with Oracle 10g.

vilmos 25th November 2010 22:13

Can you tell me where can I find the detailed informations about debugging and tracing? What documents must I read?

dave_23 27th November 2010 00:28

This document is still the gold standard:
http://www.baanboard.com/baanboard/s...ead.php?t=7665

vilmos 27th November 2010 23:08

I am sorry I was not 100% precise with my question. The thing what I want to find is the same document/guide which explains how to tune Infor Baan 5c with Oracle10g on AIX. If some of this old documents exist or you have something usefull about performance tuning for this environment. Ok the document you show me is perfect but we use Baan 5c.

dave_23 29th November 2010 22:23

There's very little in that document (other than the naming) that's LN specific. most of the techniques and tools described within will work all way down to Baan 4 (if you have a new enough porting set, and by "new" i mean 4 or 5 years old...)

Dave


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


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