Step By Step Approach To Increase the performance of the report.

·         Server Tuning

o   Disk – IO Stat

o   RAM -VMStat

o   CPU - Top

·         Instance Workload Tuning

o   Parameters – Optimizer Settings

o   MetaData –Optimizer Statistics Tuning

o   Cursor Management  - Cursor Tuning

·         Instance Object Tuning

o   Row Packing – PCTFree- PCT Used

o   Concurrency -Freelists

o   Table Row Location  - Sorted hash clusters

o   Caching – Objects into KEEP pool

·         SQL Statement Tuning

o   Rewrite SQL – Optimizer SQL with faster syntax formats.

§  Current Baseline From Production, and test environment.

§  Understand the current query completely and thoroughly.

§  Break the Data export query into 5/more section (We will focus section wise, each section we will go with below and that we will do today).

§  This is the order of the SQL execution, our analysis of data extract query must be in the order by mentioning current implementation and target implementation.

·         Where

·         Join/union

·         Order/group by

·         Select

§  List of solution identified.

·         Low

·         Medium

·         High

SQL Profiles – for static execution plans.

Process Performance - CA PPM - Process Engine Maintanence

For oracle run SHRINKS on NMS_MESSAGES daily

- often those tables get heavily fragmented due to bulk imports. when that happens, they grow in size consuming many blocks. That data is eventually deleted and normal usage does not need that many blocks.

• ALTER TABLE NMS_MESSAGES ENABLE ROW MOVEMENT
• ALTER TABLE NMS_MESSAGES SHRINK SPACE CASCADE

• ALTER TABLE NMS_MESSAGE_DELIVERY ENABLE ROW MOVEMENT

ALTER TABLE NMS_MESSAGE_DELIVERY SHRINK SPACE CASCADE

Gel Script Performance - CA PPM - Process Design

Create multiple generic process XOG users

• Process XOG users would be restricted to just the rights required for the their particular area (XOG PROJ, XOG RESOURCE, etc)
• Keep start condition, pre-condition and post condition expressions as simple as possible
• for an on -update start condition ensure actions the process takes result in the start condition evaluating to FALSE by the time the process finishes executing.
• This will prevent the process from firing over and over again. 
• Keep the total number of active process for an object minimal 
• use a single "create" process with branching rather than having multiple "create" processes.
• Use the delete process instance job to clean up old process 
• Some may need to be kept for auditing purposes. Identify processes that can be removed and delete them when they have completed via the job.
• Target frequently run processes, which are good candidates to delete as they typically carry almost no auditing benefit.
• During back ground services start up, the process engine loads, compiles, and caches all active processes. 
• This can cause an initial spike in database CPU usage. 
•  Do not use <gel:log> excessively,  Each log call results in a row in BPM_ERRORS
•  Do not use <gel:log> with in a loop with large numbers of iterations. This will affect the performance of your script.
• use a "debug" parameter on your gel script and wrap your calls to <gel:log> with a <core:if>.This will allow you to enable logging when you need it. 
• <core:if test="${debugFlag=1}">
• ⋛: log level ="debug">my debug log message </log>
• </core:if>

Gel Script Performance - CA PPM - Avoid Excessive use of the Core:Set

In complex GEL scripts that build XOG XML from SQL query output, avoid excessive use of the <core:set> tag for performance reasons. This tag use JAVA mbeans for variable storage. Multiple GEL scripts using frequent <core:set> calls will result in significant synchronization performance hit.Instead of using <core:set>, consider using a native Java Object such as a Hash map to store local variables insider tight loops.

Native Hashmap example:

<-Create a HashMap instance -->

<Core:new className="java.util.HashMap" var="myHash"/>

<-Place some values in this hashMap from a previous query -->


<core:invoke on="${myHash}" method="put" >

<core:arg value="projectid" >
<core:arg value="${myQuery.rows[0].ID}" />

</core:invoke >

<core:invoke on="${myHash}" method="put" >

