Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Performance & Tuning

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 13th March 2002, 12:50
moreau's Avatar
moreau moreau is offline
Junior Member
 
Join Date: Mar 2002
Location: france
Posts: 4
moreau is on a distinguished road
Baan: 4c2 - DB: Oracle 8.0.5 -
Question Oracle partitioning

We are analysing the use of Oracle8 partitioning,
we plan to experiment it on finance table TFGLD106.......(more than 5Gbytes on our system).The difficulties is to find the right axes for partition.
Can anybody help ?
Reply With Quote
  #2  
Old 13th March 2002, 14:12
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
pointers

Well, I have no practical experience with partitioning myself (yet) but I think the main question here would be to determine the type of partitioning you need:
  1. range partitioning
  2. hash partioning
  3. composite key partitioning
Do you want to partition because of performance issues, maintenance issues, etc?

The range - or - partition key based form would be the simplest but for that your data needs to be distributed evenly according the partition key value intervals that you choose.

e.g. for tfgld106 the Transaction Type (o$typ) would be an obvious choice to select as partition key I guess. But are your transactions evenly distributed across the transactions types?
Code:
  1  select distinct(t$otyp) "Trans. Type", count(t$otyp) "# of Records"
  2  from baan.ttfgld999255
  3* group by t$otyp
SQL> /
Trans. Type     # of Records
--------------- ------------
 AA                     3241
001                    37076
002                     5710
004                    42258
005                    51614
016                     3102
017                     5708
018                    17114
031                      306
032                     2774
037                     7346
040                     5540
ACP                     7293
ADT                        6
ALC                      506
APR                    11131
AUP                       84
AUR                       78
CZZ                      502
CVP                       92
CVR                       67
DZZ                    24665
JMR                       14
JNL                      827
JNM                     1657
LAP                       28
MSC                     2504
MSI                      696
OPB                      402
PAP                     7196
PCR                      590
PUR                     7073
SCO                      343
SCR                     2430
SLS                    42934
SZZ                     2341
VZZ                       10
XXX                        2

38 rows selected.

Above results would not point to an even distribution per transaction type. Options here I think would be to base your partition key on groups of transaction types, to use a different partition key or to use hash/composite key partioning.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #3  
Old 13th March 2002, 15:40
moreau's Avatar
moreau moreau is offline
Junior Member
 
Join Date: Mar 2002
Location: france
Posts: 4
moreau is on a distinguished road
Baan: 4c2 - DB: Oracle 8.0.5 -
Thanks for your quick answer.
The goal of partitioning for us is to improve performance (and optionnaly to help for maintenance).
The biggest performance problems we have are on sessions tfgld3206m000 (table tfgld106) and tfgld4201m000 (table tfgld410).
I don't think partitioning tfgld106 on Transaction Type (o$typ) would help the concerned session (and it's not an even distribution). As we are still using Oracle 8.0.5, the only type of partitioning available is range partitioning. So I thought about partitioning tfgld106 on vyer and vprd (year and period). What do you think about it?
I have no idea of how Baan access these tables for those sessions and it's the key of the problem (and of the partitioning). If you know that, I would be glad to share knowledge.
Thanks.
Reply With Quote
  #4  
Old 13th March 2002, 16:12
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
thoughts

I don't have much knowledge in the finance area so I can't really say how these sessions work. You are right that is the key to the problem. Assuming you would take t$vyer and t$vprd as elements of your partition key then I have the following concern:

how much of the processing that occurs in these sessions is happening in the same Tax Period and Year?

If this is the case then the (t$vyer, t$vprd) would be a bad partition key as I/O would still be concentrated on a small group of partitions?

I would like to hear the opinion of a finance expert on this.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
Sponsored Links
  #5  
Old 14th March 2002, 02:02
zaidlaz zaidlaz is offline
Member
 
Join Date: Sep 2001
Location: Singapore - been there done that?
Posts: 49
zaidlaz is on a distinguished road
Baan: B4C4 SP14 - DB: Oracle 9.2.0.4 - OS: Windows Adv Server SP4
creating gld106 & gld410 in it's own tablespace

