After upgrading to Oracle 8.1.7 from 7.3.4, we started having major performance problems. Queries that took minutes started taking hours both in the Baan and native Oracle environments. I had a suspicion that the Oracle optimizer was at the heart of this and it was. I'm not sure what those changes were, but they definitely had an impact on existing queries. After looking through Oracle Metalink issues regarding the 8i optimizer, I was made aware of an optimizer setting called "optimizer_features_enable", which by default, is set to the current Oracle version. We found that after setting it to "8.0.6", that our performance returned to normal for Baan and native Oracle. I know that the Baan/Oracle8 driver was compiled under version 8.0.6 and had heard that other Baan sites using Oracle 8.0.x were running OK, so that is why we tried that particular setting. Baan also recommends setting "optimzer_max_permutations" to 1500 and other Baan sites do too. Hope this helps.

Hadn't heard about the optimizer_features_enable. Something worth checking. The optimizer_max_permutations definitively has a major impact on some queries as it avoids the Oracle optimizer to go into too big a loop. Default number of allowed permutations is about 80,000!

Some of our sites are running 8.1.7 with the optimizer_features_enable set to default and without any major issues. Thanks for the info though!

Setting the value to 8.0.6. includes the following features (PARAMETER of init.ora) of the optimizer:

index fast full scans (9i) (FAST_FULL_SCAN_ENABLED)
bitmap access path for tables woth only btree indexes
(B_TREE_BITMAP_PLANS)
complex view merging (9i) (COMPLEX_VIEW_MERGING)
push join predicate (9i) (PUSH_JOIN_PREDICATE)

Parameter was introduces in 8.0.4.

See Oracle Metalink Note 68462.1

I'm tagging to this original thread because I think the optimizer may be part of my solution, but I have many more questions.

Here is the scoop.

NT 4.0 sp5, BaaN IVc2, Oracle 8.1.6 - upgraded April 01 from 7.3.4
Sessions started to run slow after the upgrade. Baan Tech support had me run :

analyze table {tablename} estimate statistics sample 20 percent;

for all of my tables, then I ran:

analyze index <tablename>$IDX1 compute statistics;

for what appears to be all indexes on all tables.

We also changed the value for max_permutations to 1500.

That seemed to fix the system for awhile. However, the system locked up in December, and ever since that reboot (and several after that), sessions are geting slower and slower. I have run the analyze commands agan, but that didn't seem to help this time.

Anyone have any ideas for me? Baan Tech says to upgrade Oracle to 8.1.7, check for fragmented tables and indexes, and upgrade my porting set.

What do you guys think? There appears to be an awful lot of credible knowledge here.

Thanks in advance.
Also, does anyone know where can I find documentation on how to tune Oracle/Baan?

Hi P-Matt,

There could be many reasons for your problem.

Firstly, how often do you run the analyse stats? This needs to be run at minimum once a week (!) and if time allows run at 100%. A good optimum value is 40% (any higher than 40% then Oracle chooses to 'compute' stats at 100% anyway). Even after as little as 3 days, table stats can quickly become obsolete (for busy tables) - Oracle will recognise that the stats are no longer valid and may choose not to use the stats at all and opt for potentially time consuming table scans.

Secondly its normal that over time, sessions appear to go slower. Data is constantly being appended to tables, thus inserts and selects on these tables will be slower over time. Even more so if the tables become fragmented. Lots of companies look into data warehousing or archiving to purge old data from large/frequently used tables.

Db-reorgs should be performed quite regularly on a Baan environment. At least once a year, even twice. A lots depends on how the Oracle environment has been setup also. Are all your Baan company tables in their own Oracle tablespace? And the indexes, are they in another tablespace? You can try and dump the data for one Baan company, delete the tables in Oracle, then reimport the dump - this is a quick and easy way of removing fragmentation, but you must first make sure the ora_storage file is setup correctly. It is preferable that each table is loaded into 1 Oracle extent - for this you need to estimate the size of each table, then set the appropriate initial extent size in ora_storage.

What are you using as the storage medium? (RAID etc.) Have you eliminated any disk problems? And which sessions are running slow?

Is the performance problem consistent? Or just at month end/beginning when load is high and users complaining the most? :)

Upgrading to 8.1.7, or upgrading the Porting Set are unlikely to solve the performance problem - but can help with other things.

From an Oracle point-of-view, have a DBA check out stats, and fragmentation. For Baan, check the settings in the ora_storage and db_resource files - plently of info elsewhere on this message board regarding these files.

Email or PM me if you need more info.

Good luck.

could be useful for performancel to recreate the indices for reorganize the index-tree. So do for each index : alter index indexname rebuild.
Could be done online.

Martin

To my opinion you should check if the index tree is not so nice anymore. That could be the case if you e.g. have archived. If you only add new records to tables the rebuild doesn't help. On metalink you can find queries in order to get an advice if index rebuild would help.

One of my customers has exported/imported their 30Gb Baan Oracle database to get ride of the fragmentation. The fragmentation is gone however it did give us a better performance. What I understood from Oracle is that since 7.3.4 Oracle handles the extend information better so having fragmentation is not that bad.

