Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th August 2008, 15:38
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
10g and Performance Issues
Baan: Other/Unknown
DB: Other/Unknown
OS: Other/Unknown
C/S: None/Unknown

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.
Reply With Quote
  #2  
Old 6th August 2008, 16:55
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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
Reply With Quote
Sponsored Links
  #3  
Old 6th August 2008, 17:25
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
Reply With Quote
  #4  
Old 6th August 2008, 18:17
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
Reply With Quote
  #5  
Old 6th August 2008, 18:44
Markus Schmitz's Avatar
Markus Schmitz Markus Schmitz is offline
Guru
 
Join Date: Feb 2002
Location: Cyprus
Posts: 668
Markus Schmitz is on a distinguished road
Baan: Ivc4 - DB: Oracle8i/9i/10g - OS: HP-Ux11i
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.
Reply With Quote
  #6  
Old 6th August 2008, 18:53
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Quote:
Originally Posted by mark_h View Post
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.

Quote:
Originally Posted by mark_h View Post
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

Quote:
Originally Posted by mark_h View Post
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
Reply With Quote
  #7  
Old 6th August 2008, 19:18
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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>
Reply With Quote
  #8  
Old 3rd September 2008, 08:52
makiju's Avatar
makiju makiju is offline
Senior Member
 
Join Date: Oct 2001
Location: Finland
Posts: 117
makiju is on a distinguished road
Baan: IVc4, LN6.1 - DB: Oracle, Informix - OS: all
stats

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?
Reply With Quote
  #9  
Old 3rd September 2008, 16:08
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
Quote:
Originally Posted by mark_h View Post
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
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #10  
Old 3rd September 2008, 16:41
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Quote:
Originally Posted by makiju View Post
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
Reply With Quote
  #11  
Old 4th September 2008, 16:25
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
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/s...ad.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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Oracle 10g and exchange imports davismit Operating Systems & Databases 12 10th July 2008 19:23
Documentation on Performance Monitoring baan_tools_IV Tools Administration & Installation 2 19th February 2008 04:15
Poor bdbpost data load in Oracle 10g pjohns Operating Systems & Databases 5 20th November 2007 18:32
Baan on Oracle 10g? bsyeven Operating Systems & Databases 16 24th February 2006 21:19
Oracle 10g with Baan IVc4 fouzan Operating Systems & Databases 7 28th December 2004 12:04


All times are GMT +2. The time now is 01:37.


©2001-2017 - Baanboard.com - Baanforums.com