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???

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.

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.

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

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.

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.


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.

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


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.

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?

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,

We are now 3 weeks later any new on this?

Dick

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

Assuming that everything else is working fine (you do not mention any other sessions running slow), and assuming this refers to this other discussion on the same session ...
http://www.baanboard.com/baanboard/showthread.php?t=53425

I would suspect a bad or incorrect select statement.

In order to study the source code, my suggestion is for you to download ANY recent modification to the session tpcin9280m000(Create Billable Cost Report) [that contains the offending function refered to in the other discussion] from the BAAN support site into a secluded VRC so that you can analyze it. Most of the time, as you know, such patches come with the source code officially.