Of course there aren't any trace files that are relevant, because the workload (as you said) is caused by concurrent managers. You would need to look at the .out and .log files for the concurrent requests. But anyway, I've told you what to do: adjust the number of processes each concurrent manager can launch, and assign them to different workshifts.
Thank you very much for your reply - but we need to understand "which SQL's were causing high memory" - then we can co-relate the modules and assign those concurrent managers to a different workshift .. it cant be done in trial / error fashion ... Just wanted some systematic approach here ...
Some times we do find tracefiles related to process ids .. but its strange i didnt find anything .... very unfortunate.
I am posting the PGA Stats : - Please advise if you find anything unusual here ...
PGA Aggr Target Stats
• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control
PGA Aggr Target(M) Auto PGA Target(M) PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem %Auto W/A Mem %Man W/A Mem Global Mem Bound(K)
B 2,000 1,638 316.88 1.45 0.46 100.00 0.00 204,800
E 2,000 1,002 2,408.62 1,316.59 54.66 100.00 0.00 204,800