Han

Hi Han,

the problem is not the fragmentation. If you have a lot of inserts in a table, also a lot of indices are created, so the b-tree of the index is split in a lot of chunks. Rebuild the index, recreates the indextree so Oracle could read the indextree faster.
If a rebuild of an index is usefull you could check as following :

analyze index index_name validate structure
then query the index_stats view :
select (del_lf_rows/lf_rows)* 100 as index_usage from index_stats.
if index_usage > 20% then recreate the index.

Martin

The only serious performance problem we hit 7.3.4 to 8.1.6 was the damned BAAN pdaemon.

This seemed to suddenly start eating as much CPU
as it had done with early versions of 7.3.4 before
we told it to mellow out people don't print stuff
that frequently.

Thanks to Anil and Peter for reminding me about
max_permutations.

We went to great lengths to effect this with Oracle 7.3.4 (Yes Oracle know how) but it disappeared after the upgrade.

A quick session trace suggests pdaemon might benefit from max permutations. Hmm there is a pattern developing here.

I would like to thank everyone for their input.

James: Admittingly, I do not run the analyze often enough. I was told to run this only when the system is not being used - which when my batch jobs run so long, is never. Can I run the analyze while other sessions are running?

How many extents on an index is too many?
I did what the Baan Tech said and did a reorg on one of the tables (tfgld419300) - the index for idx1 went from 17 to 19.
via the sql:
select segment_name,count(*) from dba_extents
where segment_type='INDEX' having count(*) > 5
group by segment_name;

Martin: When I issue the sql you supplied, I see an index_usage of ~1.345% on this particular table. Others came back with 0.

I think you and Han have a point with the index-tree thing.

Whatever happended, happened when the system locked up that night. I have a test server that has a duplicate of this database, and it is running fine. I'm trying to compare things, but don't really know where to start.

What a mess huh?

I am continuing to read my performance tuning sections of manuals...

Thanks again for everyones input and help.

Solaris 2.6, Oracle 8.05, Baan5.0b, two tier

We have been (and are) suffering major performance issues for some time now. We tried just about every Oracle setting there is. Some seem to work for a short time, but eventually it all goes down the drain again.

Tired of fighting fires, we ended up throwing more CPU's and memory at our E10K. This gave us some air but Oracle is still not responding properly to Baan requests.

We are now experiencing a bunch of 107 errors, caused by time-outs in the communication between Baan and Oracle. Running the queries directly in Oracle works just fine. This leads me to supsicions towards the Baan Oracle driver.

Other performance nuisances are caused by Safari and Vertex. Vertex fragments the shared memory pool at an incredible rate. Constant flushing and pinning seems to have a positive effect on our system's performance.

I don't believe there is a silver bullet in these matters. It is a long and hard recursive road of observing, identifying and improving.

Perhaps we could all compare system settings. ha...ha...ha

P-matt: sure you can analyze online.

19 extents for a table is not that much. However if it went from 17 to 19 the reorg didn't help, did you modify the ora_storage before doing the reorg with help of the baan tools? You have to because otherwise it probably will not help.

Index usage seems all right to me.

You have to exclude things: is it a general problem, is it a problem of a particular session, if it is try to trace and check if the session uses a bad query.

Some thing to check which I remember by head of my latest performance issues:
Check your index optimization settings in the ora_storage: if you use level II it should be 0214, if not you can have strange performance problems.

The same applies if Baan has defined the references wrong meaning that a delete of a record results in a full table scan in another table in order to check if a reference exists. A bug we found in 5c a few months ago...

Run a SQL_TRACE of slow queries, run tkprof. A session that took 1 hour to show the results on the screen took only 30 seconds after I removed the statistics ... The analyze can have a bad effect also!

On batch jobs use ORA_MAX_ARRAY_xxxx, RDS_FULL settings. Try to run batch jobs in full host mode.

Francesco: of course quering the oracle db directly is more efficient than Baan does. Baan has to translate the baan sql to Oracle, read the Baan dd etc. That's the consequence of being flexible, i.e. being able to support Oracle/Informix etc.

I agree that most of the time there is not a golden tip to solve matters like these. However they can be solved in nearly all situations but it takes time.

Kind regards,
Han

I would give you same generell information about tuning ORACLE 8.1.x and BaaN:

It is very difficult to tune Oracle, because every Hardware constellation is different. So you need a software to view, what the database is done when Baan running. You could take Spotlight from Quest Software (very expensive) or take the new Oracle 9i Client. With the 9i Client a new Diagnostic Pack is delivered with a very god Performance Manager. You could get a lot of Reports, detailed Information for each SQl-Statement, Locks .....
This is the only way, to see what is happening in Oracle, when Baan is running.

Some tipps for Oracle 8.1.x :

