Friday Oct 18, 2013

Rethinking Oracle Optimizer Statistics for P6 Part 2

In the previous post (Part 1), I tried to draw some key insights about the relationship between P6 and Oracle Optimizer Statistics.  The first is that average cardinality has the greatest impact on query optimization and that the particular queries generated by P6 are more likely to use this average during calculations. The second is that these are statistics that are unlikely to change greatly over the life of the application. Ultimately, our goal is to get the best query optimization possible.  Or is it?[Read More]

Tuesday Sep 17, 2013

Rethinking Oracle Optimizer Statistics for P6

This is the first in a series of posts about Oracle Optimizer Statistics and the P6 application. Over the past six months, I've done a lot of rethinking about the best process for maintaining optimizer statistics given the type of data in the PM database and the variety of queries coming from the application. I need to emphasize that this by no means constitutes general guidance with regard to Oracle Optimizer Statistics for any other applications. These recommendations are only applicable to the Primavera products and, in this case, only to P6 8.x. My hope is for a more nuanced approach to optimizer statistics to better achieve performance, scalability and stability goals.[Read More]

Tuesday May 28, 2013

AWR Data Extract

The Oracle RDBMS has an excellent repository of performance data that is collected automatically. It's called Automatic Workload Repository, or AWR for short. The AWR is a snapshot-based collector of database information. This includes not only the information from V$SYSSTAT, but also snapshots of other configuration information like database parameter values. This information is invaluable in diagnosing issues at the database-level. AWR information is typically exchanged via the AWR reports in HTML format (created with awrrpt.sql). While this can be useful, what would be better--and more flexible--is to have the raw data used to create the HTML report.[Read More]

Tuesday May 07, 2013

Exporting Schema Statistics

While most of us are familiar with the schema statistics used by the Cost-Based Optimizer (CBO), something not so well know is the ability to export/import these statistics using the DBMS_STATS package. This can be an invaluable aid in diagnosing query plan differences as these statistics are the primary information used by CBO. It is also a way to save and restore statistics in your own Primavera database.

[Read More]

Friday May 03, 2013

Optimizer Cost Parameters

There is nothing about query optimization that is ever black or white; just many, many shades of grey. This is the case with the two index-related database parameters: optimizer_index_cost_adj and optimizer_index_caching. The one thing I can say with confidence is that the default values are not ideal for Primavera P6 OLTP. If these parameters are set to the default values, chances are you will see some high Physical I/O (PIO) in the database.[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.


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


« April 2014