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]

Friday Sep 13, 2013

P6 Session Audit Basics

Auditing in P6 is primarily an opt-in only function--you need to turn it on first. But one type of auditing is on-by-default: Session Auditing. Rows in the usession table are deleted by the application when the user logs off. The Usession Audit trigger captures an image of the usession table row during delete. The resulting rows in the usessaud table provide valuable perspective into application usage.[Read More]

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]

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]

Friday Apr 19, 2013

PX Services: Project Publication Arbiter Settings

Project Publication (PX) service is a process to provide near real-time operational reporting data while not continually churning over each and every row updated in P6. Much better to perform calculations on a batch of rows to maximize efficiency of the application server. The process we call The Arbiter facilitates this by queuing project to be recalculated in a timely and configurable fashion. The process and settings around Arbiter are a source of confusion and mystery.[Read More]

Friday Apr 12, 2013

Collaborate 13 - Stored Spread for Reporting: An Overview

As Collaborate 13 comes to an end, I have a chance to reflect on conversations and comments from the various Primavera sessions I attended. This is, by far, the most valuable part of attending these conferences for developers like myself. It gets you thinking in some new directions and, sometimes, refocuses you on old topics. My take-away is to look deeper at spreads as they related to operational reporting.[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.

Tuesday Apr 09, 2013

Collaborate 13 - Publish Services: A Mile-High View

This is my first post and it is coming, appropriately, from Collaborate 13 in Denver. Conferences always spawn new ideas for me and the idea for this blog came after OpenWorld 2012. I'm here to present a “Quick Start” guide to Reporting and Analytics. No easy task. These are truly enterprise products with many moving pieces. So I've decided to take a different approach. My presentation at Collaborate 13 is just the start of a longer term process of which this blog is a major part. This post will kick things off with the "mile high view" of the pieces making up Primavera Reporting and Analytics (PR&A) starting with Publish Project.[Read More]

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


« July 2016