Have you recreated the TEMP-Tablespace defined as "local managed"
Have you recreated Rollback-Tablespace defined as "local managed" ?
Have you more than one Rollbacktablespace and split the Rollback segments over this Tablespaces ?
In which order Rollback segments will be assign online in the initxxx.ora ? (RBS1,RBS2,RBS3 .... ) Better this way : 4 Tablespaces for Rollbacksegments, assign RBS1,RBS2 to TBS1, RBS3,RBS4 to TBS2, RBS5,RBS6 to TBS 3 and RBS7,RBS8 to TBS4.
Now change the Parameter in the initxxx.ora to :ROLLBACK_SEGMENTS=(RB1,RBS3,RBS5,RBS7,RBS2;RBS4,RBS6,RBS8).
Never, never put the controlfiles on the same Disk as the Datafiles, this will degree Performance.
Have you set the JAVA_POOL_SIZE to zero ?

Hpe this help a litte bit.

Martin

Francesco,

Since you suspect the settings for the Baan Oracle driver, can you please post your db_resource settings for comment.

I have to go right now (appt in 1 minute) but one thing I would look at would be the use of the async IO since you are at an old version of Solaris -- are you using aysnc IO?

-- Jim

Fransesco,
check ora_timeout (on unix ! ) to use {0,60,60,60,0} instead of {60,60,60,60,60} to get rid of 107 errors.
Beate

We thought this optimizer setting was the magic bullet, but it turns out that it wasn't. We were desperate to try anything. Since then we have stepped back and looked at the whole picture. We have noticed that our database server's paging has gone way up which indicates not enough memory. Past experiences related by other Baan/Oracle upgrades indicated that Oracle 8i consumes more memory. So, we are going to add more memory and see what happens. Lately, I have personally spent a great deal of time reseaching Oracle memory tuning. Most of you know that there are three areas that bear looking at, and they are the Library cache, Dictionary cache, Block Buffer cache hit ratios when dealing with efficient Oracle memory usage. There are several queries based on V$ views that can be used to evaluate these that Oracle and several others provide for this purpose. Another tool that is new to Oracle 8i is STATSPACK and if you haven't installed it, do so. There is a web page that Oracle pointed me to that takes the output from the STATPACK report script and creates another report that provides a higher level of interpretation and advice for you to look at. The nice thing about it is that it is free. The information is only as useful as your understanding of Oracle internal workings. It is located at: http://www.oraperf.com, and it works for either STATSPACK or UTLBSTAT/UTLESTAT outputs. One thing that Baan Tools support has recommended that we haven't tried yet is to revert back to the Baan/Oracle 7 driver. They have indicated that it has reduced memory usage and provided better performance for some customers. One question they asked us before they suggested it was, "Is your database using the US7ASCII characterset?" If yes, then try it, otherwise its not recommended. We are not sure if we want to try this just yet. I'll keep you posted on our progress.

Hi,

After reading thru this thread I would like to highlight that baan is really an I/O intensive application. Nevertheless your RAID controller card performance plays an important part in giving you the performance required. Not to mentioned how you organised your oracle datafiles. Placing your biggest table in a tablespaces of it's own might help too. For PC Servers the latest of the RAID supports the ULTRA 160s and I believe the ULTRA 320 or more in on the way...


Best Wishes,
Zaid

Hello everyone.

Does anyone know anyhing about Locally Managed Tablespaces?
Could this be my problem? (slow baan sessions)
When I issue a
select tablespace_name, extent_management
from dba_tablespaces;

Each tablespace has a value of 'Dictionary' in Extent_management.

- How do I set my tablespaces to Locally Managed?
- Do I want to?

Thanx.

Locally managed tablespace only in 8i & above
In Oracle 8.1.6, you may now migrate dictionary managed tablespaces to locally managed tablespaces, where in 8.1.5 this was not possible.

Advantages
=========
**Reduced recursive space management
**Reduced contention on data dictionary tables
**No rollback generated
**No coalescing required

example (assuming that the database block is 2k)
CREATE TABLESPACE tdsls040_1
DATAFILE 'file_tdsls040_1.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K

This statement creates a locally managed tablespace in which every extent is 128K and each bit in the bit map describes 64 blocks. In essence, if we assume a default database block size of 2K, and that each bit in the map represents one extent (128K), then each bit maps (128k UNIFORM SIZE / 2k ORACLE BLOCK SIZE) 64 oracle blocks.

Additional steps in Baan
-------------------------------
1) bdbpre table tdsls040

2) ** You need to drop the table tdsls040 from it's old tablespace

3) Edit for ora_storage
tdsls040:555:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE TDSLS040_1_DAT INITRANS 3

3) bdbpost table tdsls040 (The data of this table will be updated to the new tablespace TDSLS040_1)

*** PS: The above steps require you to be in a careful, clear state of mind. This is an example to give you an idea of how you can improve your access to large baan tables but it is not the ultimate & the best solution coz it all depends on the current state of your Baan system (both hardware & software)***


All the above will enhance your performance to a certain extent nevertheless it would be beneficial if you also analyse your I/O problem from the hardware point of view.


Best Wishes,
Zaid

You can convert existing TS as well by means of the dbms_space_admin package.

Han