Thursday Jun 27, 2013

PMDB Block Size Choice

Choosing a block size for the P6 PMDB database is not a difficult task. In fact, taking the default of 8k is going to be just fine. Block size is one of those things that is always hotly debated. Everyone has their personal preference and can sight plenty of good reasons for their choice. To add to the confusion, Oracle supports multiple block sizes withing the same instance. So how to decide and what is the justification?[Read More]

Wednesday Apr 10, 2013

Collaborate 13 - Partitioning Benefits P6 OLTP

Attending conferences like Collaborate 13 gets me thinking deeply about the process and technologies we use in the P6 suite. If in can think of a single Oracle technology with the most bang-for-the-buck, it would be hands down Database Partitioning.

Partitioning is part of Oracle RDBMS Enterprise Edition. You might think that partitioning is only something applicable to a data warehouse. Not true. Of course it is practically a requirement for any significantly large warehouse (we support partitioning in the P6 Star Schema). But in a basic way partitioning sub-divides large tables into smaller, manageable chucks. This can be useful even in OLTP databases where there are very large tables with queries only looking at subsets of the rows.

A great example of this in P6 is the PROJWBS table. While this is a single table in the schema, it is really two logical tables. One logical table is very small, containing only the EPS nodes. The majority of the data comes from the individual WBS nodes. When querying just for WBS, this single table structure is no problem. But queries for EPS now contend with a very large table. Simply partitioning this table by EPS and WBS (using the PROJ_NODE_FLAG) can have a major impact on many queries. Another table were OLTP partitioning has significant impact is UDFVALUE because it contains many sub-tables for each subject areas.

There is much more to partitioning and we will look at these in more detail in the future.

About

An insider view of the technology behind the Primavera product suite.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today