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

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.

Markus,

As Gilbert suggested you have to check the Oracle parameters.

Also refer to the thread -

Oracle 9i on AIX has poor performance (http://www.baanboard.com/baanboard/showthread.php?s=&postid=54146#post54146)

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?

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

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

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

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

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

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?

Yes, we do run over SQLNET.

Try to make your ora_storage very small
then run the maint item and see if faster...

@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

After all good ideas maybe you just need to run some traces. My document (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665) 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 (http://www.baanboard.com/baanboard/showthread.php?s=&postid=5507#post5507)

Hope this helps,
Dick

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):

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

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.

This is taken from this one query...buffer gets value is very big:


STATSPACK SQL report for Hash Value: 2747255744 Module: ? @xxxxx (TNS V1-V3)

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
BAAN 3805179932 baan 1 9.2.0.5.0 NO xxxxx

Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
4 11-May-04 14:13:33 5 11-May-04 14:13:53 0.33

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 1,211,412 173,058.9 99.43
Disk Reads: 0 0.0 .00
Rows processed: 7 1.0
CPU Time(s/ms): 7 948.6
Elapsed Time(s/ms): 6 923.7
Sorts: 0 .0
Parse Calls: 0 .0
Invalidations: 0
Version count: 2
Sharable Mem(K): 110
Executions: 7

SQL Text
~~~~~~~~
SELECT /*+ FIRST_ROWS */ a.t$cprj,a.t$item,c.t$eitm,c.t$indt,c.t
$revi,c.t$orno FROM baan.ttiedm101771 a,baan.ttiedm010770 b,baan
.ttiedm100770 c,baan.ttiedm310771 e WHERE c.t$eitm (+) = a.t$eit
m AND c.t$revi (+) = a.t$revi AND b.t$eitm (+) = a.t$eitm AND e.
t$orno (+) = c.t$orno AND ((e.t$osta = :1 AND c.t$orno != :2) OR
(c.t$orno = :2 AND ((b.t$kitm = :3 AND (NOT EXISTS (SELECT d.t$
Refcntu FROM baan.ttiedm110770 d,baan.ttiedm010770 f WHERE ((f.t
$prdr = :4 AND d.t$cmtp = :3) OR d.t$cmtp = :4) AND f.t$eitm (+)
= d.t$comp AND d.t$eitm = a.t$eitm AND d.t$revi = a.t$revi) OR
a.t$ebcp = :4)) OR (b.t$kitm = :4 AND a.t$eicp = :4)))) AND (c.t
$exdt = :5 OR (c.t$exdt > :6)) AND a.t$cprj = :7 AND a.t$item =
:8 AND (c.t$indt <= :9) AND c.t$rele = :4 AND b.t$prdr = :4 ORDE
R BY 1,2,3 DESC,4 DESC,5 DESC

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

End of Report

Can you also post an explain plan?

Can this be formatted a bit? This is difficult too read and if you do it, we don't have to do it anymore :)

explain plan as txt-file

Do you still have info of the 8i environment?

explain plan from 8.0.5 attached.

**********
edit:
We have tested moving these five tables to 8.1.6 database and it's working normally (sqltrace total=0.04 sec. ). Seems like "sort" is important in the beginning of query.....but how to add this sort to 9.2.0.5.

CONCATENATION
0 NESTED LOOPS (OUTER)


I've never seen that in a Baan execution plan..

What does your init.ora look like? Also did you use statspack to gather the statistics ( as opposed to analyze table ..estimate statistics.. )
or did you just use it to generate the report?

Dave

We have tried to analyze several ways (dbms_stat also). Nothing seems to help.

Concatenate seems to be instead of "filter". Same query run by sqlplus is attached. Now it's showing also rows.
134957 INDEX FULL SCAN DESCENDING TTIEDM100770$IDX3 (object id 25422) seems to be the problem. How to solve this?


************
edit:
If we change hint FIRST_ROWS to ALL_ROWS it starts working ok.
We are waiting for Oracle-level solution...we don't want to change Baan code.

HI,

The CONCATENATION was prepared by the optimizer, if a order by .... desc doesn't work.

Please make sure, that all fields containing index3 are present in the select statement.

Try to replace the order by clause from index3 to the fields, containing index3.

If doesnt work proberly, set the following parameter in the init.ora and try again :
_or_expand_nvl_predicate =false
(please with the underscore at the beginning and only if you had set otimizer_features_enable >= 8.1.7).

martin

Ok: we need to force the query to start on index 1 of tiedem101. The Oracle 8.0.5 proves that it was possible. But during time the optimizer changed (some times good some toimes bad).

Maybe it helps when you change the line:and tiedm101.cmbb refers to tiedm100
into and tiedm101.cmbb refers to tiedm100 unrefer skip

This is allowed because the the relation between these 2 tables is mandatory because the and tiedm100.rele = tiedm.rele.yes
and tiedm101._index1 = {:i.cprj, :i.item}
forces a restriction on both tables.

Is this generating a different plan?

Kind regards,
Dick

How about ora_storage?
If I change just this idx3 by changing index_opt value from 0214 to something else:
nested, iterative, filter?

I'm not able to try right now.

I don't think this will help as the different ways of solving when having a > or >= over multiple fields like:
where table._index1 >= {:a, :b}

Kind regards,
Dick

Problem seems to solved by Oracle parameter and value:

_sort_elimination_cost_ratio=5


Metalink:
"<BUG:3455017> - Bad First_Rows Plan In 9204
This Bug is closed as Not a Bug."

Now I'm little bit worried of oraclebaan -process (server-side client process)memory consumption in overall. It use to be 3-7 per user with 8.0.5. Now it seems to be 50MB. Comparison done by using Glance process list res mem values. Any ideas?

Never look to RSS values as these show you the code pages that will be shared across processes. In this case you will see the memory usage for 1 user. The next user will use only a small additional amount of memory.

Instead of looking to the RSS you should look at private memory.

Hope this helps,
Dick

Hi Guys,

sorry for coming back on this too late, but here are my current discoveries:

a) For the case of performance issues with EDM: Baan is a patch for that

b) My origional problem was caused, because on two tables with 0 rows (!), indexes were missing.

Hope other people can benefit from this

regards

Markus

I got very good workaround from Baan. You can disable query hints by setting these environment variables to somewhere:

ST_OPTIMIZER_HINT=,MT_OPTIMIZER_HINT=

I put these settings into tabledef for table tdrpl100 (empty values) and my problem is solved. No more first_rows -hints. You can also try to put some "modern" values and try.

We had two serious problems with 9.2.0.5 and now they are fixed. Thank's for help!

Indeed this can help to solve your specific problem.

Two points of attention:
- These settings only work for 6.1 portingsets with Oracle.
- As you placed them in the tabledef. This can result in the startup of multiple database driver that can lead to unexpected results in other sessions.

So, test, test and test before setting this in production.

Kind regards,
Dick

Yes, there was one customized session which used this table and "count(*)" in session code. This was not working, but fixed somehow...

Hi Markus,
your Porting Set is 06.06, at Baan site it's Portingset 6.1c.07.01 min. Something that I don't know?
I will migrate from 8.1.6 to 9.2.x... HPUX. Your system is ok now?
Lima.

6.1c.06.06 is the first to support Oracle 9i..

but the 6.1c.07 is highly reccomended...

Dave

We upgraded to the latest Portingset, but this had nothing got to do with our problem.