Gathering Statistics for Cost Based Optimizations

Oracle and Oracle Autonomous Databases only support cost-based optimization, which relies on accurate statistics to determine the optimal access path for a query. Gathering the appropriate statistics helps the optimizer, which will improve database performance. To gather statistics that optimize specifically for P6, use the custom P6 stored procedure gather_statistics() which is owned by the ADMUSER schema. Calling this procedure is the preferred method for gathering table statistics for P6 EPPM tables.

To call the stored procedure, run the following four lines in SQL Developer or SQLPlus:

var ret number
var retmsg varchar2(200)
exec gather_statistics(:ret, :retmsg);
select :retmsg from dual;
Note: The ORPM_STATS_GATHER.sql script located in the \Database\scripts\common folder of the P6 EPPM physical media is also supported and performs the same function.

Last Published Friday, November 29, 2024