I migrated the BAANIV C4, porting set 6.1c.07.01, from oracle 7.3.4.5 to oracle 9.2.0.3. All the sessions are between 50% and 60% more slow.

I need some idea to have a better performance ?

Hi vitorm,

Welcome to the board :)

Firstly, note that Oracle9 is slower than version 7.3.4. So some slight performance degradation would be expected anyway.

Next, you may also have moved from 32bit Oracle to 64bit - again, the 64bit version would be expected to perform slightly slower.

There are some new configuration parameters in versions 8 and 9, and some parameters have been renamed. Try posting your old and current init<SID>.ora file and maybe something needs changing in there.

Are you using the oracle8 driver now instead of the oracle7?

How was the migration done? Export/import? Or Oracle Migration utility?

James

Hi James,

Thanks for replay,

I´m using the oracle8 driver to access the Oracle9. In the migration i used bdbpre/bdbpost. My problem is 50% poor performance. I instalded oracle9 with 64 bits e already change several parameters of oracle9.

Are you using oracle9 with Baan ?

I know this thread is old but I had not much time before.

Have you created statistics on tables (analyze table or dbms_get_stats) after doing the post?

Have you checked the buffers etc on the new environment?

Normally an upgrade can give some performance drawback, but not 50-60% in general. It is advised to look into what is going on in more detail. SQL_TRACE is your best friend here.

Kind regards,
Dick

Hi vitrom ,

Could you tell me on which O.S. you're running Oracle ?

Thanks

Thanks,


After bdbpost a used dbms_stats.gather_table_stats to collect statistics.
The BaanIV, with ORA_LEVEL2, with Oracle8 Driver to access the ORACLE9, with portingset 6.1c.07.01, i had the result below, comparing with BaanIV, with ORA_LEVEL1, with oracle7 Driver to access ORACLE7, with portingset 6.1c.07.01:
Average time of routines= >20%
Average MEMORY utilization by user = > 41%
Average CPU TIME of routines= > 35%
Are you using Oracle9 with the BaanIV C4 ? What was your experience ?

My O.S. is HP-UX 11.00. Thanks,

So, you don't experience 50-60% degradation but 35%. Is that correct?

More CPU usage has been seen after upgrading. As you know each next version of every product consumes more memory, CPU etc. But 35% is more than average. Is this 35% for all users or only some processes?

Kind regards,
Dick

I was testeing, i used only 1 user and this is the average with this user running several sessions distincts.

Hi,

I don't know if you have access to metalink, but if you do ,
I've found an article about new parameters in Oracle 9, that Improve /decrease performance. Maybe you can take a look at it. If you don't have access, I can e-mail the document if you want to. Search in metalink for Doc id :

223299.1

Greetings

It´s possible that my problem was in the Oracle8 Driver, because it pass hints to select with this:

SELECT /*+ FIRST_ROWS index(a ttfgld410195$idx1) */ a.t$amnt,a.t$amth$1

Almost all commands use hints. I read de metalink document and already change several times that commands, but i imagine my problem is the hints. Do you agree ?

Thanks,

I have seen problems with hints (too many). But as O7 and O8 work very badly without hints I have to disagree in general.

When the Baan will cast the driver for Oracle9 ? some idea ? The Oracle9 already has 2 years. Is it possible to do with the driver doesn´t generate the hints ?

Thanks a lot,

The Oracle 8 driver should be named Oracle 8 and Oracle 9 driver. It contains the logic to connect to an O9 database as well and will use O9 specific options. Probably the driver will be renamed in a near future as it probably will also support O10.

Hints are still standard. Maybe in the future they can be enabled/ disabled by parameters.

Kind regards,
Dick

FIRST_ROWS seems to be the problem in overall with 9.2
Seems like first_rows optimizer behaviour has been changed in 9.2.0.3

"Starting 9203 there is a new Behaviour in the First_Rows optimizer"

Parameter "_sort_elimination_cost_ratio"=5
helped us with one session. I'm now thinking of downgrading to 9.2.0.1

Hi, Vitorm!
We had similar problems...
Try to use CBO (Cost-Based-Optimiser) on Oracle.

Although this thread is old, I felt compelled to add to the discussion for the sake of furthering the knowledge base. Dikkie Dik has already brought up the subject of statistics, but I thought I'd expand upon that idea.

