I have an issue with PPM, where the System Performance Page times out after 5 min, sometimes data is retrieved and page get displayed.
Changers made on the env:
- Keepalive setting is in config.xml file- <PS_HOME>/webserv/<DOMAIN_NAME>/config
keep alive sec to 300
https keep alive sec to 360
- archiving the data after 7 days. right now PSPMTRANSHIST and PSPMTRANSCURR tbs are huge(5 million rows),
and need that data for analysis purpose.
When ran a SQL trace on debug mode, came across the following SQL statement( which took 5 min to run),but when you run the SQL directly
through PL/SQL it takes about 2.5 min , and wondering if anyone knows how to find the PCode that runs behind the application,
and if there is any hints or tuning can be done to make the SQL run faster against Oracle 10g database through Peoplesoft application(PPM).
select count(*), avg(pm_metric_value2 + pm_metric_value3 + pm_metric_value4), sum((pm_metric_value2 + pm_metric_value3 + pm_metric_value4) * (pm_metric_value2 + pm_metric_value3 + pm_metric_value4)), avg(pm_trans_duration), sum(pm_trans_duration * pm_trans_duration)
where pm_trans_defn_set = 1
and pm_trans_defn_id = 400
and pm_mon_strt_dttm > TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS')
and pm_agentid in (80,81,82,83,84,85,91,92,93,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,58,59,60,61,62,63,64,65,66,67,68,69,76,77,78,79,57,70,71,72,73,74,75,94,86,87,88,89,90)