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]

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]
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