We have placed a call with Baan. We have been testing a SP upgrade for months and now we are having performance issues. We will be upgrading to SP24, porting set, client and to 10g. Lotsa fun! We are looking at solution #166049.

One specific session is tpcin9280m000(Create Billable Cost Report) - runs for days now for some of our ranges. We know for a fact this session is a patched session, but without source we can not really see if it is the session or the database. We did some more digging and seems like run times can be normal one time and then long the next time. Any other recommendations for things to look at???

__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.

10g gives you access to awr reports

sqlplus
@?/rdbms/admin/awrrpt


it should be running hourly by default with 10g, so just compare an hour when this was running.

you'll get the SQL that's gone to hell. (along with a lot of other info)


Then do an explain plan on that and see what the heck.

Most likely there's something in 166049 to address it.

Also - you DID updated statistics via dbms_stats.gather_schema_stats once you upgraded.. right? and , you did set the proper 10g parameters in your spfile and aren't sitting on old 9i/8i/7 parameters.... right?

Dave

Thanks Dave. Trying to find some answers from DBA and Sys Admins to your questions.

__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.

In 10g, we have Oracle set to run statistics as Oracle deems necessary, due to data changes on the tables. I can manually run dbms_stats.gather_schema_stats on the entire schema, however, Oracle recommends you don't do this in 10g. However, I just kicked it off.

While I kept the Baan recommended parameters in our init.ora file (which transforms to an spfile in 10g), I did get rid of all obsolete parameters. Oracle 10g will not let you start the database with obsolete parameters. However, Baan's note, 166049, recommends setting obsolete parameters in 10g, which you can't do.

Above are some answers I received. At this point I can not determine if it is Oracle, baan or the hardware. Seems like everything ran okay up until the last couple of weeks.

__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.

Just as starters I would ignore any idea of the HW being the reason.

Your system runs in principle, so even if you double all resources, you will just gain half the runtime, which is most likely still not good.

So experience shows, that as you describe it, Oracle is doing either an unlucky join or a full table scan or both combined. So I also suggest to follow Daves lead and look at the trace and the statistics.

The right fresh statistics will get the optimizer to choose a better execution plan and after this certain parameters of oracle kick in. Obviously there is no parameter called "ora_run_fast=true", but there are parameters in regards to star transformations and costs of certain joins and these might help you.

The trace might give indicators, what to search for. But any performance topic of this kind is a tough nut to crack.

Good luck,

Markus

__________________
Markus Schmitz
Senior Consultant
SOFTimpact Ltd.

Oracle's auto-statistics are frequently buggy. Some people have so many problems with them that they have to generate and then lock the statistics.

Any examples on that? I think 166049 was meant for 10g most of the parameters can be set, they just might take some finesse to get 'em in

The traces will tell the tail here.

Dave

Just wanted to be clear - since i've heard the argument about "obsolete" parameters in 10g before... they're certainly hidden, and you need to be careful when you set them, but it can be done and they may even help!

There is 1 parameter in 166049 that you can't set though -
The rest set just fine.



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 6 10:09:48 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> host cat params.sql
alter system set optimizer_index_caching=10 scope=spfile;
alter system set optimizer_index_caching=0 scope=spfile;
alter system set optimizer_index_cost_adj=10 scope=spfile;
alter system set "_always_anti_join"=nested_loops scope=spfile;
alter system set "_always_semi_join"=nested_loops scope=spfile;
alter system set "_b_tree_bitmap"=FALSE scope=spfile;
alter system set "_hash_join_enabled"=FALSE scope=spfile;
alter system set "_optim_peek_user_binds"=FALSE scope=spfile;
alter system set "_parallel_broadcast_enabled"=FALSE scope=spfile;
alter system set "_push_join_predicate"=FALSE scope=spfile;
alter system set "_push_join_union_view"=FALSE scope=spfile;
alter system set "_right_outer_hash_enable"=FALSE scope=spfile;
alter system set "_sort_elimination_cost_ratio"=5 scope=spfile;
alter system set "_unnest_subquery"=FALSE scope=spfile;



SQL> @params

System altered.


System altered.


System altered.


System altered.


System altered.

alter system set "_b_tree_bitmap"=FALSE scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM



System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 452984832 bytes
Fixed Size 2041304 bytes
Variable Size 369105448 bytes
Database Buffers 75497472 bytes
Redo Buffers 6340608 bytes
Database mounted.
Database opened.
SQL>

Hi all,
How should sys/system table -statistics be run for good statistics?
How to monitor them?
Should they be locked (some of them) like Infor perf. doc is suggesting?
How about histograms?

Here's how i'd do it :

8i - run analyze table / analyze index regularly
9i - run dbms_stats.gather_schema_stats regularly
10g - run dbms_stats once let 10g autogather from there on out.

If you set the parameters in my previous post you don't need to lock statistics. If you don't set them then you may need to lock them and play with stored outlines, etc.

Dave

Mark,

We are now 3 weeks later any new on this?

Dick

__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.