Do make sure that you regularly analyze the tables/schema. For the CBO to perform its mission adequately, a recent table analysis must be done. A lack of statistics (or out of data ones) may cause the CBO to choose its path incorrectly (or switch out for RBO). An example early on here at my office was a tank in performance when the only tables analyzed were for outbound. Including the entire schema (all tables) brought performance back for the other sessions.

You could set up cron to run a script that contains (in part) something like this:


exec dbms_stats.gather_schema_stats(ownname =>'baan',-
estimate_percent => NULL, cascade => TRUE)


This would compute (as opposed to estimate) statistics on the entire Baan schema including indicies (cascade option). This can take a long time.

Most people would agree that at least estimating stats once a week is necessary. Many would further say that a weekly compute is good too. I, myself, run a compute on Sunday and estimate Wed-Sat. YMMV, so caveat scripter. You could also do this via DBMS_JOB, but (quick escape) I'll leave that as an exercise for the reader. ;)

Note further that RBO is considered by Oracle as "old school" and O9i R2 will be the last of the bunch to even offer RBO. Unless my notes are wrong, 10g will be CBO only (Oracle Note 189702.1)

Cheers,
FT

We use -

Oracle Analyze Schema

exec DBMS_UTILITY.analyze_schema('BAAN','ESTIMATE',ESTIMATE_PERCENT=>20,
method_opt=> 'FOR TABLE');
exec DBMS_UTILITY.analyze_schema('BAAN','ESTIMATE',ESTIMATE_PERCENT=>20,
method_opt=> 'FOR ALL INDEXES');

Table Level Analyzer

ANALYZE TABLE BAAN.T&1 ESTIMATE STATISTICS
SAMPLE 20 percent FOR table FOR ALL INDEXES
FOR ALL COLUMNS

NPRao is right for 8.i and before, FriarTuck is right for 9i and onwards. Please use the right tool in the right version.

Kind regards,
Dick

This would compute (as opposed to estimate) statistics on the entire Baan schema including indicies (cascade option). This can take a long time.
This really depends on the size of the databases.
Here is more info from Oracle-Metalink:


Bookmark Fixed font Go to End

Doc ID: Note:116178.1
Subject: Analyze Command - Compute Statistics vs. Estimate Statistics
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 27-JUL-2000
Last Revision Date: 28-APR-2003

PURPOSE -------
To give a brief explanation of the difference between using compute statistics and estimate statistics with the analyze command.
SCOPE & APPLICATION -------------------
Intended for DBA's, analysts, etc.
ANALYZE Command - Compute Statistics vs. Estimate Statistics
------------------------------------------------------------
COMPUTE STATISTICS When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.
ESTIMATE STATISTICS When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.


There is an init.ora parameter [B]OPTIMIZER_MAX_PRERMUTATIONS[B] and the default value is 80000. The combination of this value and the fact that the queries in question have a large number of joins, the parse time is extremely high. By setting this parameter to 1000 you can improve the execution time.

Also refer to the threads -

Baan Sessions Slow to Open (http://www.baanboard.com/baanboard/showthread.php?t=1053&highlight=permutation)
Oracle level 2 performance (http://www.baanboard.com/baanboard/showthread.php?t=247&highlight=permutation)

About optimizer_max_permutations:
- For Oracle 8 the default is unlimited (actual value 80.000 means unlimited)
- For Oracle 9 the default is 1500
- For Oracle 10 the parameter has become hidden and unsupported for a change. The default value there is 2000.

For Oracle 8 it was one of the major performance parameters to check when running Baan. For Oracle 9 and anwards the default is much better and so new parameters become important.

Kind regards,
Dick

Not sure if anyone is stil reading this, but just in case - there are documented bugs with dbms_stats in 9i, especially when computing stats on indexes. You may think it is computing stats, but if you check the stats manually you will see they are inaccurate. I would highly recommend that you check to see if you are experiencing this problem and if you are, then I recommend you use the old fashioned ANALYZE INDEX . . . COMPUTE STATISTICS for your indexes, OR apply the patch (3078144) from Oracle that fixes the problem. See Metalink note 270361.1 for more info.

A few other things: make sure you are using PGA_AGGREGATE_TARGET, LMT with ASSM, automatic UNDO and all the other new 9i features and have them tuned properly. If you continue to have performance problems, look at statspack and see where your bottleneck and what are the most poorly performing queries.

Good luck,
Brandon Allen