<core:arg value="projectName" >
<core:arg value="${myQuery.rows[0].NAME}" />

</core:invoke >

<-Log this value -->

<gel:log level="info">Processing project ${myHash.get('NAME')}</gel:log>

Gel Script Performance - CA PPM - Util Sleep

Avoid using <util:sleep> in GEL script. This tag puts the thread executing the GEL Script to sleep. There are only 12 GEL threads allocated per process engine instance.  if all 12 are sleeping, other GEL steps will be unable to execute and processes will appear to hang.

SQL Performance - CA PPM - Union Queries

• UNION queries perform poorly as they can scan through the same data multiple times.
• Require any logic changes to be made in multiple locations 

SELECT 

INVI.CODE, 

INVI.NAME, 

SUM (FORECAST_COST) FORECAST_COST, 

SUM(BUDGET_COST) BUDGET_COST FROM 

(SELECT INVI.CODE, INVI.NAME, 

FP.TOTAL_COST,

FP.FORECAST_COST

0 BUDGET_COST FROM INV_INVESTMENTS INVI

JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND

INVI.OBJECT_CODE =FP.OBJECT_CODE 

WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="FORECAST"

UNION ALL

SELECT INVI.CODE, INVI.NAME, 

0 FORECAST_COST,

FP.TOTAL_COST BUDGET_COST FROM INV_INVESTMENTS INVI

JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND

INVI.OBJECT_CODE =FP.OBJECT_CODE 

WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="BUDGET")

WHERE INVI.CODE ="PR0J01793"

GROUP BY CODE,NAME

* Most UNION queries can easily replaced with logic

SELECT 

INVI.CODE, 

INVI.NAME,

SUM(CASE WHEN FP.PLAN_TYPE_CODE="FORECAST" THEN FP.TOTAL_COST END) FORECAST_COST

SUM(CASE WHEN FP.PLAN_TYPE_CODE="BUDGET" THEN FP.TOTAL_COST END) BUDGET_COST

FROM INVI.INVESTMENTS

JOIN FIN_PLANS FP  ON INVI.ID=FP.OBJECT_ID AND INVI.ODF_OBJECT_CODE=FP.OBJECT_CODE

WHERE FP.IS_PLAN_OF_RECORD=1 AND INVI.CODE="PR0J01793"

GROUP BY INVI.CODE, INVI.NAME

SQL Performance - CA PPM - Distinct vs Exists

• Both methods are commonly used to derive the unique values for dimension keys of portlets.
• EXISTS are preferable to DISTINCT.
• DISTINCT produces the entire result set (including duplicates),sorts, and then filters out duplicates 

SELECT DISTINCT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR JOIN PRTEAM TM ON SRMR.ID=TM.PRRESOURCEID 

Exists proceeds with fetching rows immediately after the sub-query condition has been satisfied the first time 

 SELECT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR 

WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE SRMR.ID=TM.PRRESOURCEID) 

SQL Performance - CA PPM - In vs Exists

IN is typically better when the inner query contains a small result set

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.ID (SELECT TM.PRESOURCEID FROM PRTEAM TM WHERE TM.PRPROJECTID IN (5000,5001)

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE TM.PRPROJECID IN(5000,5001) AND TM.PRRESOURCEID=SRMR.ID)

EXISTS is typically better when the inner query contains a large result set

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.ID IN (SELECT TM.PRRESOURCEID FROM PRTEAM TM)


SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE TM.PRRESOURCEID =SRMR.ID)

SQL Performance - CA PPM - OBS Filtering

• Seen many ways to filter based on OBS
• Many rely on complex logic, left joins to inline views, or multiple sub-queries.
• Using EXISTS and OBS_UNITS_FLAT_BY_MODE table provides an easy solution. 
• filter by unit only, unit and descendents, or units or ancestors

