When you are maintaining either forms or report the editor takes up to 2 minutes to open.

If you are working on reports it takes that same amount of time when moving between the report layouts.

Whilst the functionality of the editor is not effected it's extremely frustrating when a simple change can take anything up to 15 minutes to complete.

Apparently we have had this problem since system implementation and the company that installed Baan for us couldn't fix the problem. Since implementation we have gone through a porting set and SP upgrade. But still the problem persists.

Even when using the ASCII interface you get the problem.

Has anybody else ever encountered this problem?

If somebody can come up with a fix you would make me a very happy man!

Baan have suggested upgrading to the latest SP's and porting sets. We did this 6 months ago. As I've said previously this did not cure the problem then and I don't really want to introduce any additional problems at this stage.

Usng 4c4/HPUX11/Ora 805

Thanks

PJ

2 mins sounds *awfully* slow. Here it takes less than 1 sec. Have you tried to run the session with a trace on? See what the database activity is when you retrieve the layouts?

Hi Pat,

We had a Baan technical consultant in for two days who looked at this problem, amongst other things. He ran a trace and the results did not point to any particular issues.

Everybody seems to be at a loss on this one!

Regards

PJ

There must be a cause for it. It's only hard to tell what's the problem with only having the info from this board.

Can you please run a TT_SQL_TRACE=0200 while reproducing the error? Further more please send the contents of the ora_storage.

Did you do an upgrade from 7 to 8?

reproduce the problem again with ORAPROF=0 and give us the output. Please show us the contents of your init.ora file.

I am sure that we can solve that problem for you!

Kind regards,

Hello Han,

I have attached a zip file containing a trace, ora_storage and init.ora.

I have little Oracle knowledge, at the moment I leave this to our DBA. He's not in today so can you tell me where you set ORAPROF? I will probably wait for him to come in on Monday before I change this, but it's worth knowing where it is. All part of the learning curve etc.

We have made no Oracle upgrades to our production server but did upgrade from 805 to 806 on our test box. The editor problem exists on both servers.

Thanks for your help so far.

PJ

You can set it on the commandline:

ORAPROF=0 ba6.1

I will have a look at the zip file and look into it later.

Hello PJ,

1. Around ttadv333 you seem to have a problem, 23 seconds to retrieve one record is slow.
2. Is the startup of other sessions ok or are they also slow? How many seconds does it take to startup 'maintain items'?

Rgrds,

Han,

Other sessions open OK.

Maintain Items (tiitm0101m000) opened in 5 seconds.

Regards

PJ

From the times, and the number of accesses to the table, it looks like the table needs to be optimized. there is nothing wrong with the sessions, but the first record fetch is too long.
I would suggest a reorganziation of the indexes to speed up.

Hi Victor,

What you suggested is exactly what the Baan consultant did. He could see that the ttadv333 & ttadv334 tables were fragmented. So he re-created them, hence the first few lines in the ora_storage file being different

Thanks

PJ

PJ,

Try to find out why it's still slow, 23 seconds is not normal. Can your reproduce the error again with the environment variable ORA_TRACE=true ?
This will generate an trace file in the dump directory of your Oracle instance (ask your dba). Afterwards you have to make it readable with tkprof.

Regards,

I will try this Monday and get back to you.

Thanks to you all for your reactive support!

Have good weekends

Regards

PJ

PJ,

What worries me about your trace is the following:


================================================
Fetch times of Query (QID : 37) Trans 4 (ottadvformedi) in SqlClose :

SELECT ttadv333.* WHERE ttadv333._index1 < { :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11 } ORDER BY ttadv333.clan DESC, ttadv333.cpac DESC, ttadv333.vers DESC, ttadv333.rele DESC, ttadv333.cust DESC, ttadv333.cmod DESC, ttadv333.crep DESC, ttadv333.layt DESC, ttadv333.fldn DESC, ttadv333.rseq DESC, ttadv333.sequ DESC AS SET WITH 1000 ROWS
------------------------------------------------
Nr Rows Fetched : 1
Fetch Time for 1st Row : 22.820 sec
Total Time : 22.820 sec
================================================

