Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Performance problem in PPM 9.1 SP2

Jump to solution

There have been several times at various levels of PPM and Oracle that we have been able to address database performance issues by increasing the SGA Target setting on the database. Recommended values for this parameter were included in the Oracle ADDM report.

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Performance problem in PPM 9.1 SP2

Jump to solution

Guys, 

 

Just some really usefull steps to improve performance:

 

• When did your client start noticing this performance degradation?
• Where do you see the delay? Logging in? PPM normal use? In a specific module or across all the application?
• Can you time up these delays and let me know how much time does PPM spends on these actions? Please compare with normal results as per your findings.
• Have you tried an index rebuild? If not, please engage your DBA on this and do it.
• What is the CPU capacity and the available RAM memory on the DB server?
• I suggest upgrading to PPM 9.12, it has big change compared to PPM 8.0 and we have solved some performance issues in PPM 9.12. Even if we solve the performance issue in 8.0, it does not mean that PPM has no performance after the customer has upgraded to PPM 9.12. Hence I suggest upgrading to PPM 9.12 first, and then we can provide performance tuning if PPM has performance issues. Perhaps the performance issue disappears after PPM is upgraded to 9.12.
• Please provide the Oracle AWR report.
• Oracle Version?
• Please take a look at the "Improving Advanced Searches" section in the Installation and Admin guide, this will also allow us to improve the system performance:
PPM Center users can search for requests based on custom fields defined in request types, request header types, and user data. Users can perform advanced searches to locate requests based on information that is defined as critical to business processes. As the number of requests logged increases, users performing advanced searches can experience slower performance. To improve performance during advanced searches, use the following guidelines:
* Specify additional request header fields in the advanced searches. Header fields are automatically indexed by PPM Center, and therefore yield faster returns.
* Add indexes to a limited number of detail fields, preferably fields that are commonly used in advanced searches. Take care not to add too many indexes, since this can affect the performance of inserts and updates to the database.
* Set the DEFAULT_REQUEST_SEARCH_ORDER_BY_ID server configuration parameter value to true to remove the sort order column on a request search. Record sorting slows performance.
* Change the value set for the REQUEST_SEARCH_RESULTS_MAX_ROWS server configuration parameter to restrict the maximum number of records retrieved.
* For portlet search queries, lower the value set for the PORTLET_MAX_ROWS_RETURNED server configuration parameter. For most portlets, 20 to 50 records is adequate. The default is 200.

• Another potential option to try in the Installation and Admin guide:
SORT_ELIMINATION_COST_RATIO
For certain restrictive (with good filters specified) and limited (returns few records) searches, PPM Center uses the FIRST_ROWS_N optimization mode. If a search such as this also uses SORT on one or more fields returned by the search, Oracle uses the INDEX on the sorted columns under the FIRST_ROW_N optimization, even if other indexes on supplied filters may yield to a better execution plan for a SQL statement. This often leads to a less desirable INDEX FULL SCAN on the index on sorted column.
Recommended Setting: Set the parameter value to 5. This directs Oracle to consider an execution plan with ORDER BY sort elimination, as long as the plan is no more expensive than five times the cost of the best-known plan (that uses sort).

• Also increasing the CPU power as recommended might help - have you checked the CPU usage on the PPM and database machines to see if they are currently overloaded. If the machines are not maxed out, increasing the CPUs is unlikely to have any effect.

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Performance problem in PPM 9.1 SP2

Jump to solution
Guys,

First of all investigate together with your oracle admin, especially if you also upgraded the oracle server to a newer version. You can also do the basic steps, such as index rebuild and statistics gathering. Another thing to check: the cursor sharing must be set to similar!

In one of our implementations, we performed the following after investigating several awr reports from both production and non production environments:

--remember to kudos people who helped solve your problem
0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Performance problem in PPM 9.1 SP2

Jump to solution
Guys,

First of all investigate together with your oracle admin, especially if you also upgraded the oracle server to a newer version. You can also do the basic steps, such as index rebuild and statistics gathering. Another thing to check: the cursor sharing must be set to similar!

In one of our implementations, we performed the following after investigating several awr reports from both production and non production environments:
1. Created a separate tablespace for the large indexes and moved all large indexes there;
2. Created a separate tablespace for the largest and most frequently updated tables and moved them there.
3. For the most intensive queries, we implemented custom indexes and, where applicable, we also created views to cache data, thus making use of the cursor sharing policy set to similar.

Hope the above helps.

Cheers
Alex

--remember to kudos people who helped solve your problem
Highlighted
Established Member..
Established Member..

Re: Performance problem in PPM 9.1 SP2

Jump to solution

Hi All,

Do you know how to rebuild index? in which table?

How to get the list of intensive PPM tables?

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.