-   Performance & Tuning (
-   -   Oracle partitioning (

moreau 13th March 2002 11:50

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 ?

patvdv 13th March 2002 13:12

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?

  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.

moreau 13th March 2002 14:40

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.

patvdv 13th March 2002 15:12

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.

zaidlaz 14th March 2002 01:02

creating gld106 & gld410 in it's own tablespace

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.

moreau 14th March 2002 08:48

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.

patvdv 14th March 2002 09:40

Let us know the outcome of your experiments!

phoenix 14th March 2002 10:23

Tuning for larger Tables
Oracle database

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.

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.

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.

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.


Create the following SQL statement in Oracle:
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 ;

patvdv 14th March 2002 11:14

The extent issue is not a problem anymore in Oracle8

Jeyaseelan 14th March 2002 12:21

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.

gguymer 5th April 2002 19:29

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.

moreau 19th April 2002 14:50

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 ?

Jeff Henslee 15th February 2006 17:40

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.

All times are GMT +2. The time now is 04:22.

©2001-2017 - -