I think Oracle is trying to fetch you a 1000 rows regardless of your request. Can you post your db_resource as well? Do you have proper statistics generated for your Baan tables? If your Oracle parameter OPTIMIZER_MODE is set to CHOOSE, then Oracle might be choosing a rule-based execution plan which is not good.

I did a trace on my test box and compared it to his tracelog. On the ttadv333,334 and 335 tables his trace shows a AS SET WITH 1000 ROWS and my tracefile shows AS SET WITH 3 ROWS. Could this be part of the problem? Maybe it is a tools patch that is needed -I do not know since I am far from a baan expert. I will leave that to you experts. I have attached my trace file in hopes it helps.


Good Luck!!!

Pat,

I've attached the db_resource file as requested.

What do you mean by "Do you have proper statistics generated for your Baan tables?"

With regards to the OPTIMIZER_MODE I will need to ask my DBA when he returns Monday.

Cheers

PJ

Wow PJ,

Your db_resource is set for batch processing not OLTP!

rds_full:1000
ora_max_array_insert:1000
ora_max_array_fetch:1000
ssts_set_rows:1000
oracle_client_home:/baan/bse/lib/ora/oracle_home

Please give it a try with these settings:


rds_full:2
ora_max_array_insert:10
ora_max_array_fetch:10
ssts_set_rows:2
oracle_client_home:/baan/bse/lib/ora/oracle_home

I would also add:

dbsinit:021
ora_init:0111000

Can you explain the 2 in the dbsinit setting?

From the BaanERP Oracle Driver Manual (U7076BUS):

This variable allows flags to be set to specify the optimizations to be used. At this time, legal values are 000 (not set), 001, 020, and 021. Other values are reserved and should not be used.

In both Level 1 and Level 2 mode, a flag of 00001 specifies that an optimistic approach should be used when checking for references in parent tables. The referenced row in the parent table is not locked, improving the overall concurrency. If this flag is not set, optimistic reference checking is not used.

In Level 2 mode, a flag of 00020 allows the driver to be less strict when selecting the iterative technique for query tuning. The driver applies the iterative method if the following conditions are met:
The query has an ORDER BY clause
There is an index on one of the query tables so that the index columns match the columns of the order by clause.
The columns in the combined column match with the columns of this index.
If the operator is > or >= then the index order must be ascending. If the operator is < or <= then the index order must be descending.
The where clause may contain an OR condition, however this OR may not be in the first nesting level of the where clause.
There is exactly one expression with on a combined column.

Not all of these conditions are actually necessary to be able to use iteration, nevertheless they must be met if the driver is to use iteration. With the driver resource dbsinit you can weaken or drop these unnecessary conditions.

Other values are reserved for future use and should not be used.
Multiple categories can be defined by adding the octal values. The value is compared bitwise to determine if a given category should be logged.
So dbsinit=021 means:
use optimistic reference checking
use iterative technique

For recent Baan installations, it is most recommended value.

I guess that I'm confused about the number of digits.

dbsinit:01 means optimistic and no iterative technique?

Does 21 mean the same as 021?

When you talk about new installs should c2 and c4 use the same dbsinit setting if c2 is oracle 7.3.4 and c4 is 8.1.7?

Can 01 be changed to 021 without causing a problem?

Does the porting set level have anything to do with it?

The number of digits does't really matter it's the octal value that counts:

* dbsinit:01 (no optimistic checking; iterative technique is level 2 only)

* dbsinit:021=21

* c2/c4 installations on the Oracle versions you mention should use dbsinit:021 on the condition that your Oracle driver level is 2

* changing the value shouldn't have any impact on your data; just the way the SQL queries work. Yet you might run into situations that could generate errors that didn't do so before. In general optimistic checking is safe to use though. (and recommendend to improve concurrency)

* portingset: depends on what the Oracle database drivers supports which comes with the portingset.

patvdv is absolutely correct about the db_resource file. It would be interesting to know when your db_resource file was last modified as many people will set the db_resource fileto very large values for bdbpre/post operations (this is a good idea). So perhaps the Baan consultant that was onsite saw the long query times, decided to optimize the table, did so with bdbpre/post, but forgot to set the db_resource file back.