Hi,

I'm not an expert in Finance.
I would like to know if your largest finance tables is in the same tablespace as all your other baan tables. I believe if you place these large tables in it's own tablespace without using oracle partitioning might give you a significant increase in performance. It allows you to monitor the growth of this tablespace and take necessary actions when due.
__________________
Best Wishes,
Zaid

"Out of intense complexities, intense simplicities emerge" -- Winston Churchill

Postings by me are of personal opinions and it does not represent any company or organisation

Last edited by zaidlaz : 14th March 2002 at 02:16.
Reply With Quote
  #6  
Old 14th March 2002, 09:48
moreau's Avatar
moreau moreau is offline
Junior Member
 
Join Date: Mar 2002
Location: france
Posts: 4
moreau is on a distinguished road
Baan: 4c2 - DB: Oracle 8.0.5 -
Re:Thoughts

To patvdv
I don't share your thoughts. For me, partitioning tfgld106 by Tax period and Year would help if the session work essentially on same periods and then on few partitions. I think it can improve performance as all others partitions would be eliminated by optimizer and also because there's no index on these columns.
Anyway, I will experiment on next weeks and actually, my point of view is only based on Oracle documentation ("Partition elimination can provide remarkable performance improvements, by using range and composite partitioning in combination with predicates in WHERE clauses. This key optimization intelligently removes from consideration whole subsets of partitions that need not be examined by queries or DML operations." )
To zaidlaz
the largest table is TFGLD106 and it's alone in its tablespace. All others tables are in several tablespace (one per company). I don't know if we can do better and tha's why we want to use partitioning.
Thanks to both of you.

Last edited by moreau : 14th March 2002 at 09:52.
Reply With Quote
  #7  
Old 14th March 2002, 10:40
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
experiments

Let us know the outcome of your experiments!
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #8  
Old 14th March 2002, 11:23
phoenix's Avatar
phoenix phoenix is offline
Member
 
Join Date: Aug 2001
Location: Germany
Posts: 40
phoenix is on a distinguished road
Baan: SSA ERP LN 6.1 - DB: Oracle - OS: Unix
Tuning for larger Tables

VERSION(S):
Oracle database

SITUATION IDENTIFIED:
Reconfiguring tables, using session ttaad4225m000 or bdbpre6.x and bdbpost6.x, a table drop can take three hours and more for a single table of a size of about 200 MB.

SITUATION DESCRIPTION:
The maximum number of extents for tables or indexes exceeds 20. It is recommended by ORACLE to have a maximum of 20 extents per index or table. Exceeding this number a considerable performance impact can be observed as a consequence. You find a script under point SOLUTION DESCRIPTION how to determine the number of extents for your tables and indexes. With the information from this script you can modify your ora_storage file as needed.

1. Recalculate your new next extents: take actual size multiplied by the actual number of extents
2. Increase the extent size according to the size of the specific table.

EXAMPLE:
Baan query shows # of extents = 112: BAANDB TTFGLD410100 Extents: 112
Current size of extent is: 64 KB

Calculation: 112 * 64 KB = 7186 KB

Old ora_storage:
*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 64K Next 64K)
*:*:I:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3 storage(initial 64K Next 64K)


Changes for new ora_storage (place the change before the * lines or they are not read):

tfgld410:100:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 7186K Next 10M maxextents unlimited)

*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 64K Next 64K)
*:*:I:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3 storage(initial 64K Next 64K)


Be aware that the change in the ora_storage only takes effect when a table is newly created. In that moment the ora_storage is read. Nevertheless, it might be useful for your system to change your ora_storage only for the Reconfigure tables and have another ora_storage for the productive environment.

N.B.:
The examples and indicated numbers are only meant for demonstration purpose. As all systems behave differently the actual values for your respective system(s) need to be verified on that respective system.

SOLUTION DESCRIPTION:

Create the following SQL statement in Oracle:
SQL>
select owner, segment_name, segment_type,
count (extent_id), sum (blocks)
from dba_extents
where owner not in ('SYS, 'SYSTEM')
group by owner, segment_name, segment_type
having count (extent_id) > 20 ;
Reply With Quote
  #9  
Old 14th March 2002, 12:14
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
Extent

The extent issue is not a problem anymore in Oracle8
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #10  
Old 14th March 2002, 13:21
Jeyaseelan's Avatar
Jeyaseelan Jeyaseelan is offline
Member
 
Join Date: Oct 2001
Location: Mumbai, India
Posts: 88
Jeyaseelan is on a distinguished road
Baan: iBaaN - DB: All - OS: SUN SOLARIS, Win
Smile oracle clustering

Oracle Database Clustering will help to resolve the performance related issues. Basically u can have different database clusters running for tfgld106 and tfgld410. The various clustering algorithms like nearest neighbour clustering, hierarchial clustering will help to increase performance rapidly.
Reply With Quote
  #11  
Old 5th April 2002, 20:29
gguymer's Avatar
gguymer gguymer is offline
Unconfirmed User
 
Join Date: Aug 2001
Location: Lufkin, Texas
Posts: 194
gguymer is on a distinguished road
Baan: Baan IVc4 SP12 - DB: Oracle 8i, 9i - OS: AIX 4.3, 5.3
I have given a lot of thought to partitioning the tfgld106 table too and feel that, for us, the partition would be on FPRD (Financial Period) and FYER (Financial Year). It tends to be a consistant number of records each period. I have moved the table into its own tablespace because of its size. I'm looking forward to trying it to see how it affects performance against this table, especially for queries run against it. Right now we have 44 periods loaded in it which adds up to 17 Gig worth of data not to mention the additional space that the indexes take up on top of that.
Reply With Quote
  #12  
Old 19th April 2002, 15:50
moreau's Avatar
moreau moreau is offline
Junior Member
 
Join Date: Mar 2002
Location: france
Posts: 4
moreau is on a distinguished road
Baan: 4c2 - DB: Oracle 8.0.5 -
I partitioned tfgld106 and tfgld410 on FPRD and FYER (financial period and year). Results are quite poor : in some case, performance is better (up to 10% faster) but it also can be slower (up to 20%) !
Then I decided to partition the indexes of tfgld106 and results were much more interesting : all sessions were faster (from 5% to 100%) !!!
The problem is that I had to add the partitioning columns (FPTRD and FYER) at the end of each index (because for a unique index, the partitioning key must be a subset of the index key).
My problem now is that Baan administrators say that indexes cannot be changed in Baan.
Is it true ? What sort of risks we run if we change indexes ?
Reply With Quote
  #13  
Old 15th February 2006, 18:40
Jeff Henslee Jeff Henslee is offline
Junior Member
 
Join Date: Dec 2001
Location: Wausau, WI
Posts: 9
Jeff Henslee is on a distinguished road
Baan: Baan IVc4 - DB: Oracle 8.1.7.4 - OS: Solaris 8
I was reading this older line of questinion on Oracle Partitioning. What was your final outcome from partitioning oracle tables? How did you address the partition with the multiple indexes defined in Baan in relation to your partitions? This part is a little 'fuzzy' for me. I'm experimenting with creating partitions on some of our larger tables.

Please advise.
__________________
Jeffrey C. Henslee (Chico)
Wausau Window and Wall Systems
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
Anyone have any BAAN/Oracle security links petefinnigan Operating Systems & Databases 3 9th September 2003 23:45
Error installing B4c4 with Oracle 8.1.7 on NT syllepage Tools Administration & Installation 2 22nd July 2002 14:52
Baan on Oracle Guru for Hire Will travel OracleBaanGuru Jobs and Resumes 1 19th June 2002 16:57
How to prevent passing of hints generated by Oracle Driver ? baaniac Performance & Tuning 6 24th May 2002 12:16
Oracle Partitioning pcarlson Operating Systems & Databases 5 17th August 2001 18:30


All times are GMT +2. The time now is 11:58.


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