Project and Portfolio Management Practitioners Forum

How to make use of dbms_stats ?

Super Contributor.

How to make use of dbms_stats ?



We want to get the statistics of PPM DB.


We got the related grant from the dba by running the fallowing command for our ppm user.


grant execute on dbms_stats to <PPM_User>


We have run the fallowing script,afterwards.


dbms_stats.gather_schema_stats (ownname => <PPM_User>,
cascade => TRUE,

So, what are we supposed to do next? Where can we see the results and the statistics?

I have come up with a several sys tables in the forums. So, Shell we ask the db for the system user password or is there anything else we can achieve with the current grants ?



Honored Contributor.

Re: How to make use of dbms_stats ?



There is a background service running every sunday that calculates statistics, and also you can do it via kconfig ui. How you can make sure it calculates statistics, im doint in toad count a table and check wheter the count is same as number of rows in stat/size; and controlling last analyzed date.



Honored Contributor.

Re: How to make use of dbms_stats ?



It depends on what kind of stats you want to gather.


There is a table introduced in PPM 9.1 sp2,  called  PPM_PERFORMANCE LOG. 


You can enable this table to store any ppm sql query taking X amount of time.


Let me know what is your PPM version and sp ?  so i can advice...



Oscar P


Super Contributor.

Re: How to make use of dbms_stats ?

Hello Oscar,


Thanks for your reply.


We are also using v 9.10 but when I check the PPM PERFORMANCE LOG table, it seemed to be empty.


Do I need to enable a service or parameter in server.conf in order to populate this table?



Acclaimed Contributor.

Re: How to make use of dbms_stats ?

If your database version is above 9i, then there is no need to specifically calculate the stats.

Starting from version 10g Oracle has inbiuld utility that by default calculates the stats and keep on improving the execution plan.

For more information you may contact you DBA team.

Going forward HP will remove script and Oracle Statastic Caluclation service, as they were intended for DB version 9i.
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Honored Contributor.

Re: How to make use of dbms_stats ?

If a page or portlet exceeds the threshold value an entry will be logged into the PPM_PERFORMANCE_LOG table.


PPM administrators can validate user complaints about performance and analyze data about specific users or pages in PPM


Server.conf settings needed:


com.kintana.core.server.UI_MONITOR_THRESHOLD=1   (this value in milliseconds)


com.kintana.core.server.PORTLET_MONITOR_THRESHOLD=1  (this value in milliseconds)


--NOTE:  If you want to store any query/page taking more than 5 seconds,  that will be 5000 miliseconds....


First, add the parameters to server.conf, run and restart.  


If you are in a 9.1 release prior to 9.1 sp4,  you need to also perform the next steps


you can enable the monitoring through the JMX console and has to be done for each node individually. 


For each node in your cluster


1)            go to http://<server>:<port>/admin-jmx  (username=admin password=mercitg)

2)            scroll to the bottom under ppm.monitor

3)            Under "name=ActionMonitorFilter,type=Action", set EnableMonitor = TRUE, and click apply changes

4)            Go back to the previous page.

5)            Under "name=ActionMonitorTrigger,type=Action", set PersistStat=TRUE and Threshold=1, and click apply change  (this will log the all pages that take more than 1ms to the database.  For our initial analysis we want everything to be logged)

6)            Go back to the previous page

7)            Under "name=PortletPerformanceMonitor,type=Portlet", set PersistStat=TRUE and Threshold=1, and click apply change

8)            In order to start the Portlet monitor you will have to click the "invoke" button under "Void  start()".   If the monitor is running the State=3 and StateString=Started.  If there are any problems you can destroy or stop then restart the monitor from this page.


You should start to see entries in the PPM_PERFORMANCE_LOG table in the PPM database schema if these are set properly.