You may see some degradation in your batch jobs when you set it to the smaller values. I suggest having two db_resource files -- one for batch and one for OLTP.

OK,

I've set my db_resource file to the values suggested by Pat and I've added the additional lines:

dbsinit:021
ora_init:0111000.

This doesn't appear to make any difference.

I am going to try the ORAPROF=0 tomorrow but can someone tell me what ORAPROF does?

Also Jim mentioned that I should have two db_resource files one for batch and one for OLTP. How would you switch use to the relevant resource file?

Thanks

PJ

PJ,

First run the same trace again from the last time, this time with the new db_resource, so that we can compare the SQL queries.

Secondly, you can switch between db_resource files by using the following ENV variables:

export USR_DBC_RES=lib/defaults/db_resource.001
export USR_DBS_RES=lib/defaults/db_resource.001

Both ENV variables take the pathnames relative to $BSE.

How's it going Pat?,

Thanks for your speedy reply!

I have attached a zip file containing an oraprof and new tracefile.

Cheers

PJ

Attachment!

Hi PJ,

I am doing fine, busy as ever :)

It seems the same queries are still bothering you:

----- Profiling value exceeded -----
<bsp><ottadvformedi>:2001-10-10[18:12:17.029]:
Time (multi_exec) : 24.270336 seconds
SQL statement:
SELECT /*+ FIRST_ROWS INDEX_DESC(a tttadv333000$idx1) */ a.t$adjr,a.t$cfbo,a.t$cfre,a.t$cful,a.t$clab,a.t$clan,a.t$cmod,a.t$colr,a.t$cpac,a.t$crep,a.t$cust,a.t$ficf,a.t$fldn,a.t$layt,a.t$lhgt,a.t$mult,a.t$plen,a.t$rele,a.t$rseq,a.t$sequ,a.t$spec,a.t$vers,a.t$xpos,a.t$ypos FROM baan.tttadv333000 a WHERE (a.t$clan <= :1 AND NOT(a.t$clan = :1 AND (a.t$cpac > :2 OR a.t$cpac = :2 AND (a.t$vers > :3 OR a.t$vers = :3 AND (a.t$rele > :4 OR a.t$rele = :4 AND (a.t$cust > :5 OR a.t$cust = :5 AND (a.t$cmod > :6 OR a.t$cmod = :6 AND (a.t$crep > :7 OR a.t$crep = :7 AND (a.t$layt > :8 OR a.t$layt = :8 AND (a.t$fldn > :9 OR a.t$fldn = :9 AND (a.t$rseq > :10 OR a.t$rseq = :10 AND (a.t$sequ >= :11)))))))))))) ORDER BY 6 DESC,9 DESC,22 DESC,18 DESC,11 DESC,7 DESC,10 DESC,14 DESC,13 DESC,19 DESC,20 DESC
-------------------------------------
================================================
Fetch times of Query (QID : 38) Trans 4 (ottadvformedi) in SqlClose :

SELECT ttadv333.* WHERE ttadv333._index1 < { :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11 } ORDER BY ttadv333.clan DESC, ttadv333.cpac DESC, ttadv333.vers DESC, ttadv333.rele DESC, ttadv333.cust DESC, ttadv333.cmod DESC, ttadv333.crep DESC, ttadv333.layt DESC, ttadv333.fldn DESC, ttadv333.rseq DESC, ttadv333.sequ DESC AS SET WITH 2 ROWS
------------------------------------------------
Nr Rows Fetched : 1
Fetch Time for 1st Row : 22.150 sec
Total Time : 22.150 sec
================================================

What is a bit odd to me - but I may be wrong - is that most of the queries that use a DESC order are considerably slower than their ASC order counterparts. That makes me wonder if the first ones are not being executed on a full table scan rather then using the indexes.

So I am going to repeat some of my earlier remarks:
check with your DBA whether you have up-to-date statistics on your tables (ANALYZE TABLE). He should know what I am talking about
check with your DBA the ratio of long (full) vs short table scans
run EXPLAIN PLAN on the troublesome queries to confirm if the index ($idx1) is indeed used at all. For this you would need - again - assistance of your DBA.