SELECT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR WHERE (OBS_ID IS NULL OR EXISTS

(SELECT 1 FROM OBS_UNITS_FLAT_BY_MODE OBSM

JOIN PRJ_OBS_ASSOCIATIONS OBSA 

ON OBSM.LINKED_UNIT_ID= OBSA.UNIT_ID AND OBSA.TABLE_NAME="SRM_RESOURCES" 

WHERE OBSM.UNIT_ID=OBS_ID

AND OBSM.UNIT_MODE=NVL(OBS_MODE,'OBS_UNIT_AND_CHILDREN')

AND OBSA.RECORD_ID=SRMR_ID))

This will give the good performance than PRJ_OBS tables. 

SQL Performance - CA PPM - Restrictive Conditions

 Begin queries with the most restrictive conditions

SELECT SRMR.FULL_NAME, AV.SLICE_DATE,AV.SLICE FROM SRM_RESOURCES SRMR
JOIN PRJ_BLB_SLICES AV ON SRMR.ID=AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID=7 WHERE SRMR.ID=1
ORDER BY SRMR.FULL_NAME, AV.SLICE_DATE

Instead of


SELECT SRMR.FULL_NAME, AV.SLICE_DATE,AV.SLICE FROM PRJ_BLB_SLICES AV
JOIN SRM_RESOURCES SRMR  ON SRMR.ID=AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID=7 WHERE SRMR.ID=1
ORDER BY SRMR.FULL_NAME, AV.SLICE_DATE


In terms performance i use first query, because slice table will have more data generally.

SQL Performance - CA PPM - Sub Queries

Sub Queries can be handy

Sub queries are good in some situation but not all situation

How many projects is a resource assigned to

SELECT SRMR.FULL_NAME,(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME


Instead of LEFT JOIN to a large inline view

SELECT SRMR.FULL_NAME, TM.TEAM_COUNT
FROM SRM_RESOURCES SRMR
LEFT JOIN (SELECT TM.PRRESOURCEID, COUNT(*) TEAM_COUNT
FROM PRTEAM TM
GROUP BY TM.PRRESOURCEID) TM ON SRMR.ID=TM.PRRESOURCEID
ORDER BY SRMR.FULL_NAME

Avoid substituting sub-queries for table joins

SELECT SRMR.FULL_NAME,
(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT,
(SELECT SUM(TM.PRALLOCSUM)/3600 FROM PRTEAM  TM WHERE TM.PRRESOURCEID=SRMR.ID) TOTAL_ALLOC,
(SELECT SUM(TM.HARD_SUM) /3600 FROM PRTEAM TM WHERE TM.PRRESOURCID =SRMR.ID) TOTAL_HALLOC
FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME

SQL Performance - CA PPM - Unnecessary Joins

Remove unnecessary tables from queries

Avoid falling back on common tables

• INV_INVESTMENTS
• SRM_RESOURCES

Query to find role allocation hours on a project

SELECT SUM(TM.PRALLOCSUM/3600) HOURS

FROM INV_INVESTMENTS INVI

JOIN PRTEAM TM ON INVI.ID=TM.PRPROJECTID

JOIN SRM_RESOURCES SRMR  ON TM.PRRESOURCEID=SRMR.ID

JOIN PRJ_RESOURCES PRJR ON SRMR.ID=PRJR.ID

WHERE INVI.ID=50000001 AND PRJR.PRISROLE=1

in this case not require to link with SRM_RESOURCES only PRJ_RESOURCES is enough.

SQL Performance - CA PPM - Using Table Aliases

Using Table Aliases

Improves readability of SQL

Use meaningful table aliases

SELECT A.ID FROM SRM_RESOURCES
JOIN ODF_CA_RESOURCE B ON A.ID=B.ID


Allows queries to be modified more easily

SELECT ID FROM SRM_RESOURCES
JOIN ODF_CA_RESOURCE ON SRM_RESOURCES.ID =ODF_CA_RESOURCE .ID


Help performance by eliminating the need for the datbase to search the tables for the referenced column